InnoDBのファイルサイズ管理

最近、InnoDBのデータ領域（テーブルスペース）が成長してしまって元に戻すことが出来ない場合の対処についてよく質問されるので、今日はテーブルスペースが成長することへの対策について説明しよう。（ここのところMySQLネタが続いているが、Planet MySQL日本語版を意識しているわけではないのであしからず！！＜＜ホントかよ？！＞俺）

InnoDBのテーブルスペースが成長してしまうのは、ズバリ自動拡張しているからである。テーブルスペースに対して何もオプションを指定しないと、デフォルトでは次のような設定と同じテーブルスペースが作成される。

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

サイズは10MBしかないが、自動拡張するのである。自動拡張してしまうと何が問題なのかというと、データが増えた場合にファイルシステムの空き領域を使い切ってしまうことである。そして、ひとたび成長してしまったデータファイルを小さくする方法はない。大きくなりすぎたらTHE ENDである。（内臓脂肪を彷彿とさせると思うのは俺だけだろうか？！）一度mysqldumpを使ってデータをダンプして、ファイルを削除、リストアする以外には。なのでテーブルスペースがファイルシステムを圧迫してしまうと厄介なのである。

元からInnoDBのテーブルスペースへ割り当てられるサイズが決まっている場合には、次のようにサイズを固定するといい。

[mysqld]
innodb_data_file_path=ibdata1:300G

もちろんこの場合、300GBを使い切ってしまうとテーブルへの更新が出来なくなるが、ファイルシステムの空き領域を使い切ってしまうことがなくなるので、空き領域をSHOW TABLE STATUSで監視しながら運用するといいだろう。空き領域が足りなくなりそうになったら、ディスクを増強してテーブルスペースを大きくすればいい。

ただし、このようにしていると別の面で管理が大変になってしまう。テーブルスペースを大きくしたい場合に、別のファイルを足さないといけないからである。次のように。

[mysqld]
innodb_data_file_path=ibdata1:300G;/data2/ibdata2:700G

これではファイルシステムのレイアウトとテーブルスペースのPATHが一体化しているので、ディスクを買い換えて大きなディスクを一つ買った・・・という場合に管理が面倒になる。（つまり、別に必要ないのにわざわざ/data2ディレクトリを作らないといけないわけである。）一度mysqldumpをとってデータを入れ直せばibdata1:1000Gというような指定が可能だが、わざわざダンプ/リストアするのは時間が掛かってしまうので無駄である。もう少し柔軟な運用はないか？ということで利用できるのが、テーブルごとにテーブルスペースを作成する方法である。オプションは次のようになる。

[mysqld]
innodb_data_file_path=ibdata1:1G
innodb_file_per_table

innodb_file_per_tableの設定をしておくと、テーブルごとにテーブルスペースが作成される。拡張子は.ibdである。.ibdファイルは要求に応じて自動拡張するのだが、テーブルを削除すると対応する.ibdファイルも削除されるのである。つまり、一度テーブルスペースが大きくなってしまってからでもテーブルを削除すればファイルシステムの空き領域を増やすことが可能になるのである。これは運用上とてもメリットがあるだろう。

テーブルごとにテーブルスペースを作成する場合でも、共有テーブルスペース（ibdata1）が必要になる点に注意しよう。ここには、各テーブルのディクショナリ情報や ロールバックセグメントなどが格納される。一度に大量のアップデートを行った場合などには大きなロールバックセグメントが必要になる。その際に共有テーブルスペースが自動拡張しないよう、サイズを決めうちにしておくといいだろう。

ただし、この運用で気をつけないといけないのは共有テーブルスペースを削除してはならない！！ということである。確かに共有テーブルスペースにテーブルのデータは入っていないが、各.ibdのメタデータを格納しているので、共有テーブルスペースがないとInnoDBが.ibdファイルを見つけられないのである。また、他のサーバへ単体の.ibdファイルだけを持っていっても使えない。（無理矢理使う方法はあるけど一般的にはオススメしない。）つまり、.ibdファイルと共有テーブルスペースはセットなのである。