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.