MySQLのInnoDBデータファイル「ibdata1」の最適化

MySQLのInnoDBは、デフォルトではibdata1ファイルにデータを蓄積していきます。そして、このibdata1ファイルは、データが蓄積されるごとに自動拡張を続けます。

もし InnoDB 設定オプションをまったく指定しなければ、MySQL は MySQL データディレクトリに、ibdata1 という名前の 10M バイトの自動延長とib_logfile0 と ib_logfile1 という名前の 5M バイトのログファイルを作成します。

MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 9.2 InnoDB 設定

また、ibdata1のファイルサイズは、データを削除しても縮小されることはないので、ファイルサイズは、メンテナンスしないとどんどん拡大していきます。

ということで、自分の環境のibdata1のデータが、かなり大きくなっていて、(最近いろいろテストしてるからなあ・・・。)ディスク容量を圧迫しつつあるので、最適化を行います。

# cd /var/lib/mysql
# ls -l
-rw-rw---- 1 mysql mysql 82881544192  4月 24 14:12 ibdata1

ibdata1の最適化

基本的なメンテナンスとしては、

mysql> use DB
mysql> ALTER TABLE tbl_name ENGINE=INNODB;

とすればよいです。

ALTER TABLE 操作を定期的に実行すればインデックス スキャンの速度を上げる事ができます:

ALTER TABLE tbl_name ENGINE=INNODB

それによって MySQL はテーブルを再構成します。

MySQL 5.1 リファレンスマニュアル :: 13.5.14.3 テーブルのデフラグメント化

ただしこの場合もファイルが縮小されるわけではありません。ファイルサイズも小さくしたい場合には、以下の方法を用いる必要があります。

現在、データ ファイルをテーブルスペースから削除する事はできません。テーブル スペースのサイズを小さくするには、この手順を利用してください:
1.全ての InnoDB テーブルをダンプする為に mysqldump を利用してください。
2.サーバを停止してください。
3.全ての存在するテーブルスペース ファイルを削除してください。
4.新しいテーブルスペースを設定してください。
5.サーバを再起動してください。
6.ダンプ ファイルをインポートしてください。

MySQL 5.1 リファレンスマニュアル :: 13.5.7 InnoDB データとログ ファイルの追加と削除

私の環境で、この方法を用いて再構築を行うと、約半分程の容量になりました。

# ls -l
-rw-rw---- 1 mysql mysql 41307602944  4月 27 19:53 ibdata1

InnoDBデータ保存先の設定

このへんの仕様がどうも不便だなあ。という時には、以下のように設定する方法もあります。

まとめると
* ibdata1 ファイルを使用している場合は,InnoDB テーブルのデフラグメント化を行っても,ファイルサイズ自体は小さくならない.
* innodb_file_per_table オプションで複数テーブルスペースを利用した場合は,InnoDB テーブルのデフラグメント化を行うと,ファイルサイズ自体は小さくなる.

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

複数のテーブル領域を有効にするには、サーバーの起動時に –innodb_file_per_table オプションを指定します。

複数のテーブル領域を有効にすると、InnoDB はテーブルが属するデータベースディレクトリ内にある、それ自体の tbl_name.ibd ファイルに、それぞれの新しく作成されたテーブルを格納します。

MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 9.2.1 Per-Table テーブル領域を利用する

ただし、この設定を行う場合、マニュアルに以下のようなことが掲載されているので、注意が必要です。

注意

InnoDB は、共有テーブル領域に内部データ辞書と取り消しログを置くので、いつもそれを必要とします。.ibd ファイルは InnoDB の作動に充分ではありません。

MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 9.2.1 Per-Table テーブル領域を利用する

カテゴリー: サーバ・インフラのこと   タグ:   この投稿のパーマリンク

トラックバック

MySQLのInnoDBデータファイル「ibdata1」の最適化 への2件のコメント

  1. odaken より:

    いつもお世話になってまーす。検索してたらたまたま発見しました!

  2. hiroyuki より:

    おお!なんという偶然!

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

*

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>