innodb_thread_concurrencyに関する話

2018/07/03

こんにちわ。せじまです。今回の話は軽く書こうと思っていたのですが、長くなりました。まぁInnoDBの話なのでしょうがないですね。

はじめ

今回はinnodb_thread_concurrencyについてお話しようと思います。できれば、事前にInnoDB の mutex の話（入門編）を読んでいただいた方が、より深く理解していただけるのではないかと思います。

長いので、最初に五行でまとめます

  • 現代において、ほとんどのケースでは、innodb_thread_concurrencyはデフォルトの0のままで問題ないと思います。なぜなら、最近のInnoDBはかなり良くなってきたからです。
  • それでもinnodb_thread_concurrencyをチューニングするとしたら、「高負荷状態になったときでもスレッド間の公平性をなるべく担保し、安定稼働させるため」と割り切って使うのが良いでしょう。
  • innodb_thread_concurrencyをチューニングするなら、 innodb_adaptive_max_sleep_delayがあるMySQLを使いましょう。
  • innodb_concurrency_ticketsやinnodb_commit_concurrencyは、いったん忘れてもよいのではないでしょうか。
  • innodb_thread_concurrencyをチューニングしなくても、MySQL8.0はInnoDBのロック周りに大きな改善が入っているようです。

では、あんまり昔の話するのも気が引けますが、過去の歴史を少し振り返りつつ、現代においてinnodb_thread_concurrencyをチューニングして嬉しいのはどのようなケースなのか、見ていきましょう。

太古のinnodb_thread_concurrency

私が innodb_thread_concurrency を意識し始めたのは、確か2010年あたりMySQL5.5がリリースされる前後くらい、まだまだ MySQL5.1 と InnoDB Plugin の時代でした。

じつはinnodb_thread_concurrency、かなり歴史の古いパラメータで、 3.23.44や4.0.1の頃にはすでに存在していたようです。太古の昔のデフォルトは8だったのですが、MySQL5.5から0がデフォルトになりました。
MySQL5.5のとき、InnoDB PluginがInnoDB本体にmergeされるなどしてCPUスケーラビリティが改善した==CPUのCoreたくさんあってもだいぶ使えるようになった、ということがありました。逆にいうと、はるかむかしのInnoDBはCoreがたくさんあってもあまり使えなかったので、デフォルトのinnodb_thread_concurrency=8は「そんなにたくさんのCore使えないんだから、同時実行スレッド数にはキャップかけた方がいいよね」という意味合いだったと認識しているのですが、そこのところどうなのでしょう。

sh2さんのblogから、当時の状況を窺い知ることができます。

これがなかなか衝撃的なお話で、2010年あたりは、InnoDB Plugin （あるいはこの年にGAが出たMySQL5.5）を使わない限り、CPUのCore数が16を超えると、8Coreのサーバより性能が出なくなっていたのです。当然、最近のMySQLは、はるかにたくさんのCoreをうまく扱えるのですが。

ちなみに、innodb_thread_concurrency のチューニングは難しいということもあって、Facebook は InnoDB のスレッドスケジューリングを独自のパッチで行っているみたいですね。興味のある方は次の記事を参照してください。

このあたり、当時を知る有識者からのご意見ご感想をお待ちしております。ちょうどこの頃にInnoDBの性能改善パッチを書いていた方にこんどお会いしたら、いろいろお話を伺ってみたいところです。

MySQL5.5とinnodb_thread_concurrency

弊社では、GAが出て半年立たないうちに、MySQL5.5をプロダクション環境で使い始めていました。当時、私は「いまはdisk I/Oに律速される部分が多いけど、これからMySQLは、CPUとメモリとロックが問題になっていくんだろうなぁ」とぼんやり思ってました。実際、何度か innodb_thread_concurrency を 0 以外の値に変更したいなぁと思ったことがありました。しかし、当時は「まだここはいじるべきではない」と見送ってました。

なぜかと言いますと、 MySQL5.5でinnodb_thread_concurrencyをチューニングするのであれば、innodb_thread_sleep_delayも調整するのが良かったのですが、innodb_thread_sleep_delayの最適値はワークロードによって異なるものであり、汎用的な設定というのがかなり難しかったわけです。あまり黒魔術的な設定を入れると、将来、MySQLのメジャーバージョンを上げたくなったときに検証が煩雑になるので、このころは、アプリケーション側でSQL直せるなら直してもらったり、shardingするなどして対応してもらっていました。

