You are here

Regularly flushing the MySQL Query Cache

Taxonomy upgrade extras: 

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.

Comments

i wonder why mysql cant flush the QC on its own. having a configuration parameter to flush every x seconds would be so much more convenient and everybody using QC would use it if reasonable defaults were set ...
Anonymouscomment

why not flush it after every N_th QC change!
Anonymouscomment

Hi Normann, Yes, why not? Great idea! Something like: If Qcache_free_blocks is bigger than 10% of Qcache_total_blocks then do a flush. It just needs a little bit more logic in the script... Oli
admincomment

How would you add this logic to the script? Maybe pull out Qcache_free_blocks and Qcache_total_blocks and set them to variables?
Anonymouscomment

somebody should make benchmarks. if you can see a batter performance sombody can make a patch, so it would be hardcoded.
Anonymouscomment

Hi, Hopefully this will help. As of 5.1.6 (iirc) the event_scheduler has been available. Simply enable it on the server you're using in my.cnf (details can be found in the manual). Then create something like this: mysql> use mysql; CREATE EVENT `flush_q_cache` ON SCHEDULE EVERY 60 MINUTE STARTS '2011-02-15 20:28:01' ON COMPLETION NOT PRESERVE ENABLE DO FLUSH QUERY CACHE This will create an event attached to the mysql system schema, which, from the the start time/date, will run FLUSH QUERY CACHE every 60 minutes. This eliminates the need for shell scripts, and passwords, and keeps everything within the realm of the DBA. The only caveat is the user will need appropriate rights to the system table so this will often be setup as the DB root user or similarly privileged account.
Anonymouscomment