mysqlでinnodbのデータ領域をテーブルごとに分割する

デフォルトだとinnodbのテーブル領域はibdata1とかにまとめられるけど、innodb_file_per_tableを設定するとテーブルごとにわかれる。


innodb_data_file_pathで指定するibdataはサイズが増えていく(サイズを固定にすれば当然増えることはない)けど、減らすことはできない(dump, restoreしない限り)。

けどテーブルを削除すれば該当のibdファイルは削除されるので運用は楽とのこと。

あとinnodbのファイルサイズ制限にひっかからなくなるので、(ディスクの空き容量があれば) innodbの残りサイズを気にしなくて良い。

innodbのファイルサイズの上限を気にしない方法にはautoextendを設定するのもありだけど。

innodb_data_file_path=ibdata1:10M:autoextend

それでも単一のファイルが肥大化していくのはなんだかなーって思うので、やはりテーブルごとに分けたい気がします。特に複数のユーザが利用する共有サーバでは。

innodbの空き容量確認

  • mysql 5.0以前
    • show table statusのcommentのところに表示
  • mysql 5.1
    • show table statusのData_free

innodb_file_per_table

innodb_file_per_tableを設定すると、show table statusでinnodbのテーブルを確認するとData_freeが0になってる(無制限?)

ただし、テーブルスペースごとにファイルを作成する場合でも共有のibdataは必要なので、消したりするのはだめとのこと。(各テーブルのメタデータなどが格納されているため)

既存のmysqlサーバでも設定を入れると、次に作成されるinnodbテーブルから分割されて共有のファイルには作成されなくなる。けどさすがに運用中のDBを触るのは怖いなー。

パフォーマンスの低下?

適当にググって斜め読みしただけですが、テーブルの数が数千/数万とかに増えるとinnodb_file_per_tableを設定するとパフォーマンスが低下するかもだそうです。(出典忘れた)