MySQL Query Cache
20 December 2010 by Shayne
As some would be aware we have been working on a multi-site installation of Joomla! that is running in excess of 130 sites for the Belgium Police. Needless to say performance is paramount hence we have spent a considerable amount of time researching and deploying the right server technologies. This has included the use of Nginx rather than Apache, APC for opcode caching and a little gem in MySQL called query_cache.
Query cache is something that to my knowledge only existed in MySQL until Oracle released 11g. It’s not the same as the caching historically found in Oracle and Microsoft SQL, both of which would cache the method/procedure but not the result set.
MySQL’s query cache is a must have configuration which is as simple to configure as to allocate it with some memory. To see if you’re already running it try this in command line…
mysql> show variables like 'query_cache_size';
+------------------------+---------------+
| Variable_name | Value |
+------------------------+---------------+
| query_cache_size | 0 |
+------------------------+---------------+
1 row in set (0.00 sec)
As the value is 0 we are not running query cache. To get it running we need to change the size (allocating it an amount of memory. For example to set the cache size to 250M we would could do it via the command line with…
mysql> set global query_cache_size=250000000;
Now we check again and find…
mysql> show variables like 'query_cache_size';
+------------------------+--------------------+
| Variable_name | Value |
+------------------------+--------------------+
| query_cache_size | 249999360 |
+------------------------+--------------------+
1 row in set (0.00 sec)
Once that has been set we can immediately see some results like so…
mysql> show status like 'qc%';
+------------------------------------+------------------+
| Variable_name | Value |
+------------------------------------+------------------+
| Qcache_free_blocks | 8 |
| Qcache_free_memory | 245510216 |
| Qcache_hits | 2428147 |
| Qcache_inserts | 1313269 |
| Qcache_lowmem_prunes | 273914 |
| Qcache_not_cached | 247762 |
| Qcache_queries_in_cache | 602 |
| Qcache_total_blocks | 1519 |
+------------------------------------+------------------+
8 rows in set (0.00 sec)
The important bits…
- Qcache_hits: number of times queries have been served from the cache.
- Qcache_inserts: number of queries that have been inserted into the cache.
- Qcache_lowmem_prunes: queries that have been removed from cache to provide space for new cached queries. If this is high it is an indication that you should increase your memory allocation.
- Qcache_not_cached: number of queries that were not cacheable.
In this example query_cache was originally running with a mere 20M of memory allocated hence the high number of prunes. More recently memory was increased to 250M with the result that prunes have pretty much stopped.
Also interesting is the number of queries unable to be cached. Whilst only SELECT queries are cached they can be made non-cacheable through the use of certain functions such as CURRENT_DATE. Additionally only 100% identical queries are served from the cache, this includes things such as case and use of spaces.
For bigger projects performance is key. With Nooku Server we are working on optimizing the Joomla 1.5 core queries to improve the query cache hit rate. If you are a Joomla!’s extension developer who want’s to play in the big league make sure to read up on how query cache works and test your queries to make sure they are cacheable. It really can make a huge difference in the performance of your extension.
Happy coding !
Update 20/12/2010 : The peeps at @joomlaworks twitted about their optimized configuration file for MySQL. You can find it here : http://snipt.net/fevangelou/optimised-mycnf-configuration/ Thanks !