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.

Learn how to flush the MyQL query cache unattended / automatic through a Linux cronjob, or on MySQL command prompt.

High Performance MySQL
High Performance MySQL

Protip: 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 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)

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)

That looks a whole lot better, don’t you think?

This may interest you:   Convert MySQL MyISAM tables to InnoDB

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.

Note: MySQL query cache deprecated

MySQL query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. – mysql.com

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';

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-cache

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.