そーだいなるらくがき帳

そーだいが自由気侭に更新します。

MySQLの監視 ~ mackerel-plugin-mysqlを読み解く

この記事は Mackerel プラグインアドベントカレンダー(全部CRE) の4日目です。

qiita.com

soudai.hatenablog.com

それでは4日目は mackerel-plugin-mysql です。

ここではMySQLの細かい説明は割愛します。

mackerel-plugin-mysqlRDBMSとして広く使われているMySQL専用のプラグインです。

github.com

インストールと設定手順

プラグインプラグイン集として提供しているパッケージの mackerel-agent-plugins に含まれています。 インストール先は /usr/bin/mackerel-plugin-mysql です。

mackerel-plugin-mysqlは様々な情報を可視化してくれますが、pluginが動作するクライアント側からMySQLに対してアクセス出来る必要があります。 MySQL サーバがlocalにあり、rootがノンパスワードでログインできてかつ、portがデフォルトの3306を使っている場合は下記のとおり、実行することができます。 それ以外の場合はコマンドの引数で設定することが出来ますのでご自身の環境に併せて適宜設定してください。 主な引数は下記の通りです。

  • hostname
  • username
  • password

またMackerelのプラグインはコマンドですので実行する事ができます。

※/usr/bin はPATHが通っているので省略出来ます

# mackerel-plugin-mysql | wc -l
90

項目が多いので行数のみにしてます。 つまり90項目もみれます!!

設定ファイルであるmackerel-agent.confは標準では /etc/mackerel-agent/mackerel-agent.conf にインストールされます。 こちらに下記のとおり追記しましょう。

[plugin.metrics.mysql]
command = "mackerel-plugin-mysql"

以上を行った上でmackerel-agentを再起動してください。

見れるメトリック

最初に伝えますがコマンドの出力結果から分かる通り、MySQLのメトリックはめちゃめちゃ沢山とれます。 それだけしっかりとモニタリング出来るということですが、もちろん書く方も読む方も大変です。 今から読む覚悟は出来ましたか?(俺は出来ている)

MySQLのデータ取得で使っているSQL

まず項目の説明をする前に事前知識の共有です。

SHOW /*!50002 GLOBAL */ STATUS

間の /*! */ のコメントは互換性維持のためのMySQLの構文です。 5.0より前はGLOBALキーワードが無いのでバージョン指定コメントで互換性維持のために書いてあります。 詳しくはこちらをどうぞ。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.6 コメントの構文

SHOW [GLOBAL | SESSION] STATUS は簡潔に言うとMySQLの色んな操作についての情報を提供してくれます。 詳しくは項目が多いので公式ドキュメントを読みましょう。 一読すると結構なんでも取れるので便利だなってなります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.6 サーバーステータス変数

SHOW /*!50000 ENGINE*/ INNODB STATUS

SHOW ENGINE INNODB STATUS デフォルトのストレージエンジンであるInnoDBの詳細な情報を提供してくれます。 例えばどこがデッドロックしていて、どのようなトランザクションが実行されているかなどの詳細が読み取ることができます。 こちらはInnoDB専用なので普段、MyISAMなどのストレージエンジンを使っている人は関係ありません。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.5.16 SHOW ENGINE 構文

ストレージエンジンを確認したいかたはこちらをご参考ください。

MySQLサーバのストレージエンジン確認方法 - Qiita

SHOW VARIABLES

SHOW VARIABLESMySQL システム変数の内容を出力します。 つまり今どんな設定がMySQLに設定されているかを調べる事ができます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.5.40 SHOW VARIABLES 構文

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

SHOW SLAVE STATUS

SHOW SLAVE STATUS はスレーブスレッドの基本的なパラメータに関するステータス情報を提供します。 レプリケーションしていない場合は当然スレーブは存在しないので気にする必要はありません。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.5.35 SHOW SLAVE STATUS 構文

SHOW PROCESSLIST

SHOW PROCESSLIST は、どのスレッドが実行されているかを提供してくれます。 具体的には実行中のSQLの一覧などを確認することができます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.5.30 SHOW PROCESSLIST 構文

mackerel-plugin-mysqlはこれらの情報を活用してメトリックを出力しています。

それでは各グラフ定義ごとに説明します。 また表に出てくるdiffとはプラグイン上で差分値計算をするかどうかです。 となっている項目はプラグインで前回の実行時の値と差分値計算して出力しています。

あと今回は量が多いのでコマンド結果のところは省略していきます。

MySQL Command