MySQL5.5ではinnodb_thread_concurrencyのデフォルトが0になっただけあって、かなりのケースでInnoDBはCPUのCoreをうまく使えるようになっていました。また、InnoDB PluginがInnoDB本体にmergeされたことにより、初期のInnoDB Adaptive Flushingが導入されたこともあり、以前と比べるとだいぶInnoDBは改善されていました。いま思えば、アプリケーション側でSQLなど修正してもらうことは、むかしに比べるとだいぶ限定的になっていたのではないかなぁという気がします。

MySQL5.6とinnodb_thread_concurrency

この頃になると、いろいろと状況が変わってきました。以前と比べて何が変わって来たかというと、ハードウェアの進化が無視できないレベルになってきました。具体的に列挙しますと、次のとおりです。

  • CPUのCore数が増えてきたので、（仮想化しないのであれば）細かくshardingしていくより、ある程度大きいDBを扱うようにしたほうがコストパフォーマンスが良い
  • NAND Flashの価格がかなり下がってきたので、大容量のSSDを導入しやすくなってきた。かつては細かくshardingしていたDBも、大容量のSSDで統合していった方が、コストパフォーマンスが良くなる
  • SSDの性能が良くなってきた＆大容量のメモリを積むことも難しくなくなってきた

また、MySQL5.6からInnoDB Adaptive Flushingの挙動など変わったため、 I/O 周りのチューニングが5.5以前より容易になりました。その結果、懸念していたとおり、MySQLの問題は、CPUとメモリ、ロックの問題へと、推移してきたわけです。（あくまでも個人的な感想ですが）

この頃になると「InnoDB Adaptive Flushingのチューニングもできるようになってきたし、大容量のSSD導入しやすくなってきたし、innodb_thread_concurrencyのチューニングした方が良いかも」と思うようになり、弊社の大部分のmysqldでは、innodb_thread_concurrencyをチューニングするようになってきました。

弊社では、MySQL5.0や5.1の時代、InnoDB Plugin 以前の時代から多種多様なmetricを取得し続けて、漸く「そろそろココにも、innodb_thread_concurrencyにも着手するか」という踏ん切りがついたわけです。

では、MySQL5.7でinnodb_thread_concurrencyはどのように機能するのか

コードを読んだ方が早いと思うので、試しにMySQL5.7におけるDELETEの実装を読んでいってみましょう。

storage/innobase/handler/ha_innodb.cc#L8242-L8246

（たいへんざっくり言いますと）InnoDBにアクセスしてレコードを更新（削除）しようとしている前後が innobase_srv_conc_enter_innodb() と innobase_srv_conc_exit_innodb() でガードされていますが、この中で innodb_thread_concurrency（ソースコード中ではsrv_thread_concurrency）が参照されています。

storage/innobase/handler/ha_innodb.cc#L1485-L1486

n_tickets_to_enter_innodb がいわゆる innodb_concurrency_tickets なのですが、ここは軽く流します。（あとでまた振り返ります。）

ここで呼ばれている srv_conc_enter_innodb_with_atomics() が本丸です。

srv_conc_enter_innodb_with_atomics()

ソースコードとそのコメントを読んでもらえればその通りなのですが、この関数は、InnoDBにアクセスしようとするアクティブなスレッドがinnodb_thread_concurrencyを超えている限り、ループしてsleepを繰り返します。
四行でざっくりまとめると

  1. InnoDB にアクセスしようとしているアクティブなスレッドがsrv_thread_concurrency（innodb_thread_concurrency）を超えていた場合、srv_thread_sleep_delay(innodb_thread_sleep_delay)だけsleepします。
  2. srv_adaptive_max_sleep_delay(innodb_adaptive_max_sleep_delay)が0より大きく、かつ、sleepのループが二周目以降だった場合、srv_thread_sleep_delayをインクリメントします（一周目はインクリメントしないわけですね。繰り返しsleepしなければならないような忙しく状況であれば、sleepする時間を長くするわけです）。
  3. ループの先頭に戻って、InnoDB にアクセスしようとしているアクティブなスレッドの数を再度確認します。
  4. InnoDB にアクセスしようとしているアクティブなスレッドがsrv_thread_concurrencyより少なければ、srv_thread_sleep_delayを減らしてループを抜けます。多ければ 2.からやり直します。

