MySQL　InnoDBストレージエンジンのチューニング（後編）

チューニングの基礎

　それでは、具体的にInnoDBでどこをチューニングするべきかを見ていこう。

バッファプール

　最も基本となるのがバッファサイズの調整だ。ワーキングセットが全てバッファに収まらない限り、バッファプールは大きければ大きいほど良い。その分ディスクアクセスが減るからだ。バッファサイズが小さいと、キャッシュミス時にディスクからReadするのに時間がかかり、I/Oがボトルネックになってしまう。予算のある限りメモリを目いっぱい搭載し、バッファプールに割り当てよう。InnoDBのバッファプールは、innodb_buffer_pool_sizeオプションで設定する。利用可能なメモリは、他の処理に必要な分を除いたすべてをInnoDBのバッファプールに割り当てよう。

　innodb_buffer_pool=32G

　ここで一つ注意がある。innodb_buffer_pool_sizeはバッファプールそのもののサイズを指定するオプションだが、InnoDBはバッファプールのサイズに比例してメモリを消費するオブジェクトが存在する。そのため、innodb_buffer_pool_sizeよりも5～10%ほど多くメモリを消費するということを覚えておこう。

ログの調整

　InnoDBでは、全ての変更はバッファプール上で行われ、後から遅れてバッファプールの内容がテーブルスペースへ書き込まれる。そして、永続性を保証するためにログに更新した内容を記録するようになっている。ログへの更新はシーケンシャルな書き込みになるためテーブルスペースへの書き込みに比べると高速だからだ。

　すると、必然的に「ログファイルおよびバッファプール上には存在するが、テーブルスペースには存在しない」というデータが生じることになる。そのようなデータを含んだページは「ダーティページ」と呼ばれる。当然、MySQLサーバーやOSがクラッシュすると、再起動後にはバッファプール内のデータは全て失われる。ここが重要なポイントなのだが、ダーティページのデータを復元するにはログにデータが存在している必要がある。そのため、どれだけのダーティページが存在できるかは、ログファイルのサイズにかかっている。ダーティページが増えすぎ、ログファイルの空き容量がなくなると、さらに更新を続けるためにはまずダーティページをテーブルスペースへフラッシュする必要がある。そうしなければ新たに更新によってダーティページを生成することができないからだ。

　どの道最終的にダーティページはテーブルスペースへ書きこまれなければならないので、ダーティページをたくさん保持出来ることはそれほどメリットがないと感じられるかも知れない。だが、ダーティページのサイズが大きいということは、次の2つの意味でメリットがある。

• 一気にたくさんのデータが更新された場合、その更新によるディスクI/Oの負荷を先延ばしに（平滑化）できる
• ダーティページがフラッシュされる前に再び更新される確率が上昇し、それにより必要なフラッシュの回数が減る

　従ってログファイルが大きい方が更新性能が高くなるというわけだ。ログファイルのサイズは、innodb_log_file_sizeおよびinnodb_log_files_in_groupで指定する。前者はログファイルひとつのサイズ、後者はログファイルの個数である。MySQL 5.5ではログファイルのサイズは合計で最大4GBまでという制限がある。だが、開発版ではMySQL 5.6.3から512GBまでログファイルを拡張することが可能になっている。大きなバッファプールを割り当てるなら、その分ログファイルも大きなものを割り当てよう。

　意外と見逃しがちなのがログバッファ。まだコミットされていないトランザクションは、最大限ログバッファ内にキャッシュしておくのが望ましい。従って、必要となるログバッファのサイズはワーキングセットサイズではなく、更新の量とディスクの速度によって変化する。アプリからの更新が多いようなら増やしてみて、効果を測定しよう。ログバッファのサイズはinnodb_log_buffer_sizeで指定する。

ログのフラッシュ方式

　トランザクションのコミット時にその内容はログファイルに書き込まれる。その際、デフォルトではディスクに確実に書き込まれるよう、fsyncによりディスクへの同期が行われる。ログの内容がディスクに残っていれば、マシンが突然クラッシュした場合などでもデータが消失することはない。ログへの書き込みはシーケンシャルなのでランダムアクセスと比較すると高速ではあるが、それでもディスクへの同期はとても時間のかかる処理だ。もし同期をしないで済むのなら、InnoDBの更新速度は飛躍的に向上する。

　例えば、レプリケーションのスレーブや準同期レプリケーションのマスターであれば、万が一クラッシュした場合でも他のサーバーからデータの復旧が可能であるため準同期レプリケーションを用いたHA化手法については、以前ブログで紹介したので興味があればそちらを見て頂きたい。参考：漢(オトコ)のコンピュータ道: 最強のMySQL HA化手法 - Semi-Synchronous Replication

　ディスクへの同期をOFFにするには、innodb_flush_log_at_trx_commitをデフォルトの1から変更しよう。