MySQLのquery_logを可視化する part2

この記事は **Retty** Advent Calendar 3日目です。
昨日は、ふくい(@atsuko-fukui)の[Retty音声検索をやってみた](http://qiita.com/atsuko-fukui/items/904d10ec0b42912ea4f1)でした。

今回は、1日目に書いた[MySQLのlogを可視化する](http://qiita.com/takumi-suzuki/items/e88db63a1bfe4295d580)の続きを書いていきたいと思います。

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

 
# 方法
1. RDSからslow_query_logを取得する。
1. slow_query_logをElasticserchに入れる。
1. Elasticsearchの設定。
1. 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ファイルの作成
以下のファイルを作成する。

```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でビジュアライズを頑張る
ここからは好みに合わせてビジュアライズして見てください。
僕のダッシュボードは以下のようになりました。

![スクリーンショット 2016-12-02 23.05.15.png](https://qiita-image-store.s3.amazonaws.com/0/137186/ab9152fd-7094-f2b6-c87e-91b0bcd5a415.png)



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

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