Aurora MySQLの使用容量の8割(約400 GB)を占めるテーブルがあったのですが、過去データについてはCSV化してアーカイブできていたので、過去レコードを削除してDB容量を解放することにしました。

レコード削除だけで容量が解放されると思っていたら予想外に手間がかかったので、備忘録として詳細を残します。

テーブル使用容量の確認

以下のクエリで各.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;

注意点

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を参照することを推奨されました。

テーブルサイズは 390 GB" という情報がinformation_schema."tables"から取得した情報となっており、統計値のため不正確であった可能性が高いと判断しております。
つきましては、今後テーブルサイズ等をご確認いただく際はinformation_schema."files"から情報を取得いただければと存じます。

DB使用容量の確認

以下のクエリでDB全体の使用容量を確認できます。
こちらについても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/%';

DB容量の解放手順

手順は以下の通りです。

レコードを削除する
必要に応じてインスタンスクラスを変更する
OPTIMIZE TABLE <table_name>;を実施する

レコードを削除するだけでは容量は解放されず、OPTIMIZE TABLEまで実施する必要があります。

また、OPTIMIZE TABLE(ALTER TABLE ... FORCE)の操作では、一時的に中間テーブルファイルが作成され、Auroraではこの一時ファイルの格納にローカルストレージが使用されます。
Auroraのローカルストレージはインスタンスクラスに依存するのですが、私の使用するdb.r6g.xlargeではローカルストレージが80 GBしかなく、削除レコードの量の方が大きかったので、db.r6g.8xlarge(640 GB)に一時的にスケールアップしました。

`OPTIMIZE TABLE`の注意点

250 GBほどのレコード削除後にOPTIMIZE TABLEを行ったところ、約130分(約2時間)かかりました。OPTIMIZE TABLE中はテーブルロックがかかるので、サービスの夜間停止などが必要になる場合があります。ちなみにレコード削除は数日にわけて行ったのですが、合計で約15時間かかりました。