メトリック名(ラベル) プラグインの出力名 diff 説明
Insert mysql.cmd.Com_insert 1分間あたりのINSERT回数
Insert Select mysql.cmd.Com_insert_select 1分間あたりのINSERT ... SELECT回数
Select mysql.cmd.Com_select 1分間あたりのSELECT回数 (クエリキャッシュは含まない)
Update mysql.cmd.Com_update 1分間あたりのUPDATE回数
Update Multi mysql.cmd.Com_update_multi 1分間あたりの複数テーブル構文のUPDATE回数
Delete mysql.cmd.Com_delete 1分間あたりのDELETE回数
Delete Multi mysql.cmd.Com_delete_multi 1分間あたりの複数テーブル構文のDELETE回数
Replace mysql.cmd.Com_replace 1分間あたりのREPLACE回数
Replace Select mysql.cmd.Com_replace_select 1分間あたりのREPLACE ... SELECT回数
Load mysql.cmd.Com_load 1分間あたりのLOAD回数
Set Option mysql.cmd.Com_set_option 1分間あたりのINSERT回数
Query Cache Hits mysql.cmd.Qcache_hits 1分間あたりのクエリキャッシュから返した回数
Questions mysql.cmd.Questions 1分間あたりのサーバーによって実行されたステートメントの数

SHOW [GLOBAL | SESSION] STATUS の結果を利用しています。 MySQL Commandは実行されているSQLの種類別で1分間あたりの回数が見えます。これによってMySQLの負荷がかかった時、実際にどのQueryが増えているのか一目瞭然です。もしQuery量はそれほどでも無いのにMySQLのCPUが悲鳴を上げる場合はクソQueryが実行されているかもしれません。

以上のことからMySQL Commandは定常的に監視すると言うよりは何か有った時の指標にしたり、定期的に見てシステムの振る舞いを知るための指標です。2週間に1回やリリースのタイミングで見るのが良いでしょう。

MySQL Join/Scan

メトリック名(ラベル) プラグインの出力名 diff 説明
Select Full JOIN mysql.join.Select_full_join 1分間あたりの2つ以上のテーブルにおいて全件同士でJOINした回数
Select Full Range JOIN mysql.join.Select_full_range_join 1分間あたりの片方のテーブルで全件、もう片方のテーブルで範囲検索を行ってJOINした回数
Select Range mysql.join.Select_range 1分間あたりの最初のテーブルの範囲が使用されたJOIN数
Select Range Check mysql.join.Select_range_check 1分間あたりのINDEXのキーなしのJOIN数
Select SCAN mysql.join.Select_scan 1分間あたりの最初のテーブルでフルスキャンしてJOIN数

同じく SHOW [GLOBAL | SESSION] STATUS の結果を利用しています。 こちらは名前の通りJOINに関する値をまとめています。 特に注意すべきは Select Full JOIN でしょう、糞Query筆頭です。こちらは簡潔に言うと 100*100 のようなテーブルフルスキャン同士のJOINが走っている回数です。これが 1000*1000*1000 だと100億レコード相当です。そりゃ遅いですよね。なのでここの値が多いときはクエリチューニングのチャンスです。

次に注目するのは Select Range Check ですね。これはINDEXが貼られてないカラムでJOINされた回数です。つまりINDEXが足りていませんので0で無いときはINDEXの利用を検討しましょう。Select Full JOINと併せてここが多いときは多くがINDEXが貼られていないことが問題ですのでINDEXを適切に設定しましょう。

逆に Select Range 多い場合は適切なINDEXが活用できているケースが多いです。 クエリチューニングやINDEXチューニングを実施してここが増えていくのが適切なグラフになります。 場合によっては Select_scan も増えるかもしれませんが最初のテーブルが小さい場合はクリティカルでは無いです。 MySQLは良くも悪くもネステッドループジョイン(NLJ)しかありません。 ですのでJOINの内部表と外部表を意識しましょう。

以上のことからMySQL Join/ScanもMySQL Commandと同じです。 事前に知っていれば見えてくる傾向が沢山ありますので日常的に確認しながらしっかりとMySQLの振る舞いを予測していきましょう。

MySQL Threads

メトリック名(ラベル) プラグインの出力名 diff 説明
Cache Size mysql.threads.thread_cache_size スレッドをコネクションの切断後にもキャッシュしておく数
Connected mysql.threads.Threads_connected 現在開いている接続数
Running mysql.threads.Threads_running スリープ状態ではないスレッド数
Created mysql.threads.Threads_created 1分間あたりの接続処理するために作成されたスレッド数
Cached mysql.threads.Threads_cached スレッドキャッシュ内のスレッド数

