EmergencyEMERGENCY? Get 24/7 Help Now!

Using MySQL Event Scheduler and how to prevent contention

 | February 25, 2015 |  Posted In: Insight for DBAs, MySQL

PREVIOUS POST
NEXT POST

MySQL introduced the Event Scheduler in version 5.1.6. The Event Scheduler is a MySQL-level “cron job”, which will run events inside MySQL. Up until now, this was not a very popular feature, however, it has gotten more popular since the adoption of Amazon RDS – as well as similar MySQL database as a service offerings where there is no OS level.

What is important to understand about the Event Scheduler is that it does not have any protection against multiple execution (neither does linux cron). Let’s imagine you have created an event that executes every 10 seconds, but the logic inside the event (i.e. queries or stored procedure call) can take longer than 10 seconds (may be in case of the high load), so it can pile-up. In the worst case, when an event contains a set of “insert” + “update”/”delete” statement inside a transaction, it can cause a deadlock.

Adding “get_lock” conditions inside of the event will help to prevent such situation:

If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking. Read more at event_scheduler documentation.

Function GET_LOCK() can be used for communications between threads:

The following example can illustrate using get_lock:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION is needed here to release lock even if the event failed or was killed.

The above GET_LOCK / RELEASE_LOCK combination will help to prevent contention inside the MySQL Event Scheduler.

PREVIOUS POST
NEXT POST
Alexander Rubin

Alexander joined Percona in 2013. Alexander worked with MySQL since 2000 as DBA and Application Developer. Before joining Percona he was doing MySQL consulting as a principal consultant for over 7 years (started with MySQL AB in 2006, then Sun Microsystems and then Oracle). He helped many customers design large, scalable and highly available MySQL systems and optimize MySQL performance. Alexander also helped customers design Big Data stores with Apache Hadoop and related technologies.

6 Comments

  • It’s better if you put the code of the Event into a stored procedure and your event becomes just CALL your_sp(). By doing this you don’t need to drop and recreate your event if you want to change its body.

  • What about if you want to create an event in a percona mysql cluster? Creating an event in one node creates it in all the nodes. I also tested the various lock functions that you used in you example in my cluster and they are global to a server, not to a cluster.

    It seems like I need one of these three options:
    1) a way to create the event on only one node
    2) a way for the event to look at a cluster wide lock
    3) a way to synchronize the events so that they take turns executing (I don’t like this idea)

    Suggestions?

  • Alexander,
    I have a more pointed version of the question above: Can the event scheduler be considered safe *at all* in XtraDB Cluster?

  • Alexander,
    After some further research, I believe I have an answer to the question I posed. It appears that any scheduled event which causes a schema change — and for this purpose, TRUNCATE TABLE must be considered a schema change — is unsafe on XtraDB Cluster, as it creates a race condition in which each node independently tries to put the same table into TOI. If any two or more nodes try to do this exactly simultaneously, the result is what could be called a TOI deadlock. The cluster appear to have no mechanism to resolve this TOI deadlock, and the conflict brings down the cluster.

  • I will migrate from MYSQL Community to Percona XtraDB Cluster with three nodes and Percona ProxySQL
    I have an issue now, How can I deal with MYSQL Events inside my database. the problem is the event works together in all nodes.

    Could you please tell me how can i deal with them just from one node and if this node fails it should works from another node.

Leave a Reply