From time to time you need to flush MySQL’s query_cache to prevent fragmentation of free query cache memory space. Whether we need to flush the query cache is provided to us 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 and 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. Flushing the query cache is still important for your MySQL database and server performance. Here are three posts with additional information:
- 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 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.