Amazon RDS for MySQL または MariaDB インスタンスでストレージがいっぱいと表示されるのはなぜですか?

簡単な説明

ストレージがいっぱいになった場合のトラブルシューティングを行うには、まず DB インスタンスで使用されている合計容量を分析する必要があります。DB インスタンスのスペースは次の目的で使用されます。

• ユーザー作成のデータベース
• 一時テーブル
• バイナリログまたは MySQL スタンバイインスタンスのリレーログ (リードレプリカを使用する場合)
• InnoDB テーブルスペース
• 一般ログ、スロークエリログ、エラーログ

ストレージスペースを使用しているものを特定したら、ストレージスペースを再利用できます。次に、FreeStorageSpace メトリクスを監視して、再び容量が不足しないようにします。

注: 使用可能なストレージが急激に減少した場合は、SHOW FULL PROCESSLIST コマンドを実行して、DB インスタンスレベルで進行中のクエリを確認してください。SHOW FULL PROCESSLIST コマンドは、すべてのアクティブな接続と各接続によって実行されるクエリに関する情報を提供します。長期間アクティブになっているトランザクションを確認するには、INFORMATION_SCHEMA.INNODB_TRX または SHOW ENGINE INNODB STATUS コマンドを実行します。出力を確認します。

解決策

DB インスタンス (ユーザー作成データベース) で使用された合計容量を分析する

ユーザーが作成した各データベースのサイズを調べるには、次のクエリを実行します。

mysql> SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" FROM information_schema.tables GROUP BY 1 ORDER BY 2 DESC;

(DB インスタンス内の) 特定のデータベースの各テーブルのサイズを確認するには、次のクエリを実行します。

mysql> SELECT table_schema "DB Name", table_name,(data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" from information_schema.tables where table_schema='database_name';

MySQL バージョン 5.7 以降、または MySQL 8.0 以降でより正確なテーブルサイズを取得するには、次のクエリを使用します。
**注:**information_schema.files クエリは MariaDB エンジンには適用されません。

mysql> SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" from information_schema.files where file_name like '%/database_name/%';

データベースレベルとテーブルレベルで完全なストレージ詳細とおおよその断片化されたスペースを取得するには、次のクエリを実行します。
注: このクエリは、共有テーブルスペースにあるテーブルには適用されません。

mysql> SELECT table_schema AS "DB_NAME", SUM(size) "DB_SIZE", SUM(fragmented_space) APPROXIMATED_FRAGMENTED_SPACE_GB FROM (SELECT table_schema, table_name, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) AS size, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2)
    AS fragmented_space FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ) AS TEMP GROUP BY DB_NAME ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

mysql> SELECT table_schema DB_NAME, table_name TABLE_NAME, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) SIZE_GB, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2) APPROXIMATED_FRAGMENTED_SPACE_GB from information_schema.tables
    WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

これら 2 つのクエリから取得したデータベースサイズを記録し、Amazon RDS の Amazon CloudWatch メトリクスと比較します。そうすると、データ使用量が原因でストレージがいっぱいになっていないかどうかを確認できます。

一時テーブル

InnoDB ユーザーが作成したテンポラリテーブルとディスク上の内部テンポラリテーブルは、ibtmp1 という名前の一時テーブルスペースファイルに作成されます。場合によっては、一時テーブルスペースファイルが MySQL データディレクトリの ibtmp2 にまで拡張されることもあります。

**ヒント:**一時テーブル (ibtmp1) が過剰なストレージを使用している場合は、DB インスタンスを再起動してスペースを解放します。

オンラインの DDL 操作では、次の目的で一時ログファイルを使用します。

• 同時 DML を記録する
• インデックス作成時に一時ソートファイルを作成する
• テーブル再構築時の一時中間テーブルファイルを作成する (一時テーブルがストレージを占有できるようにするため)

**注:**InnoDB テーブルスペースのファイルサイズは、MySQL バージョン 5.7 以降、または MySQL 8.0 以降でのみクエリできます。

InnoDB 一時テーブルスペースを見つけるには、次のクエリを実行します。

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibtmp%';

グローバルテンポラリテーブルスペースデータファイルが占有しているディスク容量を再利用するには、MySQL サーバーまたは DB インスタンスを再起動します。詳細については、MySQL Web サイトの「Internal Temporary Table Use in MySQL」を参照してください。

InnoDB テーブルスペース

MySQL は、クエリが介在しているために削除できない内部一時テーブルを作成することがあります。これらの一時テーブルは、info_schema 内の「tables」という名前のテーブルの一部ではありません。詳細については、MySQL ウェブサイトの「Internal temporary table use in MySQL」を参照してください。

次のクエリを実行して、これらの内部一時テーブルを検索します。

mysql> SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%#%';

InnoDB システムテーブルスペースは、InnoDB データディクショナリのストレージ領域です。データディクショナリに加えて、二重書き込みバッファ、変更バッファ、および元に戻すログも InnoDB システムテーブルスペースにあります。さらに、テーブルが (テーブルごとのファイルまたは一般的なテーブルスペースではなく) システムテーブルスペースに作成されている場合、テーブルスペースにはインデックスとテーブルデータが含まれる場合があります。

次のクエリを実行して InnoDB システムテーブルスペースを検索します。

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibdata%';

**注:**このクエリは MySQL バージョン 5.7 以降、または MySQL 8.0 以降で実行されます。

システムテーブルスペースのサイズを増やした後は、それを減らすことはできません。ただし、すべての InnoDB テーブルをダンプし、そのテーブルを新しい MySQL DB インスタンスにインポートすることは可能です。システムテーブルスペースが大きくなるのを避けるには、テーブルごとのファイルテーブルスペースの使用を検討してください。詳細については、MySQL Web サイトの「File-per-table tablespaces」を参照してください。

