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 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE `hoge` ( `id` int(11) NOT NULL AUTO_INCREMENT, `report_date` date NOT NULL, PRIMARY KEY (`id`,`report_date`) ) ENGINE=InnoDB PARTITION BY RANGE COLUMNS(report_date) (PARTITION p201901 VALUES LESS THAN ('2019-02-01') ENGINE = InnoDB, PARTITION p201902 VALUES LESS THAN ('2019-03-01') ENGINE = InnoDB, PARTITION p201903 VALUES LESS THAN ('2019-04-01') ENGINE = InnoDB, PARTITION p201904 VALUES LESS THAN ('2019-05-01') ENGINE = InnoDB, PARTITION p201905 VALUES LESS THAN ('2019-06-01') ENGINE = InnoDB ); |
- クエリの実行
それぞれ別のセッションで実行します- 重いクエリを実行
SELECT COUNT(*), SLEEP(600) FROM hoge WHERE report_date='2019-05-01'; - パーティション削除を実行
ALTER TABLE hoge DROP PARTITION p201901; - 参照クエリを実行
SELECT COUNT(*) FROM hoge WHERE report_date='2019-04-01'; - processlistを確認
3で実行したクエリがブロックされている事が分かります。
- 重いクエリを実行
|
1 2 3 4 5 6 7 8 |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------------+ | 9 | root | localhost | test | Query | 21 | User sleep | SELECT COUNT(*), SLEEP(600) FROM hoge WHERE report_date='2019-05-01' | | 10 | root | localhost | test | Query | 6 | Waiting for table metadata lock | SELECT COUNT(*) FROM hoge WHERE report_date='2019-04-01' | | 11 | root | localhost | test | Query | 15 | Waiting for table metadata lock | ALTER TABLE hoge DROP PARTITION p201901 | | 12 | root | localhost | | Query | 0 | starting | show processlist | +----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------------------------------+ |
- まとめ
MySQL 8.0も同じ挙動でした。
ALTER TABLEの処理中は参照をブロックしないのですが、処理の前後でブロックが発生します。
参照が長時間ブロックされるとサービス影響が出るため、ALTER TABLE実行時はlock_wait_timeoutを短く変更し、タイムアウトしたら時間を置いて再実行が良さそうです。
