カジュアルに MySQL を監視する方法

このエントリーは、MySQL Casual Advent Calendar 2011 – MySQL Casual の第 19 日目のエントリーです。

 

皆さんこんにちは、n0ts こと、Naoya Nakazawa です。

今日は、みなさん日頃からカジュアルに MySQL を運用して、日々生活されていることと思います。MySQL は、非常に安定したオープンソースソフトウェアだと思いますが、どんなものでもときにはおかしくなったりするものです。

「備えあれば憂いなし」ということで、僕は日頃から Nagios というオープンソースソフトウェアを利用して、MySQL がおかしくなっていかいか日々カジュアルに監視しています。

今日は、カジュアルに MySQL を Nagios を使って監視する方法を紹介したいと思います。なお、今回は CentOS 5.7 x86_64 というカジュアルな Linux ディストリビューションを使っていて、その中に MySQL がインストールされて、MySQL サーバが動いている環境で解説しています。

 

まず、一番カジュアルで簡単な監視方法は、Nagios 公式の監視プラグインを使った監視です。Nagios 公式の監視プラグインは、次のコマンドでインストールすることができます。


# yum install nagios-plugins -y


64bit 環境の場合、/usr/lib64/nagio/plugins に、Nagios 公式の監視プラグインがインストールされます。

たくさんの監視プラグインがありますが、この中で MySQL を監視することができるプラグインは、次の 2 種類があります。

• check_mysql
• check_mysql_query

それぞれのプラグインについて解説していきます。

 

check_mysql

check_mysql は、MySQL サーバへの接続をテストする監視プラグインです。使い方は、とっても簡単です。次のように実行するだけです。

$ /usr/lib64/nagios/plugins/check_mysql
Uptime: 1131841 Threads: 2 Questions: 708 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 1 Queries per second avg: 0.0


とっても簡単ですね！これで監視はできていますが、念のため省略されている引数をつけると次のようになります。

$ /usr/lib64/nagios/plugins/check_mysql -H localhost -u root
Uptime: 1131936 Threads: 2 Questions: 709 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 1 Queries per second avg: 0.0


上の例では、ローカルホストにある MySQL サーバに対して root というユーザ名で接続して MySQL サーバに接続できるか監視しています。
ちなみに、MySQL サーバをわざと停止してから、エラーになるかどうか確認してみましょう。

$ sudo /etc/init.d/mysql stop
Shutting down MySQL.. [ OK ]
$ /usr/lib64/nagios/plugins/check_mysql -H localhost -u root
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)


問題ないですね！

この他にもスレーブの遅延も検出することができます。これも、よく使う定番の監視といえるでしょう。

$ /usr/lib64/nagios/plugins/check_mysql -H db-ads2 -u repl_client -p repl_pass --check-slave --warning 5 --critical 10
Uptime: 8229294 Threads: 186 Questions: 2092736633 Slow queries: 10 Opens: 231 Flush tables: 1 Open tables: 143 Queries per second avg: 254.303 Slave IO: Yes Slave SQL: Yes Seconds Behind Master: 0


–check-slave というオプションは、スレーブとして動作しているか確認するためのオプションです。そのあとに閾値である warning と critical を使って、スレーブの遅延が何秒があったときエラーとして検出するのかを指定しています。上の例では、5 秒遅延になったとき警告レベル、10 秒遅延になったとき致命レベルとして検出しています。

 

check_mysql_query

check_mysql_query プラグインは、とある SQL クエリーを発行した結果、その結果の数値がある閾値内にあるかどうか監視するプラグインです。指定する SQL クエリーは、必ず結果が数字にならないといけません。
例えば、次のように実行します。


$ /usr/lib64/nagios/plugins/check_mysql_query --query "SELECT 1" --warning 2:2 --critical 1:1
QUERY WARNING: 'SELECT 1' returned 1.000000