というわけで、先にInnoDBにアクセスしようとしたスレッドほどsleepが短く、後からアクセスしようとしたスレッドほどsleepが長くなるので、挙動としてはFIFOのキューっぽいものになります。

ここで、冒頭で触れたInnoDB の mutex の話（入門編）の話に戻ってみましょう。

InnoDB の mutex 周りの実装は、spin loop(spinlock)とmutexの二段構えなわけですが、あまりにも大量のスレッドが同時に実行されていると、spinlockはCPUを無駄遣いしてしまう可能性があります。innodb_thread_concurrency=0でsysbenchなど実行しつつ

などで確認していただくと、mysqldのut_delay()がCPUをかなり使ってる場合があるかと思います。これはつまり、ut_delay()==spinlockでCPUを使っている、一台のDBで大量のスレッドを実行させようとすると、スレッド間の調停のためにCPUリソースを少なからず持っていかれる可能性があるということを示しています。これはいかにももったいないですね。また、大量のスレッドが同時に実行されて OS Waits が大量に増えているとき、 Thundering herd problem が引き起こされる可能性もあります。

このような状況になっているとき、innodb_thread_concurrencyを設定することで何が期待できるかというと、

  • InnoDBに同時にアクセスするスレッドの数を制限する
  • アクセスを制限されているスレッドは、各自sleepして待っている。sleepしているので無駄な context switch は減らせるし、 spin lock ほどCPUリソースを消費しない。
  • 後から来たスレッドほど長くsleepして待っているので、先に来たスレッドほど、先にtransactionを完了させやすい。つまり、スレッド間の公平性を担保させやすくなる。

というわけです。

innodb_adaptive_max_sleep_delay

MySQL5.6.3からinnodb_adaptive_max_sleep_delayというパラメータが追加されました。デフォルトの150000は、innodb_thread_sleep_delayは動的に150000まで増えて良いという意なのですが、innodb_thread_sleep_delayの単位はmicro seondsなので、150000 micro seonds = 150msec = 0.15sec まで増えて良い、という意味です。ただ、アクティブなスレッドの数がinnodb_thread_concurrencyより低いと、innodb_thread_sleep_delayはガンガン下がります。9とか19とかまで、ホントに競合しないなら0まで下がります。

例えば、 MySQL5.7 で sysbench 1.10 の oltp_read_write.lua を実行したとき、innodb_thread_sleep_delayは次のようにけっこう激しく変動します。


ただ、実際これだけ細やかに sleep できるかはまた別の話です。MySQL5.7では、innodb_thread_sleep_delayでのsleepは、 nanosleep(2) のある環境では nanosleep でsleepするので、その精度は処理系に依存します。innodb_thread_sleep_delay=0になったときはさておき、実際は 9 micro seconds とか 19 micro seconds ではなく、sleepする時間の最小値はnanosleepに依存し、最大値はinnodb_adaptive_max_sleep_delay次第、といったところではないでしょうか。

MySQL5.5以前はinnodb_adaptive_max_sleep_delayという概念がなかったため、innodb_thread_sleep_delayはサーバの負荷に応じて変動するわけではなく、DBAは自分でチューニングする必要がありました。

OLTPのように一つ一つのSQLがごく短時間で終了するならば、innodb_thread_sleep_delayはあまり長すぎないほうが良いのです。例えば、待ってるスレッドが長時間sleepしている間にアクティブなスレッドが居なくなった場合、そのタイミングで後からやってきたスレッドが先にトランザクションを実行できてしまうわけで、スレッド間の公平性を保ちにくくなります。
一方、OLAPのように一つ一つのSQLに時間がかかるのであれば、innodb_thread_sleep_delayは適度に長いほうが、アクティブなスレッドたちがロックを解除できるまで待てる方が良いわけです。

ゲームのように、後から機能やイベントが追加されるような性質のアプリケーションは、すべてのmysqldで適切なinnodb_thread_sleep_delayを求めることは、かなり難しいわけです。私はMySQL5.5ではそこに懸念を持っていたのですが、5.6でinnodb_adaptive_max_sleep_delayが追加されたことにより、ずいぶん楽になったなぁと感じ入っています。

現代においてinnodb_thread_concurrencyをチューニングする目的

極めて個人的な見解ですが、一言で言うと次のとおりです。

  • mysqld が高負荷状態になったときでも、スレッド間の公平性をなるべく担保しつつ、安定した respone を返せるようにする。

