MySQLのInnoDBで肥大化する「ibdata1」をなんとかする方法
2012年08月02日（木）
最近気づいたのですがMySQLで格納しているデータがかなり肥大化していたんです。 このデータは削除されることはないのでどんどん肥大化します。 このデータを最適化するにはメンテナンスが必要です。 そのための方法を少し記載したいと思います。
早速始めます。 まずは肥大化するデータの場所ですが、 僕のXAMPP環境では以下になります。
C:\xampp\mysql\data\ibdata1
大体はこちらにあるんではないでしょうか？ で、それとともに作成されるデータである、
C:\xampp\mysql\data\ib_logfile0 C:\xampp\mysql\data\ib_logfile1
で、このデータの詳細は、 デフォルト設定であれば以下のようになるようです。
ibdata1 という名前の 10M バイトの自動延長とib_logfile0 と ib_logfile1 という名前の 5M バイトのログファイルを作成します。
どんな時に肥大化するのかというと、 MySQLのクエリ処理した際とかにどんどんたまっていくようです。 INSERTやUPDATE、DELETE等ですね！ メンテナンスする点で言えば、以下を実行する事によってメンテナンスできるようです。
mysql> use DB mysql> ALTER TABLE tbl_name ENGINE=INNODB;
ただ、この処理ではファイルの容量は減少しません。 減少させるためには以下の方法で「再作成」という形に持っていかなければいけないようです。
1.全ての InnoDB テーブルをダンプする為に mysqldump を利用してください。
2.サーバを停止してください。
3.全ての存在するテーブルスペース ファイルを削除してください。
4.新しいテーブルスペースを設定してください。
5.サーバを再起動してください。
6.ダンプ ファイルをインポートしてください。
テーブルスペースファイルっていうのがおそらくibdataですね。 こちらを実行するとファイルの容量が減少するようです。
1億件のデータをINSERTやDELETE処理していたら、 僕の環境では18GBまで膨れていたものが2/3ぐらいになりました。
なんとかなりそうですね！
別の方法で、 今回はDB一つに付き一つのテーブルスペースだったんですが、 テーブルごとにテーブルスペースを付与する事もできます。
そのオプションが以下になります。
innodb_file_per_table
これをサーバー起動時に設定しておきます。 この際に作成されるidbはデフラグメント化を行うと減少させることができるらしいです。 ibdataの容量も固定できるのですが、どうやらその上限を超えてしまうとテーブル操作が出来なくなってしまう場合もあるようです…
最後に、 ibdata1は極端な肥大化は「押さえられ」ても、 肥大化事態をなくすことは不可能なようですね。。。 ではまた！！！
