MySQLのquery_logを可視化する part2

この記事は Retty Advent Calendar 3日目です。
昨日は、ふくい(@atsuko-fukui)のRetty音声検索をやってみたでした。

今回は、1日目に書いたMySQLのlogを可視化するの続きを書いていきたいと思います。

まさか、こんなに早く続きを書く日が来るとは。。。

方法

1. RDSからslow_query_logを取得する。
2. slow_query_logをElasticserchに入れる。
3. Elasticsearchの設定。
4. Kibanaでビジュアライズを頑張る。

　

RDSからslow_query_logの取得

RDSからのログの取得方法は、DBにQueryを投げて取得する方法と、
Fileとして吐き出したものを取得する2種類の方法があります。

今回は、DBに格納されたものに対してQueryを投げて取得します。
前回はfilebeatを使用して、ログの取得を行っていましたが、RDSから取得する方法に迷ったため、Embulkからログを取得する方法に変更しました。

Embulk インストール

以下のコマンドを実行し、インストールを行う。

curl --create-dirs -o ~/.embulk/bin/embulk -L "http://dl.embulk.org/embulk-latest.jar"
chmod +x ~/.embulk/bin/embulk
echo 'export PATH="$HOME/.embulk/bin:$PATH"' >> ~/.bashrc
source ~/.bashrc

プラグインのインストール

RDSはMySQLから読み込むため、MySQL用のプラグインをインストールする。
また、Elasticsearchに取得したログを飛ばすため、そのプラグインもインストールする。

embulk mkbundle bundle
vim ~/bunble/Gemfile


~~ <以下2行を追加> ~~~
gem 'embulk-input-mysql'
gem 'embulk-output-elasticsearch_ruby'
~~~ <ここまで> ~~~~


embulk bundle

インストールされた事を確認する。

[ec2-user@ip-172-19-76-160 ~]$ embulk bundle list -b bundle
2016-12-02 13:39:15.469 +0000: Embulk v0.8.15
Gems included by the bundle:
  * bundler (1.10.6)
  * elasticsearch (5.0.0)
  * elasticsearch-api (5.0.0)
  * elasticsearch-transport (5.0.0)
  * embulk (0.8.15)
  * embulk-input-mysql (0.8.0)
  * embulk-output-elasticsearch_ruby (0.1.1)
  * excon (0.54.0)
  * faraday (0.10.0)
  * liquid (3.0.6)
  * msgpack (0.7.6)
  * multi_json (1.12.1)
  * multipart-post (2.0.0)
  * rjack-icu (4.54.1.1)
[ec2-user@ip-172-19-76-160 ~]$

YAMLファイルの作成

以下のファイルを作成する。

~/bundle/rds_slowlog.yml

in:
  type: mysql
  host: ***************.ap-northeast-1.rds.amazonaws.com
  user: user-name
  password: password
  database: mysql
  query: |
    SELECT
      DATE_FORMAT(start_time, '%Y-%m-%d %T') AS query_start_time,
      user_host,
      SECOND(query_time) AS query_system_time,
      SECOND(lock_time) AS query_lock_time,
      rows_sent,
      rows_examined,
      db,
      sql_text
    FROM mysql.slow_log_backup

out:
  type: elasticsearch_ruby
  index: slowquery-%Y.%m.%d
  index_type: slowquery
  nodes:
    - host: 127.0.0.1

以下のコマンドを実行し、YAMLファイルの動作確認を行う。

embulk preview bundle/rds_slowlog.yml -b bundle

Elasticserchへインポート

以下のコマンドを実行し、Elasticsearchにデータのインポートを行う。

mysql -uuser -p -hretty-********.ap-northeast-1.rds.amazonaws.com --execute 'CALL mysql.rds_rotate_slow_log;'
embulk run bundle/rds_slowlog.yml -b bundle

　

Elasticsearchの設定

テンプレートの設定

取得した形に合わせ、テンプレートを設定する。

{
  "slowquery": {
    "template": "slowquery-*",
    "mappings": {
      "slowquery": {
        "properties": {
          "query_system_time": {
            "type": "float"
          },
          "rows_sent": {
            "type": "integer"
          },
          "rows_examined": {
            "type": "integer"
          },
          "query_start_time": {
            "format": "yyyy-MM-dd HH:mm:ss",
            "type": "date"
          },
          "query_lock_time": {
            "type": "float"
          },
          "user_host": {
            "index": "not_analyzed",
            "type": "string"
          },
          "sql_text": {
            "index": "not_analyzed",
            "type": "string"
          },
          "db": {
            "index": "not_analyzed",
            "type": "string"
          }
        }
      }
    },
  }
}

　

Kibanaでビジュアライズを頑張る

ここからは好みに合わせてビジュアライズして見てください。
僕のダッシュボードは以下のようになりました。

まとめ

RDSからデーターを取得するのに意外と時間がかかってしまって辛かったですが、手順がまとまったので楽になるかとおもいます。
Elasticsearchに投げるなら、fluentdでも良かったかとも思いましたが、DBへの負荷が怖かったので更新頻度を任意に設定できる Embulk を選択しました。

ビジュアライズがあまり上手く出来ていないので、キレイに出来た方は教えてください！！