上の例では、「SELECT 1」というクエリーを実行して、その結果が 2 から 2 までにないときは警告レベル、1 から 1 までに ないときは致命レベルとして検出しています。上の例だと、「SELECT 1」というクエリーは必ず結果が 1 になるため、警告レベルとして検出されます。

このプラグインは、SQL クエリーを指定できるため、非常に柔軟に MySQL を監視することができます。

例えば、次の例は test というデータベースにある employees テーブルのレコード件数を数えて、レコード数が 2000 件を超えたときは警告レベル、3000 件を超えたときは致命レベルとして検出します。

$ /usr/lib64/nagios/plugins/check_mysql_query --database test --query "SELECT COUNT(*) from employees" --warning 1:2000 --critical 1:3000
QUERY OK: 'SELECT COUNT(*) from employees' returned 1.000000


check_mysql_query プラグインを使うと、より実際の MySQL のデータベース内容を監視することができます。

Nagios 公式で提供されている MySQL 監視プラグインは上の二つになります。

みなさん、これで十分に MySQL をカジュアルに監視して、夜も安心して眠れるようになったでしょうか？眠れるようになったという人は、ここで読み終えてください。まだ、何か足りないんじゃない？と思った人は、このあとも読んでみてください。。。

さらに MySQL を Nagios を使って監視するプラグインに、check_mysql_health という非常に便利なプラグインがあります。

check_mysql_health

check_mysql_helath プラグインは、さきほど解説した check_mysql と check_mysql_query を組み合わせて、さらに多くの監視ができるプラグインです。公式のマニュアルは、ここ（英語）で公開されています。
具体的には、次のことができます。

• MySQL サーバへの接続にかかる時間
• MySQL サーバが動作している時間
• 現在、開かれているコネクション数
• スレッドキャッシュのヒット率 スレーブの遅延（check_mysql –check-slave と同じ）
• Slave io running の監視
• Slave sql running の監視
• クエリーキャッシュのヒット率
• クエリーキャッシュでメモリ不足のため、切り落としたエントリ数
• MyIASM のキーキャッシュのヒット率
• InnoDB バッファープールのヒット率
• InnoDB バッファープールのクリーンページが利用可能になるまで秒数
• InnoDB のログウェイト時間（これは、ログバッファーが小さすぎるときに発生する）
• テーブルキャッシュのヒット率
• テーブルロックの失敗率
• インデックスの利用率 ディスク上に作成された一時的テーブルの割合
• スロークエリー
• 長く動いているプロセスの監視
• ndnd ノードが起動していて動いている数
• あるクエリーの結果の特定の数値がどうか（check_mysql_query と同じ）

いかがでしょう！こんなにたくさんの項目を監視することができます。

この中で、比較的重要な監視の方法を解説していきます。

まず、MySQL へ接続する時間の監視です。これは、ウェブアプリケーションから MySQL へ接続する時間がかかってしまうと、ウェブアプリケーションが重い状態になってしまいます。こんなときのために MySQL へ接続する時間を監視しておくと、とても便利です。これは、次のように実行します。

$ /usr/lib64/nagios/plugins/check_mysql_health --hostname localhost --user root --mode connection-time --warning 0.5 --critical 1.0
OK - 0.02 seconds to connect as root | connection_time=0.0211s;0;1


上の例では、MySQL への接続に 0.5 秒かかったときは警告レベル、1.0 秒かかったときは致命レベルとして検出します。秒数は、1 秒以下も指定できるため、かなり厳密な時間で監視することができますので、とても重宝します。

クエリーキャッシュのヒット率を監視したい場合は、次のように実行します。

$ /usr/lib64/nagios/plugins/check_mysql_health --hostname localhost --user root --mode querycache-hitrate --warning 80: --critical 70:
WARNING - query cache hitrate 70.82% | qcache_hitrate=70.82%;80:;70: qcache_hitrate_now=62.82% selects_per_sec=420.17


