ZabbixでMysqlを監視する

今回は、Mysqlサーバをリソース監視システム(Zabbix)でリソース・死活監視する方法について整理して見ました。
運用中のMysqlのリソースを監視し、パフォーマンスのチューニングポイントを見つけてみましょう。

インストール環境とバージョン

今回、インストールするサーバとzabbixのバージョンは以下のようになります。

名前	バージョン	ダウンロード元
ubuntu	10.04.4(64bit)	http://releases.ubuntu.com/lucid/
ZABBIX	2.2.0	http://www.zabbix.com/

Zabbix Serverインストール

「Zabbix Serverのインストール手順」を参照してください。

Zabbix Agentインストール

「Zabbix Agentのインストール手順」を参照してください。
※AgentサーバにはMysqlが設置されている状態です。

Mysqlインストール

「MySQL 5.6.16のインストール手順」を参照してください。

Zabbix Agent側での設定

１．mysqlにzabbix agentユーザ登録
※パスワードは変更してください。

# mysql -uroot -p

mysql> grant process on *.* to 'zabbix_agent'@'localhost' identified by 'changeit';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user where user='zabbix_agent';
+-----------+--------------+
| host      | user         |
+-----------+--------------+
| localhost | zabbix_agent |
+-----------+--------------+
1 row in set (0.00 sec)

２．mysqlに接続するID/PWファイル生成
zabbix_my.cnfというファイルを生成し、ファイルの権限を変更します。

# vi /usr/local/etc/zabbix_my.cnf

[client]
user = zabbix_agent
password = changeit

# chmod 640 /usr/local/etc/zabbix_my.cnf
# chown zabbix:zabbix /usr/local/etc/zabbix_my.cnf

３．mysqlからstatus情報を取得するShell作成
Mysqlのshow global status,show engine innodb statusから情報を取得するためにShellを作成します。
※他のstatus情報も必要な場合は$1=="Bytes_sent" { print "Bytes_sent", $2 }部分を追加してください。

# vi /usr/local/etc/mysql_stat_zabbix.sh

#!/bin/sh
mycnf_path=/usr/local/etc/zabbix_my.cnf

echo_sql() {
  cat <<'EOF'
show global status;
show engine innodb status\G
EOF
}

echo_sql | mysql --defaults-extra-file=$mycnf_path | sed -e "s/------- TRX HAS BEEN WAITING/Trx_waiting/g" | awk '
BEGIN{w_time_sum=0} {if($1=="Trx_waiting") w_time_sum += $2 } END{print "Trx_waiting", w_time_sum}
$1=="Bytes_received" { print "Bytes_received", $2 }
$1=="Bytes_sent" { print "Bytes_sent", $2 }
$1=="Com_insert" { print "Com_insert", $2 }
$1=="Com_select" { print "Com_select", $2 }
$1=="Com_update" { print "Com_update", $2 }
$1=="Com_delete" { print "Com_delete", $2 }
$1=="Threads_connected" { print "Threads_connected", $2 }
$1=="Threads_running" { print "Threads_running", $2 }
/^Buffer pool hit rate [0-9]+ \/ [0-9]+/ {
  print "Buffer_pool_hit_rate", $5 / $7
}
/^[0-9.]+ inserts\/s, [0-9.]+ updates\/s, [0-9.]+ deletes\/s, [0-9.]+ reads\/s/ {
  print "insertsPerSec", $1
  print "updatesPerSec", $3
  print "deletesPerSec", $5
  print "readsPerSec", $7
}
'

# chown zabbix:zabbix /usr/local/etc/mysql_stat_zabbix.sh
# chmod 700 /usr/local/etc/mysql_stat_zabbix.sh

４．zabbix agentのUserParameterの設定する。
zabbix agentの設定ファイルを修正します。

# vi /usr/local/etc/zabbix_agentd.conf

コメントアウト解除
Include=/usr/local/etc/zabbix_agentd.conf.d/

Mysqlから情報を取得するUserParameterを追加します。

# vi /usr/local/etc/zabbix_agentd.conf.d/custom_mysql.conf


UserParameter=mysql.max_connections[*],/usr/local/bin/mysqladmin --defaults-extra-file=/usr/local/etc/zabbix_my.cnf variables|grep max_connections| awk '{print $$4}'

UserParameter=mysql.threads_connected[*],/usr/local/bin/mysqladmin --defaults-extra-file=/usr/local/etc/zabbix_my.cnf  extended-status|grep Threads_connected| awk '{print $$4}'

UserParameter=mysql.threads_created[*],/usr/local/bin/mysqladmin --defaults-extra-file=/usr/local/etc/zabbix_my.cnf  extended-status|grep Threads_created| awk '{print $$4}'

UserParameter=mysql.threads_running[*],/usr/local/bin/mysqladmin --defaults-extra-file=/usr/local/etc/zabbix_my.cnf  extended-status|grep Threads_running| awk '{print $$4}'

UserParameter=mysql.thread_cache_size[*],/usr/local/bin/mysqladmin --defaults-extra-file=/usr/local/etc/zabbix_my.cnf  variables|grep thread_cache_size| awk '{print $$4}'

UserParameter=mysql.connections[*],/usr/local/bin/mysqladmin --defaults-extra-file=/usr/local/etc/zabbix_my.cnf  extended-status|grep Connections| awk '{print $$4}'

UserParameter=mysql.query_per_sec_avg[*],/usr/local/bin/mysqladmin --defaults-extra-file=/usr/local/etc/zabbix_my.cnf  status|awk '{print $$22}'

UserParameter=mysql.status[*],/usr/local/bin/mysqladmin --defaults-extra-file=/usr/local/etc/zabbix_my.cnf extended-status |grep -w $1 | awk '{print $$4}'

