MySQL道普請便り

第55回innodb_file_per_tableオプションについて

innodb_file_per_tableというオプションはご存知でしょうか。このオプションはMySQL4.1.1から導入され、MySQL 5.6.6とそれ以降ではデフォルトでONになっているオプションです。

innodb_file_per_tableを有効にすることで、InnoDBの各テーブルのデータとインデックスを個別のファイル管理にすることができます。もしこのオプションがOFFの場合、InnoDBの各データはibdata1というシステムテーブルスペースにデータが保持されていきます。以前からMySQLを扱っている方は、このibdata1の肥大化に悩まされたことがあるのではないでしょうか?

今回は、デフォルトONになったためにあまり意識しなくなったかもしれないinnodb_file_per_tableについて、できることを説明したいと思います。

innodb_file_per_tableをONでできるようになること

テーブルをtruncateやdropするときにOSのディスク容量を開放できる

従来のibdata1ではdropやtruncateを利用してデータの削除を行っても、ibdata1ファイルそのものの容量は小さくなりません(ただし、スペースを再利用することはできます⁠⁠。しかし、innodb_file_per_tableをONにすることで、各テーブルの.ibdファイルごと削除するためディスク容量を空けることが可能になります。

また、truncateやdrop処理はinnodb_file_per_tableをONにしたほうが速くなります。これはOFFの場合だと、削除対象のテーブルのibdata1上のデータに対して削除済みとマークしていくため、ibdata1の中を走査する必要があるからです。

データの保存場所を変更することができる

CREATE TABLE ... DATA DIRECTORYという構文を使ってdatadir外部にテーブルのデータを保持することができます。こちらを利用することで、よりI/Oの良いSSDにデータを保持したり、大容量のテーブルのみをHDDに保持するといったことが可能になります。通常はdatadir配下に<database名>/<テーブル名>といった形でデータを保持しますが、この構文を使うことでdatadir側には保存先のファイルパスのみを保持し、外部にibdファイルを保持する形となります。

例としてmysqlのhomeディレクトリ配下にtest_dirを作って、そこにデータを置いてみましょう。

$ pwd
/home/mysql
$ mkdir test_dir

上記のディレクトリにデータを保持するようにしてみます。

mysql > use d1
Database changed
 
mysql [d1]> CREATE TABLE t1(id int,message varchar(256)) DATA DIRECTORY = '/home/mysql/test_dir';
Query OK, 0 rows affected (0.64 sec)

mysql [d1]> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `message` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin DATA DIRECTORY='/home/mysql/test_dir/' |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

作成されたテーブルとして、通常のdatadir配下には<テーブル名>.frmファイルと<テーブル名>.islファイルが生成されています。

$ cd <datadir配下/database名>
$ ls | grep t1
t1.frm
t1.isl
$ cat t1.isl
/home/mysql/test_dir/d1/t1.ibd

このislファイルにはidbファイルのパスが記載されています。

ibdファイルがあるか確認してみましょう。

$ ls /home/mysql/test_dir/d1/
t1.ibd

たしかにt1.ibdファイルを確認することができました。

OPTIMIZE TABLE

OPTIMIZE TABLE構文を利用することで、各テーブルの最適化を行うことができます。OFFになっている場合は全てのテーブルが1つにまとまったファイルに保存されてしまうため、OPTIMIZE TABLEを行っても領域の解放がされません。ただし、空いている領域はInnoDBで必要に応じて再利用されます。より詳しくは、第35回 OPTIMIZE TABLEでテーブルを最適化するをご参照ください。

Transportable Tablespaceを利用して他のDBにテーブルをコピーする

トランスポータブルテーブルスペースという機能を使って、別のサーバからテーブルスペースのコピーを実施することができます。今回はd1データベースにt1テーブルを作成し、これを別のサーバーのd2データベースにコピーしたいと思います。

まず、サンプルデータとして、d1データベースにt1テーブルを作成し、データを挿入します。

mysql [d1]> use d1
Database changed
mysql [d1]> CREATE TABLE t1(id INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql [d1]> INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

続いて別のサーバのデータベースd2にd1と同じテーブル構造のテーブルを作成し、テーブルスペースを破棄します。

mysql2 [d2]> use d2
Database changed
mysql2 [d2]> CREATE TABLE t1(id INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.29 sec)
mysql2 [d2]> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.06 sec)

再びd1データベースに戻り、データをエクスポートします。

mysql [d1]> FLUSH TABLES t1 FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

エクスポートされたデータを確認し、ibdファイルとcfgファイルを対象のサーバにコピーします。

$ cd /data/mysql1/d1/
$ ls -alt
合計 224
-rw-r----- 1 mysql mysql    60  9月 25 20:36 2017 db.opt
-rw-r----- 1 mysql mysql   364  9月 25 21:01 2017 t1.cfg
-rw-r----- 1 mysql mysql  8556  9月 25 20:51 2017 t1.frm
-rw-r----- 1 mysql mysql 98304  9月 25 20:57 2017 t1.ibd

$ scp -p t1.{cfg,ibd} <対象サーバー>:/data/mysql2/d2/

続いて、t1テーブルのロックを開放します。

mysql [d1]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

最後にd2データベースで取得したデータをインポートします。

mysql2 [d2]> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected (0.15 sec)

mysql2 [d2]> SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

トランスポータブルテーブルスペースを利用してデータコピーすることができました。

ただし、こちらの機能を実行するにはいくつか制約があるため、事前に公式ドキュメントにて、対象のテーブルが実行できるか確認してから実行してください。

innodb_file_per_tableをONにした時のデメリット

innodb_file_per_tableをONにした場合には、いくつかのデメリットもあります。

  1. ファイルディスクリプタの使用量が増える
  2. dropテーブルやtruncateテーブルを実行した場合、バッファプールのサイズが大きいとロックがかかってしまう

とくに2番目は、データを削除した時にバッファプールの中身を洗い替えするために起きてしまいます。これを引き金に障害となるケースも考えられるため、十分注意しましょう。

まとめ

今回は、innodb_file_per_tableをONにすることでできることについて紹介しました。MySQL 5.6.5とそれ以前のバージョンをお使いの場合でダンプリストアなどを行う機会があれば、ぜひONにして利用するようにしましょう。

おすすめ記事

記事・ニュース一覧