InnoDBの超高負荷更新処理安定性

最近は沢山CPUコアのある高速なサーバーとか高回転数のHDDが沢山付いたRAIDストレージとか、もの凄く更新系の負荷がかかるベンチマーク(「db_STRESS」 by Dimitriさん)とかがあるので、InnoDBの構成の更新系での様々な限界が見えてきています。

まぁ、現実的にそのような限界を突破する必要のあるシステムがあるかどうかは判りませんが、将来のためにも色々アイデアを加えてXtraDBを作成してきました。今、大幅な変更無しに実装できる範囲のオプションが揃ってきたので高負荷更新系処理のチューニングをXtraDBベースで一旦書き出してみます。

今回もサクサクとポイントだけ。
（IOスレッドを増やす　とか、他でも語られている既知のものは省略します。）

今回のチューニングの方針は、 「mutexやrw_lockなどの競合をできるだけ避ける」 ということと 「あまり沢山溜めてはイケナイものを溜め込まない」 ということです。

まず、最大スループットを上げるために、更新系で競合を回避するポイントをいくつか。

"rseg->mutex" の競合：
ロールバックセグメントを増やしましょう。XtraDB には、innodb_extra_rsegments というオプションがあり、InnoDBの初期化の時に限り、指定数の追加ロールバックセグメントを作成できます。information_schema の INNODB_RSEG ビューで状況確認可能です。

"index->lock" の競合：
その索引全体に対するロックです。5.1のパーティション機能で分割することにより回避できるかも知れません。表定義時に "PARTITION BY HASH(xxxx) PARTITIONS 16" の様にします。db_STRESSの場合、パーティション化するのは HISTORY 表のみで良いでしょう。

"dict_operation_lock" の競合：
パーティションを利用すると多く発生するようです。tablespaceの空き領域を算出する処理がパーティション分増えてしまうのでしょうか。今回は空き領域表示の正確性よりも性能が大事なので、切ってしまいましょう（ぁ）。XtraDBの次バージョンで innodb_stats_update_need_lock に 0 を指定しましょう。動的パラメータなので、空き領域を正確に知りたいときは 1 に戻せます。


以上で db_STRESS のRW=1のスループットがかなり上がったと思います（…）。

あ、はい。どうやって競合を確認するか、ですね？
以前何処かでお話ししたかもしれませんが、少々乱暴な判断法ですが、SHOW INNODB STATUS を何回か実行して、SEMAPHORE セクションに沢山出てくる行を見て、ソースと照らし合わせて何の競合か判断します。結構、的を射た結論が得られます。

さて次はそのスループットを維持／安定化することを考えます。

忘れられがちなポイントを３つ挙げます。

古いダーティブロックを溜めすぎない：
トランザクションログの許容量ぶんよりも古い更新はflushしないままにはしておけません。なので大量に溜め込んでいると、ある時突然大量のデータブロック書き込みが発生する場合があります。普段からコツコツ書き出して減らしてバランスを取る必要があります。
Plugin-1.0.4 から追加された innodb_adaptive_flushing かそれともこれを無効化して、XtraDB の "innodb_adaptive_checkpoint = estimate" を指定すると適切な量の書き出しをコンスタントにするようになります。どちらのオプションが最適かは、サーバーの性能と処理内容とその他設定のバランスに依存するようですので、試してみてください。
XtraDBだと SHOW INNODB STATUS の出力の LOGセクション に Max checkpoint age と Modified age が表示されるので容易に確認できます。

insert bufferを溜めすぎない：
InnoDBは主キー以外の索引列に対する変更は、insert bufferに挿入して整合性を取りながら、後で実際の変更を実施します。しかし、InnoDBは insert buffer が buffer pool の半分の大きさまで肥大化しないと本気で処理しないようです。XtraDB のオプション innodb_ibuf_active_contract = 1 でいつも本気で insert buffer を減らそうとします。

history listを溜めすぎない：
これが起こるのは相当更新の激しい処理です。ロールバックセグメントの中身に対する後処理が新しくエントリを発生するスピードに追いつかずに肥大化した状態かと思われます。ロールバックセグメントが巨大化してしまうと、処理が重くなります。
そのpurgeと言う後処理を専任で実行するスレッドをつくると言うアイデアもありますが、db_STRESS は1CPUのpurge threadではその他CPU全ての更新処理は受け止めきれないようです。
XtraDBの次のバージョンでは、このpurge threadにさらに子分のスレッドをつけて"purge力"を上げることができます。例えば innodb_use_purge_thread = 4 の様な感じで 親分1+子分3 の構成でpurgeするようになります。(この設定で16コアのマシン上の32セッションの並列処理に対応できました。)
苦し紛れの innodb_max_purge_lag 指定はもう必要無くなるのではないでしょうか？


以上です。
最後のpurge threadの効果を中心とした実際のデータは、近いうちに mysqlperformanceblog で見ることができると思います。

では、MySQLマニアの皆さんも快適なベンチマークライフを！
（検証用に急に良いマシンを借りれてしまったときに失敗せぬように！）