Be careful of MySQL Query Cache

Configuring MySQL server for high traffic site is very hard. You have to consider many things. One of the pitfall for unweary person like me is query_cache_size. I must confess I thought it would make my mysql server would perform better with query_cache_size. So I gave it 400M.

query_cache_limit = 400M

But I was dead wrong.

The problem was when ever you run INSERT/UPDATE sql, MySQL gets rid of all of the cache. It also locks the Table. This is one of the main features which limits query cache effectiveness.

If you see “Waiting for query cache lock” when you run SHOW ENGINE INNODB STATUS\G. Then you know you have to set it 0.

For more details http://www.percona.com/blog/2006/07/27/mysql-query-cache/

About Zakir Hyder

This entry was written by .

11. September 2014 by Zakir Hyder
Categories: Linux, MySql, Server Management | Tags: , , | Comments