MySQL 11 min read

MySQLでDELETEしてもディスクが減らない ― .ibdを縮める手順とbinlogの増え方

MySQL（InnoDB）で古いデータを DELETE しても、ディスクの使用量は減りません。表の .ibd ファイルの大きさは DELETE の前と同じままです。空いた領域は同じ表の次の INSERT で使われますが、OS には返りません。

この記事の数値は MySQL 8.4.6 で測りました（innodb_file_per_table=1、binlog 有効）。

まず見る（結論）

  • .ibd を縮めるには表を作り直す。OPTIMIZE TABLE か ALTER TABLE ... ENGINE=InnoDB のどちらでも同じ結果だった
  • 作り直しは、表を使っている長いトランザクションが終わるまで始まらない
  • DELETE は binlog も増やす。ディスクを空けたいなら SHOW BINARY LOGS も確かめる
  • 全行消してよいなら TRUNCATE TABLE

DELETE の前後でファイルの大きさを比べる

表 big に 300 万行を入れ、9 割の 270 万行を DELETE しました。

DELETE FROM big WHERE id % 10 <> 0;
Query OK, 2700001 rows affected (52.27 sec)
行数 big.ibd DATA_LENGTH DATA_FREE
投入直後 3,000,000 976M 947,912,704 6,291,456
DELETE 直後 299,999 976M 947,421,184 6,291,456
OPTIMIZE TABLE 後 299,999 120M 108,675,072 5,242,880

DATA_LENGTH と DATA_FREE は information_schema.TABLES の値で、どれも ANALYZE TABLE の直後に取りました。ファイルの大きさは information_schema.INNODB_TABLESPACES の FILE_SIZE でも引けます。

SELECT NAME, FILE_SIZE, ALLOCATED_SIZE
  FROM information_schema.INNODB_TABLESPACES
 WHERE NAME = 'spdb/big';

DELETE 直後は DATA_LENGTH も DATA_FREE もほとんど変わりませんでした。DATA_FREE を見て「空きが無いので作り直しても縮まない」と判断すると読み違えます。

OPTIMIZE TABLE と ALTER TABLE … ENGINE=InnoDB

InnoDB の表に OPTIMIZE TABLE を実行すると、次のメッセージが出ます。

OPTIMIZE TABLE big;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| spdb.big | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| spdb.big | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (8.50 sec)

「対応していない」と出ますが、エラーではありません。表を作り直して ANALYZE した、という意味です。

データを入れ直して同じ 9 割を DELETE し、ALTER TABLE big ENGINE=InnoDB; で作り直した結果も 120M でした（13.41 秒）。作り直しの間は新しいファイルを別に作るので、残る行の分の空きディスクが要ります。

長いトランザクションがあると作り直しが始まらない

別のセッションでトランザクションを開いて big を読んだままにして、ALTER TABLE big ENGINE=InnoDB; を実行すると、ALTER は待ち状態になりました。

SHOW PROCESSLIST;
+-----+------+-----------+------+---------+------+---------------------------------+-------------------------------+
| Id  | User | Host      | db   | Command | Time | State                           | Info                          |
+-----+------+-----------+------+---------+------+---------------------------------+-------------------------------+
| 415 | root | localhost | spdb | Sleep   |    6 |                                 | NULL                          |
| 416 | root | localhost | spdb | Query   |    3 | Waiting for table metadata lock | ALTER TABLE big ENGINE=InnoDB |
+-----+------+-----------+------+---------+------+---------------------------------+-------------------------------+

（2行を抜粋）415 がトランザクションを開いたままのセッションです。Sleep で何もしていないように見えても、トランザクションが終わるまで表のメタデータロックを持っています。作り直す前に、information_schema.innodb_trx で古いトランザクションが残っていないかを確かめます。見つかったら、そのトランザクションを終わらせるか、接続を KILL してから作り直します（KILL された側の未コミットの更新はロールバックされます）。

DELETE で binlog と undo も増える

DELETE した行は binlog にも書かれます。270 万行の DELETE で、binlog は 849,778,290 バイトから 1,581,928,888 バイトに増えました（約 730MB）。

SHOW BINARY LOGS;
SELECT @@binlog_expire_logs_seconds;

binlog は binlog_expire_logs_seconds（既定 30 日）を過ぎるまで残ります。レプリカやバックアップが必要としていないことを確かめてから、PURGE BINARY LOGS BEFORE NOW(); で消せます。検証の最後に実行すると、データディレクトリは 4.9G から 496M になりました。

DELETE の前後で、undo 表スペースの innodb_undo_001 も 32MB から 160MB に増えました。

SELECT NAME, FILE_SIZE FROM information_schema.INNODB_TABLESPACES
 WHERE SPACE_TYPE = 'Undo';

innodb_undo_log_truncate は既定で ON になっており、innodb_max_undo_log_size（既定 1GB）を超えた undo 表スペースは自動で切り詰められます。今回の 160MB はこの値に届かないので、切り詰められずに残ります。

全行消すなら TRUNCATE、表ごと要らないなら DROP

TRUNCATE TABLE big; の後、big.ibd は 128K になりました。DROP TABLE big; の後はファイルごと無くなりました。どちらも DELETE と違って行ごとの binlog を書きません。

定期的に古い行を消す表なら、日付でパーティションを切っておきます。古いパーティションを ALTER TABLE ... DROP PARTITION で落とせば、作り直さなくてもファイルごと消えます。

RELATED MySQL