Innodb_file_per_table を有効にすると、各テーブルは独自のテーブルスペースファイルにデータとインデックスを格納します。そのテーブルで OPTIMIZE TABLE を実行すると、(データベースとテーブルの断片化から) スペースを再利用できます。OPTIMIZE TABLE コマンドは、テーブルの新しい空のコピーを作成します。次に、古いテーブルのデータが 1 行ずつ新しいテーブルにコピーされます。このプロセス中に、新しい .ibd テーブルスペースが作成され、スペースが再利用されます。このプロセスの詳細については、MySQL ウェブサイトの「OPTIMIZE TABLE statement」を参照してください。

**重要:**OPTIMIZE TABLE コマンドは、COPY アルゴリズムを使用して、元のテーブルと同じサイズの一時テーブルを作成します。このコマンドを実行する前に、十分なディスク容量があることを確認してください。

テーブルを最適化するには、次のコマンド構文を実行します。

mysql> OPTIMIZE TABLE <tablename>;

または、次のコマンドを実行してテーブルを再構築することもできます。

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

バイナリログ

Amazon RDS インスタンスで自動バックアップを有効にすると、DB インスタンスでもバイナリログが自動的に有効になります。これらのバイナリログはディスクに保存され、ストレージ容量を消費しますが、バイナリログの保持設定ごとに削除されます。インスタンスのデフォルトのバイナリログ保持値も「Null」に設定されています。つまり、ファイルはすぐに削除されます。

ストレージ容量不足の問題を回避するには、Amazon RDS for MySQL で適切なバイナリログ保持期間を設定してください。バイナリログが保持されている時間数は、mysql.rds_show_configuration コマンドの構文で確認できます。

CALL mysql.rds_show_configuration;

この値を小さくしてログの保持期間を短くすることで、ログが使用する容量を減らすこともできます。値が NULL の場合、ログはできるだけ早く消去されます。アクティブインスタンスにスタンバイインスタンスがある場合は、スタンバイインスタンスの ReplicaLag メトリクスを監視します。ReplicaLag メトリクスは、アクティブインスタンスでのバイナリログ処理中、またはスタンバイインスタンスでのリレーログ処理中に発生する遅延を示します。

アクティブインスタンスにスタンバイインスタンスがある場合は、スタンバイインスタンスの ReplicaLag メトリクスを監視します。ReplicaLag メトリクスは、アクティブインスタンスでのバイナリログの削除とスタンバイインスタンスのリレーログの削除中の遅延を示します。消去またはレプリケーションの問題があると、これらのバイナリログは時間が経つにつれて蓄積され、追加のディスク容量を消費する可能性があります。インスタンスのバイナリログの数とファイルサイズを確認するには、SHOW BINARY LOGS コマンドを使用します。詳細については、MySQL ウェブサイトの「SHOW BINARY LOGS statement」を参照してください。

DB インスタンスがレプリケーションスタンバイインスタンスとして機能している場合は、次のコマンドを使用してリレーログ (Relay_Log_Space) 値のサイズを確認します。

SHOW SLAVE STATUS\G

MySQL ログ (一般ログ、スロークエリログ、エラーログ)

Amazon RDS for MySQL には、データベースのモニタリングに使用できるログ (一般ログ、スロークエリログ、エラーログなど) が用意されています。エラーログはデフォルトで有効になっています。ただし、一般ログとスロークエリログは、RDS インスタンスのカスタムパラメータグループを使用して有効化できます。スロークエリログと一般ログが有効化されると、MySQL データベース内の **slow_log ** テーブルと general_log テーブルに自動的に格納されます。スロークエリ、一般ログ (「FILE」タイプ)、およびエラーログのサイズを確認するには、データベースログファイルの一覧を表示します。

スロークエリログテーブルと一般ログテーブルがストレージを過剰に使用している場合は、ログテーブルを手動でローテーションしてテーブルベースの MySQL ログを管理します。古いデータを完全に削除してディスク容量を再利用するには、次のコマンドを 2 回続けて呼び出します。

mysql> CALL mysql.rds_rotate_slow_log;
mysql> CALL mysql.rds_rotate_general_log;

**注:**テーブルには、ログの正確なファイルサイズが表示されません。slow_log と general_log の log_output の値が「Table」ではなく「File」になるようにパラメーターを変更します。

Amazon CloudWatch を使用して Amazon RDS DB インスタンスをモニタリングすることもベストプラクティスです。FreeStorageSpace メトリクスで CloudWatch アラームを設定すると、ストレージスペースが特定のしきい値を下回るたびにアラートを受け取ることができます。最後に、DB インスタンスの空き容量が少なくなるたびに通知を受け取るように CloudWatch アラームを設定して、FreeStorageSpace メトリクスをモニタリングします。詳細は、「CloudWatch アラームを作成して Amazon RDS の空きストレージ容量をモニタリングし、ストレージがいっぱいになる問題を防ぐにはどうすればよいですか?」を参照してください。

また、Amazon RDS ストレージ自動スケーリング機能を使用して容量を自動的に管理することもできます。ストレージの自動スケーリングでは、データベースストレージを手動でスケールアップする必要はありません。Amazon RDS のストレージ自動スケーリングの詳細については、「Amazon RDS DB インスタンスのストレージを使用する」を参照してください。

---

関連情報

予想よりも多くのストレージを使用している Amazon RDS for MySQL DB インスタンスの問題を解決する方法を教えてください。