拡張され続ける InnoDB のデータファイルのサイズを小さくする方法
2006-07-07-2: [MySQL]
MySQL でトランザクションを可能にするストレージエンジンとして InnoDB があります．
InnoDB のデータファイルは，MyISAM テーブルと異なって，デフォルトでは ibdata1 というファイルにデータが蓄積されていくとこになります．
MySQL の datadir に自動拡張する 10 MB の ibdata1 ファイルが 1 つと、5 MB の ib_logfile ログファイルが 2 つ作成されます
- 7.5.3. InnoDB 起動オプション
http://dev.mysql.com/doc/refman/4.1/ja/innodb-start.html
この ibdata1 は，大量のデータを追加していくと，自動的にサイズを拡張していきます．
ただし，データが削除されても自動的には縮小されません．
InnoDB テーブルのデフラグメント化の方法は，
- 7.5.13.3. InnoDB テーブルのデフラグメント化
http://dev.mysql.com/doc/refman/4.1/ja/innodb-file-defragmenting.html
にあるように，
ALTER TABLE tablename TYPE=InnoDB
で行います．ただし，これを行っても，データファイルのサイズ自体は小さくなりません．
# ls -la ibdata1
-rw-rw---- 1 mysqld mysqld 723517440 Jul 6 20:32 ibdata1
# mysql -uroot -p database
mysql> ALTER TABLE FileInfo TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (3.98 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> quit
# ls -la ibdata1
-rw-rw---- 1 mysqld mysqld 723517440 Jul 6 20:33 ibdata1
でも，容量不足に陥った場合，これでは困りますよね．
そこで，データファイルのサイズ自体を小さくしてしまおうというのが本題です．
InnoDB 関係ののマニュアルを見ていくと以下のような項目があります．
- 7.5.5.3. 複数テーブルスペース - 各テーブルを独自の .ibd ファイルに入れる
http://dev.mysql.com/doc/refman/4.1/ja/multiple-tablespaces.html
これは，簡単に言うと，ibdata1 一つに持たせていたデータを，それぞれのテーブルごとに持たせてしまう方法です．
やり方はいたって簡単で，設定ファイルに innodb_file_per_table を加えてあげるだけです．
[mysqld]
innodb_file_per_table
これで MySQL を再起動すると，設定が有効になってデータが各テーブルごとにもたれるようになります．
いままで，tablename.frm というファイルしかなかったのが，tablename.frm に加えて tablename.idb ができました．
さて，本題ですが，この状態で InnoDB テーブルのデフラグメント化を行ってみます．
# ls -la tablename.ibd
-rw-rw---- 1 mysqld mysqld 754974720 Jul 6 21:01 tablename.ibd
# mysql -uroot -p database
mysql> ALTER TABLE FileInfo TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (5.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> quit
# ls -la tablename.ibd
-rw-rw---- 1 mysqld mysqld 98304 Jul 6 21:04 tablename.ibd
754974720 バイト (720MB) から 98304 バイト (96KB) に劇的にサイズが縮小されているのが分かると思います．
まとめると
- ibdata1 ファイルを使用している場合は，InnoDB テーブルのデフラグメント化を行っても，ファイルサイズ自体は小さくならない．
- innodb_file_per_table オプションで複数テーブルスペースを利用した場合は，InnoDB テーブルのデフラグメント化を行うと，ファイルサイズ自体は小さくなる．
ということが言えると思います．
ただし，MySQL 4.1.1 以上からの機能っぽいです．動作確認環境は 4.1.20 on Solaris 10 です．
