How to flush MySQL query cache from time to time

From time to time you need to flush MySQL's query cache to prevent fragmentation of free query cache memory space. Whether you need to flush the query cache is told by MySQL's Qcache_free_blocks status information: the higher the value, the more fragmented the free space is. In this post, you'll learn how to flush the MySQL query cache manually on the MySQL shell, and unattended through a Linux cronjob.

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 prompt), 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. Let's 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?

If you'd like more information about the MySQL query cache, read this Database Administrators Stack Exchange thread (Wayback Machine) and Percona's blog post The MySQL Query Cache: How it works, plus workload impacts.

The MySQL query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0 (MySQL documentation, Wayback Machine). However, in MariaDB the query cache is still available. Here are three posts with additional information about MySQL database and server performance:

• MySQL InnoDB performance improvement: InnoDB buffer pool instances
• MySQL database optimization with indices
• Optimize all MySQL tables with PHP/MySQLi multi_query

Query cache flushing automated with a cronjob

If you've followed my post Check, repair and optimize MySQL tables with mysqlcheck, you should already have a 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 cron job as a file in /etc/cron.d:

echo "0 4 * * Sun root mysql -u maintenance -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 system's root user starts the mysql command as MySQL user 'maintenance'. We created that user without a password, so there is no need for a --password option: without a value, --password makes the mysql client prompt for a password, which doesn't work in a cronjob.

In this article you learned how to flush the MySQL query_cache to prevent fragmentation of free query cache memory space.