MySQL - InnoDB チューニング！

当方、今まで MySQL での膨大なデータ量のデータベース管理では、高速化重視でストレージエンジンを MyISAM に設定して運用していました。
しかし、更新処理が多いデータベースでしたので、考え方を安全・整合性重視の InnoDB に戻しました。

当然ながら、InnoDB は安全性重視のためデフォルトの設定では更新箇所が多くなる等の理由で MyISAM に比べて速度が遅いです。
InnoDB の場合、チューニングして使用するのが半ば常識でもありますし。。。

0. 前提条件

• MySQL 5.5.31 での作業を想定。
  （5.1 系や 5.6 系、MariaDB でもだいたい同じであるが、若干異なる部分もある）
• ストレージエンジン InnoDB の仕組みについてある程度理解できている。
• 使用するマシンの搭載メモリは 4GB.

1. 設定サンプル

当方の設定例を掲載する。
以下は、ダンプファイルインポート時に高速化するための設定（InnoDB 関連部分のみ抜粋）。通常使用時は、コメントを参考に変更する。

File: /etc/mysql/my.cnf

# InnoDBデータファイルのパス
innodb_data_file_path = ibdata1:1G:autoextend

# InnoDBテーブルスペースをテーブル単位で作成
innodb_file_per_table

# InnoDBテーブルスペース自動拡張サイズ
innodb_autoextend_increment = 64

# InnoDBのデータやインデックスをキャッシュするメモリ領域
innodb_buffer_pool_size = 1G

# InnoDBの内部データなどを保持するための領域
innodb_additional_mem_pool_size = 20M

# InnoDBの書込み要求に使用されるバックグラウンドスレッドの数
innodb_write_io_threads = 8

# InnoDBの読取り要求に使用されるバックグラウンドスレッドの数
innodb_read_io_threads = 8

# InnoDBの更新ログを記録するメモリ領域
innodb_log_buffer_size = 32  # 通常時:8程度, データインポート時:32程度

# InnoDBの更新ログを記録するディスク上のファイル
innodb_log_file_size = 256M

# flush logの設定(0:なし、1:ディスク、2:メモリ、)
innodb_flush_log_at_trx_commit = 2  # 通常時:1(デフォルト), データインポート時:2 or 0

# ダブルライトバッファへの書き込みをスキップ
skip_innodb_doublewrite  # データインポート時のみ指定(通常時は指定しない)

次項より各項目についての説明をする。

1-1. innodb_data_file_path について

InnoDBデータファイルのパス・初期サイズを file_name:file_size[:autoextend[:max:max_file_size]] で指定する。
明示的に指定しなければ、ibdata1:10M:autoextend を指定したことになる。

• autoextend :テーブルスペースに空き領域が無くなったら自動で拡張する。
• max:max_file_size :自動拡張データファイルの最大値。

デフォルトのままだと、すぐに 10M を使い尽くしてしまい自動拡張が発生してしまう。
データファイルの大きさが予め分かる場合は、余裕を持った値を設定しおけば自動拡張が発生せず高速化につながる。
ただし、膨大なデータベースであったり、テーブルが多数あるようなデータベースなら、次項のようにテーブル単位でテーブルスペースを作成するように設定するほうがパフォーマンスがよくなる。

1-2. innodb_file_per_table について

テーブルスペースをテーブル単位で作成する。
この項目を設定しない場合は、全てのテーブルスペースが１つにまとまってしまう。
膨大なデータベースであったり、テーブルが多数あるようなデータベースなら、テーブル単位でテーブルスペースを作成するように設定するほうがパフォーマンスがよくなるし、扱いやすい。
ただし、運用の途中でこの設定を行った場合は、以降に作成したテーブルのみが別のテーブルスペースになるので注意！（既存のテーブルについても適用させるなら、一旦データをエクポートしておいて、データベースを作り直し、再度データをインポートする、というような作業が必要になる）

1-3. innodb_autoextend_increment について

