最近、2007年にPeter Zaitevが書いた「InnoDBパフォーマンス最適化の基礎」という記事を見つけた。これは素晴らしい記事で、読んでいると、MySQLとPercona Serversそして今日利用可能な全ての基盤技術に関して、6年近くの間に何が変わってきたのかを見直してみたいと思わせるものだ。
本当にたくさんのことが変わったものだ！この記事では、InnoDBの使用に効果的なパラメータの多くに、特にパフォーマンスの観点から焦点を当てる。私はサポートエンジニアで、Percona SupportではInnoDBパラメータの適切なサイズに関する質問がたくさん寄せられている。この記事が同じような疑問や問題を抱えている人の助けになればうれしい。
ハードウェア
巨大なデータセットを持っている場合、今日では数百ギガ単位あるいはテラバイト級のメモリを持っていることも驚くべきことではなくなった。MySQLは、それなりのパフォーマンスを得るためには大量のメモリを必要とする。アクセスが頻繁なデータセットやインデックス、更新処理などをキャッシュすることによって、InnoDBはレスポンスを高速化し、よりよい方法でディスクIOを効率化する。CPUの観点から言うと、複数のコアを持つ高速なプロセッサは、より良いスループットを出せる。32コア、64コア、さらにそれ以上のコアを持つCPUは一般的になりつつあり、最新のMySQLバージョンでは、以前よりもそういったCPUに最適化できるようになっている。ストレージの点では、費用対効果の大きいSSDが伝統的な回転式のディスクを置き換えつつある。現在も多くの処理に対してRAID 10が推奨される構成だが、RAIDコントローラがSSDのパフォーマンスを引き出せるものかどうか、ボトルネックになりはしないか確認する必要がある。さらにIOPSが必要なら、PCI-eのFlashドライブも多く出回っている。
オペレーティングシステム
LinuxはハイパフォーマンスなMySQLサーバにおいて最も一般的なOSだ。Linuxでは、EXT4やXFSといった最新のファイルシステムを最新のカーネルと組み合わせて使おう。それぞれ制限と長所がある。例えば、XFSは巨大ファイルの削除が高速で、一方EXT4は高速なSSDではより良いパフォーマンスが出る。選定の前にベンチマークをしよう。このブログ記事で、EXT4がXFSよりどう高速か見てみよう。innodb_file_per_tableが有効でテーブルがたくさんあるならnoatimeとnodiratimeオプションが使えるが、それほど利点は大きくない。LinuxのデフォルトIOスケジューラはCompletely Fair Queing(CFQ)だが、多くの場合、NoopあるいはDeadlineの方がよいだろう。swappinessを0にするのは、MySQLの専用サーバにおいてはスワップを減らすために推奨される。MySQLにとってスワップは最悪なことで、メモリへのキャッシュの効果もなくしてしまう。スワップに関しての詳しい情報は、このブログ記事を参考にしてほしい。
InnoDB設定
MySQL 5.5からはInnoDBがデフォルトのストレージエンジンになったので、以前と比べてパフォーマンスのためには以下のパラメータはより重要になっている。特に重要なのは以下のものだ。
innodb_buffer_pool_size: InnoDBはバッファプールに強く依存しており、正しく設定する必要があり、十分なメモリを割り当てなければならない。一般的には、使用可能なメモリの70%から80%程度がよい。より詳しく言うと、データよりも大きいRAMを持っているなら、データサイズよりも少し大きく設定しておくことによって、データセットの増大とそれに伴うInnoDBバッファサイズの再調整ができるようになる。さらに、Percona Server 5.1あるいはPercona Server 5.5を使っているなら、InnoDBバッファのスケーラビリティが改善されている。詳しくはこちらを参照して欲しい。innodb_buffer_pool_instances: InnoDBバッファプールを複数持つ機能は、InnoDB 1.1とMySQL 5.5から導入された。MySQL 5.5では、デフォルト値は1だったが、MySQL 5.6では8に変更された。innodb_buffer_pool_instancesは、1(最低)から64(最高)に設定できる。innodb_buffer_pool_instancesを有効にすると、グローバルミューテックスの衝突を減らすことで高負荷な並列処理に対応できるようになる。バッファプールのダンプとリストア : バッファプールの中身を保存したりリストアしたりするこの機能によって、再起動を高速化する。Percona Server 5.5で最初に導入された機能で、これについてはこちらで読むことができる。また、Vadimのこの機能のベンチマークもこの記事で確認できる。Oracle MySQLでは5.6から使用できるようになり、起動停止時にダンプとリストアを行うようにするには、innodb_buffer_pool_dump_at_shutdownとinnodb_buffer_pool_load_at_startupパラメータをONに設定する。
innodb_log_file_size: InnoDBトランザクションログを十分大きくしておくことは、高速で安定した書き込みに極めて重要な意味がある。ただしこれは、クラッシュ時のリカバリが遅くなるということでもある。しかし、5.5からの素晴らしい改善により、あまり大きな問題ではなくなっている。MySQL 5.6ではデフォルト値が5MBから50MBに変更されているが、多くの処理にとってはまだまだ小さい値だ。また、MySQL 5.6では、innodb_log_file_sizeが再起動時に変更されていると、MySQLは起動時に自動的に新しいサイズにログをリサイズしてくれる。全てのログファイルを合わせたサイズは、以前の4GBから、MySQL 5.6で512GBまで大きくなった。最適なログファイルサイズを導くには、こちらの記事を参照して欲しい。innodb_log_buffer_size: InnoDBは、変更されたデータのレコードを、メモリ上のログバッファに書き込み、トランザクションがコミットされる前にログがディスクに書き込まれないようにすることで、大きなトランザクションのディスクIOを節約する。大量の巨大なblobを書き込むのでなければ、4MBから8MBがよいだろう。innodb_flush_log_at_trx_commit: innodb_flush_log_log_at_trx_commitが1の時、ログバッファはトランザクションがコミットされるたびにディスク上のログファイルに書き出され、データの整合性を最大限保つ。しかし、これはパフォーマンスへの影響がある。この値を2にすると、ログバッファはトランザクションのコミットごとにOSのファイルキャッシュへ書き出される。これにより、ACIDを意識しないのであればパフォーマンスを最適化・高速化できるが、OSのクラッシュ時には数秒分のトランザクションが消えてしまうか可能性がある。innodb_thread_concurrency: InnoDBへの改善がなされたため、デフォルト値(0)のままにしておいてエンジンに並列度を制御させるのが推奨だ。並列度で問題が出た場合は値を調整することもできる。この場合の推奨値はCPU数の2倍にディスクの数を足したものだ。動的変数なのでMySQLを再起動せずに反映させられる。innodb_flush_method: DIRECT_IOはIO負荷を軽減する。ダイレクトIOではキャッシュをしないが、O_DIRECTに設定した場合、バッファプールとファイルシステムキャッシュの両方にバッファされないようになる。ハードウェアRAIDコントローラにライトキャッシュのバッテリが付いているなら設定しよう。innodb_file_per_table: MySQL 5.6ではこれはデフォルトでONになっている。共有表領域が巨大になるのを防ぎ、テーブルを削除した時にその領域を再利用できるようにするために、通常はこの設定が推奨される。表領域が分かれていると、Xtrabackupで一部だけバックアップするスキームが使えるという利点もある。
これ以外にも、InnoDBにはたくさんの改善が施されている。Percona Server 5.5やOracle MySQL 5.6では特にそうだ。永続化されたオプティマイザの統計は、XtraDBでinnodb_use_sys_stats_tableを有効にすることで使用できるPercona Server 5.5で最初に導入された機能の一つだ。今ではこの機能はOracle MySQL 5.6にも含まれている。MySQL 5.6では、統計情報はmysql.innodb_index_statsとmysql.innodb_table_statsという2つのテーブルに保存される。ここにあるクエリ計画は、より正確で一貫性のあるものだ。詳しくはドキュメントを参照して欲しい。また、Percona Server 5.5では、MariaDBから移植されたスレッドプールの機能も持っている。これについてもドキュメントを参照して欲しい。これに関連して、Vadimのスレッドプールについてのブログ記事も読むことをおすすめする。
Percona Serverはフリーでオープンソースだ。拡張され簡単に使い始められるOracle MySQLの代替品であり、ここで挙げたいくつかの機能はPercona Serverだけで使うことができる。
他にも色々とチューニングしたいパラメータがあるかもしれないが、この記事ではInnoDBだけに焦点を当ててみた。
InnoDBのためのアプリケーションチューニング
特にMyISAMを使っていた背景がある人にとっては、あなたのアプリケーションと一緒に使いたい変更点がいくつかあるだろう。まずはじめに、一貫性とより良いパフォーマンスを得るために、更新の際にはトランザクションを使うこと。次に、アプリケーションが何らかの更新をする際には、起こりうるであろうデッドロックをハンドリングできるようにすること。第三に、テーブル構造を見直し、クラスタインデックスとしてのプライマリキーや、全インデックスにプライマリキーを含んだり(これによってプライマリキーを短く保てる)、プライマリキーによる高速検索(JOINで使ってみよう)、非圧縮の巨大なインデックスなど、InnoDBの機能の優位性を得られるようにすることだ。
結論
MySQLサーバのパフォーマンス最適化に関連する、ほとんど全ての一般的かつ重要なInnoDBのパラメータやOSに関する調整、ハードウェアについて書いてきた。言及された全ての値を正しく設定することで、間違いなくMySQLサーバのパフォーマンスを全体的に引き上げることができるはずだ。
