この記事は Mackerel プラグインアドベントカレンダー(全部CRE) の4日目です。
それでは4日目は mackerel-plugin-mysql
です。
ここではMySQLの細かい説明は割愛します。
mackerel-plugin-mysqlはRDBMSとして広く使われているMySQL専用のプラグインです。
インストールと設定手順
本プラグインはプラグイン集として提供しているパッケージの 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 VARIABLES
はMySQL システム変数の内容を出力します。
つまり今どんな設定が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_size
や SET 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
を実行し細かい調査を行うことをオススメします。
実際の調査方法については奥野さんの最高の記事があるのでご一読ください。
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の監視です。