shimxmemo

メモをのこすよ!

古いバージョンのInnoDBだとTRUNCATE TABLEしてもデータファイルのサイズが減らない件

久々のエントリー。

 

仕事中ちょっと問題になった事がありまして

サーバの容量が問題になって"容量不足だ(゚Д゚)ゴルァ!!"って警報が鳴り止まず

不要なテーブルを消して容量確保せねば…!

けどDROPしてプログラムいじるのめんどいなあ…

( ゚д゚)ハッ! TRUNCATEなら…それでもTRUNCATEならきっとなんとかしてくれる…!

TRUNCATEだん。dfっと、あれ…空き容量増えてねえ…( ゚д゚)

という事がありました。

TRUNCATEって挙動的にはDROP->CREATEでOPTIMIZEなんざいらねえよ!
だと思っていたので、データファイルも消してくれると信じてたんですが、そんな事全然なかったんだぜ!

 
一体どうなってるんだと、まずはTRUNCATEの公式マニュアルを確認
 
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上のファイルサイズは減らない。