Regularly flushing the MySQL Query Cache

When we analyze our customers systems we see typically a high fragmentation of the query cache after a while. This leads to a less optimal use of the Query Cache than possible.

With the following Query you can see the values for your Query Cache:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 11328    |
| Qcache_free_memory      | 89442000 |
| Qcache_hits             | 6595644  |
| Qcache_inserts          | 1041831  |
| Qcache_lowmem_prunes    | 717896   |
| Qcache_not_cached       | 1040936  |
| Qcache_queries_in_cache | 17775    |
| Qcache_total_blocks     | 46990    |
+-------------------------+----------+

Watch out for the value of Qcache_free_blocks and Qcache_free_memory.

The MySQL documentation states: You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache. [ 1 ].

So we planned already for long time to write a script to do this job on a regular base. Now we finally found some time to do it:

#!/bin/bash
#
# flush_query_cache.sh
#

parameter="$@"

USER='root'
PASSSWORD=''
HOST='127.0.0.1'
PORT=3306
MYSQL='mysql'
SQL="FLUSH QUERY CACHE"

if [ "$parameter" == '' ] ; then
  parameter="--user=$USER --password=$PASSWORD --host=$HOST --port=$PORT"
fi

cmd="$MYSQL $parameter --execute='$SQL'"
# echo $cmd
eval $cmd
exit $?

This script is run from our crontab for maintenance purposes:

#
# crontab.txt
#
# Defragement the Query Cache from time to time
42 * * * *  cd /home/mysql/myenv ; bin/flush_query_cache.sh --user=root \
--host=127.0.0.1 --port=3306 >>log/flush_query_cache.log 2>&1
43 * * * *  cd /home/mysql/myenv ; bin/flush_query_cache.sh --user=root \
--host=127.0.0.1 --port=3307 >>log/flush_query_cache.log 2>&1

If a significant improvement of the system performance can be felt we cannot say at the moment.