MySQL または MariaDB を実行している Amazon RDS DB インスタンスのストレージを使用している内容を確認する方法を教えてください。

最終更新日: 2020 年 3 月 13 日

MySQL または MariaDB を実行している Amazon Relational Database Service (Amazon RDS) DB インスタンスで自分のストレージを使用している内容を表示するにはどうすればよいですか?

簡単な説明

自分の DB インスタンスで使用している容量の合計を分析します。DB インスタンスの容量は次の用途で使用されます。

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

ストレージ容量を使用している内容を特定すると、ストレージ領域を再利用できます。次に、将来的に容量が不足しないように FreeStorageSpace のメトリクスをモニタリングします。

解決方法

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;

データベースレベルとテーブルレベルで断片化された領域を特定するには、次のクエリを実行します。

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;

注: MySQL 5.7 または MariaDB 10.2.1 以降の DB エンジンバージョンを使用する場合は、information_schema 内で次のクエリを使用することで、より正確なデータベース/テーブルサイズを取得できます。

mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS table_schema,
ROUND(SUM(its.allocated_size)/1024/1024/1024, 2) "size in GB" FROM
information_schema.innodb_sys_tables it INNER JOIN information_schema.innodb_sys_tablespaces its ON it.space =
its.space GROUP BY 1 ORDER BY 2 DESC;
mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS table_schema, ROUND(SUM(its.allocated_size)/1024/1024/1024,2) "size in GB",
ROUND(SUM(t.data_free)/1024/1024/1024,2) "fragmented size in GB"  FROM
information_schema.innodb_sys_tables it INNER
JOIN  information_schema.innodb_sys_tablespaces
its ON it.space = its.space INNER JOIN information_schema.innodb_sys_tablestats istat ON istat.table_id =
it.table_id INNER JOIN information_schema.tables t ON t.table_schema = SUBSTRING_INDEX(it.name,
'/', 1) AND t.table_name = SUBSTRING_INDEX(it.name, '/', -1) GROUP BY 1 ORDER BY 2 DESC;
mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS
table_schema, t.table_name, ROUND(its.allocated_size/1024/1024/1024,2)
"size in GB", ROUND(t.data_free/1024/1024/1024,2) "fragmented
size in GB"  FROM information_schema.innodb_sys_tables
it INNER JOIN information_schema.innodb_sys_tablespaces its ON it.space = its.space INNER JOIN
information_schema.innodb_sys_tablestats istat ON istat.table_id = it.table_id INNER JOIN information_schema.tables t
ON t.table_schema = SUBSTRING_INDEX(it.name, '/', 1) AND t.table_name =
SUBSTRING_INDEX(it.name, '/', -1)  WHERE
t.table_schema NOT IN ('performance_schema', 'mysql', 'information_schema')
ORDER BY 4 DESC;

DB インスタンスで利用できる各バイナリログファイルの名前とサイズを取得するには、次のコマンドを実行します。

SHOW MASTER LOGS;

Amazon RDS コンソールまたは AWS コマンドラインインターフェイス (AWS CLI) を使用して、スロークエリ―ログ、一般ログ、エラーログのサイズを確認します

注意: デフォルトで、スロークエリーログと一般ログは MySQL データベース上の slow_loggeneral_log テーブルに格納されています。テーブルからはログの正確なファイルサイズを知ることができません。そこで、パラメータを変更しますlog_outputslow_loggeneral_log でのパラメータ値を、File にします (Table ではなく)。

場合によっては、MySQL が内部一時テーブルを作成し、介入中のクエリがあるため、削除できないことがあります。これらの一時テーブルは information_schema 内の「tables」には含まれません。詳細については、MySQL ドキュメントの 「Internal Temporary Table Use in MySQL」をご参照ください。次のクエリを実行して、これらの内部一時テーブルを見つけます。

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

MySQL バージョン 5.5 と 5.6 では InnoDB テーブルスペースのファイルサイズをクエリできませんが、MySQL バージョン 5.7 以降ではファイルサイズをクエリできます。次のクエリを実行し、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%';

次のクエリを実行し、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%';

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

SHOW SLAVE STATUS\G

ストレージ領域を再利用する

ユーザーデータベースとテーブルで断片化した領域を再利用するには、InnoDB テーブルを最適化します。InnoDB テーブルの場合、OPTIMIZE TABLEALTER TABLE... にマッピングされます。 FORCE は、テーブルを再構築して、インデックス統計を更新し、クラスター化したインデックスの未使用領域を解放しするものです。詳細については、OPTIMIZE TABLE ステートメントに関する MySQL ドキュメントをご参照ください。OPTIMIZE TABLE の次のコマンド例をご覧ください。

mysql> OPTIMIZE TABLE foo;

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

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

重要: このコマンドは COPY ALGORITHM を使用して、元のテーブルと同じサイズの一時テーブルを作成します。このコマンドを実行する前に、使用可能なディスク領域が十分あることを確認してください。

MySQL 5.7 以降では、一時テーブル (ibtmp1) が過剰にストレージ領域を使用すると、DB インスタンスを再起動して領域を解放します。

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

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

今後のストレージ容量不足の問題を防止する

バイナリログの保持期間は、バイナリログが DB インスタンス上にどの程度の期間保存されるかを決定します。現在のバイナリログの保持期間を確認するには、次のコマンドを実行します。

mysql> CALL mysql.rds_show_configuration;

この値を減らしてログの保存期間を短縮することで、ログが使用する領域量を減らすことも可能です。NULL 値は、可能な限り迅速にログを消去することを意味します。アクティブなインスタンスのスタンバイインスタンスがある場合、スタンバイインスタンスの ReplicaLag メトリクスをモニタリングします。このメトリクスで、アクティブなインスタンスでのバイナリログ処理やスタンバイインスタンスでのリレーログ処理が遅れるためです。

使用可能なストレージに急激な減少が見られた場合には、SHOW FULL PROCESSLIST; コマンドをデータベースで実行し、DB インスタンスレベルで継続的にクエリを確認します。このコマンドを実行すると、アクティブな接続すべての詳細、各接続で実行されているクエリ、現在の状態がそのクエリでどのくらい続いているかを確認できます。長期間アクティブになっているトランザクションを表示するには、SHOW ENGINE INNODB STATUS; または SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX のコマンドを実行します。そして、出力を確認します。

最後に、Amazon CloudWatch アラームを設定して FreeStorageSpace メトリックをモニタリングし、DB インスタンスの空き容量が低下したときに通知を受け取れるようにします。