Sunday, October 6, 2013

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. [ ].
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.

Related Posts:

  • Compile httpd 2.4.6 - P 5 Introduction to PCRE The PCRE package contains Perl Compatible Regular Expression libraries. These are useful for implementing regular expression pattern matching using the same syntax and semantics as&… Read More
  • Compiling Software With Debian LinuxThere is a lot of free software out there that is written for Linux/UNIX that's only available in source code format. As explained on the Basics page, this is the most efficient way for the developers to distribute … Read More
  • Compile httpd 2.4.6-P1 Introduction to Apache HTTPD The Apache HTTPD package contains an open-source HTTP server. It is useful for creating local intranet web sites or running huge web serving operations. This package is known to buil… Read More
  • Debugging PHP Scripts Using slow_log and more If you are an old PHP programmer, you must have used PHP’s error_log function sometime. But PHP itself does not provide a way to find out slowly executing scripts. Slow scripts are not the ones whi… Read More
  • Log rotate Log files are the most valuable tools available for Linux system security. The logrotate program is used to provide the administrator with an up-to-date record of events taking place on the system. The logrota… Read More