MySql database query cache and WPMU
This page is part of the Caching for WPMU section, be sure to read there for more info !
This is probably the most straightforward cache to put in place for WPMU, and not the least efficient. There are no drawbacks whatsoever to using it, so do it.
The mysql query cache will store in memory result sets from SELECT type of queries, and serve those directly instead of actually running the query until the accessed table has been modified.
To turn on the mysql query cache, find your my.cnf file (generally in /etc/my.cnf or /etc/mysql/my.cnf ) and add the following two lines if they do not exist :
query_cache_limit = 2M
query_cache_size = 256M
This sets the total memory allocated to caching in mysql to 256M, while no result sets larger than 2M will be stored (very large result sets are better not cached, because they consume lots of memory, and you’re not sure that’s useful because the query might not be a very common one). Then restart mysql.
256M is just an indication of what can be used, but the optimal value might vary depending on your system and platform activity.
To see if your query cache needs to be made bigger, wait for a few hours (ideally a day) so that your cache gets filled, then run in command line :
mysql -Bse « show status like ‘Qcache_free_memory’ »
(you might need to add user / password information depending on how your mysql is setup).
This line will output how much memory is still free in your query cache. If it’s too low, raise the cache value, if it’s too high, lower it .