Mysql queries stuck in "sending data" state

Posted by MarkPW on Server Fault See other posts from Server Fault or by MarkPW
Published on 2011-02-26T06:12:43Z Indexed on 2011/02/26 7:26 UTC
Read the original article Hit count: 491

Filed under:
|
|

I'm running a Litespeed web server and a database server (2 x Clovertown 5335) with MySQL 5.1.52-log (running on Cent OS 4.5 and 4.6 32bit respectively). Last week, I upgraded from 5.0.51a-community-log and since then I've been having a problem whereby my database server's load starts increasing for no apparent reason.

Running SHOW PROCESSLIST; I see all of my SQL_CACHE queries that use a wildcard in WHERE, backing up and getting stuck in "sending data" state. However, other queries that use SQL_CACHE but no wildcard do not get caught up in this.

To get things going again, the first time it happens (after about 24 hours), I have to restart mysql. 4/5 times it will re-occur after about 20 minutes or so and this time and for subsequent occurrences it is not necessary to restart mysql. Simply stopping the web server for a few minutes will suffice while I allow the stuck queries to clear themselves up.

I had no such problem with the the previous mysql install.

What could be causing the issue and how do I resolve it?

Thanks

© Server Fault or respective owner

Related posts about mysql

Related posts about Performance