Advanced MySQL Features - High Performance MySQL - page 288

Events
Events are a new form of stored code in MySQL 5.1. They are akin to cron jobs but are
completely internal to the MySQL server. You can create events that execute SQL code
once at a specific time, or frequently at a specified interval. The usual practice is to wrap
the complex SQL in a stored procedure, so the event merely needs to perform a CALL .
Events are initiated by a separate event scheduler thread, because they have nothing to
do with connections. They accept no inputs and return no values—there's no connec-
tion for them to get inputs from or return values to. You can see the commands they
execute in the server log, if it's enabled, but it can be hard to tell that those commands
were executed from an event. You can also look in the INFORMATION_SCHEMA.EVENTS table
to see an event's status, such as the last time it was executed.
Similar considerations to those that apply to stored procedures apply to events. First,
you are giving the server additional work to do. The event overhead itself is minimal,
but the SQL it calls can have a potentially serious impact on performance. Further,
events can cause the same types of problems with statement-based replication that
other stored code can cause. Good uses for events include periodic maintenance tasks,
rebuilding cache and summary tables to emulate materialized views, or saving status
values for monitoring and diagnostics.
The following example creates an event that will run a stored procedure for a specific
database, once a week (we'll show you how to create this stored procedure later):
CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK
DO
CALL optimize_tables('somedb');
You can specify whether events should be replicated. In some cases this is appropriate,
whereas in others it's not. Take the previous example, for instance: you probably want
to run the OPTIMIZE TABLE operation on all replicas, but keep in mind that it could
impact overall server performance (with table locks, for instance) if all replicas were to
execute this operation at the same time.
Finally, if a periodic event can take a long time to complete, it might be possible for the
event to fire again while its earlier execution is still running. MySQL doesn't protect
against this, so you'll have to write your own mutual exclusivity code. You can use
GET_LOCK() to make sure that only one event runs at a time:
CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
DECLARE CONTINUE HANLDER FOR SQLEXCEPTION
BEGIN END;
IF GET_LOCK('somedb', 0) THEN
DO CALL optimize_tables('somedb');
END IF;
DO RELEASE_LOCK('somedb');
END