capacity planning が理想的にうまく行くなら、 innodb_thread_concurrency=0 のままにして、スケールアップやスケールアウトをして、システムを増強していけば良いと思います。しかし、 sharding やスケールアップが間に合わないという事態も、想定しなければならないときがあります。そういったとき、（innodb_thread_sleep_delayの分だけnanosleepするので、ごく僅かとはいえ待っていただくことになりますが）少しでも安定して response を返せるよう、なんとか努めたいのです。そういったときの備えとして、セーフティとしてinnodb_thread_concurrencyをチューニングしておくと、innodb_thread_concurrency=0のときより安定してresponseが返せるようになります。

mysqldが高負荷状態になる要因ですが、アプリケーションサーバにrequestがたくさん来たとか、ゲームで新しいイベントが始まったとか、新しいサービスがリリースされたとか、そういったものだけではありません。大容量のSSDにDBを集約していくと、一台のサーバのhostdownが与える影響や、ハードウェア故障の影響が大きくなります。

より安全にサーバを集約していくためには、高負荷状態になったときでも安定稼働できるようなチューニングが求められるわけなのです。

innodb_thread_concurrencyをチューニングする際のポイント

では、そういったことを踏まえて、敢えてinnodb_thread_concurrencyをデフォルト以外の値にしたいと考えたとき、どれくらいの値が適切と言えるでしょうか。公式ドキュメント内のinnodb_thread_concurrencyに関する記述は、あながち間違いでもないと思われます。というかこのあたり、使っているハードウェアやMySQLのバージョン、ワークロードによって千変万化するところなので、一概にコレという最適解はないように思われます。

innodb_thread_concurrency は SET GLOBAL で動的に変更できるので、変えたくなったら都度変えてもいいと思うのですが、シンプルに考えると

「どんなに高負荷な状態であったとしても、安定して同時にInnoDBにアクセスできる」というスレッド数の上限を、ワーストケースとして考えて設定する

というだけのことです。

個人的な見解を交えつつ言い換えると

（sync array の仕組みを踏まえると）pthread_cond_broadcast()で一度に起床させたいスレッド数の上限はいくらか

となります。

弊社では、 I/O bound な mysqld はそれほど多くないので、次のように設定しているところが大部分です。

innodb_thread_concurrency = CPUのCore数（Hyper-Threading有効にしたときの論理コア数）

ごくごく稀に、EBSなどが重くてCPU使い切る前にinnodb_thread_concurrencyで設定した上限に達するケースも有るのですが、「かといってinnodb_thread_concurrency=0のままだと、spin lock でCPU持っていかれて、結局mysqldがSQL捌くためにCPUうまく使い切れないこともありえるし」と、ある程度割り切っています。

ただ、RDS for MySQL など、仕組み上 I/O が重くなると最初からわかっているものであれば、 次のように設定したところもあります。

innodb_thread_concurrency = CPUのCore数（Hyper-Threading有効にしたときの論理コア数）*2

innodb_thread_concurrencyを初めて0以外の値にするとき、他になにか参考になるものがあるとしたら、しいて言えばThreads_runningかなぁと思います。

Threads_running

この値は mysqld の中でスリープ状態ではないスレッド数なので、 Binlog Dump Thread なども含まれ、InnoDB にアクセスしていないスレッドも含まれます。よって、Threads_runningはinnodb_thread_concurrencyの最適値より大きい値になると思うのですが、他に何も基準となる値がない状態であれば、最初はThreads_runningを見ればよいのではないでしょうか。実際、私は最初にThreads_runningを眺めて考えました。

Monitoringのポイント

では、innodb_thread_concurrencyを0以外の値にした場合、どのmetricをmonitoringして行けばよいのでしょうか。わかりやすいところでいうと、mysqldが刺さったとき、 load average などにも現れます。innodb_thread_concurrencyが0だと、mysqldが刺さったときに load average が数百に達してもおかしくはないですが、innodb_thread_concurrencyが0以外のとき、innodb_thread_concurrencyに準じて load averageはある程度低い値になります。

かといって、mysqldが刺さるまで何も見るものがないかというと、値になって現れるものもあります。 例えば、次のようなものがあります。

SHOW ENGINE INNODB STATUS のqueries inside InnoDBとqueries in queue

