カジュアルにMySQLスローログ可視化with Fluentd+Elasticsearch+Kibana

この記事は

• MySQL Casual Advent Calendar 2015 - Qiita
• Elasticsearch Advent Calendar 2015 - Qiita
• Hamee Advent Calendar 2015 - Qiita

の第４日目です。

TL;DR

開発者の皆さんに、CasualにMySQLスローログを分析しもらうために、Fluentd + Elasticsearch + Kibana でMySQLスロークエリを下図のようにビジュアライズしました。（Kibana上で EXPLAIN の結果も確認できるようにしてあります）

ついでに、以下の Fluentd の filter plugin を作成しました。

• kikumoto/fluent-plugin-sql_fingerprint · GitHub
• kikumoto/fluent-plugin-mysql_explain · GitHub

目次

背景

MySQLのスローログの一覧をくれ、と言われたのがきっかけですが、せっかくなので EXPLAIN の情報も渡したいし、発生状況を随時確認できるほうがうれしいかなということで、可視化する方向にしました。

最初は nata2 を使えばいいやと思っていたのですが、スロークエリとなっているSQLが巨大過ぎて nata2 が作るURIが長大になりすぎる問題に当たったたり、EXPLAINがないしってところでそのまま使うことは諦めました。

他には、

• Elasticsearch - Kibana4を入れてRDSのslow queryをグラフにしてみた - Qiita
• [db tech showcase Tokyo 2015] C15:DevOps MySQL in カカクコム～ OSSによる可用性担保と…

というやり方もありましたが、nata2のようにSQLのfingerprintでまとめるようになってないし、やはりEXPLAIN情報もないしってことで、結局、以上の情報をMIXして Fluentd（+自作プラグイン） + Elasticsearch + Kibana で可視化することにしました。 その方法をこのエントリーで書きます。

Elasticsearch + Kibana 環境

環境、バージョンは下記の通りです。

名前	バージョン
OS	CentOS 6
Java	Open JDK 1.8
Elasticsearch	1.7.3
Kibana	4.1.2

Java, Elasticsearch は yum でインストールしています。 Kibana は kibana4セットアップ - Qiita を参考にしました。

Elasicsearch では文字列の解析をされると困るので、下記のコマンドで文字列を解析しないように設定してあります。

curl -XPOST 127.0.0.1:9200/_template/strig_not_analyzed_template -d '{
  "template": "*",
  "mappings": {
    "_default_": {
      "dynamic_templates": [
        {
          "string_template": {
            "mapping": {
              "index": "not_analyzed",
              "type": "string" 
            },
            "match_mapping_type": "string",
            "match": "*" 
          }
        }
      ]
    }
  }
}'

Fluentd

fluentdでの処理の流れとしては下記のようなイメージとなっています。

in_mysqlslowquery_ex (fluent-plugin-nata2同梱）で、スロークエリログファイルをparse
　↓
filter_record_transformer でホスト名設定
　↓
filter_mysql_explain （自作プラグイン）で、EXPLAIN 結果取得
　↓
filter_sql_fingerprint （自作プラグイン）で、sql の fingerprintを取得
　↓
out_elasticsearch で Elasticsearch にデータ登録

実際には、filter_record_transformer より後、out_elasticsearch 前で、out_foward, in_forward を使ってデータを１箇所に集約する感しています。

以下に、使用している一部のプラグインについてコメントします。

in_mysqlslowquery_ex

MySQL のスローログファイルをfluentdに取り込むプラグインには yuku-t/fluent-plugin-mysqlslowquery · GitHub もあるのですが、 studio3104/fluent-plugin-nata2 · GitHub の方が SET timestamp をあらかじめ除外してくれたりとか、アクセスしているDB情報も保持してくれたりして、すぐ使うに便利だったのでこちらを選択しました。

できれば、nata2のプラグインに同梱ではなくて、昔のように分離しておいてくれるとPRとか出しやすいかなぁと思っています。

filter_mysql_explain

自作プラグイン kikumoto/fluent-plugin-mysql_explain · GitHub です。

このプラグインは、in_mysqlslowquery_ex で取得されたJSONの sql 属性に保持されている SQL に対して EXPLAIN を実行して、その結果を explain 属性に保持する filter plugin となっています。

filter_sql_fingerprint

自作プラグイン kikumoto/fluent-plugin-sql_fingerprint · GitHub です。

このプラグインは、in_mysqlslowquery_ex で取得されたJSONの sql 属性に保持されている SQL 文のパラメータ部分を抽象化するものです。抽象化されたSQLは fingeprint 属性に保持されます。

標準入力にSQLを受け取り、標準出力に抽象化された SQL （fingerprint） を出力する外部ツールに依存しています。実際のところは Percona Toolkitに含まれる pt-fingerprint を利用していますが、条件を満たすものであればなんでもOKです。

設定例

以下、td-agent.conf の設定例です。

<source>
  type mysqlslowquery_ex
  path /var/log/mysql/mysql-slow.log
  tag mysqlslowquery.myapplication
  pos_file /var/log/td-agent/mysql-slow.log.pos
  last_dbname_file /var/log/td-agent/mysql-slow.log.lastdb
</source>

<filter mysqlslowquery.**>
  type record_transformer
  <record>
    hostname ${hostname}
  </record>
</filter>

<filter mysqlslowquery.**>
  type mysql_explain
  host 127.0.0.1
  port 3306
  database mydb
  username dbuser
</filter>

<filter mysqlslowquery.**>
  type sql_fingerprint
  fingerprint_tool_path /usr/bin/pt-fingerprint
</filter>

<match mysqlslowquery.**>
  type elasticsearch
  type_name myapp-mysqlslowquery
  host 127.0.0.1
  port 9200
  logstash_format true
  logstash_prefix mysqlslowquery
  include_tag_key true
</match>

これで、Elasticsearch に EXPLAIN もついたデータが登録されるようになります。

Kibanaのグラフ作成例

初回 Kibana にアクセスすると Index の設定を求められるので、上記の設定であるなら、

• Index contains time-based events にチェック
• Index name or pattern に mysqlslowquery-* を入力

して、Create すれば OK です。

グラフを出すには、Visualize で例えば、Vertical bar chart を選んで、下図のようのな設定を入れれば、fingerprint 単位で各ホストごとに色分けされたグラフが出力できます。

あとは、グラフ幾つかつくって適当にDashboardに登録してお好みにレイアウトすれば、冒頭に貼ってあるようなものができあがります。

まとめ

Fluentd（+自作フィルタープラグイン） + Elasticsearch + Kibana でMySQLスロークエリを可視化しました。

ログをカジュアルに取り込める Fluentd、データをカジュアルに放り込める Elasticsearch、カジュアルにグラフを作ることのできる Kibana、どれも大変便利！

世の中からクソスロークエリがなくなることに貢献できれば幸いです！