読者です 読者をやめる 読者になる 読者になる

kikumotoのメモ帳

インフラ・ミドル周りを中心に、興味をもったことを適当な感じで。twitter : @takakiku

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

この記事は

の第4日目です。

TL;DR

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

f:id:kikumoto:20151129234053p:plain:w300

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

目次

背景

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

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

他には、

というやり方もありましたが、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 を使ってデータを1箇所に集約する感しています。

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

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 で取得されたJSONsql 属性に保持されている SQL に対して EXPLAIN を実行して、その結果を explain 属性に保持する filter plugin となっています。

filter_sql_fingerprint

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

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

標準入力に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 patternmysqlslowquery-* を入力

して、Create すれば OK です。

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

f:id:kikumoto:20151202100937p:plainf:id:kikumoto:20151202100936p:plainf:id:kikumoto:20151202100935p:plain

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

まとめ

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

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

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