List:General Discussion« Previous MessageNext Message »
From:shawn green Date:May 9 2013 11:36pm
Subject:Re: Slow Response -- What Does This Sound Like to You?
View as plain text  
Hello Eric,

On 5/9/2013 7:13 PM, Robinson, Eric wrote:
>> -----Original Message-----
>> From: Wm Mussatto [mailto:mussatto@stripped]
>> Sent: Thursday, May 09, 2013 3:50 PM
>> To: Robinson, Eric
>> Cc: Rick James; mysql@stripped
>> Subject: RE: Slow Response -- What Does This Sound Like to You?
>>
>> On Thu, May 9, 2013 15:25, Robinson, Eric wrote:
>>>>
>>>>> -----Original Message-----
>>>>> From: Robinson, Eric [mailto:eric.robinson@stripped]
>>>>> Sent: Thursday, May 09, 2013 1:58 PM
>>>>> To: mysql@stripped
>>>>> Subject: Slow Response -- What Does This Sound Like to You?
>>>>>
>>>>> We have a situation where users complain that the system
>>>> periodically
>>>>> freezes for 30-90 seconds. We check the slow query logs and
>>>> find that
>>>>> one user issued a complex query that did indeed take 30-90
>>>> seconds to
>>>>> complete. However, NO slow queries are recorded for the other 50
>>>>> users, before, during, or after the freeze. Note that
>> the complex
>>>>> query in question always shows: "Lock_time: 0".
>>>>>
>>>>> Q: What conditions could cause single query to lock up a
>>>> database for
>>>>> a while for all users (even though it shows "lock time:
>> 0")  but no
>>>>> other slow queries would show in the logs for any other
>>>> users who are
>>>>> hitting the database at the same time?
>>>>>
>>>>> OS: RHEL3 x64
>>>>> CPU: 8 x 2.9GHz Xeon
>>>>> RAM: 32GB
>>>>> Disk: RAID 5 (6 x 512GB SSD)
>>>>> MySQL: 5.0.95 x64
>>>>> Engine: MyISAM
>>>>>
>>>
>>>
>>>> MyISAM?  Or InnoDB?
>>>> Lock_time perhaps applies only to table locks on MyISAM.
>>>>
>>>> SHOW ENGINE InnoDB STATUS;
>>>> You may find some deadlocks.
>>>>
>>>> Is Replication involved?
>>>>
>>>> Anyone doing an ALTER?
>>>
>>>
>>>
>>> MyISAM, no replication involved, and nobody is altering the
>> database.
>>> This happens whenever people run certain reports.
>>>
>>>
>>> --Eric
>> This may be a dumb question, but have you verified that the
>> applications do not issue a "Lock TABLES ..."? Either the big
>> one or one of the others.
>>
>
> I have not verified this, but it should be easy to find out. Hopefully that is not
> the case as it is a canned application and we don't have access to the code.
>
> --Eric
>

Another option to keep in mind is the effect of a very large Query 
Cache. Each change to a table must invalidate every query (and their 
results) that derived from that table. For large caches, that can bring 
the server to a cold halt until the purge complete.

Try disabling it entirely and see how that affects performance or make 
it much smaller.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Slow Response -- What Does This Sound Like to You?Eric Robinson9 May
  • RE: Slow Response -- What Does This Sound Like to You?Rick James9 May
    • RE: Slow Response -- What Does This Sound Like to You?Eric Robinson9 May
      • RE: Slow Response -- What Does This Sound Like to You?Wm Mussatto9 May
        • RE: Slow Response -- What Does This Sound Like to You?Eric Robinson9 May
          • Re: Slow Response -- What Does This Sound Like to You?shawn green9 May
      • Re: Slow Response -- What Does This Sound Like to You?Bruce Ferrell10 May
        • RE: Slow Response -- What Does This Sound Like to You?Rick James10 May
          • RE: [>Suspected Spam<][Characteristics] RE: Slow Response -- What Does This Sound Like to You?Eric Robinson10 May
            • RE: Slow Response -- What Does This Sound Like to You?Rick James10 May
  • Re: Slow Response -- What Does This Sound Like to You?Johnny Withers9 May
  • Re: Slow Response -- What Does This Sound Like to You?Denis Jedig9 May
RE: Slow Response -- What Does This Sound Like to You?Vikas Shukla10 May