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

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 等を使う必要がありそうで、今のところ手動起動してる

参考にしたサイト

Kibanaを3から4にしてみた - まっしろけっけ http://shiro-16.hatenablog.com/entry/2015/03/14/234023

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などの文字列全体で意味を成すものを蓄積して集計しようとするとこの機能がネックになります。

slow queryを集計し上位10位を出すと、

from
select
messages（テーブル名）
count
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") }}'

参考にしたサイト

Kibana+Elasticsearchで文字列の完全一致と部分一致検索の両方を実現する - Qiita http://qiita.com/harukasan/items/4ec517d8d96f557367e1

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で設定したtype_name}"と入力して絞り込み。

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

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

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

ハマったポイント

きちんと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 さんに助けてもらえました。

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