拡張され続ける InnoDB のデータファイルのサイズを小さくする方法

  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 です.