MySQLのテーブル定義変更の並列性

投稿者: | 2019年11月6日

GMOアドマーケティングのT.Kです。
ALTER TABLE 実行時に排他的ロックが発生する事を見落とし、パーティション削除を実行したら、Waiting for table metadata lockを大量発生させてしまいました。
対象テーブルが別セッションで参照されていない時なら、きわめて短時間で終わる処理でしたが、重いクエリの実行中だったのでロック取得待ちになりました。
その間の新しい参照はWaiting for table metadata lockでブロックされました。
忘れないために、ここに再現手順を残します。

前に触れた例外とは、ALTER TABLE が、テーブルの .frm ファイルの新しいバージョンをインストールし、古いファイルを破棄して、テーブルおよびテーブル定義キャッシュから古くなったテーブル構造をクリアする準備ができた時点で (書き込みだけでなく) 読み取りをブロックすることです。この時点で、このステートメントは排他的ロックを取得する必要があります。それを行うために、現在の読み取り側が完了するのを待って、新しい読み取り (および書き込み) をブロックします。

引用元 | MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文 https://dev.mysql.com/doc/refman/5.6/ja/alter-table.html

  • テーブルを作成

  • クエリの実行
    それぞれ別のセッションで実行します

    1. 重いクエリを実行
      SELECT COUNT(*), SLEEP(600) FROM hoge WHERE report_date='2019-05-01';
    2. パーティション削除を実行
      ALTER TABLE hoge DROP PARTITION p201901;
    3. 参照クエリを実行
      SELECT COUNT(*) FROM hoge WHERE report_date='2019-04-01';
    4. processlistを確認
      3で実行したクエリがブロックされている事が分かります。

  • まとめ
    MySQL 8.0も同じ挙動でした。
    ALTER TABLEの処理中は参照をブロックしないのですが、処理の前後でブロックが発生します。
    参照が長時間ブロックされるとサービス影響が出るため、ALTER TABLE実行時はlock_wait_timeoutを短く変更し、タイムアウトしたら時間を置いて再実行が良さそうです。
The following two tabs change content below.

アバター

T.K

アバター

最新記事 by T.K (全て見る)

カテゴリー: SQL