1

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?

Balon
  • 145
  • 1
  • 8

1 Answers1

3

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!

Marco Ramos
  • 3,120
  • 23
  • 25
  • 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