LoginSignup
3

More than 5 years have passed since last update.

MySQLのquery_logを可視化する part2

Posted at

この記事は 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でビジュアライズを頑張る

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

スクリーンショット 2016-12-02 23.05.15.png

まとめ

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3