Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have some problems with my server applications and it sometimes causes some tables to lock themselves and then I got thousands of queries waiting for the table being unlocked. But it won't happen. Is there any way to check what query could cause the lock?

share|improve this question
up vote 3 down vote accepted

I think show processlist; is the best way to start debugging this problem. show processlist shows which threads are running and their state.

You might use the Explain command as well, especially in your most common queries. This will show the execution plan for the queries you want. Possibly, some of the queries have no indexes or are not using them properly.

On the other hand, you're probably using MyISAM storage engine. If you don't need full text searches (or other MyISAM specific features), I'd recommend you to migrate to InnoDB, which provides row based locks instead of locking the entire table, among other features, which will probably improve the performance of your MySQL. Here's a nice comparison between MyISAM and InnoDB.

Possibly, you also need to tune some of your MySQL variables. I'd recommend you to tune key_buffer_size, which is the size of the buffer used for index blocks. Tweak this to at least 30% of your RAM or the re-indexing process will probably be too slow.

Last but not least, enable the slow query log. This will show which queries are taking too much time to run.

Hope this helps!

share|improve this answer
Thanks for your answer! Actually the problem is some bug with mysql and I'm going to report it. The problem is that queries can sometimes freeze on calculating the execution plan for the querie and those queries can't be killed. Anyway your answer will be useful for others so I'll accept it. Thank you so much! – Balon Apr 24 '11 at 21:56

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.