第121回　event_scheduler

innotopやSHOW PROCESSLISTを使ってMySQLで実行中のクエリを確認すると、event_schedulerというユーザーを確認できることがあります。このevent_schedulerはcronのjobのように定期実行を行うスレッドで、システム変数のevent_schedulerをONにすることで利用できるようになります。

今回はevent_schedulerを使って定期実行するクエリを作成し、動作確認と設定の際の注意点を見ていきましょう。なお、利用している環境はCentOS 7でMySQLのバージョンは8.0.19を利用しています。

event_schedulerの設定

システム変数event_schedulerには'ON'、'OFF'、'DISABLED'の3つの設定があります。'ON'にすることで設定してあるeventを定期実行し、'OFF'にすることですべての定期実行を停止することができます。変更はSET GLOBAL event_scheduler = 'ON/OFF'で設定し、SHOW VARIABLES like 'event_scheduler' で設定を確認することができます。

mysql> SET GLOBAL event_scheduler = 'ON';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

実際に'ON'にしてSHOW PROCESSLISTを実行するとevent_schedulerのスレッドが確認でき、'OFF'にすることでevent_schedulerのスレッドがなくなることが確認できます。'DISABLED'はMySQLを起動時に--event-scheduler=DISABLEDをつけて実行するか、my.cnfにevent_scheduler=DISABLEDを記述しておくことで設定でき、この状態で起動した場合は'ON'にも'OFF'にも設定できない状態になり、event_schedulerは利用できません。

mysql> SHOW VARIABLES like 'event_scheduler';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| event_scheduler | DISABLED |
+-----------------+----------+
1 row in set (0.39 sec)

mysql> SET GLOBAL event_scheduler = 'OFF';
ERROR 1290 (HY000): The MySQL server is running with the --event-scheduler=DISABLED or --skip-grant-tables option so it cannot execute this statement
mysql> SET GLOBAL event_scheduler = 'ON';
ERROR 1290 (HY000): The MySQL server is running with the --event-scheduler=DISABLED or --skip-grant-tables option so it cannot execute this statement

なお、デフォルトの設定はバージョン5.7ではOFF、バージョン8.0ではONになっています。

eventの作成

eventを作成するにはCREATE EVENT構文を用います。CREATE EVENT構文はCREATE EVENT <event名> ON SCHEDULE <実行間隔> DO <実行するSQL statement>の形式で記述します。作成したeventは作成したDBのスキーマに紐づき、DEFINER権限で実行されます。もし、特定のスキーマでイベントを作成する場合は<schema名>.<event名>で修飾する必要があります。

ためしにd1データベースで30秒間sleepを実行するeventを実行してみましょう。

mysql> use d1
Database changed
mysql> CREATE EVENT sleep30
    ->     ON SCHEDULE
    ->       EVERY 1 MINUTE
    ->     COMMENT 'sleep 30'
    ->     DO
    ->       SELECT sleep(30) FROM dual;
Query OK, 0 rows affected (0.03 sec)

作成したEVENTはSHOW EVENTSで確認することができます。

mysql> SHOW EVENTS\G
*************************** 1. row ***************************
                  Db: d1
                Name: sleep30
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2020-04-29 16:31:24
                Ends: NULL
              Status: SLAVESIDE_DISABLED
          Originator: 39355
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_bin
1 row in set (0.00 sec)

実際にSHOW PROCESSLISTで確認すると、SELECT sleep(30) FROM dual;が1分に一度実行されることが確認できます。

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------+
| Id | User            | Host            | db   | Command          | Time | State                                                         | Info                       |
+----+-----------------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon           |    0 | Waiting for next activation                                   | NULL                       |
|  9 | repl            | 127.0.0.1:54622 | NULL | Binlog Dump GTID | 1509 | Master has sent all binlog to slave; waiting for more updates | NULL                       |
| 10 | repl            | 127.0.0.1:54620 | NULL | Binlog Dump GTID | 1509 | Master has sent all binlog to slave; waiting for more updates | NULL                       |
| 27 | root            | localhost       | NULL | Query            |    0 | starting                                                      | SHOW PROCESSLIST           |
| 28 | root            | localhost       | d1   | Connect          |    0 | User sleep                                                    | SELECT sleep(30) FROM dual |
+----+-----------------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------+
5 rows in set (0.01 sec)

続いて60秒間sleepするeventを作成し、sleep30と同時に実行できているか確認してみましょう。

mysql> CREATE EVENT sleep60
    ->     ON SCHEDULE
    ->       EVERY 1 MINUTE
    ->     COMMENT 'sleep 60'
    ->     DO
    ->       SELECT sleep(60) FROM dual;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------+
