Hello Eric,
On 5/9/2013 7:13 PM, Robinson, Eric wrote:
-----Original Message-----
From: Wm Mussatto [mailto:mussa...@csz.com]
Sent: Thursday, May 09, 2013 3:50 PM
To: Robinson, Eric
Cc: Rick James; mysql@lists.mysql.com
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.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 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