同じく SHOW [GLOBAL | SESSION] STATUS の結果を利用しています。 MySQLはスレッドタイプなので大量の接続を捌くためにはMySQLのThreadの状況を知ることが大事です。 Threads_createdは本来は起動してから新規に生成されたThreadの総数なのでプラグイン側で1分平均の差分値を計算するようになっています。 こちらも監視して閾値を設定するというよりはMySQLの振る舞いを知るためのグラフです。 またCreated が大きい場合、Cache Size の値を大きくした方がよい場合もあります。 Cache Sizeはmy.confの thread_cache_sizeSET GLOBAL thread_cache_size = 値; で変更する事ができます。 5.6.8未満 まではDefaultが0(無効)だったのですが 5.6.8 からは-1(自動調整)となっているため新しいバージョンを使っている人は気にしなくていいと思います。 ただし、バージョンは新しくてもRDSをご利用中などで5.5から5.6にバージョンアップした場合などは前の設定を引き継いでいる場合もありえます。 心当たりがある人は一度確認しておきましょう。

MySQL Connections

メトリック名(ラベル) プラグインの出力名 diff 説明
Max Connections mysql.connections.max_connections サーバが許容可能な最大同時クライアントコネクション数
Max Used Connections mysql.connections.Max_used_connections 起動してから同時に使用された接続の最大数
Connections mysql.connections.Connections 1分間あたりのMySQL Serverへの (成功またはそれ以外の) 接続の試行数
Threads Connected mysql.connections.Threads_connected 現在開いている接続数
Aborted Clients mysql.connections.Aborted_clients 1分間あたりのクライアントが接続を適切に閉じることなく終了したため中止された接続数
Aborted Connects mysql.connections.Aborted_connects 1分間あたりのMySQL Server への接続に失敗した試行数

Max Connectionsは SHOW VARIABLES から取得し、他は同じく SHOW [GLOBAL | SESSION] STATUS の結果を利用しています。 こちらは接続状況を見るためのグラフになっています。 Max Connectionsは設定された最大値なのでConnectionsが超えることはありませんがConnections=Max Connectionsとなった場合はクライアント側からすると新たな接続ができなくなります。 よく見かけるシーンはアクセスが急増した場合にDBがさばけなくなったり、アプリケーション側が1回の処理で何度もコネクションを生成したり正しくコネクションをクローズできずにコネクションが溜まっていく場合などです。 この状態になると即障害であることが大半なのでConnectionsの値はMax Connectionsの7割から8割くらいを閾値にして監視するようにしましょう。 またMax Used Connectionsはそのシステムのピークを表しています。 Max Used ConnectionsがMax Connectionsよりも大きく下回っているのであればリソースを余らせているのでサーバスペックを下げることも検討して良いでしょう。RDSなどのクラウドのメリットはスケールダウンも気軽に出来ることです。

MySQL Slave status

メトリック名(ラベル) プラグインの出力名 diff 説明
Seconds Behind Master mysql.seconds_behind_master.Seconds_Behind_Master スレーブ SQL スレッドとスレーブ I/O スレッドの間の時間差 (秒単位)

SHOW SLAVE STATUS の結果を利用しています。 端的にいうとスレーブがどれだけ「遅延している」かを示します。 なのでマスター側ではこの値は取得されません。 この値はスレーブがただのバックアップならば厳しく見る必要は無いかもしれませんが参照用のリードレプリカならある程度見ておく必要があります。 こちらの値がどんどん大きくなる場合はなんらの対応が必要ですが原因は一定ではありません。 例えばネットワーク遅延でも起こりますし、マスター側の更新にスレーブ側が追いつけていない場合も発生します。 そのためレプリケーション遅延が起こっている場合は対象のスレーブでNICやネットワークが詰まっていないか、DiscI/Oなどは問題ないか確認しましょう。 その上で SHOW SLAVE STATUS を実行し細かい調査を行うことをオススメします。 実際の調査方法については奥野さんの最高の記事があるのでご一読ください。

nippondanji.blogspot.jp

MySQL Table Locks/Slow Queries

メトリック名(ラベル) プラグインの出力名 diff 説明
Table Locks Immediate mysql.table_locks.Table_locks_immediate 1分間あたりのブロックされることなくテーブルロックを取得した回数
Table Locks Waited mysql.table_locks.Table_locks_waited 1分間あたりのブロックされてテーブルロックを取得に待機が必要だった回数
Slow Queries mysql.table_locks.Slow_queries 1分間あたりの long_query_time 秒よりも時間を要したクエリ数

