Events were introduced after MySQL 5.1. They are similar to scheduled tasks (CRon) in the operating system, but periodic tasks are built into the MySQL server. You can specify that SQL code is executed either once or at regular intervals. A stored procedure is usually used to encapsulate complex SQL statements and then periodically call the stored procedure to perform certain tasks.

Instead of establishing a server connection, events are initialized by a separate event scheduler thread. The event has no input parameters and no return value, because without a connection there would be no input and output. When enabled, instructions executed can be viewed through the server logs, but it is difficult to know which event they are from. You can also query the Information_schema.Events table for the status of EVENTS, such as the time of the last execution.

Similar to stored procedures (see MySQL Advanced Features (6) : The pros and cons of stored procedures), events need to be considered similarly. First, events add extra work to the MySQL server. Although the load of the event itself is small, the SQL statements invoked by the event can have a serious impact on performance. In addition, events can have the same kind of problems as statement-based replication of stored procedures. A good use of events is for tasks such as periodic maintenance tasks, rebuilding caches, data statistics, and saving status values for monitoring and diagnostics.

The following example creates an event that calls the stored procedure to run table optimizations against the specified database on a weekly basis:

CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK
DO 
CALL optimize_tables('somedb');
Copy the code

You can specify whether the event needs to be repeated. In some cases it works, but in some cases it doesn’t. For example, you might want to run the OPTIMIZE TABLE directive on all of your replicas. However, it is important to know that if all replicas perform this operation at the same time, it can affect overall server performance (for example, locking tables). Moreover, periodic events can take a long time to complete, or even start executing before the next event has finished. MySQL does not prevent this, so you need to write your own code to implement mutual exclusion of the same task. This can be done by locking:

CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK
DO 
BEGIN
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  	BEGIN END;
  IF GET_LOCK('somedb'.0) THEN
  	DO CALL optimize_tables('some_db');
  END IF;
  DO RELEASE_LOCK('somedb');
END
Copy the code

A seemingly “redundant” Continue handler guarantees that the lock will be released even if an exception occurs.

Although the event is not connected, it is thread-specific. MySQL server has a main event scheduler thread, which can be enabled in the server configuration:

SET GLOBAL event_handler := 1;
Copy the code

Once enabled, this thread executes events for the specified schedule. You can learn about event execution by viewing the error log on the server.

Although the event scheduler is single-threaded, the event itself can be executed concurrently. The server creates a new process each time the event is executed. Inside the event, you can call CONNECTION_ID() to get a unique value (even though there is no connection), and what is actually returned is the thread ID. Processes and threads are destroyed after the event is executed. You can see this with SHOW PROCESSLIST, which shows up as Connect in the Command column.

Although the process creates the thread that actually executes the event, the thread is destroyed after the event completes and is not put into the cache, so Threads_created, the state counter, does not see an increase.

Conclusion: Events are more efficient and less expensive than application-level, or operating system-level, scheduled tasks because there is no SQL connection establishment process. It is suitable for SQL script tasks that need to be run periodically, such as data table optimization, generating statistical report data, and so on. Note, however, that the event itself may have concurrency problems, which can be solved by locking. At the same time, if events need to be repeated, it is best not to perform tasks that are too complex and time-consuming.