MySQLを本番で運用する場合、ヘルスチェック（監視）は欠かせません。ヘルスチェックには大きく分けて死活監視（MySQLのプロセスが稼働しているかどうか）と傾向監視（レスポンスを悪化させるような兆候が表れていないか）の2つがあると思います。

今回は第12回から少し応用をきかせた監視方法を説明したいと思います。第12回で説明した事柄は今回は省略しますので、ご覧になっていない方は、先に第12回をご覧いただくことをお勧めします。

mysqlコマンドを利用した監視スクリプト

データベースが問題なく動いているかどうかを確認するためにはMySQLの外側から監視する必要がある、という話を第12回の最後にしました。それでは、MySQLを外側から監視する簡単な実装を見てみましょう。サンプルということで、mysqlコマンドを利用したシェルスクリプトとして作ってみます。

#!/bin/bash

export MYSQL_PWD="test"

if mysql -h127.0.0.1 -P64056 -uyoku0825 -e "quit" > /dev/null 2>&1 ; then
  echo "Connection succeeded."
  exit 0
else
  echo "Connection failed."
  exit 2
fi

MYSQL_PWD環境変数にはMySQLへのログイン用パスワードを設定します（これにより、-pオプションにパスワードを指定することなくそのパスワードを利用してログインを試行します⁠）⁠。スクリプトの中でやっていることは、mysqlコマンドに-hオプションで接続先IPアドレスを指定、-Pオプションでポート番号を指定、-uオプションで監視用のユーザー名を指定して、-eオプションで"quit"コマンドを送信しています。つまり、ホスト127.0.0.1の64056番ポートに対してyoku0825ユーザで接続し、接続できれば"Connection Succeeded"を、失敗すれば"Connection failed"を出力するシンプルなものです。

mysqladmin pingコマンドに比べると、「⁠サーバからの接続拒否」はクライアント上でエラーになるため、失敗としてハンドルされます。アカウントとパスワードの組が間違っている場合も失敗としてハンドルです。これで少なくとも"Error: 1040 Too many connections"などのエラーは拾えることになりました。

続いて、特定のテーブルから読み取りができるかどうかを試してみることにしましょう。

#!/bin/bash

export MYSQL_PWD="test"
set_timeout="SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=1"

if mysql -h127.0.0.1 -P64056 -uyoku0825 -e "quit" > /dev/null 2>&1 ; then
  if mysql -h127.0.0.1 -P64056 -uyoku0825 --init-command="$set_timeout" -e "SELECT * FROM d1.t1 WHERE rand() < 0.01 ORDER BY _rowid DESC LIMIT 100 FOR UPDATE" > /dev/null 2>&1 ; then
    echo "Connection and data fetching succeeded."
    exit 0
  else
    echo "Connection succeeded but data fetching failed."
    exit 1
  fi
else
  echo "Connection failed."
  exit 2
fi

innodb_lock_wait_timeoutオプションとlock_wait_timeoutオプションを1（単位は秒です）に設定することで、1秒間ロック待ちが継続した場合タイムアウトします。例の中で利用しているステートメントは、「⁠直近の1万行程度をロックしてみる」ステートメントだと思ってください。

InnoDBのSELECTは本来ロックフリーですが、FOR UPDATEを明示することで排他ロックを取ることができます。このステートメントの取ったロックはmysqlコマンドの終了時点で開放されます。他の書き込みステートメントと競合してロック待ちのタイムアウトが発生するとmysqlコマンドが0以外の終了コードを返すため、「⁠"Connection succeeded but data fetching failed."」の出力と終了コード1を返すことができます。このスクリプトであれば、d1スキーマのt1テーブルに関しては、直近1万行程度はロックが競合していないところまでは検出できそうです。

今度はこれを複数テーブルに対応させてみます。

#!/bin/bash

export MYSQL_PWD="test"
set_timeout="SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=1"

if mysql -h127.0.0.1 -P64056 -uyoku0825 -e "quit" > /dev/null 2>&1 ; then
  mysql -h127.0.0.1 -P64056 -uyoku0825 -sse "SHOW TABLES FROM d1" | while read table ; do
    if mysql -h127.0.0.1 -P64056 -uyoku0825 --init-command="$set_timeout" -e "SELECT * FROM d1.${table} WHERE rand() < 0.01 ORDER BY _rowid DESC LIMIT 100 FOR UPDATE" > /dev/null 2>&1 ; then
      echo "Connection and data fetching succeeded on d1.${table}."
    else
      echo "Connection succeeded but data fetching failed on d1.${table}"
      exit 1
    fi
  done
  exit 0
