一時ファイルによるディスクフールでロックされたインスタンス - ApsaraDB RDS

現象

ApsaraDB RDS for MySQL インスタンスは、一時ファイルがディスク領域を使い果たすとロック状態になります。ID が rm-***xez のインスタンスの 実行状態 は ロック です。このインスタンスは MySQL 5.7 を実行し、2 コア CPU を搭載、最大 1,200 接続をサポートします。インスタンスは中国 (フフホト) リージョンにあり、2021 年 8 月 11 日に作成されました。

原因

テーブルのソート、グループ化、または結合を行うクエリによって、インスタンスのディスク領域を使い果たす一時テーブルファイルが作成される可能性があります。また、コミットされていない大規模なトランザクションの binlog キャッシュ も、この問題の原因となる可能性があります。データ損失を防ぐため、ApsaraDB RDS はインスタンスを自動的にロックします。ロックされている間は、すべての書き込み操作がブロックされます。

解決策

緊急時には、インスタンスのストレージ容量を増やしてください。ストレージを拡張すると、約 5 分でインスタンスのロックが解除されます。詳細については、「構成変更」をご参照ください。

ストレージ容量を増やせない場合は、インスタンスのメジャーエンジンバージョンに応じた方法で対処してください。

MySQL 5.7 以前：インスタンスの再起動。
MySQL 8.0：インスタンスがロックされると、すべてのユーザーセッションが自動的に終了します。セッションが終了すると、トランザクションのロールバックが開始されます。ロールバックに必要な時間は、実行されていたクエリによって異なります。トランザクションが完全にロールバックされると、インスタンスのストレージ領域が解放されます。
長時間経過してもインスタンスが自動的にロック解除されない場合は、次の手順に従ってください。
1. Data Management (DMS) を使用して、インスタンスに接続します。
2. 次の SQL ステートメントを実行して、データベースセッションを表示します。
```
show processlist
```
3. 結果で、[State] 列をクリックしてセッションを並べ替えます。'Copy to tmp table' や 'Sending data' などの状態にあるセッションを探し、その ID を記録します。このコマンドにより、[Id]、[User]、[Host]、[db]、[Command]、[Time]、[State] の各列を含むプロセスリストが返されます。[Command] 列と [State] 列を使用して、問題のあるセッションを特定します。
4. 次の SQL ステートメントを実行してセッションを終了します。
```
kill ID;
```
  説明
  このステートメントの ID は、前の手順で記録したセッション ID です。重要：セッションを終了する前に、この操作がサービスに影響を与えないことを確認してください。

防止策

インスタンスのロックが解除されたら、問題の再発を防ぐために次の手順に従ってください。

ストレージの自動拡張を設定します。この機能は、インスタンスのストレージが少なくなると、ストレージ容量を自動的に拡張します。詳細については、「ストレージの自動拡張の設定」をご参照ください。
大規模な一時ファイルを作成する SQL クエリを最適化します。ORDER BY および GROUP BY 操作の頻繁な使用は避けてください。tmp_table_size および max_heap_table_size パラメーターの値を増やすこともできます。ただし、メモリはディスク領域よりも貴重なリソースであるため、ディスク使用量を削減するためだけにこれらの値を増やすことは推奨されません。SQL ステートメントで EXPLAIN コマンドを実行して、内部一時テーブルを使用しているかどうかを確認します。出力の Extra フィールドに Using temporary が含まれている場合、そのクエリは内部一時テーブルを使用しています。
```
explain select * from alarm group by created_on order by alarm_name;
```
以下に出力例を示します。結果の type の値が ALL の場合は、フルテーブルスキャンを示します。Extra 列に Using temporary と Using filesort の両方が含まれている場合、クエリがファイルベースのソート操作も実行していることを示します。
binlog キャッシュ を管理するには、大規模なトランザクション (特に、異なる接続で同時に実行される複数の大規模なトランザクション) を減らします。大規模なトランザクションが頻繁に発生する場合は、binlog_cache_size パラメーターの値を増やすこともできます。ただし、ディスク領域を節約するためだけにこのパラメーターを調整することは推奨されません。代わりに、短期間の接続を使用して大規模なトランザクションを実行することで、一時領域のオーバーヘッドを削減します。
ディスク使用量を監視し、データのクリーンアップやデータシャーディングを実装します。ディスク使用率を 80% 未満に維持してください。

詳細情報

一時ファイルをすぐにクリアしてインスタンスのロックを解除できない場合は、他の種類のファイルをクリアすることでディスク領域を解放できます。詳細については、次のトピックをご参照ください。

よくある質問

Q：[基本情報] ページで [インスタンスの再起動] ボタンが利用できない場合はどうすればよいですか。
A：次の手順に従って再起動をトリガーできます。
1. RDSインスタンスにアクセスし、上部のリージョンを選択し、対象のRDSインスタンスのIDをクリックします。
2. 左側メニューで、[パラメーター] をクリックします。
3. [編集可能なパラメーター] タブで、[再起動が必要] 列が [はい] になっているパラメーターを見つけて変更します。
Q：多数の一時ファイルを生成している SQL ステートメントを特定するにはどうすればよいですか。

A：過剰な一時ファイルを生成している SQL ステートメントを特定するには、次の手順に従います。
1. SQL Explorer and Audit 機能を有効にします。
2. 問題が再発するのを待ちます。次に、モニタリング情報を使用して一時ファイルの生成を監視し、SQL Explorer and Audit でキャプチャされたデータを分析します。
3. SQL Explorer and Audit の実行ログで、一時ファイルの生成に関連する SQL ステートメントをフィルター処理します。