pt-oscの後のトリガーの掃除でレプリケーション遅延した

by pyamaに 日々on 投稿日:

そりゃそうやろ、って話なんだけど、備忘としてブログにしておくことにした。

背景

MySQLで大きなテーブルに対して特定のALTER TABLEを実行すると、テーブル全体のロックが発生し、アプリケーションの更新処理が停止してしまうことがあります。
そのため、僕の所属する部では Percona Toolkit に含まれる pt-online-schema-change（以下 pt-osc）を利用して、ダウンタイムを最小限にしたスキーマ変更を行なっています。

pt-oscは、既存テーブルに直接ALTERをかけるのではなく、以下のような手順で安全に構造変更を行います。

  1. 対象テーブル（例：orders）をもとに、一時テーブル（例：_orders_new）を作成する
  2. _orders_new に対して ALTER TABLE を実行する
  3. Perlプロセスが元のテーブルのレコードを _orders_new に少しずつコピーする
  4. 元テーブル（orders）にトリガーを設定し、変更を _orders_new にも反映させる（差分同期）
  5. 同期が完了したら、以下のようにリネームを行う RENAME TABLE orders TO _orders_old, _orders_new TO orders;
  6. _orders_old と古いトリガーを削除して完了

この手順によって、アプリケーションからの読み書きの停止を抑えてテーブル構造を変更することができます。

今回の事象

あるテーブル（ここでは例として orders）に対して pt-osc を実行し、スキーマ変更を行いました。
pt-osc によるコピーと同期が完了し、リネームも無事成功しました。

この時点で残っているのは以下の2つです。

  • 新しい orders テーブルに残っているトリガー
  • 古い _orders_old テーブル

次の手順として、プライマリ（マスター）側で orders に設定されていたトリガーを削除しました。
この DROP TRIGGER は即座に完了しましたが、その後、レプリケーション遅延 が発生しました。

レプリケーション側で何が起きていたか

レプリカ（スレーブ）側では、マスターの DROP TRIGGER ステートメントがSQLスレッドを通じて実行されます。
このタイミングで、レプリカの1台（仮に replica01）では以下のようなクエリがちょうど実行中でした。

SELECT
  c.*
FROM
  customers AS c
LEFT JOIN
  orders AS o ON o.customer_id = c.id
...

このクエリは orders テーブルを参照しており、実行に30秒ほどかかっていました。

MySQLでは、テーブルを参照中のクエリが存在する場合、そのテーブルに対するDDL文（例：ALTER、DROP、RENAMEなど）は、メタデータロック（metadata lock, MDL）を取得できずに待たされます。

そのため、DROP TRIGGER がこのクエリの終了を待機していました。

さらに発生した影響

DROP TRIGGER が完了しない間、レプリケーションSQLスレッドは次のステートメントを実行できず待機状態となります。
つまり、INSERT / UPDATE / DELETE など後続のトランザクションも全てブロックされ、レプリケーション遅延が発生します。

このように、「テーブルを参照中のSELECTクエリが原因でDDLがブロックされ、結果としてレプリケーションが遅延する」という現象が今回の本質的な原因でした。

まとめ

振り返ると、pt-osc起動時の問題と同じ話なんだが、innodb buffer poolをはかしておけば、油断しても大丈夫って思って完全に油断していた。

replicaでも長時間実行されるようなクエリはない方がいいし、すぐに無くせないなら関連テーブルをpt-oscするときは気をつけないとなって思った。