else
  echo "Connection failed."
  exit 2
fi

-ssオプション（行ヘッダを出力しません）を利用して、SHOW TABLESステートメントでテーブルの一覧を取り出してwhile readでループさせています。SELECTに成功した場合は続きのテーブルを改めてSELECTしますが、ひとつでもタイムアウトしたテーブルがあれば、そこでスクリプトは終了するようになっています。これでテーブルが増えても、スクリプトを改修することなく監視を継続することができそうです。ただし、_rowidというシノニムを使っているため、全てのテーブルにauto_incrementのカラムがあることにスクリプトの動作が依存してしまっています。auto_incrementを使わないカラムがあっても動くようにスクリプトを対応させてみましょう。

#!/bin/bash

export MYSQL_PWD="test"
set_timeout="SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=1"

if mysql -h127.0.0.1 -P64056 -uyoku0825 -e "quit" > /dev/null 2>&1 ; then
  mysql -h127.0.0.1 -P64056 -uyoku0825 -sse "SHOW TABLES FROM d1" | while read table ; do
    have_autoinc=$(mysql -h127.0.0.1 -P64056 -uyoku0825 --init-command="$set_timeout" \
                     -ssEe "SHOW CREATE TABLE d1.${table}" | grep "AUTO_INCREMENT" | wc -l)
    if [ "$have_autoinc" = "1" ] ; then
      check_sql="SELECT * FROM d1.${table} WHERE rand() < 0.01 ORDER BY _rowid LIMIT 100 FOR UPDATE"
    else
      check_sql="SELECT * FROM d1.${table} WHERE rand() < 0.01 ORDER BY NULL LIMIT 100 FOR UPDATE"
    fi

    if mysql -h127.0.0.1 -P64056 -uyoku0825 --init-command="$set_timeout" -e "$check_sql" > /dev/null 2>&1 ; then
      echo "Connection and data fetching succeeded on d1.${table}."
    else
      echo "Connection succeeded but data fetching failed on d1.${table}"
      exit 1
    fi
  done
  exit 0
else
  echo "Connection failed."
  exit 2
fi

SHOW CREATE TABLEの出力結果を縦表示（-Eオプション）させ、AUTO_INCREMENTを含む行の数を数えることで、そのテーブルがauto_increment属性を持ったカラムを持っているかどうか判定しています。これでauto_increment属性がない場合でもエラーになることはなくなりました。

細かいことを言えば、このスクリプトはmysqlコマンドが起動するたびにコネクションを張り直しているため、タイムアウト以外でも接続に失敗すれば"Connection succeeded but data fetching failed on d1.${table}"が出力されてしまいます。正しくは、監視スクリプトの先頭で接続を確保し、そのコネクションを使いまわすべきでしょう（ただしそれをシェルスクリプトで書くのは、筆者には少し面倒です⁠）⁠。

また、トランザクションに対応したライブラリを利用していれば、直近の1万行程度を排他ロックでスキャンするよりも、ダミーのデータを1行INSERTしてROLLBACKする方が確実にロック以外の面も含めて書き込みができるであろうことを保証できます。試しに排他ロックを取ってみる必要もないかも知れません、アカウントに権限を付与する必要がありますが、information_schema.INNODB_TRXテーブルで実行中のトランザクションを確認することができますので、その有無によって判断しても良いでしょう。

おっと、サーバがスレーブの場合、SHOW SLAVE STATUSの結果も欠かせません。マスターとの接続は途切れていないか、スレーブの遅延は発生していないかをSeconds_behind_masterカラムの値から判断することができます。余裕があればSHOW GLOBAL STATUS LIKE 'Threads_connected'とSHOW GLOBAL VARIABLES LIKE 'max_connections'の比較をしてみてもいいでしょう。Threads_connectedがmax_connectionsの9割近くを占めているなら、もうすぐ"Error: 1040 Too many connections"が発生する予兆かも知れません。

おわりに

監視プラグインは「このトラブルが起こった場合はこの操作が拒否されるだろう⁠」⁠、「⁠このトラブルが起こる前兆はこうだろう」という予測に基づいて作成します。丁寧に作り込めば（あるいは、誰かが丁寧に作り込んだものであれば⁠）⁠、監視プラグインの中だけで一次切り分けを済ませ、復旧のための手を打ちやすくすることもできます。既成の監視プラグインには先人の経験が活かされたコードが数多くありますので、自分に適したものを探す時には「どのような監視用のコマンドがあるのか」という点にも着目して見てみてください。新しい発見があるかも知れません。