ApsaraDB RDS:メタデータロックによるデータベース障害の解決

背景情報

MySQL 5.5 は、データ定義言語 (DDL) 操作とデータ操作言語 (DML) 操作間の一貫性を確保するためにメタデータロック (MDL) を導入しました。 しかし、DML 操作中に ALTER 操作が実行された場合や、長時間実行クエリがアクティブなときに ALTER 操作が試みられた場合など、一部のシナリオではブロックが発生する可能性があります。

シナリオ

• インデックスの作成または削除

• テーブル構造の変更

• optimize table や repair table などのテーブルメンテナンス操作の実行

• テーブルの削除

• テーブルレベルの書き込みロックの取得

原因

• テーブルで長時間実行クエリがアクティブになっています。

• 明示的または暗黙的に開始されたトランザクションが、コミットもロールバックもされていません。 たとえば、クエリは完了しても、そのトランザクションは開いたままです。

• 失敗したクエリを含むトランザクションがテーブルに存在します。

手順

1. DMS を使用して ApsaraDB RDS データベースにログインします。

2. [SQL コンソール] で、show full processlist コマンドを実行してすべてのデータベーススレッドのステータスを表示します。

3. [状態] 列に [Waiting for table metadata lock] の値が多数含まれているかどうかを確認してください。[Waiting for table metadata lock] が表示されている場合は、ブロッキングが発生しています。

4. ブロックしているセッションのセッション ID を見つけます。

   1. ステータスが [テーブルメタデータロックを待機中] のセッションでは、[情報] 列を確認して、それがアクセスしようとしているテーブル (たとえば sbtest2) を特定します。

   2. 他のセッションの [情報] カラムで、テーブル sbtest2 を操作しているセッションを探し、そのセッションの [ID] を記録します。

      説明

      ロックを待機しているセッションではなく、テーブルのロックを保持しているセッションを見つける必要があります。ロックを待機しているセッションでは、[テーブルメタデータロックの待機中] ステータスが表示されます。[状態] 列と [情報] 列を分析することで、ブロックしているセッションを特定できます。

      たとえば、あるセッションの [State] が [Waiting for table metadata lock] の場合、その [Info] カラムのコマンドから、このセッションが sbtest2 テーブルを操作する必要があると判断できます。同じく sbtest2 テーブルを操作する必要がある他のセッションの中から、[Id] が 267 のセッションの [State] カラムから、そのセッションが現在 sbtest2 テーブルを操作しており、ブロックの原因となっていることを判断できます。

      説明

      [データ送信中] 状態は一例です。ブロッキングセッションの実際の状態は異なる場合があります。

      次のクエリを実行して、長時間実行トランザクションを見つけることもできます。 ブロックしているステートメントを開始したユーザーが現在のユーザーと異なる場合は、そのユーザーとしてログインしてセッションを終了する必要があります。

      select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
        (select 
               id, time
           from
               information_schema.processlist
           where
               time = (select 
                       max(time)
                   from
                       information_schema.processlist
                   where
                       state = 'Waiting for table metadata lock'
                           and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p
        where timestampdiff(second, i.trx_started, now()) > p.time
        and i.trx_mysql_thread_id  not in (connection_id(),p.id);

5. kill <セッション ID> (例： kill 267) を実行してセッションを終了し、メタデータロックを解放します。

推奨事項

• インデックスの作成や削除などの操作は、オフピーク時間に実行してください。

• オートコミットを有効にしてください。

• lock_wait_timeout パラメーターをより低い値に設定してください。

• 長時間実行トランザクションを自動的に終了するイベントの使用を検討してください。 たとえば、次のイベントは 60 分以上実行されているトランザクションを終了します。

  create event my_long_running_trx_monitor
  on schedule every 60 minute
  starts '2015-09-15 11:00:00'
  on completion preserve enable do
  begin
    declare v_sql varchar(500);
    declare no_more_long_running_trx integer default 0; 
    declare c_tid cursor for
      select concat ('kill ',trx_mysql_thread_id,';') 
      from information_schema.innodb_trx 
      where timestampdiff(minute,trx_started,now()) >= 60;
    declare continue handler for not found
      set no_more_long_running_trx=1;
    open c_tid;
    repeat
      fetch c_tid into v_sql;
   set @v_sql=v_sql;
   prepare stmt from @v_sql;
   execute stmt;
   deallocate prepare stmt;
    until no_more_long_running_trx end repeat;
    close c_tid;
  end;