UserParameter=mysql.innodb.status[*],/usr/local/etc/mysql_stat_zabbix.sh | grep -w $1 | awk '{print $$2}'


#max_connections：これまでに記録された同時接続数の最大値
#Threads_connected：現在開いている接続の数
#thread_cache_size：接続を処理するために生成されたスレッド数。起動してから生成されたスレッドの数。この値が大きい場合はthread_cache_sizeの値を大きくした方が良いかも。
#Threads_running：スリープ状態になっていないスレッド数
#thread_cache_size：スレッドキャッシュでキャッシュされているスレッドの数。
#Queries per second avg：問い合わせ平均応答秒数

zabbix agentを再起動します。

# /etc/init.d/zabbix-agent restart

Zabbix Serverでの確認

zabbix-agentで設定した内容が問題なく取得できているのか確認してみましょう。

# zabbix_get -s 192.168.56.102 -k mysql.innodb.status[Trx_waiting]
0

# zabbix_get -s 192.168.56.102 -k mysql.max_connections
300

結果が問題なく返ってくると正常です。
zabbix_getを設置する方法については以下のページの【zabbix-getのInstall】部分をご参照ください。
Zabbix Server2.2.0のインストール手順

ZabbixのWEB管理ページでの設定

１．テンプレートをダウンロードする
テンプレートは以下のページでダウンロードしてください。
・Template_JP_App_mysql
※こちらのファイルはhttp://www.zabbix.jp/のテンプレートを基に修正したファイルになります。

２．テンプレートを管理画面にインポートする
設定⇒テンプレート⇒インポートページにてダウンロードした【Template_JP_App_mysql】ファイルをインポートしてください。


３．ホストにテンプレート追加
設定⇒ホスト⇒監視対象の設定ページ(名前の部分を押下)⇒テンプレートタブ
上記のページにて【Template_JP_App_MySQL】を追加してください。

４．グラフの確認
ここまで、問題なく設定ができたら、グラフページにて７つのグラフがご確認いただけると思います。
監視データ⇒グラフ

MySQL Command Counters
MySQL Connections
MySQL Handlers
MySQL InnoDB Current Lock Waits
MySQL Select Types
MySQL Threads
MySQL Transaction Handler

※グラフの色などは自由に変更してください。

MySQLのチューニングポイント

・SQL の種類を確認する MySQL Command Counters

Com は Command の略で Com Select / Delete / Insert / Update / Replace はその名前のとおりの SQL の実行回数です。 Com xxx Multi と付いているのは複数テーブルを一括して Update するMySQL 独特の構文です。
QuestionsはQuestionsはMySQL Serverが発行したクエリの総数で、SET等の補助的なコマンドとエラーの応答もカウントされます。
Questionsだけが増加して来たら、サーバ側で何かのエラーを起こしている可能性が高いそうです。 SET @key := '1'

・ロック待ち時間が確認できる InnoDB Current Lock Waits

SHOW ENGINE INNODB STATUSで表示されるTRANSACTIONS情報のうち、ロックの時間が記録されておりますTRX HAS BEEN WAITING 【秒】 SEC FOR THIS LOCK TO BE GRANTEDの秒数の合計値で、InnoDBのテーブルもしくは行のロックがかかった時に表示されます。
該当の数値が増えた場合はSHOW FULL PROCESSLIST;もしくはslow queryログ等で、どの部分でロックがかかっているのか確認し、対応する必要があります。

・トランザクション状況が分かる MySQL Transaction Handler

このグラフはトランザクションの完了、ロールバック数を表します。
ロールバックの現状を通じてクエリのエラーとDeadlockも予測できます。

・SELECTの実行計画が確認できる MySQL Select Types

このグラフは個別クエリに対してEXPLAINすることで、どの実行計画を使っているのかの確認が出来ます。
Select_full_join：2つ以上のテーブルにおいて全件同士で(Indexを使わずに)JOINした回数。最も致命的。
Select_full_range_join：片方のテーブルで全件、もう片方のテーブルで範囲検索を行ってJOINした回数。
Select_scan：テーブルのデータを全件検索した回数。
Select Range：範囲が限定された探索(WHERE,HAVINGなど)を行った回数。

・クエリの I/O 動作を知ることが出来るグラフ MySQL Handlers

MySQLクエリ実行後ストレジエンジンAPIを通じて発生しているFileやDisk I/Oの確認が出来ます。

• Handler Read First : インデックスから最初のエントリーが読み込まれた回数。【Full Index Scanした回数】この値が大きい場合は、サーバがインデックスのフルスキャンを多く行っているという -> チューニング必要
• Handler Read Key : キーに基づくレコード読み取り要求の回数。この値が大きい場合、クエリおよびテーブルが適切にインデックス化されていると考えられる。
• Handler Read Next : キー値に基づいて行を特定した後、後続の行を読んだ回数
• Handler Read Prev : キー順序での前のレコードの読み取り要求の回数。これは主に、ORDER BY ... DESC を最適化するのに使用される。
• Handler Read Rnd : 固定位置に基づくレコード読み取り要求の回数。 結果のソートを必要とするクエリを多く実行すると、この値が大きくなる。
• Handler Read Rnd Next : データファイルでの次のレコードの読み取り要求の回数。 テーブルスキャンが多く実行されると、この値が大きくなる。この場合、一般的に、テーブルが適切にインデックス化されていないか、クエリがインデックスを有効に利用していないかを意味する。 -> チューニング必要

【参照したサイト】

• percona-monitoring-plugins
• MySQLの監視テンプレートを適用する
• これだけ見れば大丈夫！ーMySQLパフォーマンス監視のツボ（クエリ編）
• Zabbixでmysqlをモニタリングするスクリプト