Regularly flushing the MySQL Query Cache without cron

This is a reply on Regularly flushing the MySQL Query Cache.


The original acticle is about regulary flushing the MySQL Query Cache as it will fragment over time.

There are some drawbacks for the cron method for flushing the query cache:

• It will only work on UNIX like platforms as MS Windows uses the task scheduler to schedule tasks.
• It needs credentials to login to the database.
• It's not included in your database backup

There is another method, which is native to MySQL: the event scheduler.

Step 1: Enable the event scheduler:

mysql> SET GLOBAL event_scheduler=ON;
Query OK, 0 rows affected (0.00 sec)

And don't forget to set/change this in your my.cnf or my.ini

Step 2: Create the event:

mysql> CREATE EVENT flush_query_cache ON SCHEDULE EVERY 1 HOUR DO FLUSH QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW EVENTS\G
*************************** 1. row ***************************
Db: test
Name: flush_query_cache
Definer: msandbox@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: HOUR
Starts: 2011-06-19 12:57:46
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

Please keep in mind that the query cache doesn't always give you a performance benefit due to mutex contention. See also the query cache tuner from Domas Mituzas.

You schould create a stored procedure for multi statement and/or complex statements and call the procedure from your event instead of putting it directly in your event.