queries inside InnoDBが実際にSQLを実行しているアクティブなスレッド数、queries in queueが、innodb_thread_sleep_delayのぶんnanosleepしているスレッド数、キューの長さとなります。余談ですが、queries in queueの最大長は、理論上だいたいmax_connections程度になるのかな？と私は認識しています。

innodb_adaptive_max_sleep_delay, innodb_thread_sleep_delay

mysqldが忙しくなればなるほど、 innodb_thread_sleep_delay が増えていきます。その時間だけnanosleepしているため、mysqldの応答を待つアプリケーションサーバやクライアントは、その時間だけ長く待たされるようになります。アプリケーションの応答時間には適正値があるでしょうから、innodb_thread_sleep_delayが増えすぎていると思われたときは、なんらかの対応をしてもよいのではないでしょうか。

補足１：innodb_concurrency_ticketsについてはどのように考えるのか

正直、わたしはまだinnodb_concurrency_ticketsをチューニングしようと思ったことが、innodb_concurrency_ticketsいじらないといけないほど困ったことがありません。
SHOW ENGINE INNNODB STATUS 叩くと、トランザクション実行中のスレッドの残りチケット数を見ることができます。逆にいうと、一つ一つのSQLの実行時間がごくごく短いのであれば、Monitoringすることさえ困難なので、あまり気にしなくても良いのではないでしょうか。それよりも、確実に観測できる事象を優先して改善していけば良いと思います。

5.7のha_innobase::index_read()やha_innobase::general_fetch()を読む限り、

  1. チケット残ってたら innobase_srv_conc_enter_innodb() で使って
  2. 行読んで
  3. srv_stats.n_rows_read.add() で InnoDB_rows_read が +1 される

ようなので、一つのSQLを実行するとき、少なくとも Rows_examined の数はチケットを使うみたいですから、 Rows_examined がデフォルトの innodb_concurrency_tickets より充分小さいOLTPの用途であれば、ひとまず考えなくて良いのでは？その前にSQL直してRows_examined小さくする方が建設的なのでは？と思うなどします。

補足２：innodb_commit_concurrencyについてはどのように考えるのか

世にある過去のblogなどを読むと、innodb_thread_concurrencyとセットでinnodb_commit_concurrencyというものが出てくることがあります。極めて個人的な見解ですが、innodb_commit_concurrencyが言及されていた時代だと、 InnoDB Adaptive Flushing は今ほど賢くありませんでした。現代においては、先ずは InnoDB Adaptive Flushing にチューニングの余地がないか、確認してみる方が良いと思います。

たいへんざっくり言いますと、innodb_commit_concurrencyはI/Oで刺さるのを抑制するためのパラメータかと思います。 block device は read only or write only より、read と write が混在すると重いですが、 write の頻度や比率を減らすことで、 read をそのぶん軽くすることができます。 InnoDB Adaptive Flushing のチューニングで write はあるていどコントロールできるので、私はinnodb_commit_concurrencyより先に、InnoDB Adaptive Flushingのチューニングをおすすめします。

※disk I/O やInnoDB Adaptive Flushingの話を始めるとまったくもって紙幅が足りないので、今回はこのへんにしておきます。

MySQL8.0 では、さらに何が改善されたのか

MySQL8.0では、innodb_thread_concurrency周りが直接改善されたわけではない（と、いまのところ認識しています）が、こういったInnoDBのロックの競合周りで、大きな改善が２つあります。

"New Lock free, scalable WAL design" の記事で書かれている方は、MySQL8.0のGAがリリースされる前、MySQL Labsで公開されていたので、私の方でも評価し、フィードバックさせていただきました。InnoDBのI/Oに関するものなので。この redo log の最適化が入ったことにより、より一層、innodb_commit_concurrency は気にしなくても良くなったのでは？と思うなどしています。

Contention-Aware Transaction Scheduling については、昨年Sunnyに会ったときに「これソースコードまだ読んでないけど、WorkLog見る限りgood patchだよね」的なことを伝えたのですが、昨年の Oracle Open World のセッションで Sunny も「good patchだ」と紹介してた機能なので、けっこう期待してます。私としては、MySQL8.0に移行するモチベーションの一つになっています。

時間ができたらこのへんのコードも読みたいところです。

おわりに

今回は思った以上に長くなってしまったので、次回は軽めにしたいと思います。

References

時系列に並べてありますので、 InnoDB のCPUスケーラビリティについて振り返るのによろしいかと思います。