InnoDBデータファイル拡張時のサイズを MB 単位で指定する。
明示的に指定しなければ、8 を指定したことになる。
通常、前述の innodb_data_file_path とセットで指定する。

1-4. innodb_buffer_pool_size について

InnoDBのデータやインデックスをキャッシュするメモリ領域サイズを指定する。
通常、搭載メモリの50～80%程度を指定するが、データベースサーバ以外の環境ではシステムに不具合が出ない程度に小さく設定する方がよいようだ。

1-5. innodb_additional_mem_pool_size について

内部データなどを保持するための領域。
大量に割り当てる必要もない。不足した場合にはエラーログに警告メッセージが出力される。

1-7. innodb_write_io_threads について

InnoDBの書込み要求に使用されるバックグラウンドスレッドの数を指定する。

1-8. innodb_read_io_threads について

InnoDBの読取り要求に使用されるバックグラウンドスレッドの数を指定する。

1-5. innodb_log_buffer_size について

InnoDBの更新ログを記録するメモリ領域サイズを指定する。
通常、バッファはトランザクションがコミットされる時か毎秒ディスクにフラッシュされるので、あまり大きく設定する必要はない。
データインポート時には、大きく設定しておくと高速できるようだ。

1-6. innodb_log_file_size について

InnoDBの更新ログを記録するディスク上のファイルサイズを指定する。
1MB < innodb_log_file_size < innodb_buffer_pool_size / innodb_log_files_in_group(Default:2) < 4GB(32bit環境の場合) になるように設定するといいらしい。
innodb_log_file が一杯になると、メモリ上の innodb_buffer_pool 内の更新される部分のデータを、ディスク上のInnoDBデータファイルに書き込む仕組みになっているので、innodb_log_buffer_size と併せて調整することでパフォーマンスが向上する。
innodb_buffer_pool_size の４分の１程度の値がよいだろうか？また、ログファイルのサイズを大きくすればするほど、クラッシュリカバリに長時間を要してしまうので注意する！

また、この innodb_log_file_size の値を変更した場合は、古いログファイル(“ib_logfile0”, “ib_logfile1”)を削除しないと、MySQL の起動に失敗するので注意する！

1-7. innodb_flush_log_at_trx_commit について

flush logの設定を行う。
明示的に指定しなければ、1 を指定したことになる。

• 0 ・・・ メモリ上のログバッファは１秒に１回ディスク上のログファイルに書き込まれる。フラッシュはログファイル上で行われるが、トランザクションコミットの際には何も行われない。
• 1 ・・・ 各トランザクションコミットの時にログファイルに書き込まれ、フラッシュはログファイル上で行われる。
• 2 ・・・ メモリ上のログバッファはコミット毎にログファイルに書き込まれるが、フラッシュはそこでは行われない。しかし、この時もログファイル上でのフラッシュは１秒に１回行われる。１秒に１回のフラッシュは、処理スケジュールの発行のため100%保証されたものではないということに注意する。

通常は、1 でよいが、データインポートの高速化が要求される場面では 0 か 2 にするのがよいだろう。

1-8. skip_innodb_doublewrite について

ダブルライトバッファへの書き込みをスキップする場合に指定する。
デフォルトでは、InnoDBは全てのデータを２回書き込むようになっている。１回目はダブルライトバッファへ、２回目は実際のデータファイルへ。
データインポート等で安全性より高速性を重視する場合には、ダブルライトバッファへの書き込みをスキップする設定にする。

2. 参考サイト

• MySQL :: MySQL 5.5 Reference Manual :: 14.3.2 Configuring InnoDB

---

ダンプデータインポート時、MyISAM で５分程度かかっていたものが、チューニングしていない InnoDB だと何時間もかかっていました。
チューニングすることで MyISAM とほぼ変わらない時間でダンプデータインポートが完了しました。（速度は実測してないが体感で）

一方通常使用時は、ダンプデータインポート時に比べ格段にレコード更新の量が少ないので、今のところ全く不具合は感じていません。

以上。