なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLに対して定期的に実行して結果を保存しておくと幸せになれるアレ

ワンライナー

mysql -u ユーザ -pパスワード -h 接続先 -e "SQL文" | sed -e 's/\t/\" \"/g' | sed -e 's/^/\"/g' | sed -e 's/$/\"/g' | sed -e "s/^/$(date '+%Y%m%d %H:%M:%S') /g" >> ファイル

パスワードを生テキストで書くなって人はゴニョゴニョしてください。

追記:ゴニョゴニョするはなし、書きました。
MySQLで幸せになれるヤツの続き-パスワードを隠蔽する方法 - なからなLife


取りたい情報に応じて、権限が異なります。DBのroot相当の権限があるといいのですが、少なくとも「全スキーマへのSELECT」と「PROCESS」は必要になるはず。

何するやつ?

  • MySQLにログインして、-eの後に指定したSQLを実行して、ログアウトする。
  • SQLの結果をパイプで繋いだsedで加工して、出力値を列単位でダブルクオートで囲み、セパレータはスペースにする。
  • sedで行ヘッダとして年月日時分秒を入れる。
  • 加工結果をファイルに追記する。


シェルスクリプトとして、各所の変数化をしておくとメンテナンス性高くなりますよね。
そのシェルをcron等から一定間隔で実行するように設定しておきます。


追記:セパレータがスペースじゃないほうがいい人は、カンマでもタブでもお好きなものでどうぞ。後ろにつながる話があるので、スペース前提で話を勧めます。


出力先ファイルは肥大化するので、ログローテートも忘れずに。


あなたはどこから?私はここから

まあ、私が読んでなんとか理解できるのがこの辺、って話でもあるんんですけど、このあたりの情報を拾うように仕掛けています。

  • SHOW FULL PROCESSLIST; または、SELECT * FROM information_schema.processlist; *追記も参照!
  • SHOW GLOBAL STATUS;
  • SELECT * FROM information_schema.innodb_trx;
  • SELECT * FROM information_schema.innodb_locks;
  • SELECT * FROM information_schema.innodb_lock_waits; (一発でロック待ちヤツとロック待たせヤツを見られるSQLでも可)
  • SELECT * FROM information_schema.innodb_metrics; <- さっき知った!*1
  • SHOW ENGINE INNODB STATUS\G*2


詳細は今日は触れませんが、各セッションが何してるか、ロックはどうか、I/Oはどうか、キャッシュヒットはどうか。そのへんの情報です。


SHOW FULL PROCESSLISTとか、コマンド実行時の断面でしかないので、実行されたSQLをすべて拾えるわけではないけれど、SLOW QUERY LOGは処理完了しないと記録されないですし、こちらは実行中のスローなクエリーは拾えますから、かなり助かります。


負荷的にgeneral log吐いてても問題ないなら、そうしますけど。*3


SHOW ENGINE INNODB STATUSは、この中では異色のレポート出力コマンドですが、出力される情報は、MySQLの内部情報を知る上では超が付く重要度。
この中で1つだけ選べ、って言われたらSHOW ENGINE INNODB STATUSにします。


何故嬉しい?

MySQLって、基本的に内部情報のヒストリーを持ってないですね。状態監視できるコマンドはたいてい「そのコマンドを実行した時点の断面」です。
なので、何か問題出たときに、後から追いかけるのツラいし、問題を「推移から検知する」のもツラいです。


そんなわけで、現時点情報を取得するコマンドを定期実行して履歴を蓄積する、という方法で対処することになります。
bin-logを読む?あれ更新しか記録されませんよね。参照クエリが原因の高負荷は追いかけられないです。
Performance_Schema/Information_Schemaあるじゃない?ま、あるんですけどね。まだ自分のレベルじゃ使いこなせないってのもあるんですけど、イベント回数多いと、あっという間に流れてしまうんですよね。


そんなときに、ここで書いたようなやりかた、ここに書いてあるような対象を定期的に取得しておくと、解析に役立つわけです。
「自分では手に負えなくて、分かる人に調べてもらう」ってケースでも、「当時の情報」がないとお手上げなわけでして。
中身を読める様になるのは後でもいいので、とにかく溜めておきましょう。


