In my continued efforts to make the site run faster, I’ve set my sights on query caching on MySQL. I think query caching is turned on by default when you install MySQL. At least for me, I didn’t have to do anything. I notice it’s been working because whenever I run a query a second time, it’s always significantly faster.
SETTINGS
You can control settings for query caching in the MySQL config file, usually located at:
/etc/mysql/my.cnf
Just go to the “Query Cache Configuration” section. You should see three settings:
- query_cache_type
Turn it on/off. 1 is on; 0 is off.
- query_cache_limit
The amount of memory to allocate per query. Any query that takes up more than that amount will not be cached.
- query_cache_size
Total amount of memory to allocate for query caching. The more you use, the more queries that fit fall within the query_cache_limit will be cached.
If they’re not there, you can just add them yourself.
MY MODIFICATIONS
Here were my original settings:
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 16M
Here are my modified settings:
query_cache_type = 1
query_cache_limit = 4M
query_cache_size = 128M
Because Linode celebrated their anniversary recently by giving their users 40-50% more RAM on their VPS’s, I decided it’s time to put some of that extra memory to use. I upped the query_cache_limit to 4M and query_cache_size to 128M. I noticed a performance increase almost immediately. Now, larger queries are being cached (within 4MB), and I can cache a lot more queries with more memory (128MB).
PERFORMANCE IMPROVEMENTS
My server load dropped from an average of 3-5 to 1-3. You can check this by using the “uptime” command in linux.
You can see how your new settings are affecting mysql by logging into it and typing in the “show status” command:
mysql> SHOW STATUS LIKE ‘%qcache%’
You’ll get a bunch of values for these variables:
Qcache_free_blocks
Qcache_free_memory
Qcache_hits
Qcache_inserts
Qcache_lowmem_prunes
Qcache_not_cached
Qcache_queries_in_cache
Qcache_total_blocks
The two that you’re most interested in are Qcache_hits and Qcache_inserts. You want the ratio of Qcache_hits to Qcache_inserts to be as high as you can get. Before upping the numbers, my ratio was about 1 to 1. Afterwards, it was about 2 to 1, so for every query that’s cached, it’s getting hit twice. I’m not sure how great that is, but it’s better than before. And I notice the performance boost.
REFERENCE:
Turn on MySQL query cache to speed up query performance?
http://www.techiecorner.com/45/turn-on-mysql-query-cache-to-speed-up-mys...
(A good general overview)
MySQL's Query Cache
http://www.databasejournal.com/features/mysql/article.php/3110171/MySQLs...
(Discusses Qcache varirables in more detail)
MySQL Performance Blog: MySQL Query Cache
http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
(None of the details, but a good descriptive article with lots of readable text)
