List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 10 2013 1:47am
Subject:RE: Slow Response -- What Does This Sound Like to You?
View as plain text  
1. MyISAM locks _tables_.  That can cause other connections to be blocked.  Solution:
switch to InnoDB.  Caution:  There are a few caveats when switching; see
https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/

2. As mentioned by Shawn, the Query Cache can be more trouble than it is worth.  However
90 seconds cannot be blamed on the QC.  Still, shrink it or turn it off:
* If frequently writing to tables, turn it off (type=OFF _and_ size=0)
* If less frequently, then decide which queries will benefit, add SQL_CACHE to them, set
type=DEMAND and size=50M (no larger).

3. Meanwhile, try to make that long query more efficient.  Can you show it to us, together
with SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN ?

> -----Original Message-----
> From: Bruce Ferrell [mailto:bferrell@stripped]
> Sent: Thursday, May 09, 2013 6:05 PM
> To: mysql@stripped
> Subject: Re: Slow Response -- What Does This Sound Like to You?
> 
> On 05/09/2013 03:25 PM, 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 InnoDBm to have been finished 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
> 
> One thing I'd look at to start is the error log, if enabled.  After
> that, I'd look at running mysqltuner to get a look at statistics before
> and after one of these events.  I know there are those who prefer the
> Percona toolkit, but those pull lots raw stats and offers little in
> terms of suggestions... Unless you wish to engage Percona.
> 
> Be aware, there are two versions of mysqltuner.  The one I use is found
> at http://mysqltuner.pl.  I know, it's old, but it at least runs.  The
> newer one doesn't seem to have been brought to completion.
> 
> You might want to enable the slow query option that logs queries that
> execute without indexes.  They can be real killers.  Reports that use
> views often cause this as views become complex joins under the hood
> that can easily miss your indexes resulting in full table scans.
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

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