innodb_file_per_table について
MySQL 4.1 から innodb_file_per_table が導入され、個々のテーブルを個別のファイルに格納できるようになった。これにより、クラッシュリカバリの成功率の向上や、個別の InnoDB テーブルのコピー (トランスポータブルテーブルスペース) などが可能になった。
14.5.2 InnoDB File-Per-Table モード
一方で、クラッシュリカバリを遅延させてしまう効果もある。
テーブルのデータを格納するファイルは .ibd という拡張子を持つファイルとなっている。
$ sudo ls /var/lib/mysql/world/
City.frm City.ibd Country.frm Country.ibd CountryLanguage.frm CountryLanguage.ibd db.opt InnoDB 内部ではそれぞれのデータファイルが個別のテーブルスペースという扱いになっており、個別のテーブルスペース ID が割り振られている。MTR に記録される操作では、どの部分が更新されたかという情報をテーブルスペース ID とページ ID の組み合わせで表現している。しかし、どのファイル上のデータを更新するべきかはテーブルスペース ID から判断ができず、MySQL 5.6 までは、起動時にすべての .ibd ファイルをスキャンしてそのヘッダに記録されているテーブルスペース ID を読み取る必要があった。テーブル数が多い場合、これは高コストな処理となる。
Amazon RDS のドキュメントでは以下のように記載されている。
MySQL DB インスタンスの一般的な DBA タスク #InnoDB テーブルスペースの操作によるクラッシュ回復時間の短縮
(以下抜粋)
多数のテーブルがある場合 (標準 (磁気) または汎用 SSD ストレージを使用するときは 1,000 以上のテーブル、プロビジョンド IOPS ストレージを使用するときは 10,000 以上のテーブル) は、innodb_file_per_table パラメータを 0 に設定する必要があります。このパラメータを 0 に設定すると、個別のテーブルスペースは作成されないため、データベースのクラッシュ回復時間を短縮できます。
また、MySQL 5.6 では CREATE TABLE において DATA DIRECTORY 句が InnoDB でも指定できるようになり、さらに状況が悪化した。
DATA DIRECTORY 句は .ibd ファイルを作成するディレクトリを指定するものとなる。DATA DIRECTORY を指定しない場合、本来は datadir または innodb_data_file_path に .ibd ファイルが格納される。
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.01 sec) 一方で、DATA DIRECTORY を指定すると元のディレクトリには .ibd ファイルのパスを記録した .isl というファイルのみが残され、これは .ibd ファイルをオープンするために .isl ファイルもオープンしなければならなく、クラッシュリカバリ時のオーバーヘッドをさらに増やす要因となっている。
MySQL 5.7 では REDO ログにどの .ibd ファイルを更新したかという情報が記録されるようになり、起動時に全ての .ibd ファイルをスキャンする必要は無くなった。