Help us understand the problem. What is going on with this article?

Kibana4を入れてRDSのslow queryをグラフにしてみた

More than 3 years have passed since last update.
  • 2015/05/20 にShibuya.rbで話した資料です
  • 弊社はAnsibleを使っているのでプロビジョニングコードはAnsibleになっています。

1. Kibana4のinstall...の前に

Elasticsearchのバージョンが古かったのでバージョンアップ。
Kibana4を入れるには1.4以上が必要。現行の最新版は1.5だったので1.5にアップデート。

- name: Ensure Elasticsearch 1.5 repository is installed
  sudo: yes
  apt_repository: repo="deb http://packages.elasticsearch.org/elasticsearch/1.5/debian stable main" state=present update_cache=yes

- name: Ensure elasticsearch is installed
  sudo: yes
  apt: pkg={{item}} state=latest
  with_items:
    - openjdk-7-jre
    - elasticsearch

2. Kibana4のinstall

- name: 'KibanaのDownload'
  get_url: url=https://download.elasticsearch.org/kibana/kibana/kibana-4.0.2-linux-x64.tar.gz dest=/home/ubuntu
  sudo_user: ubuntu

- name: ディレクトリを作成
  file: path=/var/www/kibana4 state=directory owner=ubuntu group=ubuntu mode=0755

- name: 'Kibanaの解凍'
  unarchive: src=/home/ubuntu/kibana-4.0.2-linux-x64.tar.gz dest=/var/www/kibana4 copy=no
  sudo_user: ubuntu

#- name: 'Kibanaを起動'
#  command: 'nohup /var/www/kibana4/kibana-4.0.2-linux-x64/bin/kibana &'
  • パッケージ登録などはされていないので、Elasticsearchのサイトからダウンロードしてきて解凍/配置
  • 起動は supervisor 等を使う必要がありそうで、今のところ手動起動してる

参考にしたサイト

3. nginxの設定

Kibana4はまたサーバアプリに戻ったようなので、リバースプロキシ介さなくてもアクセスできるのですが、Kibana3を入れた時に入れているのでそのまま再利用。

+upstream kibana {
+  server 127.0.0.1:5601;
+}

   location / {
-    root /home/www/kibana-3.1.1;
-    index index.html index.htm;
+    proxy_pass http://kibana;
   }

ここまでで、Kibana4が使えるようになります。

4. ElasticsearchにFluentdから投げる前にやっておくべき作業

Elasticsearchに文字列を投げると、スペースや'/'などの区切り文字ごとに単語を分析して自動的に分割してくれる機能があります。
これにより、テキストの全文検索が特に設定しなくてもできる便利な機能なのですが、SQLやURLなどの文字列全体で意味を成すものを蓄積して集計しようとするとこの機能がネックになります。

スクリーンショット_2015-05-17_17_23_20.png

  • slow queryを集計し上位10位を出すと、
  1. from
  2. select
  3. messages(テーブル名)
  4. count
  5. id(カラム名)

とかになる。かなしい。

analyzeされないようにtemplateを作る

Elasticsearchに、事前にこのtypeのこのfieldはanalyzeしない、と設定しておくことでこうなるのを防ぐことができます。multi_fieldという機能を使い、analyzeされるもの、されないものの両方を取っておくのが一般的みたいです。

こんな感じで対応。

es_template.json
{
  "template": "logstash-*",
  "mappings": {
    "slowlog": {
      "properties": {
        "sql_text": {
          "type": "multi_field",
          "fields": {
            "sql_text": {"type": "string"},
            "full": {"type": "string", "index": "not_analyzed"}
          }
        }
      }
    }
  }
}
- name: elasticsearchのformat変換
  shell: curl -XPUT http://localhost:9200/_template/logstash_template \
         -d '{{ lookup("file", "es_template.json") }}'

参考にしたサイト

5. RDSのslow queryをFluentdに投げる

fluent-plugin-sqlを利用。

fluent/fluent-plugin-sql https://github.com/fluent/fluent-plugin-sql

以下のように設定

<source>
  type sql

  host {rds_host}
  database mysql
  adapter mysql2
  username ***
  password ***

  tag_prefix rds.mysql

  select_interval 60s
  select_limit 500

  state_file /var/log/td-agent/slowquery_sql_state

  <table>
    table slow_log
    tag slow_log
    update_column start_time
    time_column start_time
  </table>
</source>

6. fluentdに来たsloq queryをElasticsearchに投げる

fluent-plugin-elasticsearchを利用。

uken/fluent-plugin-elasticsearch https://github.com/uken/fluent-plugin-elasticsearch

以下のように設定

<match rds.mysql.*>
  type elasticsearch
  type_name slowlog
  host { Elasticsearch host }
  port 9200
  logstash_format true
  include_tag_key true
</match>

ここまでで、RDSのslow queryをElasticsearchに渡すことができます。

7. グラフ表示と自分がハマったポイント

Visualize > Vertical bar chart > From a new search を選んだあと、検索窓に、
"type:{ fluent-plugin-elastichsearchで設定したtypename}"と入力して絞り込み。

X-AxisをTermsにし、Fieldで"sql_text.raw"を選びます。(※analyzeしないfield名をrawにした場合)

これで、クエリの内容で集計されるようになります。

スクリーンショット_2015-05-17_17_46_38.png

  • 赤枠部分が切れていますが、カーソルをバーに合わせると全クエリが表示されます。

ハマったポイント

きちんとtemplateの設定をしたにもかかわらず、Visualizeに"sql_tet.raw"が出ない問題に数日ハマっていました。

こういうときにチェックするべきポイントとして、

  • templateが適用されるのは、新しくindexが作られたタイミング。fluent-plugin-elasticsearchでは一日ごとにindexが作られるので、templateを設定した次の日本時間の朝9時以降に作られたデータに対して適用される
  • 実際にtemplateが更新されているかどうかは、以下のコマンドで確認

curl -XGET 'http://{Elasticsearch host}:{Elasticsearch port}/_template?pretty'

  • 実際にtemplateからindexのmappingに適用されているかどうかは、以下のコマンドで確認

curl -XGET 'http://localhost:9200/{index名}/_mapping/?pretty'

などがあるのですが、どれをやっても正常にmulti_fieldでデータが作られており途方に暮れていたのですが、Twitterでつぶやいたところ @johtani さんに助けてもらえました。

https://twitter.com/johtani/status/599048923269828609

設定画面でindexを選んで、Fieldのreloadを行う必要があるということでした。
やってみたところ、うまくいきました。

スクリーンショット_2015-05-17_19_24_34.png

スクリーンショット_2015-05-17_19_24_42.png

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした