Posted inMySQL

Metadata LockingによるMySQLのハングアップ対処法：ALTERコマンドでシステムをダウンさせないために

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

背景：ALTER TABLEコマンドが本番環境の「悪夢」に変わる時

想像してみてください。ordersテーブルにstatus列を追加するためにALTER TABLEコマンドを実行したとします。ステージング環境では1秒もかからずに完了しました。しかし、本番環境でEnterキーを押した瞬間、Slackに504 Gateway Timeoutのエラー通知が飛び交い始めます。データベースのCPU使用率は90%に急上昇し、ordersテーブルへのすべてのクエリが突然停止してしまいます。

私は以前、約4000万レコードを含む50GBほどのMySQL 8.0データベースでこの苦い経験をしました。犯人は通常の行ロック（row lock）ではなく、Metadata Locking (MDL)でした。これは MySQLのテーブル構造を保護するためのメカニズムです。あるトランザクションがデータを読み取っている間、MySQLは一貫性を確保するためにスキーマの変更をすべてブロックします。

問題の核心はここにあります。バックグラウンドで長時間実行されているSELECTコマンドや、コミットを「忘れた」トランザクションが一つでもあるだけで、ALTER TABLEコマンドは順番待ち行列に並ぶことになります。さらに悪いことに、このALTERコマンドが列の先頭でブロックされると、その後に続くすべてのSELECTやINSERTクエリもハングアップしてしまいます。結果として、アプリケーション全体が麻痺してしまうのです。

Metadata Lockingエラーを3ステップで再現する

問題を解決するには、その発生の仕組みを理解する必要があります。2つのターミナルウィンドウを使用して、ローカル環境でこの状況を再現できます。

ステップ1：データの準備

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

INSERT INTO users (name) VALUES ('An'), ('Bình'), ('Chi');

ステップ2：「ハング」したトランザクションの作成（セッション1）

最初のセッションを開き、トランザクションを開始しますが、絶対にCOMMITしないでください。

START TRANSACTION;
SELECT * FROM users WHERE id = 1; 
-- このセッションはそのままにし、閉じたり何も入力したりしないでください。

ステップ3：ALTERコマンドの実行（セッション2）

2番目のウィンドウで、新しい列を追加してみましょう。

ALTER TABLE users ADD COLUMN email VARCHAR(255);

この時、セッション2は停止します。もしセッション3を開いて単純なSELECTコマンドを実行しても、それもハングアップします。ようこそ、Metadata Lockingの世界へ。

動作メカニズム：なぜMySQLはそのような挙動をするのか？

バージョン5.5.3以降、MySQLはMDLを使用してテーブル構造へのアクセス権を管理しています。区別すべき2種類のロックがあります：

  • Shared Metadata Lock (SU): データの読み取りまたは書き込み（SELECT, INSERTなど）時に有効になります。複数のユーザーが同時にこのロックを保持できます。
  • Exclusive Metadata Lock (X): 構造の変更（ALTER, DROPなど）時に有効になります。このロックを保持できるのは一度に1人だけです。

上記の例では、セッション1がShared Lockを保持しています。セッション2はExclusive Lockを取得したいため、順番待ちをする必要があります。厄介なのは、Exclusive Lockが待機している間、後続のすべての新しいShared Lockも優先的にブロックされることです。これが、システムが急速にダウンするドミノ倒し効果の正体です。

よくある間違いは、lock_wait_timeoutの値をデフォルトのままにすることです。MySQLはこの数値を31,536,000秒（つまり1年！）に設定しています。これは、サーバーがダウンするまでALTERコマンドが待ち続けることを意味します。この値を60秒程度に下げることをお勧めします。

-- 現在の設定を確認
SHOW VARIABLES LIKE 'lock_wait_timeout';

-- システム保護のため待機時間を60秒に制限
SET SESSION lock_wait_timeout = 60;

データベースがハングアップした時の「レスキュー」方法

システムが遅くなり始めたと感じても、焦ってMySQLを再起動しないでください。クラッシュ後のリカバリプロセスに非常に時間がかかるため、事態を悪化させるだけです。

1. SHOW PROCESSLISTによる調査

何個の接続がWaiting for table metadata lock状態になっているか確認します。

SHOW FULL PROCESSLIST;

注意：このコマンドは誰が待機しているかを示すだけで、誰がロックを保持しているかは特定できません。

2. Performance Schemaを使用して「犯人」を特定する

MySQL 5.7以降では、これが最も強力なツールです。まず、MDLの監視を有効にします：

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME = 'wait/lock/metadata/sql/mdl';

その後、以下のクエリを実行して、すべてをブロックしているスレッドの正確なIDを見つけます：

SELECT 
    OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'GRANTED';

3. sysスキーマによるクイックレスキュー

長いクエリを打つのが面倒な場合は、sysスキーマに非常に直感的なビューが用意されています：

SELECT * FROM sys.schema_table_lock_waits;

blocking_pid列を確認し、そのIDを見つけてすぐにKILLし、テーブルを解放します：

-- 例：見つかったPIDが456の場合
KILL 456;

実戦経験：予防は治療に勝る

大規模システムのトラブルシューティングを何度も経験した結果、私は4つの黄金律を導き出しました：

  • Online Schema Changeツールの使用： 10GBを超えるテーブルでは、決して直接ALTER TABLEを使用しないでください。Perconaのpt-online-schema-changeやGitHubのgh-ostを使用しましょう。これらは一時テーブルを作成し、データを徐々にコピーするため、長時間のテーブルロックを引き起こしません。
  • 長時間トランザクションの確認： マイグレーション前に、実行中のcronジョブやレポートがないか確認してください。10分間続くSELECTコマンドは、MDLロックの導火線になります。
  • オフピーク時間の選択： どんなに優れたツールであっても、トラフィックが最も少ない時間帯（通常は午前2時〜3時）に実行してください。
  • 短いタイムアウトの設定： ALTERを実行するセッションには常にlock_wait_timeoutを設定してください。ウェブサイト全体をハングアップさせるよりは、マイグレーションコマンドを失敗させる方がマシです。

Metadata Lockの対処には冷静さが必要です。何百もの接続がハングアップしているのを見ても、パニックになってサーバーを再起動するのではなく、ロックを保持している正しいIDを見つけて対処することを忘れないでください。

Share:
Tags: