monによるMySQLのデッドロック検知とロギング
更新が激しいDB(MySQL)でInnoDBのロック競合が発生し、アプリケーションサーバが詰まる状況が発生してしまいました。
障害監視はmonというアプリケーションで行なっているのですが、
今回はこのmonを使ってMySQLデッドロックの検知とロギングを行いたいと思います。
monについては下記の資料をご参照ください。
Mon, Muninによる楽々監視生活
デッドロック解析は下記サイトのSQLを利用しています。
MySQL InnoDBにおけるロック競合の解析手順
前提
・MONのサーバは既に構築済みであること
・DBサーバはMySQL5.5であること(MySQL5.1+InnoDB pluginでも可)
〜〜〜 以下、監視対象のDBサーバにて作業 〜〜〜
SNMPインストール
yum install net-snmp
lockを検知するスクリプト作成
vim /usr/local/sbin/mysql_lock_check.sh
#!/bin/sh #--------------------# # Define ENV # #--------------------# export PATH=$PATH:/usr/local/mysql/bin:/usr/bin/mysql HOST=`hostname` #--------------------# # DIR & FILE # #--------------------# NAME=`basename $0 .sh` PIDFILE=`dirname $0`/$NAME.pid MYSQL_DIR='/data/mysql' if [ -f "$PIDFILE" ] then echo 2 exit 2; fi echo $$ > $PIDFILE CNT=`mysql -N -uroot -e 'select count(*) from information_schema.INNODB_LOCK_WAITS'` if [ "${CNT}" != "0" ]; then rm $PIDFILE echo `date` >> ${MYSQL_DIR}/${HOST}_innodb_lock.log mysql -uroot -e 'select t_b.trx_mysql_thread_id blocking_id,t_w.trx_mysql_thread_id requesting_id,p_b.HOST blocking_host,p_w.HOST requesting_host,l.lock_table lock_table,l.lock_index lock_index,l.lock_mode lock_mode,p_w.TIME seconds,p_b.INFO blocking_info,p_w.INFO requesting_info from information_schema.INNODB_LOCK_WAITS w,information_schema.INNODB_LOCKS l,information_schema.INNODB_TRX t_b,information_schema.INNODB_TRX t_w,information_schema.PROCESSLIST p_b,information_schema.PROCESSLIST p_w where w.blocking_lock_id = l.lock_id and w.blocking_trx_id = t_b.trx_id and w.requesting_trx_id = t_w.trx_id and t_b.trx_mysql_thread_id = p_b.ID and t_w.trx_mysql_thread_id = p_w.ID order by requesting_id,blocking_id \G' >> ${MYSQL_DIR}/${HOST}_innodb_lock.log echo 1 exit 1; fi rm $PIDFILE echo 0 exit 0;
SNMPの設定変更
vim /usr/local/net-snmp/etc/snmpd.conf
下記を追記
extend .1.3.6.1.4.1.3000.40 mysql_lock /bin/sh /usr/local/sbin/mysql_lock_check.sh
〜〜〜 以下、MONサーバで作業 〜〜〜
MONのチェックスクリプト作成
vim /usr/local/mon/mon.d/mysql_InnoDB_lock_check.monitor
#!/bin/sh RET='' ERRORWORD="MySQL InnoDB lock check failed." ERRORWORD1="MySQL InnoDB locked." ERRORWORD2="MySQL InnoDB lock check is already running." for host in "$@" do #EXEC RET=`/usr/local/net-snmp/bin/snmpwalk -v2c -c casnmp ${host} .1.3.6.1.4.1.3000.40.3.1.4 | gawk '{print $4}'` #RET CHECK if [ "${RET}" = 0 ]; then : else if [ "$SummaryOutput" = "" ]; then SummaryOutput=${host} eval DetailedText=\${host}\" : \"\${ERRORWORD${RET}} else SummaryOutput="${SummaryOutput} ${host}" eval DetailedText=\${DetailedText}\" \\n\"\${host}\" : \"\${ERRORWORD${RET}} fi fi done # ERROR CHECK if [ "${SummaryOutput}" != "" ]; then echo "${SummaryOutput}" echo -e "${DetailedText}" exit 1 fi exit 0
MONの設定ファイルに追記
vim /usr/local/mon/etc/mon.cf
hostgroup hogehoge-mysql-InnoDB_lock {監視対象IPアドレス} watch hogehoge-mysql-InnoDB_lock service mysql_InnoDB_lock interval 3m monitor mysql_InnoDB_lock_check.monitor allow_empty_group period wd {Sun-Sat} alertevery 5m alertafter 3 15m alert mail.alert -f {アラートメール送信先@hogehoge.com} upalert mail.alert -f {アラートメール送信先@hogehoge.com} -S "hogehoge server is back up [mysql_InnoDB_lock]" -u {アラートメール送信先@hogehoge.com}
MONサーバ再起動
/etc/init.d/mon stop
/etc/init.d/mon start
ロック情報のログの見方
mysqlのディレクトリにログを吐いてます。
less ${MYSQL_DIR}/hostname_innodb_lock.log
0 : 2013年 3月 14日 木曜日 16:02:20 JST *************************** 1. row *************************** 1 : blocking_id: 220 2 : requesting_id: 227 3 : blocking_host: 10.*.*.101:53890 4 : requesting_host: 10.*.*.102:32783 5 : lock_table: `hoge`.`user_status_5` 6 : lock_index: `PRIMARY` 7 : lock_mode: X 8 : seconds: 38 9 : blocking_info: NULL 10: requesting_info: update user_status_5 set f_cnt = f_cnt+1 where user_id = 130
このログからは
1行目と2行目と8行目の情報から
220番のスレッドが227番のスレッドを38秒待たせている
5行目と6行目の情報から
220番のスレッドはuser_status_5テーブルの主キーを排他ロックしている
3行目の情報から
10.*.*.101のサーバがブロックしている
9行目の情報から
220番のスレッドは現在アイドル状態である
10行目の情報から
227番のスレッドはuser_id:130のレコードを更新しようとしている
デッドロックを検知したら、どのアプリケーションサーバがブロックしているのかわかるのでそのプロセスを殺すなりしましょう。
頻繁に発生するようなら、データ更新の仕組み自体も改善してもらいましょう…。
エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
- 作者: 奥野幹也
- 出版社/メーカー: 技術評論社
- 発売日: 2010/06/12
- メディア: 大型本
- 購入: 16人 クリック: 204回
- この商品を含むブログ (35件) を見る