Sunday, June 19, 2011

Regularly flushing the MySQL Query Cache without cron

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

Alarm Clock 3
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.

7 comments:

  1. Thank you for this tip. But when I run create even, it shows no database selected error. How do I fix it?


    mysql> CREATE EVENT flush_query_cache ON SCHEDULE EVERY 1 HOUR DO FLUSH QUERY CACHE;
    ERROR 1046 (3D000): No database selected
    mysql>

    ReplyDelete
    Replies
    1. You should select a database: For example the test database: 'USE test'
      Also make sure 'event_scheduler=ON' is in your configuration.

      Delete
    2. Yes yes. Thank you so much. It work as you mentioned. I think it's better if you mentioned it in this posts. Then newbies can easier.

      Is this output correct?

      Database changed
      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: mydatabse name here
      Name: flush_query_cache
      Definer: root@localhost
      Time zone: SYSTEM
      Type: RECURRING
      Execute at: NULL
      Interval value: 1
      Interval field: HOUR
      Starts: 2014-02-15 20:09:44
      Ends: NULL
      Status: ENABLED
      Originator: 0
      character_set_client: utf8
      collation_connection: utf8_general_ci
      Database Collation: utf8_general_ci
      1 row in set (0.00 sec)

      Delete
  2. Quick reply. :) thanks Daniel. I'll try it tomorrow and let you know the result.

    ReplyDelete