Closed11
AuroraMySQLにてVolumeBytesUsedが急激に増加し続ける
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
事象
- BIツールが発行したSQLがBIツール側の異常終了でトランザクションとして残ってしまった
- トランザクションが残ったことに気づかず放置されていたらVolumeBytesUsedが急激に増加し
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
原因切り分け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;
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
ファイルサイズにて以下の増加率がVolumeBytesUsedと同様に増加
file_name | tablespace_name | engine | total_extents | extent_size | Size in MB |
---|---|---|---|---|---|
./ibdata1 | innodb_system | InnoDB | 634770 | 1048576 | 634770.00000000 |
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
ibdata1とは?
- InnoDBデータ辞書、ダブルライトバッファ、変更バッファ、およびアンドゥログの格納領域
- デフォルトでは、ibdata1という名前の1つのシステム・テーブルスペース・データ・ファイルがデータ・ディレクトリに作成
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
RollbackSegmentHistoryListLengthメトリクス
- コミットされたトランザクションが削除とマークされたレコードを記録する UNDO ログ。これらのレコードは、InnoDB のパージオペレーションによって処理されるようにスケジュールされています。
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
RollbackSegmentHistoryListLengthが増加する要因
- 行が変更されると、変更中のデータの修正前のバージョンが、undo レコードとして undo ログに保存されます。
- InnoDB 履歴リストは、コミットされたトランザクションの undo ログのグローバルリストです。
- MySQL は、トランザクションで履歴が不要になったときに、履歴リストを使用してレコードとログページを削除します。
- 履歴リストが長くなる一般的な原因には次のものがあります。
- 実行時間の長いトランザクション (読み取りまたは書き込み)
- 書き込み負荷が高い
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
実行時間の長いトランザクション調査
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;
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
Auroraでのセッション終了方法
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
VolumeBytesUsedの縮小可否のAWSサポート回答
ご期待に沿えず恐縮でございますが、ibdata1 はシステムテーブルスペースのデータファイルでございまして、削除や縮小はできないものとなっております。そのため VolumeBytesUsed につきましても、残念ながら減少させる方法はございません。
なお、mysqldump クライアントユーティリティを使用して、論理バックアップを実施し、新たな Aurora クラスターを作り直すことで、VolumeBytesUsed が増加する前の状態を再現することは可能かと存じます。
ご不便おかけし申し訳ございませんが、ご理解を賜りますようお願い申し上げます。
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
システムテーブルを削減するには
注意: スナップショットからの復元やリードレプリカの作成では、システムのテーブルスペースから領域を回復できません。どちらの方法でも、ソースのインスタンスストレージボリュームのスナップショットを使用しますが、これにはシステムのテーブルスペースが含まれるためです。
![dehio3](https://res.cloudinary.com/zenn/image/fetch/s--QhYDL4sU--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_70/https://storage.googleapis.com/zenn-user-upload/avatar/eb30c9307a.jpeg)
Aurora mysqldump事例
このスクラップは2023/09/02にクローズされました