How to flush MySQL query_cache to prevent fragmentation of free query cache memory space? Here is a how to for unattended and automatically clearing MySQL’s query cache using a Linux cronjob. And I’ll show you how to on the mysql shell.
Whether or not you need to flush the query cache is provided by MySQL’s Qcache_free_blocks status information. The higher the value, the more fragmented free space is. In this post, you’ll learn how to flush the MySQL query cache unattended / automatic through a Linux cronjob. Or manually on the MySQL shell.
Flushing the query cache with flush query cache does not delete or clear the query cache. You have to use reset query cache to fully clear MySQL’s query cache.
On the mysql shell (command line), you can determine if the query cache needs to be flushed. First look up the Qcache status information:
MariaDB [(none)]> SHOW STATUS LIKE '%Qcache%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 7163 |
| Qcache_free_memory | 1009814376 |
| Qcache_hits | 2787626 |
| Qcache_inserts | 1371741 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 46422 |
| Qcache_queries_in_cache | 25783 |
| Qcache_total_blocks | 58904 |
+-------------------------+------------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)Depending on the circumstances, 7163 Qcache_free_blocks can be pretty high. Lets execute flush query cache and see the results:
MariaDB [(none)]> flush query cache;
Query OK, 0 rows affected (0.32 sec)
MariaDB [(none)]> SHOW STATUS LIKE '%Qcache%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 2 |
| Qcache_free_memory | 1008447288 |
| Qcache_hits | 2791957 |
| Qcache_inserts | 1373213 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 46455 |
| Qcache_queries_in_cache | 27238 |
| Qcache_total_blocks | 54670 |
+-------------------------+------------+
8 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)That looks a whole lot better, don’t you think?
If you’d like more information about the MySQL query cache, read the following answer on Database Administrators / Stack Exchange and Percona’s blogpost The MySQL Query Cache: How it works, plus workload impacts.
MySQL query cache deprecated. MySQL query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. – mysql.com – However, in MariaDB query cache is still available.
Query cache flushing automated with a cronjob
Flush MySQL cache through cron
If you’ve followed my post Check, repair and optimize MySQL tables with mysqlcheck you should already have an user ‘maintenance’ on your system.
As you’ve read in that article, my user already needed the reload privilege. If yours doesn’t have that privilege, add it since it’s required for the FLUSH statement:
mysql> grant reload on *.* to 'maintenance'@'localhost';Code language: Bash (bash)Now you can add the following command to your daily, weekly or monthly cron (on one line):
echo "0 4 * * Sun root mysql -u maintenance --password
-e 'flush query cache' /etc/cron.d/mysql-flush-query-cacheCode language: PHP (php)As with the previous article, this cron runs every Sunday morning at 04:00 AM. The systems root user starts the mysql command under MySQL’s user ‘maintenance’. We created the user without a password, therefore you need to provide an empty password with --password.
Protip: Donate $10, 20 or 30 through Paypal (or see my donate page) and support this site. Thank you <3
