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

最終更新日: 2019 年 11 月 14 日

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

解決方法

FreeStorageSpace Amazon CloudWatch メトリックを使って RDS DB インスタンス用に利用できるストレージ容量を監視できますが、このメトリックは DB インスタンスがどのようにストレージを消費しているかは説明しません。

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

OPTIMIZE TABLE を実行する

テーブル用の領域の一部は実際に使用されない場合でも割り当てられます。innodb_file_per_table が有効化されている場合 (デフォルトでは有効化)、OPTIMIZE TABLE を使ってその容量を解放できます。OPTIMIZE TABLE は InnoDB、MyISAM、ARCHIVE テーブルに対して機能し、Amazon RDS は OPTIMIZE TABLE コマンドを受け入れますが、Amazon RDS は 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 列で確認できます。テーブルが 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 バイナリログにアクセスする」を参照してください。

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

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

注意: パフォーマンスおよびディスク使用上の問題が起きないように、トラブルシューティングで使用する場合を除いて、一般ログとスロークエリログは無効にしておいてください。

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

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

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

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

インプレースでシステム表領域を縮小することは不可能なので、現在のデータベースのデータをエクスポートしてから、そのデータを新しいインスタンスにインポートします。ダウンタイムを短縮するには、新しい MySQL インスタンスをソース Amazon RDS インスタンスのレプリカとして設定します。レプリカがソース Amazon RDS インスタンスと同期化されたら、新しいインスタンスに切り替えます。手動レプリケーションの詳細については、「Amazon RDS の外部で実行される MySQL または MariaDB インスタンスとのレプリケーション」を参照してください。

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