古いバージョンのInnoDBだとTRUNCATE TABLEしてもデータファイルのサイズが減らない件
久々のエントリー。
仕事中ちょっと問題になった事がありまして
サーバの容量が問題になって"容量不足だ(゚Д゚)ゴルァ!!"って警報が鳴り止まず
↓
不要なテーブルを消して容量確保せねば…!
↓
けどDROPしてプログラムいじるのめんどいなあ…
↓
( ゚д゚)ハッ! TRUNCATEなら…それでもTRUNCATEならきっとなんとかしてくれる…!
↓
TRUNCATEだん。dfっと、あれ…空き容量増えてねえ…( ゚д゚)
という事がありました。
TRUNCATEって挙動的にはDROP->CREATEでOPTIMIZEなんざいらねえよ!
だと思っていたので、データファイルも消してくれると信じてたんですが、そんな事全然なかったんだぜ!
InnoDB テーブルの場合、InnoDB は、テーブルを参照するいずれかの FOREIGN KEY 制約が存在する場合に行を 1 つずつ削除することによって TRUNCATE TABLE を処理します。
8.2.10. TRUNCATE 構文
http://dev.mysql.com/doc/refman/5.1-olh/ja/truncate.html
知らなかった、外部キー制約つけてるとDELETEになるんですね。
InnoDB テーブルの場合、InnoDB は、テーブルを参照するいずれかの FOREIGN KEY 制約が存在する場合に行を 1 つずつ削除することによって TRUNCATE TABLE を処理します。
8.2.10. TRUNCATE 構文
http://dev.mysql.com/doc/refman/5.1-olh/ja/truncate.html
ほら、DROPしてCREATEじゃん。おかしいなー。
調べてみると同じような状況の方がいらっしゃった。
"TRUNCATE ibtable;" does not reclaim space when ran with --innodb-file-per-table
http://bugs.mysql.com/bug.php?id=58800
ここに書いてあるリンクを見てみると
Previous versions of InnoDB would re-use the existing .ibd file, thus releasing the space only to InnoDB for storage management, but not to the operating system. Note that when the table is truncated, the count of rows affected by the TRUNCATE command is an arbitrary number.
8.4. TRUNCATE TABLE Reclaims Space
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-truncate.html
今使っているMySQLでは、InnoDB Pluginじゃなくて、組み込みのInnoDB(Plugin化以前のやつだから当然古い)使っているから、これに当たるのかな?
自分の英語力じゃ、but not to あたりがわからん…素直に、「OS上じゃなくてInnoDBのストレージ管理上のスペースのみ解放する」って読めばいいのかな?
Svetaが言っているように、ドキュメントの書き方の問題だったっぽくて、この時修正されているみたい。
とりあえず自分でも試してみる。
試した環境
* CentOS release 5.4 (Final)
※CentOSのバージョン確認は "cat /etc/redhat-release"
* MySQL 5.1.42 innodb_file_per_table有効
※MySQLのバージョン確認は "mysql --version"
* InnoDBはプラグインを使っているかいないか
mysql> select @@innodb_version\G; ERROR 1193 (HY000): Unknown system variable 'innodb_version'エラーが出た。使ってないって事ね。
* テーブル定義(外部キー制約なし)
mysql> show create table CREATE TABLE `hoge` ( `id` varchar(32) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `platform` varchar(8) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `u_id` varchar(32) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `kind` varchar(16) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `description` varchar(32) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `category` varchar(32) CHARACTER SET sjis COLLATE sjis_bin NOT NULL DEFAULT '', `name` varchar(32) NOT NULL, `price` int(11) NOT NULL DEFAULT '0', `entry` datetime NOT NULL, PRIMARY KEY (`id`), KEY `u_id` (`u_id`,`entry`) ) ENGINE=InnoDB DEFAULT CHARSET=sjis;
-- カラム数、カラム名適当に変えてます。
まずはTRUNCATE前の状況を確認
mysql> show table status LIKE 'hoge'\G; *************************** 1. row *************************** Name: hoge Engine: InnoDB Version: 10 Row_format: Compact Rows: 246663 Avg_row_length: 117 Data_length: 28917760 Max_data_length: 0 Index_length: 63242240 Data_free: 5242880 Auto_increment: NULL Create_time: 2012-08-01 12:15:43 Update_time: NULL Check_time: NULL Collation: sjis_japanese_ci Checksum: NULL Create_options: Comment: db# ls -la -rw-rw---- 1 mysql mysql 9112 Aug 1 12:15 hoge.frm -rw-rw---- 1 mysql mysql 100663296 Aug 1 12:16 hoge.ibd
ここでTRUNCATEして、サイズを確認してみる。
mysql> TRUNCATE hoge; mysql> show table status LIKE 'hoge'\G; *************************** 1. row *************************** Name: hoge Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 65536 Data_free: 96468992 Auto_increment: NULL Create_time: 2012-08-01 12:15:43 Update_time: NULL Check_time: NULL Collation: sjis_japanese_ci Checksum: NULL Create_options: Comment: db# ls -la -rw-rw---- 1 mysql mysql 9112 Aug 1 12:15 hoge.frm -rw-rw---- 1 mysql mysql 100663296 Aug 1 12:16 hoge.ibd
減ってない…だと…
InnoDBでは、OPTIMIZEはあんまり必要ない(大量にDELETEした時以外)と思ってたんだけどなー。
OPTIMIZEするかー、と調べてみると
実は、InnoDBにはOPTIMIZE TABLEに相当する機能は実装されておらず、代わりにALTER TABLEが実行される。OPTIMIZE TABLE t1は次のコマンドを実行するのと同じなのである。mysql> ALTER TABLE t1 ENGINE INNODB;大人のためのInnoDBテーブルとの正しい付き合い方。
http://nippondanji.blogspot.jp/2010/09/innodb.html
へー!
ALTER TABLEの挙動はこちらを参照されたし。
ALTER TABLEを上手に使いこなそう。
http://nippondanji.blogspot.jp/2009/05/alter-table.html
一時テーブル作ってデータ入れて終わったら切り替えてるんですねー。
ここではALTERをやってみる
mysql> ALTER TABLE hoge ENGINE INNODB;
からのー
mysql> show table status LIKE 'hoge'\G; *************************** 1. row *************************** Name: hoge Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 65536 Data_free: 0 Auto_increment: NULL Create_time: 2012-08-01 12:17:35 Update_time: NULL Check_time: NULL Collation: sjis_japanese_ci Checksum: NULL Create_options: Comment: db# ls -la -rw-rw---- 1 mysql mysql 9112 Aug 1 12:17 hoge.frm -rw-rw---- 1 mysql mysql 163840 Aug 1 12:17 hoge.ibd
わーい減った!
そしてCreate_timeが変わってるから作り直されてる!
無駄かもしれないけど、DELETEと比較してみよう。
mysql> delete from hoge; Query OK, 246274 rows affected (5.61 sec) mysql> TRUNCATE TABLE hoge; Query OK, 0 rows affected (0.11 sec)
TRUNCATE早すぐる!
やっぱりibdファイル再利用するだけでDELETEとは挙動が違うね。
さらに気になったので、同じデータを使ってMyISAMで試してみた。
* テーブル定義(外部キー制約なし)
mysql> show create table CREATE TABLE `hoge_1` ( `id` varchar(32) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `platform` varchar(8) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `u_id` varchar(32) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `kind` varchar(16) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `description` varchar(32) CHARACTER SET sjis COLLATE sjis_bin NOT NULL, `category` varchar(32) CHARACTER SET sjis COLLATE sjis_bin NOT NULL DEFAULT '', `name` varchar(32) NOT NULL, `price` int(11) NOT NULL DEFAULT '0', `entry` datetime NOT NULL, PRIMARY KEY (`id`), KEY `u_id` (`u_id`,`entry`) ) ENGINE=MyISAM DEFAULT CHARSET=sjis;
-- カラム数、カラム名適当に変えてます。
mysql> show table status LIKE 'hoge_1'\G; *************************** 1. row *************************** Name: hoge_1 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 246274 Avg_row_length: 81 Data_length: 20036160 Max_data_length: 281474976710655 Index_length: 14133248 Data_free: 0 Auto_increment: NULL Create_time: 2012-08-01 12:31:04 Update_time: 2012-08-01 12:31:10 Check_time: 2012-08-01 12:31:12 Collation: sjis_japanese_ci Checksum: NULL Create_options: Comment: db# ls -la -rw-rw---- 1 mysql mysql 20036160 Aug 1 12:31 hoge_1.MYD -rw-rw---- 1 mysql mysql 14133248 Aug 1 12:31 hoge_1.MYI -rw-rw---- 1 mysql mysql 9112 Aug 1 12:31 hoge_1.frm mysql> TRUNCATE hoge_1; mysql> show table status LIKE 'hoge_1'\G; *************************** 1. row *************************** Name: hoge_1 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2012-08-01 12:33:39 Update_time: 2012-08-01 12:33:39 Check_time: NULL Collation: sjis_japanese_ci Checksum: NULL Create_options: Comment: db# ls -la -rw-rw---- 1 mysql mysql 0 Aug 1 12:33 hoge_1.MYD -rw-rw---- 1 mysql mysql 1024 Aug 1 12:33 hoge_1.MYI -rw-rw---- 1 mysql mysql 9112 Aug 1 12:31 hoge_1.frm
ちゃんと減りますなー。
他のバージョンで確認する気力はもうなし。
と思ったんだけど、モヤモヤしたので調べてみた。
* CentOS release 5.4 (Final)
* MySQL 5.5.18 innodb_file_per_table有効
* InnoDBはプラグインを使っているかいないかmysql> select @@innodb_version\G; *************************** 1. row *************************** @@innodb_version: 1.1.8
mysql> show table status LIKE 'hoge'\G; *************************** 1. row *************************** Name: hoge Engine: InnoDB Version: 10 Row_format: Compact Rows: 246450 Avg_row_length: 117 Data_length: 28917760 Max_data_length: 0 Index_length: 63242240 Data_free: 5242880 Auto_increment: NULL Create_time: 2012-08-01 17:56:35 Update_time: NULL Check_time: NULL Collation: sjis_japanese_ci Checksum: NULL Create_options: Comment: db# ls -la -rw-rw---- 1 mysql mysql 9112 Aug 1 17:56 hoge.frm -rw-rw---- 1 mysql mysql 100663296 Aug 1 17:56 hoge.ibd mysql> TRUNCATE hoge; mysql> show table status LIKE 'hoge'\G; *************************** 1. row *************************** Name: hoge Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 65536 Data_free: 0 Auto_increment: NULL Create_time: 2012-08-01 17:56:35 Update_time: NULL Check_time: NULL Collation: sjis_japanese_ci Checksum: NULL Create_options: Comment: db# ls -la -rw-rw---- 1 mysql mysql 9112 Aug 1 17:56 hoge.frm -rw-rw---- 1 mysql mysql 163840 Aug 1 18:03 hoge.ibd
減ってた作りなおされたー!
やーすっきり。
結論
古いバージョンのInnoDB使っていると、TRUNCATE時にibdファイル自体はそのまま再利用されるので、OPTIMIZEなりALTERなりで再生成しない限り、ibdファイルのOS上のファイルサイズは減らない。