MySQLのmetricに関する話

こんにちわ。せじまです。さいきん、ジム用に左右独立型スポーツモデルのBluetoothイヤホン買ったのですが、あまりの快適さに、ジムに通うモチベーションが５割増しになりました。

はじめに

innodb_thread_concurrency について書こうかと思ったんですが、まずはその前に MySQL の metric の話から入ったほうが良いかなぁと思ったので、今回は metric の話を書こうと思います。はじめにお断りしておきますが長くなります。

弊社では７年以上前から、 MySQLのサーバ一台あたり（OS側のも含めると）200-300 以上の metric を だいたい15秒間隔で記録しています。最近はSSDなども使ってますが、むかしはHDDにmetricを保存していました。例えばMySQLのサーバ一台分のmetric表示した画面をキャプチャすると、次のような感じになります。

※画像は縮小してあります。

なんでそんなにたくさん取っているかというと、単純に見たかったというのと、disk i/o が好きなのでrrdcachedのチューニングしてたら取れたからです。また、常時いろいろな metric を取得し続けていると、 MySQLのソースコード読んだ後、「その理解が正しいかどうか、いままで取得し続けていた metric をみて確認する」といった使い方もできるので便利です。

今回は、そういった metric群の一部から、比較的有益なもの、専門的な知識が必要とされるけど有益そうなものなどを抜粋し、列挙していきたいと思います。
ほとんどのmetricは、InnoDBを主に使っている素のMySQLを想定して記述しています。

基本的には

弊社では、ganglia の gmond という agent 向けに python module を書いて

• SHOW GLOBAL STATUS
• SHOW VARIABLES
• SHOW ENGINE INNODB STATUS
• SHOW ENGINE PERFORMANCE_SCHEMA STATUS
• SHOW SLAVE STATUS

を叩いて出力結果をパースしています。 MySQL のバージョンが上がる度に SHOW ENGINE INNODB STATUS のフォーマットがビミョウに変わり続けるので、真心込めて対応しています。 ganglia 使われてる方はそれほど多くないでしょうから、「どういった metric をどういった観点で見ているか」といったことを、これから記述していきます。
SHOW ENGINE INNODB STATUS をパースするのは手間でしょうから、様々な監視ツールがすでに取得しているであろう、 SHOW GLOBAL STATUS から取得できるものを優先していきます。
SHOW ENGINE INNODB STATUS ではなく Information Schema から取得できる場合もあるのですが、いまのところ SHOW ENGINE INNODB STATUS を参照しないと取れない metric もあるので、 Information Schema より SHOW ENGINE INNODB STATUS から情報を取得するようにしています。

以下、 metric について

だいたいの人が見て役に立ちそうなもの

 Innodb_rows_read

とてもざっくり考えると、

Innodb_rows_read/sec = 一秒間あたりのRows_examinedの総量

と捉えると、 EXPLAIN 叩いて調べる人には、「ああ見といたほうが良いのかも」と得心が行くのではないでしょうか。
MySQLのバージョン上げて実行計画変わったりゲームの新しいイベントがはじまったんだけどINDEXがうまく効いてなかったりすると、うなぎのぼりで上がっていってCPU使い切ることがあります。その予兆として見ておくと良いのではないでしょうか。
table がまだ小さい段階であれば、強い意志を持ってオンラインで ADD INDEX して助かるケースもあります。（最近の MySQL は、 Online DDL が改善しているのでこういうとき助かります。）
油断できないのは、時間の経過に比例してデータが蓄積していくケースです。長期に渡って運営しているサービスや、ゲームのイベント日程後半で、大量のレコードを読むようになってDBが刺さる、あるいはバッチジョブなどで巨大な配列を扱うようになってバッチサーバで Out Of Memory が発生するなどの問題が生じる可能性があります。

Seconds_Behind_Master, Read_Master_Log_Pos, Exec_Master_Log_Pos

残念ながら WL#7374: Performance schema tables to monitor replication lags and queue に、力強く

The current concept of SECONDS BEHIND MASTER is buggy and not meeting the present needs.

と書いてありますが、現時点で Seconds_Behind_Master はわかりやすくてそこそこ機能する監視項目です。
MySQL8.0のSeconds_Behind_Masterに関する記述にあるように、Seconds_Behind_Masterが常に正確な値を示せるわけでもありません。また、精度が秒単位でしかありません。そういったときは Read_Master_Log_Pos と Exec_Master_Log_Pos の差分を求めて、 slave がどれくらい遅延しているか、補完的に見ても良いのではないかと思います。

MySQL8.0 からはマイクロ秒単位で replication の遅延を監視できるようにするために、上記の WorkLog#7374 の対応がなされ、 binary log や performance schema に修正が入りました。興味のある方は以下の記事などを参照してください。

• New monitoring replication features and more!
• 25.11.11.2 The replication_connection_status Table

Com_change_db, Com_delete, Com_insert, Com_insert_select, Com_replace, Com_replace_select, Com_select, Com_truncate, Com_update, Com_update_multi, Questions

Com_xxx シリーズは大量にあるので、全部記録するのはなかなか大変かと思います。このあたりのめぼしいものを取っておきつつ、 Questions も合わせて取ることで「残りの Com_xxx がどれくらい飛んできてるだろうか？」といった確認ができれば良いかなぁと思っています。

Connections, Threads_connected, max_connections, Max_used_connections, Connection_errors_max_connections

アプリケーションの性質や設計にもよると思うのですが、コネクションプーリングしにくいアプリケーションの場合、MySQLの Too many connections は避けなければならない error です。普段、アプリケーションサーバからDBに対してどれくらい connection が張られているか記録しておくのは、とても良いことだと思います。また、瞬断ないしfail overなどが発生した際、どのDBにどれくらい connection が滞留していたか、これらの metric を記録しておくことで、「瞬断が発生したとしても、mysqldのslaveをあと何台か足しておけば、 Too many connections は避けられそうだ」といった振り返りができます。
max_connections と Max_used_connections をセットで記録するのは、 max_connections が SET GLOBAL でいつでも増やせることを考慮して、ですね。
Connection_errors_xxx もいろいろあるのですが、Too many connections が発生したかどうかを後から振り返ることができるので、 Connection_errors_max_connections は地味に良いのでは、と思っています。

Slow_queries, Innodb_row_lock_time, Innodb_row_lock_waits

このあたり言わずもがなかな、と思います

Handler_rollback

これはアプリケーションの設計によると思うのですが、弊社の場合、InnoDB で Handler_rollbackの値が高すぎるときは、SQLなどに改善の余地があるのでは？と見直しています。 InnoDB で Handler_rollback が増えているとき、 UNIQUE INDEX の duplicate や、 query のキャンセルが発生している場合があるからです。

専門知識がある程度ある人は、見ても良いと思うもの

まず

• SHOW ENGINE INNODB STATUS の Buffer pool hit rate
• Innodb_rows_deleted, Innodb_rows_inserted, Innodb_rows_updated

あたりは見ても良いと思うのですが、
これら以外に特筆すべきものを列挙していきます。

SHOW ENGINE INNODB STATUS の Log Sequence Number - Last checkpoint at

とてもざっくり言いますと、 InnoDB は background で dirty page を少しずつ flush するのですが、その flush の頻度の基準となるものの一つが、 REDO Log の使用率、Log Sequence Number と Last checkpoint at の差分となります。

InnoDB Adaptive Flushing に興味のある方は、こちらのスライドなど参照してください。

• さいきんの InnoDB Adaptive Flushing （仮）

Innodb_log_write_requests, Innodb_log_writes

innodb_flush_log_at_trx_commit を変更するような人は、このあたりの変化量も見て良い気はします。

SHOW ENGINE INNODB STATUS の History list length

こちらは InnoDB の purge に関する metric です。ざっくりいうと UNDO Log がどれくらい溜まってるかを示すものです。 srv0srv.cc には 「5000 より小さいなら敢えて溜め込もう」的なコード も入ってるんで、あんまり高くないならほっとけばいいと思うんですが、高くなってくると ibdata1 などが肥大化したり purge が重くなってきたりするので、ほどほどに見ておいたほうが良い感じです。

Innodb_buffer_pool_pages_data, Innodb_buffer_pool_pages_dirty, Innodb_buffer_pool_pages_flushed, Innodb_buffer_pool_pages_free,

特筆すべきは Innodb_buffer_pool_pages_free です。 MySQL5.6以降のInnoDB Adaptive Flushingは、buffer pool instance ごとに innodb_lru_scan_depth で指定しただけ free page を確保しようとするので、更新処理が発生しているのに free page が枯渇しているという状況だと、 dirty page の flush が追いついていない可能性があり、 single page flush が発生してしまうかもしれません。
InnoDB Adaptive Flushingの詳細については、前述したスライドなど参照してください。

InnoDB の OS Waits など

以前 InnoDB の mutex の話（入門編） で書かせていただきましたが、 InnoDB の mutex まわりの metric です。だいたいのケースでは spin rounds, spin waits, os waits だけでも良いかもしれません。これらは次のようにして information_schema からも取得できますね。

弊社では、SHOW ENGINE INNNODB STATUS から、以下のソースコード中のread lockやwrite lockなども、metricとして記録しています。

• btr0sea.cc
• buf0buf.cc
• row0{ins,upd,sel}.cc
• trx0rseg.cc

Handler_rollback 以外の Handler_xxx（Handler_read_prev, Handler_read_rnd_nextなど）

慣れてくると Handler 関連の metric 見ただけで「今日は ORDER BY ... DESC 多いなぁ」など、どういうSQL飛んできているか察することができるようになるのでオススメです。先ずは Innodb_rows_read を見て、多いと思ったら Handler 関連の metric で当たりをつけてから、改善の余地がありそうなSQL探していくという流れです。

専門知識があっても、たまに振り返るくらいで良さそうなもの

「いつかパラメータチューニングする日が来るかもしれない」という備えとして、いくつかmetricを記録し続けています。

Sort_merge_passes

かつて弊社は、 sort_buffer_size を減らすというチューニングを実施しました。

弊社では、以前sort_buffer_sizeにかなり大きな値を割り当てていたのですが、それは秘伝のタレのようなものでした。現代においてもその値が妥当なのかどうか、その判断に使ったのが Sort_merge_passesです。
若者にsort_buffer_size の割当を減らした slave の DB を投入してもらう際、 Sort_merge_passes にどれほどの変化がでるか比較してもらいました。まず、Sort_merge_passesがほとんど増えることもなく、サービスの response time が悪化しないことを確認しました。それから、 sort_buffer_sizeを減らした設定を他のサーバにも展開していった後、「sort_buffer_sizeを減らしたので、 mysqld の 1conneciton あたりのメモリ消費量のワーストケースは安全に減らせた」という判断をし、 max_connections の引き上げを実施しました。

将来また sort_buffer_size を減らすことになったとしたら、次回もおそらくSort_merge_passesを確認するでしょう。

SHOW ENGINE INNODB STATUS の Ibuf: size あたりや、 SHOW ENGINE INNODB STATUS の delete mark あたり

むかし InnoDB の元開発者の方に「innodb_change_buffer_max_sizeは 1 でも良いんじゃないでしょうか」と教わりました。実際、 1 で問題ないケースは多々見受けられます。ただ、たまにバッチ更新されているDBなどで change buffer の size が 1 を超えるなどしているので、「いつかinnodb_change_buffer_max_size=1というチューニングをする日が来るかもしれないので、既存のmysqldでchange bufferの使い方をmonitoringしておく」といった使い方をしています。

innodb_change_buffer_max_sizeについて言及されている木下さんのスライドはこちらになります。

• Innodb Deep Talk #2 でお話したスライド

SHOW ENGINE INNODB STATUS の hash searches/s や non-hash searches/s

InnoDB Adaptive Hash Index は「いまはdefaultで有効になっているけれど、今日では無効にした方がよいワークロードもある」と、何年も前からMySQLのドキュメントで言及されている機能です。

CPUにそれほど多くのCoreが積まれていなければ気にすることもないかなと思ったり、MySQL5.7でinnodb_adaptive_hash_index_partsが追加されてmutexの競合が改善していたり、 弊社はinnodb_thread_concurrencyのチューニングを実施しているので、Adaptive Hash Indexによるmutexの競合って、いまのところ気にするまでもないのかな？と感じています。

そういった諸々のことを踏まえつつ、現時点で SHOW ENGINE INNODB STATUS で btr0sea.cc の競合を monitoring する限り、実際に対して競合してないことが確認できているのですが、将来の備えとして、どれくらい hash search と non-hash search が行われているのか、記録として取得し続けています。

innodb_thread_concurrency を変更している人は見ても良いもの

次のものについては、後日また innodb_thread_concurrency について解説する際に述べたいと思います。

• Threads_running
• SHOW ENGINE INNODB STATUS の queries inside InnoDB と queries in queue
• innodb_adaptive_max_sleep_delay, innodb_thread_sleep_delay

おわりに

他にもまだいろいろ metric を取得しているのですが、続きはまた別の機会にしたいと思います。
次回は innodb_thread_concurrency についてお話できると良いかなぁと思うなどしています。