上の例では、クエリーキャッシュのヒット率が 80% をきったときは警告レベル、70% をきったときは致命レベル、として検出します。クエリーキャッシュを使っている環境の場合は、とても重要な監視となるでしょう。

スレッドキャッシュのヒット率を監視したい場合は、次のように実行します。

$ /usr/lib64/nagios/plugins/check_mysql_health --hostname localhost --user root --mode threadcache-hitrate --warning 80: --critical 70:
CRITICAL - thread cache hitrate 7.50% | thread_cache_hitrate=7.50%;80:;70: thread_cache_hitrate_now=7.50% connections_per_sec=0.00


上の例では、スレッドキャッシュのヒット率が 80% をきったとき警告レベル、70% をきったとき致命レベル、として検出します。上の場合だと、スレッドキャッシュのヒット率が 7.50% なので致命レベルとなっています。

InnoDB のバッファープールのヒット率を監視したい場合は、次のように実行します。

$ /usr/lib64/nagios/plugins/check_mysql_health --hostname localhost --user root --mode innodb-bufferpool-hitrate --warning 80: --critical 70:
OK - innodb buffer pool hitrate at 80.00% | bufferpool_hitrate=80.00%;80:;70:;0;100 bufferpool_hitrate_now=100.00%


とても簡単ですね。

check_mysql_health プラグインを使うと、さらに多くの監視を MySQL に対して行うことができます。

さらにカジュアルにもう一方踏み込んで監視するプラグインに mysql_health_check があります。

mysql_health_check

mysql_health_check プラグインは、「SHOW FULL PROCESSLIST」や「SHOW GLOBAL VARIABLES」や「SHOW GLOBAL STATUS」の結果を監視するプラグインです。引数の指定で、これらの出力を一定時間キャッシュさせておくことができます。残念ながら、現時点でのバージョンでは「SHOW ENGINE INNODB STATUS」はコメントになっており機能していません。将来的にはきっと対応してくれることでしょう。

mysql_health_check プラグインは、必ずモードを –mode で指定する必要がありますが、long-query、lock-query、varcomp から指定することができます。それぞれの意味は、次のとおりです。

• long-query: 指定した秒数以上実行しているクエリーを検出します
• lock-query: 指定した秒数以上ロックしているいクエリーを検出します
• varcomp: 「SHOW GLOBAL VARIABLES」や「SHOW GLOBAL STATUS」の中にある変数やステータスが閾値にあるかどうか検出します

long-query の実行例は、次のとおりです。

$ /usr/lib64/nagios/plugins/mysql_health_check.pl --hostname localhost --user root --mode long-query --warning 30 --critical 60
mysql_long-query OK


上の例では、30 秒以上実行しているクエリーを検出したときは警告レベル、60 秒以上実行しているクエリーを検出したときは致命レベル、として検出します。

varcomp の実行例は、次のとおりです。これは、すこし複雑かもしれません。。。

$ /usr/lib64/nagios/plugins/mysql_health_check.pl --hostname localhost -u root --mode varcomp --expression="Connections" --warning=">100" --critical=">200" --cache_dir=/tmp --max_cache_age=90
mysql_varcomp OK - Comparison check passed: (Connections) >100 >200 = '59'


上の例では、「Connections」という変数およびステータスが 100 以上になったとき警告レベル、200 以上になったとき致命レベルとして検出します。さらに /tmp にキャッシュディレクトリを指定して、90 秒の出力結果をキャッシュしています。

mysql_health_check プラグインを使うとさらに MySQL の内部の内部まで監視することができるので、とても便利です。

この他にも MySQL を監視するプラグインはたくさんありますし、自分で作成している人も多いと思います。
ちなみに僕は、カジュアル派なので、今のところ今回紹介した Nagios プラグインだけを使って MySQL を監視しています。

明日は、@sohgoh さんです。

Tags: mysql nagios, mysqlcasual