Oracleで言えばSTATSPACK、さらにその進化版のAWR、みたいなものがMySQLには無いので、こうやって自前で用意しておくわけです。
ただし、単発SQL実行時には、いつなんどき実行したかもわからないので、sedで行ヘッダに実行日時を付けてあげます。



AWS RDS利用者なら、尚更。

オンプレMySQL利用については、Percona Toolkitやら何やらをMySQLが動いているサーバに仕込んでいい感じにまわしているところもあるかもしれません。


AWSのRDSは「Database as a Service」ということもあり、MySQLが稼働しているサーバーインスタンスのOS層にアクセスすることや、ましてやツールを仕込んだりってこともできませんから、別途EC2-Linuxを起動して、そこから刺しに行くように設定しておきます。
稼働はしているけど負荷は低めのサーバーに仕込んでおくのが良いかと。


EC2にアタッチするEBSはそんなに安いものではないので、ファイルに吐き出した内容は、CloudWatchLogsに吸い上げるようにすると、単価も安いし、フィルタリングを使ったアドホックな分析や自動アラートなども作れて便利です。
数値系ならばカスタムメトリクスとしてCloudWatch内でグラフ化もできますし。


この、CloudWatchLogsに集約してフィルタをかけて使用する際に、「sedでダブルクオート加工」する部分の意味が出てきます。
CloudWatchLogsは、基本的に「スペースでフィールドを認識」します。
なので、SQL実行結果を無加工で出力すると、列値にスペースが含まれる値を出力した時、フィルタが意図したとおりに機能してくれません。
ダブルクオート(または[])で囲むと、それで1つのフィールドとして認識するようになりますので、この問題を回避できます。


もちろん、LambdaやKinectなどと組み合わせて別のシステムに流し込んで、リアルタイムにグラフィカルレポートに変換してもよいですね。
既存で運用監視システムを持っていて、そちらと統合したい、ってケースも多々あるかと思います。


逆に、CloudWatchLogsに打ち上げた後、そこから数値データを使って分析、グラフなどを作ろうとするとそれはそれでツライので、単位時間毎にローテートさせたログファイルをそのままS3に打ち上げておいてもよいです。
S3上のファイルオブジェクトをSQLで解析できるAWS Athenaがリリースされましたので、S3からでもいい感じに分析できるでしょう。(実行都度でお金掛かるけど)

追記

Information_schema.processlistとSHOW FULL PROCESSLISTコマンドは、ほぼ同じ情報を取得できますが、同様に、performance_schema.threadsでも、ほぼ同じ情報が取得できます。

そして、performance_schema.threadsの参照は、MySQLサーバーにかける負荷が少ない、とのことです。

performance_schemaを有効にすること自体、MySQLサーバーに若干の負荷をかけることになるのでトレードオフはありますが、もしすでにperformance_schemaを有効にしている環境であるならば、performance_schema.threadsから取得した方が良さそうです。
MySQL5.7 からMySQL 5.6.6からはデフォルトでperformance_schemaが有効になっていますし。


この辺の話は、公式ドキュメントにも書いてありました。

threads へのアクセスには相互排他ロックは必要なく、サーバーパフォーマンスへの影響は最小です。INFORMATION_SCHEMA.PROCESSLIST と SHOW PROCESSLIST では相互排他ロックが必要になるため、パフォーマンスの低下につながります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 22.9.10.3 スレッドテーブル

まとめ

  • MySQLの定期監視結果の蓄積大事。
  • SHOW ENGINE INNODB STATUS、超大事。
  • 中身を読めるようになるのは、仕掛けたあとでOK!そう頑張ってる途中!


やさしく学べるMySQL運用・管理入門【5.7対応】

やさしく学べるMySQL運用・管理入門【5.7対応】

*1:SHOW ENGINE INNODB STATUSに出てくるような数字を、レポート形式ではなく普通のテーブル形式で出力できるもの。

*2:これだけは出力形式が違うのでsedでダブルクオート加工しない。

*3:General Log出力前提でパフォーマンス&ストレージ維持できる構成が理想的。