List:General Discussion« Previous MessageNext Message »
From:Eric Robinson Date:May 9 2013 11:13pm
Subject:RE: Slow Response -- What Does This Sound Like to You?
View as plain text  
> -----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





Disclaimer - May 9, 2013 
This email and any files transmitted with it are confidential and intended solely for Wm
Mussatto,Rick James,mysql@stripped. If you are not the named addressee you should
not disseminate, distribute, copy or alter this email. Any views or opinions presented in
this email are solely those of the author and might not represent those of Physicians'
Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care
or Physician Select Management has taken reasonable precautions to ensure no viruses are
present in this email, the company cannot accept responsibility for any loss or damage
arising from the use of this email or attachments. 
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
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