Posted inMySQL

MySQL Event Scheduler入門：定期タスクの自動化とデータベース定期メンテナンスの実践ガイド

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

背景と必要性

システムを数か月運用していると、audit_logsやuser_sessions、sms_otpsといったデータの蓄積速度が速いテーブルで問題が表面化しがちです。テーブルが数万件から1,000万件超へと肥大化すると、ディスク容量が急速に圧迫されます。さらに深刻なのは、普段は50ms程度で完了していたページネーションや検索のSELECTクエリが、3〜4秒にまで急増してしまうことです。

多くの開発チームでは、PythonやNode.jsでスクリプトを作成し、夜間にLinuxのcrontabで定期実行させる手法が一般的です。これは目先の問題解決にはなりますが、アプリケーション外部への新たな依存関係を生み出します。crontabが停止しても気づきにくく、外部スクリプト用に接続認証情報を別途管理する必要があり、数百万行ものデータをやり取りするネットワークのラウンドトリップ（network round-trip）オーバーヘッドも発生します。

MySQLには標準で内部ソリューションが用意されています。それがMySQL Event Schedulerです。この機能は、データベースエンジン内部で動作するcron jobのような役割を果たします。サーバー自身がスケジュールを管理し、中間接続を介すことなくデータ層で直接SQLステートメントを自動実行します。

実際の運用事例として、容量60GBのMySQL 8.0データベースにおいて、毎夜期限切れのログを10万行ずつ自動パージしたところ、メインテーブルのサイズを18GBから3.5GBへと大幅に縮小できました。B-Treeインデックスのサイズも目に見えて小さくなり、buffer poolにより多くのホットデータを保持できるようになり、ディスクI/O負荷を最大40%削減することに成功しました。

セットアップ・有効化

タスクをスケジュールする前に、MySQLサーバー上でスケジューラスレッド（scheduler thread）が正常に稼働しているか確認する必要があります。

ステップ1：Event Schedulerの稼働状態を確認する

ターミナルからMySQL CLIに接続し、以下のクエリを実行します。

SHOW VARIABLES LIKE 'event_scheduler';

Value列がOFFまたはDISABLEDと表示されている場合、この機能は無効化されており、登録されたイベントは実行されません。

ステップ2：Event Schedulerを有効化する

MySQLを再起動せずに、現在のセッションで即座に一時有効化する場合：

SET GLOBAL event_scheduler = ON;

ただし、SET GLOBALによる設定はサーバー再起動時にデフォルト値へリセットされます。MySQL 8.0以降では、設定ファイルを手動編集することなく、SQLコマンドから直接設定を永続化できます。

SET PERSIST event_scheduler = ON;

MySQL 5.7を使用している場合や設定ファイルで管理したい場合は、/etc/mysql/mysql.conf.d/mysqld.cnf（または/etc/my.cnf）の[mysqld]セクションに以下を追記します。

[mysqld]
event_scheduler=ON

設定ファイルを直接編集した場合は、サービスを再起動します。

sudo systemctl restart mysql

ステップ3：実行ユーザーへの権限付与

タスクを実行するアカウントには、対象データベースに対するEVENT権限が必要です。root権限でログインし、以下の権限付与コマンドを実行します。

GRANT EVENT ON my_project_db.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

詳細な設定と実装例

MySQLにおけるイベントの基本構文は以下の通りです。

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule_definition
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
DO
  event_body;

1. 基本タスク：期限切れログインセッショントークンの削除

リフレッシュトークンを保持するuser_tokensテーブルを例に考えます。テーブル容量を削減するため、30日を過ぎて失効したトークンを定期削除する必要があります。以下のイベントは、毎日午前3時に自動実行されます。

CREATE EVENT IF NOT EXISTS ev_clean_expired_tokens
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 3 HOUR)
ON COMPLETION PRESERVE
ENABLE
DO
  DELETE FROM user_tokens 
  WHERE expires_at < NOW();

各設定パラメータの意味は以下の通りです。

  • EVERY 1 DAY：実行間隔。要件に応じて1 HOUR、30 MINUTE、1 WEEKなどに柔軟に変更できます。
  • ON COMPLETION PRESERVE：イベント実行完了後もデータディクショナリから自動削除せず、イベント定義を保持します。
  • ENABLE：作成完了後、直ちにスケジュールを有効化します。

