MySQL uses events to complete scheduled tasks

Events can specify the execution of SQL code once or at certain intervals. Usually, complex SQL statements are encapsulated using stored procedures, and then the stored procedures are called periodically to complete certain tasks.

Events do not need to establish a server connection, but are initialized through a separate event scheduler thread. Events have no input parameters and no return values, because without a connection there is no input or output. After enabling, you can view the executed instructions through the server log, but it is difficult to know which specific event it came from. You can also query the INFORMATION_SCHEMA.EVENTS table to learn the status of an event, such as the time of its most recent execution.

Similar to stored procedures, events also need to consider similar issues. First, events add extra work to the MySQL server. Although the event itself has a small payload, the SQL statements invoked by the event can have a serious impact on performance. In addition, events will also have the same problems as stored procedures that are caused by statement-based replication. Good applications of events are tasks such as periodic maintenance tasks, rebuilding caches, data statistics, and saving status values ​​for monitoring and diagnosis.

The following example creates an event that calls a stored procedure to run table optimization on a specified database every week:

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

You can specify whether the event needs to be repeated. In some cases this is fine, but in others it is not. Using the example above, you might want to run the OPTIMIZE TABLE command on all replicas. However, it is important to note that if all replicas perform this operation at the same time, this will affect the performance of the entire server (for example, locking the table). Moreover, periodic events may take a long time to complete, and it is even possible that a new event will start executing before the next event is finished. MySQL will not prevent this situation, so you need to write your own code to implement mutual exclusion of the same tasks. This can be achieved by using 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

The seemingly "redundant" continue handler ensures that the lock is released even if an exception occurs.

Although events have nothing to do with connections, they do have to do with threads. The MySQL server has a main event dispatching thread that can be enabled in the server configuration:

SET GLOBAL event_handler := 1;

Once enabled, this thread will execute the events specified in the scheduler. You can view the error log on the server to get information about event execution.

Although the event scheduler is single-threaded, the events themselves can be executed concurrently. Each time an event is executed, the server will create a new process. Inside the event, you can call CONNECTION_ID() to get a unique value (even though there is no actual connection), and what is actually returned is the thread id. Processes and threads are destroyed after the event is executed. You can view it through SHOW PROCESSLIST, and it will be displayed as Connect in the Command column.

Although the process creates the thread that actually executes the event, the thread will be destroyed after the event is completed and will not be placed in the cache, so the Threads_created status counter will not see an increase.

Conclusion: Compared with application or operating system-level scheduled tasks, events are more efficient and less expensive because there is no SQL connection establishment process. Applicable to SQL script tasks that need to be run periodically, such as data table optimization, generating statistical report data, etc. However, it should be noted that the event itself may have concurrency problems, which can be solved by locking. At the same time, if an event needs to be executed repeatedly, it is best not to perform tasks that are too complex and time-consuming.

The above is the details of how MySQL uses events to complete scheduled tasks. For more information about how MySQL uses events to complete scheduled tasks, please pay attention to other related articles on 123WORDPRESS.COM!