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:

  • Apache OptimizationAll the important configuration options are stored by Apache in a config file called httpd.conf that is located at /usr/local/apache/conf/httpd.conf. We will start by opening this file in your favorite text editor.  For… Read More
  • Chmod CHMOD là gì? CHMOD - viết tắt của Change Mode, đây là lệnh dùng để thay đổi thuộc tính của File (tập tin) và Folder (thư mục) trong hệ điều hành Unix, Linux... CHMOD thiết đặt những quyền cho phép đối với File và Folder, đ… Read More
  • How to Install php-mcrypt on Plesk 10.3 / CentOS-6 / PHP5.3.3yum repolist-----------------------------------repo id repo name statusbase CentOS-6 - Base 6.346elrepo ELRepo.org Community Enterprise Linux Repository - el6 223epel Extra Packages for Enterprise Linux 6 - x86_64 8.351extras… Read More
  • Chỉnh local time cho vps mv /etc/localtime /etc/localtime.old ln -sf /usr/share/zoneinfo/Asia/Ho_Chi_Minh /etc/localtime rdate -s rdate.cpanel.net Nếu gặp lỗi khi chạy rdate, thì install rdate: yum -y install rdate File script nào có sử dụng hàm … Read More
  • Install php PHP Modules PHP also has a variety of useful libraries and modules that you can add onto your server. You can see the libraries that are available by typing: yum search php- Terminal then will display the list of possible … Read More