MySQLのInnoDBで肥大化する「ibdata1」をなんとかする方法

Mysql-1

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_logfile0ib_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は極端な肥大化は「押さえられ」ても、 肥大化事態をなくすことは不可能なようですね。。。   ではまた!!!    

スポンサードリンク

運営サービスPR

スポンサードリンク

運営サービスPR