パフォーマンスチューニング
多くあるパラメータの中で最初に決めるべき項目は、バッファープールのサイズである innodb_buffer_pool_size となる。バッファープールはデータファイルとログファイルが書き込まれる順序を調整するためのバッファであるが、ディスクアクセスを低減させるキャッシュとしての役割が大きい。よってバッファプールが大きいほど、データ参照時の無用なファイルアクセスを減らすことができ、性能向上に役立つ。
チューニングのセオリーとしては、ほかのバッファに割り当てるべき空きメモリを除き、バッファのサイズが最大になるようにメモリを割り当てるのがよい。このようにバッファプールを割り当てた場合は innodb_flush_method を O_DIRECT に設定し、InnoDB により行われたデータファイルへの I/O に対し、OS がファイルシステム上でキャッシュすることを防ぐようにする。ファイルシステムキャッシュはすぐに大きくなり、システムの空きメモリを枯渇させてしまう。ファイルシステムキャッシュも不要なディスクアクセスを減らすという意味では InnoDB バッファプールと同様であるが、InnoDB のデータをキャッシュする場合には InnoDB バッファプールを使用した方が効率は良い。
なお、MySQL 5.5 からは、ロック競合を避ける目的で、バッファプールのインスタンス数を増やせるようになっている。MySQL サーバはマルチスレッドにんっているため、バッファプールの操作には排他制御が必要となる。同時実行スレッド数が増えると、バッファプールのミューテクスに対するアクセスも集中する。バッファプールを分割することでアクセスが分散され、それぞれのスレッドが同時に同じバッファプールインスタンスへアクセスする確率が下がることになる。そのため CPU コア数が多い場合には、インスタンス数を増やすことを検討したい。インスタンス数を決めるオプションは innodb_buffer_pool_instances となる。
mysql> SHOW VARIABLES LIKE 'innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
10 rows in set (0.00 sec) なお、MySQL 5.7 からは innodb_buffer_pool_size をオンラインでリサイズできるようになっている。
具体的には以下のように変更、確認ができる。
mysql> SET GLOBAL innodb_buffer_pool_size = 200 * 1024 * 1024;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 180816 7:42:25. |
+----------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+
1 row in set (0.01 sec) 次に重要なのはログサイズとなる。InnoDB のログファイルサイズは固定であり、同じ領域が何度も使い回される挙動となる。InnoDB はログファイルサイズに応じたダーティページを持っているが、ダーティページをどこまで許容できるかは、突発的な更新不可に対応するために重要となる。ログファイルを使い切ると、新たに REDO ログ (MTR) を生成するには古いダーティページがフラッシュされ、チェックポイントが生成するのを待つ必要がある。更新時にログファイルだけにデータを書き込めば良い場合と比べ、ダーティページのフラッシュが必要になるケースは非常に遅い。
十分大きなログ (Innodb_log_file_size x innodb_log_files_in_group) を持つことで、非常に遅いフラッシュを先延ばしにすることができる。ただし REDO ログのサイズが大きくなると、クラッシュリカバリの時間が伸びてしまうという副作用が生じる。そもそもダーティページはバッファプールよりも大きくはできないので、バッファプール以上に REDO ログを大きくしても意味はない。
mysql> SHOW VARIABLES LIKE 'innodb_log_file%';
+---------------------------+----------+
| Variable_name | Value |
+---------------------------+----------+
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
+---------------------------+----------+
2 rows in set (0.00 sec) しかしながら、遅いフラッシュを先延ばしにするのにも限界がある。フラッシュの速度よりも速い更新が継続的に行われるとダーティページは増え続け、やがて REDO ログを使い切ってしまう。その状態でさらに更新を行おうとするとダーティページのフラッシュとチェックポイントが完了するのを待つ必要がある。つまり最終的に InnoDB が実行できる更新の限界性能を決めるのはフラッシュの速度となる。
よって、システムの計画時にはフラッシュの速度が更新速度の平均値を下回ってはならないことに注意する必要がある。InnoDB はディスクに合わせて自動でフラッシュの速度を調整することはしないので、innodb_io_capacity をディスクの IOPS と同程度に設定する必要がある。