mysqlのinnodb_file_per_table設定

 

innodb_file_per_table設定とは

innodbのデータ領域をテーブルごとに分解するmysqlの設定

設定方法

以下の設定を追加

vim /etc/my.conf
innodb_file_per_table = 1

してmysql再起動!

設定確認

以下のコマンドで確認

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

ONであればちゃんと設定されていること!

 

innodb_file_per_table ON設定のメリット

テーブルスペースをテーブルごとに分離することでI/Oの性能を向上することができる

 

innodb_file_per_table ON設定のデメリット

 

データとインデックスが同様のテーブルスペースに保存されること

あるテーブルだけI/Oが偏った場合

innodb_file_per_table設定をOFFするより容量が増えてしまう(理由は

あまりにも大量のテーブルスペースになるとリカバリ処理に相当な時間がかかる

容量が増えてしまうとバックアップする際相当な時間がかかる

その理由はリカバリをする際、MySQLクラッシュリカバリプロセスはテーブルスペースのヘッダーを読みに行く必要がある。その際テーブルスペースが大量に分解されている一つ一つ読みに行くので時間かかるのだ

データを削除しても即容量が減らない

その理由はinnodb_file_per_tableをONにすると共有の表領域であるibdata1も増えてしまうからである。(OPTIMIZE TABLEを実行することで解放することは可能)

参考:http://yakst.com/ja/posts/68

ibdata1の容量が増えてしまうのは大雑把にいって共通的に使われる内容があるからだ(詳しくは別途整理してポストする)

その他

InnoDBでinnodb_file_per_table が ON のとき以下のディレクトリに二種類のファイルがある

cd /usr/local/mysql/var/db名/

テーブル名.frm テーブル定義が追記されているファイル
テーブル名.ibd データおよびインデックスを保存するファイル

 

もうちょっと細かく
パターン1

A,B,Cというテーブルスペースが分かれていた状態で

/etc/my.cnfから「innodb_file_per_table 」ラインを消して再起動すると

これから生成するテーブルは共有テーブルスペースファイルの内部に生成される(たとえばDというテーブルを作ると共有テーブルスペースに格納される)

設定を変更する前、A,B,Cというテーブルスペースには続けて接続することが可能

パターン2

A,B,Cというテーブルが共有テーブルスペースにあって

/etc/my.cnfから「innodb_file_per_table 」ラインを追加して再起動すると

これから生成するテーブルはそれぞれ.ibdファイルで生成される。

また、設定変更する前共有テーブルスペースにあったA,B,Cというテーブルに接続することも可能

 

参考したページ:

http://tommylee.egloos.com/1247442

http://d.hatena.ne.jp/hogem/20100115/1263485420

http://www.mk-mode.com/octopress/2013/05/28/mysql-innodb-tablespace-for-a-table/

http://wnstjqdl.tistory.com/20

http://dev.classmethod.jp/server-side/db/rds-mysql-innodb_file_per_table/

 

http://www.mysqlkorea.com/gnuboard4/bbs/board.php?bo_table=community_03&wr_id=3818

コメントを残す

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