Amazon RDS for MySQL DB インスタンスが想定よりも多くのストレージを使用している理由を教えてください。

所要時間2分

Amazon Relational Database Service (Amazon RDS) for MySQL データベース (DB) インスタンスが予想よりも多くのスペースを使用しています。その理由を理解し、ディスクストレージを最適化したいです。

簡単な説明

Amazon CloudWatch の FreeStorageSpace メトリクスを使用すると、Amazon RDS DB インスタンスで利用可能なストレージスペースを監視できます。FreeStorageSpace メトリクスでは、DB インスタンスのストレージを使用している主体はわかりません。次の方法でストレージの使用状況を把握し、ストレージスペースを回復させます。

解決策

OPTIMIZE TABLE を実行する

RDS for MySQL は、アクティブに使用されていないテーブルにスペースを割り当てます。行を削除すると、InnoDB は再利用可能な空のページを残します。InnoDB は物理テーブルファイルを圧縮しません。innodb_file_per_table が有効な場合は、OPTIMIZE TABLE コマンドを実行するとスペースを回復できます。詳細については、MySQL のウェブサイトで「OPTIMIZE TABLE ステートメント」を参照してください。

InnoDB、MyISAM、および ARCHIVE テーブルに OPTIMIZE TABLE を使用できます。OPTIMIZE TABLE は、Amazon RDS デフォルトの innodb_file_per_table 構成設定に従って、別個のテーブルスペースにテーブルを作成する際に機能します。詳細については、MySQL のウェブサイトで「File-per-table tablespaces」を参照してください。

Amazon RDS は OPTIMIZE TABLE コマンドを受け付けますが、実際には ALTER TABLE...FORCE コマンドを実行します。この場合、次のような警告メッセージが表示されます。

「Table does not support optimize, doing recreate + analyze instead (テーブルは最適化をサポートしていません。代わりに再作成 + 分析を実行してください)」

ALTER TABLE... FORCE コマンドは、テーブルの統計情報も更新します。更新されたテーブル統計に基づいてストレージ使用量を推定するには、次のクエリを実行します。

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

注: schema_name をデータベーススキーマの名前に置き換えてください。

data_free 列には、アクティブに使用されていないテーブルに割り当てられている空きスペースの量が表示されます。

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

アプリケーションテーブルが RDS for MySQL DB インスタンスで使用しているストレージ量を確認するには、次のクエリを実行します。

SELECT
    TABLESPACE_NAME,
    ROUND(DATA_FREE / EXTENT_SIZE, 0) FREE_DATA_MB,
    FREE_EXTENTS FREE_EXTENTS_MB,
    TOTAL_EXTENTS TOTAL_EXTENTS_MB
FROM
    INFORMATION_SCHEMA.FILES;

RDS for MySQL DB インスタンスで最大のアプリケーションテーブルを特定するには、次のクエリを実行します。

SELECT
    TABLESPACE_NAME,
    ROUND(DATA_FREE / EXTENT_SIZE, 0) FREE_DATA_MB,
    FREE_EXTENTS FREE_EXTENTS_MB,
    TOTAL_EXTENTS TOTAL_EXTENTS_MB
FROM
    INFORMATION_SCHEMA.FILES
ORDER BY
    TOTAL_EXTENTS_MB DESC;

注: データベースに 768 バイトを超える可変長の列を持つテーブルが含まれている場合、データベースとテーブルが使用する個々の記憶域を計算することはできません。この制限は、BLOB、TEXT、VARCHAR、および VARBINARY データタイプに含まれます。

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

RDS for MySQL DB インスタンスにリードレプリカを追加すると、ソースインスタンスのバイナリログは追加のストレージを使用します。バイナリログが使用するストレージの量を確認するには、BinLogDiskUsage CloudWatch メトリクスを参照してください。バイナリログでストレージの使用量が増えている場合は、リードレプリカを同期してください。

一般ログとスロークエリログの保存を減らすか無効にする

一般ログとスロークエリログのパラメータをオンにすると、インスタンスはログおよび、そのログのバックアップを保存するようになります。問題のトラブルシューティング時以外は、一般クエリログとスロークエリログを無効にすることをおすすめします。ファイルをローテーションしてディスク使用量を制御するには、mysql.rds_rotate_general_log および mysql.rds_rotate_slow_log を参照します。

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

システムテーブルスペースの初期容量は 10 MB であり、InnoDB データディクショナリと undo スペースを含みます。スペースを割り当てると、このファイルは 10 MB 以上になり、使用可能なストレージをさらに使用する可能性があります。

デフォルトでは、Amazon RDS は innodb_file_per_table を 1 に設定します。この設定では、データベースは各テーブルスペースのデータを独自の .ibd ファイルに格納します。関連するテーブルの再利用可能なスペースを回復するには、OPTIMIZE TABLE を使用して各テーブルのスペースファイルのサイズを変更します。または、テーブルをドロップしてください。

innodb_file_per_table を 0 に設定すると、Amazon RDS はすべてのテーブルをシステムテーブルスペースに割り当てるようになります。テーブルまたはインデックスを削除した場合、またはシステム内の割り当てられたテーブルからデータを削除したり切り捨てたりした場合、テーブルスペースはそのスペースを再利用可能としてマークします。ただし、innodb_file_per_table はファイルシステム用のスペースを解放しません。

システムテーブルスペースを縮小するには、現在のデータベースからデータをエクスポートし、そのデータを新しいインスタンスにインポートします。システムテーブルスペースが存在するインスタンスのシステムテーブルスペースは縮小できません。ダウンタイムを削減するには、新しい RDS for MySQL インスタンスをソースインスタンスのレプリカとして設定します。レプリカをソース RDS インスタンスと同期した後、新しいインスタンスに切り替えます。

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