同じく SHOW [GLOBAL | SESSION] STATUS の結果を利用しています。 RDBMSの鬼門、ロックの情報を出してくれています。 まず確認するの次の2つの値です。 Table Locks Immediate は通常のSELECTクエリでレコードを取得する場合なども内部でロックが取られているので、クエリ実行してブロックすることなしに即時に実行できれば1とカウントされます。 Table Locks Waitedはテーブルのロックを取得した時に既に対象がロックされていたためスレッドがブロックされた時にカウントされます。 そのためTable Locks Waitedが大きいということはそれだけテーブルロックの競合が発生しているのでパフォーマンスの問題になりやすいといえます。 しかしTable Locks Waitedが多いからといって問題になるとは限りません。 そしてグラフの嬉しい所はSlow Queriesも一緒に有ることです。 Slow Queriesはlong_query_time(デフォルト 10秒)を超える実行時間のクエリ、つまりその名のとおりスロークエリの数ですがスロークエリが発生するときはロックが絡んでる事が多いのです。 そのため大量のTable Locks Waitedと併せてスロークエリが発生している場合は長時間のロックを取っているクエリや大量のロック競合が発生している可能性があります。 そこでMySQL Commandと組み合わせて見ることでボトルネックは更新なのか?参照なのか?などが見えてくるのです。 逆説的にロックが発生していてもスロークエリ数が少ないのであればサービス的には問題ないかもしれません。 そのため、この3つを一緒に見ることでスロークエリの勘所を知ることができます。 この他にもTable Locks Waitedが少ないがSlow Queriesがどんどん増える場合はクソクエリが潜んでいる可能性があります。前述のMySQL Join/Scanなども見てみましょう。

MySQL Traffic

メトリック名(ラベル) プラグインの出力名 diff 説明
Sent Bytes mysql.traffic.Bytes_sent 1分間あたりのすべてのクライアントに送信されたバイト数。
Received Bytes mysql.traffic.Bytes_received 1分間あたりのすべてのクライアントから受信したバイト数

同じく SHOW [GLOBAL | SESSION] STATUS の結果を利用しています。 こちらはMySQL Serverの送受信量を表示してくれます。 MySQL Serverの更新や参照が増えればもちろんこの値も増えるでしょう。 例えばMySQLに大きなデータを登録したり参照したりしている場合に クエリ量は少なく、かつロックも発生していないのに負荷がかかっている 場合があります。 例えばMySQLに画像データを保存しているような場合です。 このようにMySQL Trafficの値がクエリ数に対して明らかに多い場合などの指標になります。 またシステムメトリックスのdiscやinterfaceが高い場合にそのサーバがMySQLならばMySQL Trafficを見てみると原因切り分けの良い指標になるでしょう。

MySQL Capacity

メトリック名(ラベル) プラグインの出力名 diff 説明
Percentage Of Connections mysql.capacity.PercentageOfConnections 現在の接続数/最大接続の割合
Percentage Of Buffer Pool mysql.capacity.PercentageOfBufferPool BufferPoolの割合

この2つの値は下記の式で割合を計算しています。

PercentageOfConnections = 100.0 * Threads_connected / max_connections

PercentageOfBufferPool = 100.0 * database_pages / pool_size

Percentage Of Connections はその名の通り、接続の割合です。 MySQL Connectionsの章で Connectionsの値はMax Connectionsの7割から8割くらいを閾値にして監視するようにしましょう。 と言いましたがConnectionsは数なので割合は自分で計算して指定する必要があります。1台のみなら良いですがDBが沢山あってMax Connectionsの値がそれぞれ違う場合に計算が面倒です。 そんなあなたのために用意されてるのがPercentage Of Connectionsです。 まさにこの値を70~80程度に設定して監視しましょう。

PercentageOfBufferPoolの2つの値は SHOW ENGINE INNODB STATUS から取り出しています。 また INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS でもdatabase_pages と pool_size を見ることはできます。 それぞれの値は次の通りです。

POOL_SIZE

  • InnoDB バッファープールのサイズ (ページ単位)。

database_pages

  • データを含む InnoDB バッファープール内のページの数
  • 数には、ダーティーページとクリーンページの両方を含みます。

つまりInnoDB Buffer Poolを使っているかどうかの割合を示してくれます。 MySQLは何をするにしてもInnoDB Buffer Poolを活用するのでこの値が少ないということはリソースを余らせている事がわかります。

ですのでMySQL Capacityの2つの値を有効活用することでMax Connectionsの数やMySQLインスタンスが適正か考える事が出来るのです。とても大切な値ですし、長期的に見る必要のある値なので直近二週間だけでなく、1年通じてどうか?など定期的に長期間で見るとより良いでしょう。

さてここまで如何でしたか? 量が多くて疲れてきたことでしょう。 なんとまだ半分くらいです。 ということでここで一旦締めて、次回に続きます。 (InnoDBの監視の監視は別の日にやります) 次回はインメモリデータベースとして使ってる人も多いのではないでしょうか。 Redisの監視です。

5日目 mackerel-plugin-redis