| Id | User            | Host            | db   | Command          | Time | State                                                         | Info                       |
+----+-----------------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon           |    4 | Waiting for next activation                                   | NULL                       |
|  9 | repl            | 127.0.0.1:54622 | NULL | Binlog Dump GTID | 1753 | Master has sent all binlog to slave; waiting for more updates | NULL                       |
| 10 | repl            | 127.0.0.1:54620 | NULL | Binlog Dump GTID | 1753 | Master has sent all binlog to slave; waiting for more updates | NULL                       |
| 27 | root            | localhost       | d1   | Query            |    0 | starting                                                      | SHOW PROCESSLIST           |
| 34 | root            | localhost       | d1   | Connect          |   18 | User sleep                                                    | SELECT sleep(60) FROM dual |
| 35 | root            | localhost       | d1   | Connect          |    4 | User sleep                                                    | SELECT sleep(30) FROM dual |
+----+-----------------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------+
6 rows in set (0.00 sec)

同時に2つ実行されていることが確認できました。

eventの詳細な作成方法や利用方法については、公式ドキュメントの13.1.13 CREATE EVENT Statementをご確認ください。

event_schedulerを設定する際の注意点

インターバル内で完了していないeventは多重で実行される

実行中のeventがインターバルの間隔内で終了しなかった場合には、インターバルの時間が経過した場合にさらに実行されます。試しに、60秒間隔でsleep(90)を実行するeventを作成し、時間を置いてSHOW PROCESSLISTを実行してみます。

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------+
| Id | User            | Host            | db   | Command          | Time | State                                                         | Info                       |
+----+-----------------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon           |   12 | Waiting for next activation                                   | NULL                       |
|  9 | repl            | 127.0.0.1:54622 | NULL | Binlog Dump GTID | 2587 | Master has sent all binlog to slave; waiting for more updates | NULL                       |
| 10 | repl            | 127.0.0.1:54620 | NULL | Binlog Dump GTID | 2587 | Master has sent all binlog to slave; waiting for more updates | NULL                       |
| 27 | root            | localhost       | d1   | Query            |    0 | starting                                                      | SHOW PROCESSLIST           |
| 55 | root            | localhost       | d1   | Connect          |   72 | User sleep                                                    | SELECT sleep(90) FROM dual |
| 56 | root            | localhost       | d1   | Connect          |   12 | User sleep                                                    | SELECT sleep(90) FROM dual |
+----+-----------------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------+
6 rows in set (0.05 sec)

sleep(90)のクエリが2つ実行されています。このeventはsleepするだけなので二重で実行されることによる影響はありませんが、もし多重に実行してしまうと影響があるクエリを実行する場合には十分に気をつける必要があります。実行間隔を十分にとるか、ロックを取得して後続のクエリをロック待ち状態にするような排他的な制御を入れる必要があります。

レプリケーション環境でのeventの作成

レプリケーション環境下でeventを作成すると、SLAVE側ではレプリケーションを通してステータスが'SLAVESIDE_DISABLED'状態でeventが作成されます。これにより、更新系のクエリをeventとして作成してもSLAVE側ではeventは実行されず、データの不整合を防いでくれます。

ただし、このSLAVEをスイッチオーバーなどでMASTERに昇格する場合は、手動でeventのステータスを'ENABLE'に変更する必要があります。さらに、この昇格したMASTERを再びSLAVEに戻すようなことがある場合は、手動でeventをOFFにすることを忘れないようにしてください。

また、SHOW EVENTSやINFORMATION_SCHEMA.EVENTSテーブルで確認できるORIGINATORの値には本来eventを作成したマスターのserver_idが入力されていますが、ALTER EVENTによってステータスを変更するとORIGINATORは自身のserver_idに書き換わることに注意してください。

詳細な動作については17.5.1.16 Replication of Invoked Featuresをご確認ください。

mysqldumpでMySQLを復元させる場合

mysqldumpで論理バックアップを取得する場合、デフォルトではeventは入っていません。もし、eventの作成情報もバックアップで取得する場合はmysqldumpを実行するときにオプションの--eventsをつけて実行するか、my.cnfの[mysqldump]セクションにeventsをを追加してeventの取得も忘れないようにしましょう。

まとめ

今回はMySQLにあるevent_schedulerについて紹介しました。データ削除やレポートのサマリーデータ作成など定期的に実行したいクエリがある場合や、cronのジョブが設定できない環境では役に立つかもしれません。定期実行処理はプログラムとcronやジョブスケジューラーとの組み合わせで実行するのが良いかと思いますが、MySQL単一だけでもこのような方法があると認識いただけると幸いです。