On 05/09/2013 03:25 PM, Robinson, Eric wrote:
-----Original Message-----
From: Robinson, Eric [mailto:eric.robin...@psmnv.com]
Sent: Thursday, May 09, 2013 1:58 PM
To: mysql@lists.mysql.com
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
