ダブルライトバッファ
InnoDBは、テーブルスペースを更新中にOSがクラッシュした場合などにデータの不整合が起きないよう、ダブルライトバッファという仕組みを用いている。ダブルライトバッファは、バッファという名前が付いているが、テーブルスペース内に存在する領域だ。InnoDBはテーブルスペースへ実際の更新を行う前に、いったんダブルライトバッファに同じデータを書き込む。その後、改めて該当するページを書き換えるのだ。そうすることで、もしテーブルスペースの更新が中途半端になってしまっても次のようなロジックで不整合を解消することができる。
- ダブルライトバッファへの更新が中途半端になってしまった場合、ダブルライトバッファの内容を破棄する。（テーブルスペースでは不整合はそもそも起きていない。）
- テーブルスペースへの更新が中途半端になってしまった場合、ダブルライトバッファの内容で該当するページを更新する。
当然ながら、ダブルライトバッファへの更新は余計なI/Oを生じさせることになる。ZFSのようにファイルへの更新がアトミック（オール・オア・ナッシング）であることが保証されているファイルシステムを使用している場合には、ダブルライトバッファをOFFにすることでI/Oの帯域を節約することを考えよう。ダブルライトバッファを無効化するには、skip_innodb_doublewriteオプションを使う。
ファイルシステム
InnoDBのI/O性能は、当然ながらその下にあるストレージやファイルシステムの影響を受ける。特に意識すべきなのは、InnoDBが16KBのページ単位でデータを管理しているという点である。そのため、ファイルシステムやストレージのI/Oの単位が16KBになるのが望ましい。RAIDカードやストレージ装置を使用している場合などには、ストライプサイズを16KBに設定するといいだろう。大きなストライプサイズにするとシーケンシャルアクセスは高速になるかも知れないが、16KB単位のI/Oが必要な場合には余分なデータを読み込むことになってしまう。だが、ストレージ装置によっては内部の構造に起因して、ストライプサイズを16KBより大きくした方が性能が出るものもあるかも知れない。もしそういう話があるならストライプサイズを変更してベンチマークをとってみよう。最終的にはInnoDBを利用してベンチマークをとるべきだが、bonnie++などを利用したベンチマークもある程度指標になるだろう。
また、ファイルシステムにもI/Oの単位（ストライプサイズ、チャンクサイズ）を調整するパラメータがあるので調整を行うと良いだろう。また、そもそもファイルシステムの種類によって性能が変化するので、異なるファイルシステムを検証することも必要だ。
普段のInnoDBのI/O性能とは関係ないが、ファイルを削除する速さが重要な場合もある。（innodb_file_per_table使用時にDROP TABLEをした場合など）ext3ではファイルの削除に時間がかかることが知られているので、削除の性能が重要な場合にはext4やxfsの使用を検討すると良い。
ファイルシステムを使わずにRAWデバイス上にテーブルスペースを作成することも可能なので、バリエーションの一つとして検討するのも良いだろう。
どのファイルシステム（またはRAWデバイス）を使用すれば最適な性能が出るかという問いに対して唯一の回答は存在しない。使用するストレージによって最適な組み合わせは異なるからだ。最適な組み合わせを見つけるのは少々骨が折れるかも知れないが、ワーキングセットが大きくI/O性能が問われそうな場合には、チューニングする価値はあるだろう。
I/Oの調整
InnoDBがディスクの性能をフルに活かし切るためには、I/Oまわりのパラメータを調整するようにしよう。もし、あなたがMySQL 5.0以前のバージョンまたはMySQL 5.1のビルトインを使っているならば、今すぐMySQL 5.1のInnoDB PluginかMySQL 5.5へアップグレードしよう。話はそれからだ。
InnoDBは専用のスレッドでI/Oを行う。Plugin以降のバージョンでは、I/O用のスレッド数を調整できるようになっている。（古いバージョンにはそのような機能はない。）I/O用にいくつのスレッドがあるかということは、最大で何多重でI/Oリクエストを発行するかということに言い換えることができる。何多重でI/Oリクエストを行うと最もパフォーマンスが出るのか？というのはInnoDB側からは分からない。OS、ストレージ、ファイルシステム次第だからだ。Read/Writeがそれぞれ独立して多重度を決められるようになっていて、デフォルトは双方4だ。I/Oスレッド数は次のように指定する。
innodb_read_io_threads=8
innodb_write_io_threads=8
I/Oが少ないならデフォルトのまま使っていても良いが、I/Oがボトルネックになりそうな場合には、ベンチマークで最適値を算出しておくといいだろう。
もうひとつ検討すべきパラメータにinnodb_io_capacityがある。これはダーティページのフラッシュ、挿入バッファのマージといったバックグラウンド処理に対するI/Oの上限を設定するパラメータだ。デフォルト値は200。innodb_io_capacityが小さいとバックグラウンド処理がたまってしまう。バックグラウンド処理が溜まっている状態とは、すなわちダーティページが増えている状態のことだ。innodb_max_dirty_pages_pctで決まる許容量までダーティページが増えるか、ログを使いきってしまうと、古いダーティページをフラッシュするまで一切の更新処理が止まってしまう。従って、安定した運用をするには適切なバックグラウンドI/Oが必要となる。ただしinnodb_io_capacityが大きすぎると、現在進行中のトランザクションの性能に影響が出てしまうので注意しよう。
また、当然ではあるが、たくさんの更新処理を捌くには速いディスクが必要になる。innodb_io_capacityはディスクの速度（IOPS）以上に設定することは意味がないからだ。更新処理が多い場合には、パラメータの調整以前にハードウェアの選定の時点で考慮が必要となるわけである。innodb_io_capacityの効果を測定するには、ログを使い切る、またはダーティページの許容量を使い切るような更新の負荷を掛けなけれならない点に注意しよう。目いっぱい更新の負荷をかけてもログを使い切らない、ダーティーページが増えないという状況ならオッケーだ。
innodb_io_capcity=1000
デフォルトではテーブルスペースが自動拡張するようになっているが、これはキャパシティ管理およびI/O性能の観点から好ましくない場合がある。安定した運用を突き詰めるなら、自動拡張ではなくデータファイルのサイズを決め打ちにするといいだろう。ファイルシステムによってはフラグメンテーションを防ぐ効果も期待できる。
innodb_data_file_path=ibdata1:200G
また、テーブルスペースへの更新のポリシーはDirect I/Oが良いとされている。ファイルシステムキャッシュに余分なメモリが割り当てられなくなるからだ。その分バッファプールにメモリを割り当てるといいだろう。
innodb_flush_method=O_DIRECT
※Windowsでは変更不可
