List:General Discussion« Previous MessageNext Message »
From:Vikas Shukla Date:May 10 2013 4:06am
Subject:RE: Slow Response -- What Does This Sound Like to You?
View as plain text  
Hi everybody,

I think we need to focus on three things:-

A) temp tables created on disk
B) table cache size
C) buffer sizes

If you find the number of temp tables created on disk is very large, please increase the
temp_table_size.

Enable the slow query log And check if sort buffer size and join buffer size needss to be
increased if multiple joins are used.

Also check whether the tables used in the slow queries do have index build on them or not.
This heavily impacts the performance. If not create index on frequently used tables.

Please try the above and let us know if resolved.

Regards 

Vikas shukla 

-----Original Message-----
From: "Rick James" <rjames@stripped>
Sent: ‎10-‎05-‎2013 07:24
To: "Bruce Ferrell" <bferrell@stripped>; "mysql@stripped"
<mysql@stripped>
Subject: RE: Slow Response -- What Does This Sound Like to You?

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


-- 
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