Our mysql server is behaving strangely - doing a heavy query on one table will pretty much lock it, no other query will be responded to, even if it's a trivial one on completely unrelated table (like SELECT by primary key, INSERT, or even EXPLAIN) - it will just wait for the heavy one to finish. I'm really out of ideas what could be causing that - there's plenty of free threads, CPU is in idle/wait state, network is free, there's plenty of memory etc. Anybody has any ideas what to look for?
It's all InnoDB. There are many queries that "freeze" mysql, a SELECT+GROUP BY on one very big table is the simplest one.
taw
2009-02-20 10:22:06
Maybe your sort_buffer_size is too small? You can use mysqltuner ( http://mysqltuner.pl/ ) to check your config values.
Julien Tartarin
2009-02-20 10:59:15
A:
How much memory does MySQL have? My guess is that it's having to go to disk for temporary tables and that can be killer. How big is your innodb_buffer_pool_size in your my.cnf/my.ini file? By increasing that (which ups memory usage) smaller queries (and queries that need to do lots of sorting) won't have to hit disk.
The next time a query starts to do this to you, log onto MySQL, do "show full processlist" and record what's there. If you issue an EXPLAIN or a DESCRIBE on the query that's going slow, what does it is? Does it include the note that it's going to a temporary table?
When MySQL starts using temp tables, things can feel like they are dying, especially if you don't have a fast disk.
MBCook
2009-02-20 16:02:31