2. 応用タスク：BEGIN … ENDブロックによるログ退避と実行履歴の記録

古いログをアーカイブ用テーブルへ退避し、メインテーブルから物理削除した上でシステム履歴を記録するなど、複数ステップを要する複雑なシナリオでは、一時的にDELIMITERを変更した上でBEGIN ... ENDブロックに処理をまとめます。

DELIMITER $$

CREATE EVENT IF NOT EXISTS ev_archive_and_purge_logs
ON SCHEDULE EVERY 1 WEEK
STARTS (CURRENT_DATE + INTERVAL 2 HOUR)
ON COMPLETION PRESERVE
DO
BEGIN
  -- 1. 90日を経過したデータをアーカイブテーブルにコピー
  INSERT INTO audit_logs_archive
  SELECT * FROM audit_logs 
  WHERE created_at < NOW() - INTERVAL 90 DAY;

  -- 2. コピーした行を削除して容量とインデックスを解放
  DELETE FROM audit_logs 
  WHERE created_at < NOW() - INTERVAL 90 DAY;

  -- 3. メンテナンステーブルに実行ログを記録
  INSERT INTO maintenance_history (task_name, executed_at, status)
  VALUES ('ev_archive_and_purge_logs', NOW(), 'SUCCESS');
END $$

DELIMITER ;

3. デプロイ時のイベント一時停止と再開

スキーママイグレーションや大規模なテーブル構造変更の最中にバックグラウンドでイベントが実行されると、ロック競合（lock conflict）が発生するリスクがあります。デプロイ前にはイベントを一時停止しておくのが安全です。

-- イベントの実行を一時停止
ALTER EVENT ev_clean_expired_tokens DISABLE;

-- デプロイ完了後にスケジュールを再開
ALTER EVENT ev_clean_expired_tokens ENABLE;

動作確認とモニタリング

イベントはバックグラウンドスレッドで完全に非同期実行されます。画面への直接的な出力がないため、トラブルを未然に検知できるよう監視手法を把握しておく必要があります。

1. イベント一覧と次回実行時刻の確認

データベース内に存在するイベントの一覧を素早く確認するには次を実行します。

SHOW EVENTS FROM my_project_db\G

直近の実行日時（LAST_EXECUTED）や次回トリガー日時（NEXT_RUN）を詳細に把握するには、システムテーブルに対してクエリを実行します。

SELECT 
  EVENT_NAME, 
  STATUS, 
  INTERVAL_VALUE, 
  INTERVAL_FIELD, 
  LAST_EXECUTED, 
  NEXT_RUN 
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'my_project_db';

2. システムエラーログの確認

イベント実行中に構文エラーやデッドロックが発生しても、クライアントへ直接エラーは通知されません。代わりに、詳細な内容はすべてサーバーのerror.logへ出力されます。ターミナルから以下のコマンドで確認可能です。

sudo tail -n 50 /var/log/mysql/error.log

スケジューラ関連のログには、通常[Warning]や[ERROR] Event Scheduler: [user][db.event_name] ...のようなプレフィックスが付与されます。

3. イベント実行時のデータベース負荷・パフォーマンス低下を防ぐ勘所

  • 数百万行の大規模な一括DELETEを避ける： 1回のDELETEで50万行を一括削除しようとすると、広範な行ロックやギャップロック（row/gap lock）が発生し、buffer poolを逼迫させ、スレーブサーバーへのレプリケーション遅延（Replication Lag）を急増させます。LIMIT句を併用して5,000行単位のバッチに分割するか、短いスリープを挟むストアドプロシージャのループ処理として実装してください。
  • WHERE句の検索条件に必ずインデックスを貼る： expires_atやcreated_atといった日時条件のカラムには必ずインデックスを付与してください。インデックスがない場合、イベント実行時にフルテーブルスキャン（Full Table Scan）が発生し、CPU使用率が100%に張り付いて通常のビジネスクエリを停止させてしまいます。
  • ピーク時間帯の実行を避ける： トラフィックが最も少ない時間帯（通常は深夜2:00〜4:30頃）を選定し、重いクリーンアップや集計タスクを実行するようにスケジュールを設計してください。
Share:
Tags: