今回から何回かに分けて調査の際に監視したほうがよさそうなMySQLの変数(show variablesとかshow statusとかで見られる値)についてまとめてみます。（不定期&&続くかは不明）

なお、これらは僕の経験上からのものであり、これらの変数を確認することが十分条件でもなければ必要条件でもありません。あくまでも参考までにということで。

ウォーミングアップ

今回は初回なので、調査の際に必要となる考え方ややっておいた方がよいことなどを最初に示しておきます。

ステータス変数の累積値をあてにしてはいけない

SHOW GLOBAL STATUSはMySQLが起動してからの累積値を表示します。大抵の場合この累積値だけを見ても何もわかりません。

調査を行う場合必ず時間を決めて差分を求めるようにしましょう。例えばSHOW GLOBAL STATUSを15秒おきに実行すれば、その15秒間で増えた量が分かります。低規定に取得するのが目的であれば、最低でも1分間隔程度で取得するのが望ましいでしょう。（ただし取得する変数にもよる）

こういう時にmysqladminのextended-statusオプションは便利です。例えば下記のコマンドを実行するとCom_select(SELECTを発行した回数)を5秒間隔に取得してその差分を表示してくれます。

$ mysqladmin -uroot -p extended-status -i 5 -r | grep Com_select

-iで取得間隔を制御できます。(単位は秒) -rを付けないと差分ではなく累積の値が表示されます。 1発目に表示される値は累積値なので注意しましょう。この値は捨てていいです。

定常値を知っておく

差分を求めるだけではまだ不十分です。今度は取得したその値が定常値と比べて多いのか少ないのか、またそれは異常なのか正常なのかといったことを考える必要があります。

例えば、ある時間帯に取得したCom_selectの値が秒間1万だとしましょう。この値は異常なのでしょうか。それとも正常なのでしょうか。正解は「（それだけでは）分からない」です。

取得した値が異常値or正常値なのか判断するためには定常値を知っておく必要があります。またサービスによっては特定の時間、曜日に負荷が偏る傾向があります。（例えば社内サービスであれば平日の業務開始頃～がピークで、ゲームであれば夜や祝日が負荷のピークになるなど傾向が異なる）そのため定常値は少なくとも1週間スパン、できれば1か月以上で確認できることが望ましいです。

オンプレ環境であればPrometheusやZabbixなどが監視ツールとして利用できます。クラウドであれば大抵監視ツールが使えます。(AWSではCloudWatchなど)

各変数の性質をしっかり把握しておく

すべてのことに長所と短所があり、ある変数をチューニングすると必ず大なり小なりデメリットが生じます。いわゆるあちらを立てればこちらが立たずというやつです。例として、インデックスは(正しく使えば)参照性能を飛躍的に向上させてくれる一方で、更新性能に影響を与えたりストレージを余分に必要とします。

同じように性能調査などをしていてパラメータ値を変更する場合は注意が必要です。そのパラメータ値を変更することによるメリット・デメリットをしっかり把握しておきましょう。

例えばソートのパフォーマンスを上げるためにsort_buffer_sizeを引き上げたとしましょう。これによりソート操作のパフォーマンスはある程度改善されるかもしれませんが、一方でサーバー全体で消費されるメモリ量は多くなる可能性があります。もともとメモリがカツカツな環境であればSwapが頻繁に発生し、全体のパフォーマンスは低下するかもしれません。

また、そのパラメータ値が「Dynamicに変更可能か」も重要な要素です。 Dynamicに変更可能であれば、たとえパラメータ値の変更が原因でサービスに影響を与えてしまったとしてもオンラインで変更可能なので傷は浅く済みます。ところが、オフラインでしか変更可能な場合MySQLの再起動が必要なため、影響範囲は広くなります。

パラメータ値を変更する場合はその変数の性質をしっかり把握しておきましょう。

本題

さて、ここからが本題

今回は接続関連の変数です。

各項目の意味は

Variable name : 変数の名前
Type : variableであればシステム変数、statusであればステータス変数
Scope : そのシステム変数のスコープ、Global/Session/Both ステータス変数は常にN/A
Dynamic : そのシステム変数がSET GLOBALコマンドで変更が可能か、すなわち変更にMySQLの再起動が必要かを示す、ステータス変数は常にN/A （バージョンが新しくなるにつれてStaticだったのがDynamicになったりするのでRelease noteは常にチェックしておいた方がいい）
Description : その変数の説明や見方の説明

なお、これらの変数はMySQL 8.0.21に準拠しています。バージョンによっては変数名や、Deprecatedになっている変数がある可能性もあります。

Variable name	type	Scope	Dynamic	Description
max_connections	varaible	Global	Y	MySQLサーバーに同時接続できる最大数ただしrootやadmin_port(MySQL8.0.14以降)経由の接続は除かれるセッションメモリを考慮して増減させる
connect_timeout	variable	Global	Y	MySQLサーバーに接続するとき、この時間を超えるとコネクション生成エラーになる単位は秒でデフォルトは10秒コネクション生成エラーが頻繁に発生する場合はこの値を増やすことを検討する
thread_cache_size	variable	Global	Y	MySQL内部で保持するスレッドキャッシュサイズ後述するThreads_created値が短時間で大きく増えるようであれば増やすことを検討する
Aborted_clients	status	N/A	N/A	MySQLサーバーに接続して適切に接続が閉じられなかった回数 killコマンドやwait_timeoutなどで接続が切れるとこの値が増える
Aborted_connects	status	N/A	N/A	MySQLサーバーに接続を試みて失敗した回数例えば認証情報が違うなど
Connections	status	N/A	N/A	MySQLサーバーに接続されたもしくは接続しようとした数(ログイン失敗も含む) この値だけを見てもあまり意味はない
Max_used_connections	status	N/A	N/A	MySQLが起動してから計測された最大同時接続数
Max_used_connections_time	status	N/A	N/A	Max_used_connectionsを計測した時間(MySQL5.7以降)
Threads_connected	status	N/A	N/A	現在MySQLサーバーに接続されているスレッド数レプリケーションスレッド、イベントスケジューラーなどはカウントされない
Threads_running	status	N/A	N/A	現在MySQLサーバー上でステータスがSleep以外になっているスレッド数すなわち同時実行数でこの値が大きい場合、ロック待ち、単純に負荷が高いなど様々な理由が考えられる
Threads_cached	status	N/A	N/A	現在MySQLサーバーで保持しているスレッドキャッシュの数この値が0の状態で新規の接続の際に後述するThreads_created値が増える
Threads_created	status	N/A	N/A	MySQLがスレッドキャッシュを使用できず、新規スレッドを生成した数アプリケーション側でコネクションプールを利用しておらずこの値が継続的に増える場合は前述したthread_cache_sizeを大きくすることを検討する

max_connections

MySQLへの接続数がmax connectionsに達してしまうとその後の新規接続は悉く失敗します。（この状態でもrootユーザーやadmin_port経由での接続は可能です）そのためMax_used_connections/Threads_connectedは常に監視しておくのがいいでしょう。例えば僕は過去にこの値がmax_connectionsの75%を超えたらアラートを飛ばしていました。

max_connectionsはDynamicに変更可能なので変更自体は簡単ですが、それによりサーバー全体で消費されるメモリも増える可能性があります。この値を増やす場合は予め1スレッド当たりの最大使用メモリを把握しておきましょう。場合によってはその他のシステム変数値を下げることや、メモリのスケールアップを検討する必要があります。

thread_cache_size

MySQLのスレッド生成のコストは意外と高いです。このオーバーヘッドを減らす目的でMySQLにはスレッドキャッシュという仕組みが備わっています。これはスレッドが返却されたときにそれを完全に削除するのではなく次の接続リクエストのためにキャッシュしておく仕組みです。次回以降の接続はそのキャッシュを利用するためスレッド生成のオーバーヘッドを減少できます。

現在のthread_cache_size値が適切かどうかはThread_createdからある程度判断が可能です。 Thread_createdはスレッドキャッシュを使用せずにスレッドが生成された回数を表します。この値が増えている＝thread_cache_sizeが不足していると判断できます。（当たり前だがMySQL再起動直後は確保しているスレッドキャッシュが全くないのでThread_createdは増える）

Threads_running

この値はその瞬間にMySQLサーバー上で何らかの処理をしている（つまりSleepでない）スレッド数を表します。この値が大きいということはそのサーバー上で多くのスレッドが動いているということです。

この現象にはいくつかの理由が考えられます。例えばあるスレッドがロックを長時間確保してしまっていて、他のスレッドが待機してしまっている状態です。ロックの解析についてはまた改めて考えていきます。

もう1つの理由として単純にサーバーの負荷が高いというものもあります。クエリ自体が瞬時に完了しているようであれば問題ありませんが、時間のかかるクエリがいくつものスレッドで実行されるとサーバーの負荷は非常に高くなってしまいます。その場合はボトルネックとなっているクエリを特定して改善します。そもそもCPUがボトルネックであればスケールアップも検討します。

どの程度が高いかというとそれはサーバーが積んでいるCPUコア数によります。 Threas_runningが張り付いているかつ、その数値がCPUコア数の2～3倍以上であれば高いと言えるでしょう。（例えば16vCPUであれば30～50程度）だいたいその場合はLoad Average(LA)も高い数値になっているはずです。オンプレやMySQL on IaaSであればサーバーに直接ログインして、topなりuptimeを叩けばLAが分かります。

三流エンジニアの落書き帳

さあ、今日も未知という扉を開けてみよう

MySQLで監視したほうがよさそうな変数Part1~Connection関連~