不要なデータを削除して Aurora MySQL ストレージを最適化する

Aurora MySQL データベース内のテーブルが、総ストレージの約 80% (約 400 GB) を消費していました。古いデータを CSV ファイルとしてアーカイブできたため、古いレコードを削除してストレージを解放することにしました。

最初はレコードを削除すればストレージ容量が解放されるのではないかと考えましたが、予想よりも複雑であることが判明しました。そのため、今後の参考のために詳細な手順を文書化します。

テーブルのストレージ使用量の確認

次のクエリを使用して、各 .ibd ファイルのサイズを確認できます。

SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" 
FROM information_schema.files 
GROUP BY file_name 
ORDER BY total_extents DESC;

リファレンス: MySQL ドキュメント

重要な注意事項

AWS re:Post はテーブル サイズを確認するために次のクエリを推奨しましたが、ターゲット テーブルの結果は最初のクエリと比較して約 150 GB 小さくなりました。

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';

AWS サポートに問い合わせたところ、information_schema.tables は統計値のみを提供しており、多くの場合不正確であることが確認されました。彼らは、正確なデータを取得するには、information_schema.files を使用することを推奨しました。

テーブルサイズ(390GB)に関する情報はinformation_schema.tablesから取得したものであり、統計データであるため不正確である可能性があります。今後は、テーブル サイズ情報の取得に information_schema.files を使用することをお勧めします。

参考: AWS re:Post

データベースストレージの使用状況を確認する

次のクエリは、データベース全体の使用状況をチェックします。これは、正確性を高めるために、information_schema.files も使用します。

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/%';

データベースストレージを解放する手順

ストレージを解放する手順は次のとおりです:

1. 古いレコードを削除します。
2. 必要に応じてインスタンス クラスを変更します。
3. OPTIMIZE TABLE ; を実行します。

レコードを削除するだけではストレージ領域は解放されません。 OPTIMIZE TABLE を実行してスペースを解放する必要があります。

さらに、OPTIMIZE TABLE (または ALTER TABLE ... FORCE) 操作中に、一時中間テーブル ファイルが作成されます。 Aurora では、これらの一時ファイルはローカル ストレージに保存されます。ローカル ストレージの量はインスタンス クラスによって異なります。私の場合、db.r6g.xlarge インスタンスには 80 GB のローカル ストレージしかなく、削除されたレコードのサイズに対して十分ではありませんでした。そこで、一時的に db.r6g.8xlarge (640 GB) にスケールアップしました。

リファレンス: テーブルの最適化

参照: テーブル変更

参照: InnoDB オンライン DDL スペース要件

参考: Aurora MySQL 一時ストレージ

OPTIMIZE TABLE 使用時の注意

約 250 GB のレコードを削除した後、OPTIMIZE TABLE を実行するのに約 130 分 (約 2 時間) かかりました。 OPTIMIZE TABLE はテーブルをロックするため、ダウンタイムをスケジュールするか、オフピーク時にこの操作を実行する必要がある場合があります。参考までに、すべてのレコードを削除するのに数日に分けて合計約 15 時間かかりました。

以上が不要なデータを削除して Aurora MySQL ストレージを最適化するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。