My blog doesn’t get a lot of traffic. It has never been referenced on Slashdot. No one Diggs me. Basically, folks who find my site do so via a search engine, more or less by accident, or are friends and family. The fact that my site has been here at kgadams.net for a number of years and I’ve made an effort to make sure I keep the search engines moderately happy means that I get *some* traffic: maybe ten thousand hits a month, more or less. Just so we are on the same page: many “moderately popular” websites get that many hits in an hour. Seriously popular sites get that many in a minute.
But despite the fact that my site isn’t generating massive influxes of visitors, I still care about performance. It bugs me when my main page takes more than a second or two to generate, or when my administration interface takes ten seconds to appear. So what do I do to improve responsiveness? Well, I visit another blog…
I found this post by Elliot Back, entitled “Why My Site is So Much Faster Than Yours”. In his post he gives several tips for improving WordPress performance (my site is based on WordPress), most of which I ignored as excessive for my little blog. Caching and PHP compilation is probably critical if you are getting a few hundred thousand visitors a day, but for me it would be adding some complexity to my site without much benefit.
I did find something applicable for my purposes, however, amongst Elliot’s recommendations. Query caching in MySQL, which is the database behind the WordPress application, seemed like just the thing for my needs. I opened my MySQL configuration file (/etc/my.cnf) and put the following settings in place:
query_cache_type=1
query_cache_size=64M
query_cache_limit=4M
The first line turns query caching on by default, the other two statements set the total size and individual query limits for the cache. The values I used are undoubtedly too large: I will be monitoring the cache utilization and will tune these values a bit over time. A quick way to see how the cache is working is to connect to mySql (E.G.: using the MySQL Query browser) and issue a command like:
show status like ‘Q%’
which will produce output something like this:
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 63330304 |
| Qcache_hits | 4211 |
| Qcache_inserts | 1887 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1226 |
| Qcache_queries_in_cache | 1158 |
| Qcache_total_blocks | 2396 |
| Questions | 10231 |
If you divide Qcache_hits by Questions, that will give you your cache hit percentage: in my case, it is currently (about an hour after I activated query caching) about 42%. As I mentioned above, my cache size seems far to large- I have 63 MB free out of 64 MB allocated, so I’m wasting a lot of space. I’ll let it run for a few days and see what the numbers look like, and then will tune the query_cache_size and query_cache_limit values. There is more information in the MySql documentation about query caching that is worth reading if you are going to play with these values.
What was the outcome of these changes? Well, the big one for me was administering my comments. Before making the change, getting a list of comments to administer was taking nearly 30 seconds. After the change, it seems to be taking about two seconds. That is a worthy change, in my books at least 🙂