私の Amazon RDS for MySQL DB インスタンスが想定よりも多くのストレージを使用しているのはなぜですか?

所要時間2分

Amazon Relational Database Service (Amazon RDS) for MySQL DB インスタンスが予想以上のストレージを使用しています。このような問題が発生しているのはなぜですか? ディスクストレージはどのように最適化できますか?

解決方法

FreeStorageSpace Amazon CloudWatch メトリクスを使用して、RDS DB インスタンスの利用可能なストレージの容量をモニタリングできます。ただし、FreeStorageSpace は DB インスタンスがどのようにストレージを消費しているのかについては説明していません。

以下の戦略を使ってストレージ容量を解放します。

OPTIMIZE TABLE を実行する

テーブルはアクティブに使用されていないスペースをいくらか消費しますが、Amazon RDS はいずれにせよそのスペースをテーブルに割り当てます。innodb_file_per_table がオンになっている (デフォルトでオンになっている) 場合は、OPTIMIZE TABLE を使用してその容量を解放できます。OPTIMIZE TABLE は InnoDB、MyISAM、および ARCHIVE テーブルで動作します。Amazon RDS は OPTIMIZE TABLE コマンドを受け入れますが、実際には代わりに ALTER TABLE... FORCE コマンドを実行します。これが発生すると、「テーブルが最適化をサポートしていません。代わりに、再作成 + 分析を実行します」のような警告メッセージが表示されます。詳細については、OPTIMIZE TABLE に関する MySQL ドキュメントを参照してください。

フラグメンテーションをチェックするには、次のようなクエリを実行します。

SELECT
 table_name,
 data_length,
 max_data_length,
 index_length,
 data_free
FROM
 information_schema.tables 
WHERE table_schema='schema_name'
;

テーブルに割り当てられていても実際には使用されていない空き領域は、data_free 列で確認できます。OPTIMIZE TABLE を使用すると、この容量を解放できる場合があります。テーブルが Amazon RDS のデフォルト innodb_file_per_table 構成設定に従って個別の表領域に作成されている場合、OPTIMIZE TABLE を使ってこの容量を解放できる可能性があります。詳細については、テーブルごとのファイルに対するテーブルスペースの MySQL ドキュメントを参照してください。

アプリケーションテーブルのストレージを削減する

DB インスタンス上のアプリケーションテーブルによって使用されているストレージの量を確認するには、以下のようなクエリを実行します。

SELECT 
 table_schema,
 SUM(data_length + index_length + data_free)/1024/1024 AS total_mb,
 SUM(data_length)/1024/1024 AS data_mb,
 SUM(index_length)/1024/1024 AS index_mb,
 SUM(data_free)/1024/1024 AS free_mb,
 COUNT(*) AS tables,
 CURDATE() AS today 
FROM 
 information_schema.tables
 GROUP BY table_schema
 ORDER BY 2 DESC
;

DB インスタンスにある最大のアプリケーションテーブルを見つけるには、以下のようなクエリを実行します。

SELECT 
 table_schema,
 table_name,
 (data_length + index_length + data_free)/1024/1024 AS total_mb,
 (data_length)/1024/1024 AS data_mb,
 (index_length)/1024/1024 AS index_mb,
 (data_free)/1024/1024 AS free_mb,
 CURDATE() AS today
FROM 
 information_schema.tables
 ORDER BY 3 DESC
;

注意: データベース内のテーブルに 768 バイトを超える可変長列がある場合、個別のデータベースやテーブルで使用されているストレージの合計は計算できません。例えば、これには BLOB、TEXT、VARCHAR、または VARBINARY が含まれます。

バイナリログのストレージを減らす

リードレプリカを追加すると、ソースインスタンスのバイナリログで追加のストレージが使用されます。ソースインスタンスのバイナリログで使用されているストレージ領域は、CloudWatch メトリクス「BinLogDiskUsage」で確認できます。使用領域が大幅に増加している場合、1 つ以上のリードレプリカが同期されていない可能性があります。

一般ログとスロークエリログのストレージを削減または無効化する

一般ログとスロークエリログのパラメータをオフにすると、インスタンスはこれらのログを保存し始めます。また、これらのログのバックアップも保存されます。これらのファイルを更新してディスク使用量を制御するには、「mysql.rds_rotate_general_log」と「mysql.rds_rotate_slow_log」を参照してください。

**注意:**トラブルシューティングに頻繁に使用していないときは、一般的なクエリログとスロークエリログをオフにしてください。

InnoDB システムのテーブルスペースサイズを管理または削減する

システムのテーブルスペースには、InnoDB データディクショナリと取り消し用のスペースとして、最初に 10 MB が割り当てられます。容量が割り当てられた後、ファイルは常に少なくともこのサイズになりますが、長時間に及ぶトランザクションでは、利用可能なストレージがより多く消費される場合があります。

デフォルトでは、Amazon RDS は innodb_file_per_table を 1 に設定します。つまり、各テーブルスペースのデータは独自の .ibd ファイルに保存されます。関連するテーブルで再利用可能とマークされた領域を回復するには、OPTIMIZE TABLE を使用して、テーブルごとのテーブルスペースファイルのサイズを小さくするか、テーブルを削除します。

innodb_file_per_table を 0 に設定すると、すべてのテーブルがシステムのテーブルスペースにも割り当てられます。テーブルやインデックスを削除するか、システムのテーブルスペースに割り当てられているテーブルのデータの削除や切り捨てを行うと、以前に占有されていた領域が再利用可能としてマークされます。ただし、innodb_file_per_table はファイルシステムにスペースを解放しません。

システムテーブルスペースをその場で圧縮することはできないため、現在のデータベースのデータをエクスポートしてください。次に、データを新しいインスタンスにインポートします。ダウンタイムを短縮するには、新しい MySQL インスタンスをソース Amazon RDS インスタンスのレプリカとして設定します。レプリカがソース Amazon RDS インスタンスと同期化されたら、新しいインスタンスに切り替えます。

注意: スナップショットからの復元やリードレプリカの作成では、システムのテーブルスペースから領域を回復できません。どちらの方法でも、ソースのインスタンスストレージボリュームのスナップショットを使用しますが、これにはシステムのテーブルスペースが含まれるためです。