Zenn
Closed11

AuroraMySQLにてVolumeBytesUsedが急激に増加し続ける

dehio3dehio3

事象

  • BIツールが発行したSQLがBIツール側の異常終了でトランザクションとして残ってしまった
  • トランザクションが残ったことに気づかず放置されていたらVolumeBytesUsedが急激に増加し
dehio3dehio3

原因切り分けSQL

テーブルデータ量の確認

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free ) / 1024 / 1024 "Free Space in MB",
count(*) "Table count"
FROM information_schema.TABLES
GROUP BY table_schema;

ファイルサイズの確認

SELECT file_name, tablespace_name, engine, total_extents, extent_size, (total_extents*extent_size)/1024/1024 as "Size in MB" from information_schema.files;
dehio3dehio3

ファイルサイズにて以下の増加率がVolumeBytesUsedと同様に増加

file_name tablespace_name engine total_extents extent_size Size in MB
./ibdata1 innodb_system InnoDB 634770 1048576 634770.00000000
dehio3dehio3

RollbackSegmentHistoryListLengthが増加する要因

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/proactive-insights.history-list.html

  • 行が変更されると、変更中のデータの修正前のバージョンが、undo レコードとして undo ログに保存されます。
  • InnoDB 履歴リストは、コミットされたトランザクションの undo ログのグローバルリストです。
  • MySQL は、トランザクションで履歴が不要になったときに、履歴リストを使用してレコードとログページを削除します。
  • 履歴リストが長くなる一般的な原因には次のものがあります。
    • 実行時間の長いトランザクション (読み取りまたは書き込み)
    • 書き込み負荷が高い
dehio3dehio3

実行時間の長いトランザクション調査

SELECT a.trx_id, 
      a.trx_state, 
      a.trx_started, 
      TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open", 
      a.trx_rows_modified, 
      b.USER, 
      b.host , 
      b.db, 
      b.command, 
      b.time, 
      b.state 
FROM  information_schema.innodb_trx a, 
      information_schema.processlist b 
WHERE a.trx_mysql_thread_id=b.id
  AND TIMESTAMPDIFF(SECOND,a.trx_started, now()) > 10 
ORDER BY trx_started;
dehio3dehio3

VolumeBytesUsedの縮小可否のAWSサポート回答

ご期待に沿えず恐縮でございますが、ibdata1 はシステムテーブルスペースのデータファイルでございまして、削除や縮小はできないものとなっております。そのため VolumeBytesUsed につきましても、残念ながら減少させる方法はございません。
なお、mysqldump クライアントユーティリティを使用して、論理バックアップを実施し、新たな Aurora クラスターを作り直すことで、VolumeBytesUsed が増加する前の状態を再現することは可能かと存じます。
ご不便おかけし申し訳ございませんが、ご理解を賜りますようお願い申し上げます。

dehio3dehio3

システムテーブルを削減するには

https://repost.aws/ja/knowledge-center/rds-mysql-storage-optimization

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

このスクラップは2023/09/02にクローズされました