The MySQL query cache is used to store data retrieved using SELECT statements. The advantage of the query cache is that subsequent and identical SELECT statement results are retrieved directly from the query cache, which resides in memory. So instead of executing the SELECT statements again and again, the stream of data is pulled from memory. The end result is lower CPU and disk hits, which can improve server load average and most importantly responsiveness.
The query cache functionality has enough smartness that once configured, it runs itself. MySQL will make sure to flush cached data if there’s an update that takes place. This ensures stale data is flushed out and replaced with fresh entries. There is, however, an extra step that is required. The cache tends to become fragmented over time. But nothing that cannot be addressed with a simple script (see below).
Stock MySQL configurations set the query cache to 32MB. This means that 32MB worth of server memory is pre-allocated to MySQL. If you have a relatively large database, you will most certainly gain from increasing that value. After configuring the query cache, it takes a while for MySQL to build the cache depending on how busy your MySQL server is.
Let’s get the query cache configured. Inside the [mysqld] section in the MySQL configuration file (ex: /etc/my.cnf), configure the options below. For a server with 8GB memory and a very busy database, set the cache to 1GB:
[mysqld]
query_cache_size = 1024M
Then restart MySQL for the new option to take effect. Run this command to verify that the new query cache value is in place and that the query cache feature is indeed enabled:
# mysql -u root -e “show variables like ‘%query%’”
+——————————+————+
| Variable_name | Value |
+——————————+————+
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1073741824 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+——————————+————+
If the have_query_cache option is set to YES, it means that the query cache is indeed up and running. query_cache_size represents the cache size in bytes. You can also monitor the mysqld process using top and you will see its RES value increase over time to above 1gb. This would be a good sign asb MySQL fills up the query cache with result sets from SELECT. There is another way to check on cache usage and health. Run the following command:
# mysql -u root -e “show status like ‘qc%’”
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| Qcache_free_blocks | 69776 |
| Qcache_free_memory | 756433256 |
| Qcache_hits | 9518567 |
| Qcache_inserts | 3878394 |
| Qcache_lowmem_prunes | 49447 |
| Qcache_not_cached | 173233 |
| Qcache_queries_in_cache | 122754 |
| Qcache_total_blocks | 315609 |
+————————-+———–+
If Qcache_free_memory stays high over a few days means that the query_cache_size value is set too high. A high Qcache_free_blocks means that memory is fragmented. So it is time to coalesce (make memory blocks contiguous) the region. This is done with the following command:
# mysql -u root -p -e “flush query cache”
Contrary to the command name, this does not flush the cache in the sense that all entries become invalid. It simply defragments the memory region that is assigned to the query cache. The above command should be put in a script that run every 6 hours or so. Create a root crontab entry as such:
* 6/* * * * /root/coalesce.sh
Where coalesce.sh is a shell script:
#!/bin/bash
mysql -u root -pyour_root_password -e "flush query cache";
The query cache can be further configured to match your database’s usage pattern. Be sure to read the MySQL documentation for the details. Hopefully, this post will come in handy for those that have unused memory and need to make the most out of their server. As our customer, we configure the query cache on your server from the get-go. Be sure to ask us how we can help with your online project!
UNIXy Crash Course cache, coalesce, high CPU, memory usage, MySQL, performance improvement, query cache, query_cache_size, speed up mysql