timelionで見るMySQLステータスモニタリング

みなさまおはようございます。
~~朝から仕事というなの趣味に走っているstです~~

<img width="400" src="https://qiita-image-store.s3.amazonaws.com/0/70732/49a944d8-4525-9b61-8a20-76d64755fcc3.png" alt="スク![アップロード中 スクリーンショット 2016-12-05 9.54.07.png]()リーンショット 2016-12-05 9.54.07.png">

<img width="400" src="https://qiita-image-store.s3.amazonaws.com/0/70732/dc8b93e0-3d7c-e39d-77c4-280e96f2e4dd.png" "スクリーンショット 2016-12-05 11.24.39.png">

普段お世話になっているOSSなのに受け身だけじゃいかんということで、
頑張って書いてみようかと思います。
時間があまりないのでインストール系や他のブログで紹介されている点はリンクのみの紹介とさせてください。
すいません。。。
あと来週予定されているelastic on tourにもお邪魔させて頂く予定ですー！

# timelion is なに
概要はこちらに素晴らしい資料があるので参考いただければ。
余談ですが、Elastic Stack5.0からtimelionはKibanaのcore_pluginとして取り込れるようになってます。

* [Timelionの紹介_@johtaniの日記 2nd] (http://blog.johtani.info/blog/2015/12/01/introduction-timelion/)
* [思い切ってelasticのTimelionを取り入れてみた_Acroquest Technology株式会社のエンジニアが書く技術ブログ] (http://acro-engineer.hatenablog.com/entry/2016/02/04/121500)
* [Health PlanetのデータをKibana Timelionで可視化してみた](http://qiita.com/yoshikaw/items/b81d36b334d277be5768)

# timelionの良いところ
* 動作が軽い
* 複数のindexの値を一つのVisualizeとしてグラフに描画することが出来る。
    * 1グラフ内に棒グラフはapacheのアクセス数、折線グラフはMySQL SlowQuery数など

# 可視化してみたい内容

* dstat

    ```bash
$ dstat -tlcmgdn --socket --tcp --io
----system---- ---load-avg--- ----total-cpu-usage---- ------memory-usage----- ---paging-- -dsk/total- -net/total- ------sockets------ ----tcp-sockets---- --io/total-
  date/time   | 1m   5m  15m |usr sys idl wai hiq siq| used  buff  cach  free|  in   out | read  writ| recv  send|tot tcp udp raw frg|lis act syn tim clo| read  writ
05-12 08:53:16|   0    0    0|  2   1  90   8   0   0| 532M 22.1M  329M  112M|   0     0 | 235k 1185k|   0     0 | 69   3   1   0   0|  3   2   0   0   0|5.44  15.3
```

* MySQL

    ```
mysql> SHOW GLOBAL STATUS WHERE Variable_name like 'Threads_%' OR Variable_name like 'Slow_queries' OR Variable_name like 'Com_select' OR Variable_name = 'Com_insert' OR Variable_name = 'Com_update' OR Variable_name = 'Com_delete' OR Variable_name = 'Sort_merge_passe
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Com_delete               | 0      |
| Com_insert               | 0      |
| Com_select               | 114336 |
| Com_update               | 0      |
| Created_tmp_disk_tables  | 0      |
| Opened_table_definitions | 70     |
| Opened_tables            | 70     |
| Slow_queries             | 0      |
| Sort_merge_passes        | 0      |
| Threads_cached           | 2      |
| Threads_connected        | 2      |
| Threads_created          | 4      |
| Threads_running          | 2      |
+--------------------------+--------+
13 rows in set (0.00 sec)
　
mysql> 
```

# 環境
* OS:CentOS6.8
* fluentd:td-agent-2.3.3
* jdk:jdk1.8.0
* Elasticsearch:elasticsearch-5.0.2
* Kibana:kibana-5.0.2
* MySQL:mysql-community-server-5.6

# 可視化してみた
すいません、間に合うかギリっぽかったのでグラフはQueryPerSecとIO waitを１つのグラフにしたものだけにしぼってます。。。(´・ω・｀)

<img width="752" alt="スクリーンショット 2016-12-05 20.13.26.png" src="https://qiita-image-store.s3.amazonaws.com/0/70732/c4377b6a-232c-8268-f950-fd062e404a42.png">


# ハマりどころ
* Elasticsearchのnetwork.hostがlocalhostじゃない場合に起動に失敗する＠number of threads系のエラーが出る

    ```:/var/log/elasticsearch/${CLUSTER.NAME}.log
max number of threads [1024] for user [elasticsearch] is too low, increase to at least [2048]
```
    * limits.confを修正してあげる

        ```:/etc/security/limits.conf
elasticsearch             soft    nproc           2048
elasticsearch             hard    nproc           2048
```

* MySQLのCom_selectのように累積型の値で1秒あたりの値をグラフ化したい時はどうすればよいのか
	* es関数のoffset＋subtractを使ってあげることで差分をグラフ化することが出来ます。
* bucketsの数が多すぎてエラーが出る
![スクリーンショット 2016-12-05 15.29.16.png](https://qiita-image-store.s3.amazonaws.com/0/70732/e7d138ae-e214-92dd-5dc1-28a5f9f3f415.png "スクリーンショット 2016-12-05 15.29.16.png")
	* 集計するbucketsの数が多すぎて集計時にelasticsearchに負荷がかかりすぎないためのものです。
よくある対応パターン以下の通り。
		* グラフに描画されているデータポイントのintervalを長め or autoにしてみる
		* データポイントをもう少し多めに集計出来るよう設定を修正する
			* 以下ファイルのmax_bucketsを変更すると採取するbuckets数を変更できます。

            ```/usr/share/kibana/src/core_plugins/timelion/timelion.json
{
  "quandl": {
    "key": "someKeyHere"
  },
  "es": {
    "timefield": "@timestamp",
    "default_index": "_all"
  },
  "graphite": {
    "url": "https://www.hostedgraphite.com/UID/ACCESS_KEY/graphite"
  },
  "default_rows": 2,
  "default_columns": 2,
  "max_buckets": 2000,
  "target_buckets": 200
}
```

# 手順
## M/Wの導入
以下公式ドキュメントを参照
* [fluentd](http://docs.fluentd.org/articles/install-by-rpm)
* [jdk](http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html)
* [Elasticsearch](https://www.elastic.co/guide/en/elasticsearch/reference/current/_installation.html)
* [Kibana](https://www.elastic.co/guide/en/kibana/current/install.html)
* [MySQL](http://dev.mysql.com/downloads/repo/yum/)

## dstatの可視化
dstatについては既に記載されている方がいらっしゃいますのでこちらを参照いただければ。
http://qiita.com/ymtszw/items/debbc6d6063c85531fd1

## MySQL関連の可視化
* MySQL Query数取得スクリプト

    ```bash:
$ wget https://raw.githubusercontent.com/st1t/elastic-tools/master/visual-scripts/get_mysql_status.sh
　
# mysqlの情報取得ユーザはrootでパスワードなしの初期設定で取得できるスクリプトになっているので、
# 必要に応じてユーザ追加等を行ってください。
$ ./get_mysql_status.sh | jq .
{
  "Threads_running": 2,
  "Threads_created": 5,
  "Threads_connected": 2,
  "Threads_cached": 3,
  "Sort_merge_passes": 0,
  "Slow_queries": 0,
  "Opened_tables": 72,
  "time": "20161205T202400+09:00",
  "hostname": "ip-IP.ap-northeast-1.compute.internal",
  "Com_delete": 0,
  "Com_insert": 209755,
  "Com_select": 1306408,
  "Com_update": 142203,
  "Created_tmp_disk_tables": 1,
  "Opened_table_definitions": 72
}
```

* fluentdの設定

    ```bash:/etc/td-agent/td-agent.conf
## for mysql query count
<source>
  @type exec
  tag es.mysql.querycount
  command /bin/bash /usr/share/visual/get_mysql_status.sh
  format json
  time_key time
  time_format %Y%m%dT%H%M%S
  run_interval 5s
</source>
　
<match es.**>
  @type elasticsearch_dynamic
  logstash_format true
  host localhost
  port 9200
  logstash_prefix ${tag_parts[1]}-${tag_parts[2]}
  logstash_dateformat %Y%m%d
  type_name all-type
  flush_interval 1s
  reload_connections false
  buffer_type file
  buffer_path /var/log/td-agent/buffer/es.local.buffer
  buffer_chunk_limit 256m
  buffer_queue_limit 128
</match>
```

* KibanaでIndexPatternsの登録

    <img width="1140" alt="スクリーンショット 2016-12-05 20.32.30.png" src="https://qiita-image-store.s3.amazonaws.com/0/70732/d672694f-dd19-2304-f21f-f7bc7ab81426.png">

* timelionでQueryの試しうち
<img width="1143" alt="スクリーンショット 2016-12-05 20.33.47.png" src="https://qiita-image-store.s3.amazonaws.com/0/70732/a53b3b7f-a9ca-51e5-c1c1-ad23a72eb4b9.png">

* 詳細Query

    ```bash:
$index="mysql-querycount-*",(.subtract(.es(metric=max:Com_select,index=$index),.es(metric=max:Com_select,index=$index,offset=-60s)),.subtract(.es(metric=max:Com_insert,index=$index),.es(metric=max:Com_insert,index=$index,offset=-60s)),.subtract(.es(metric=max:Com_update,index=$index),.es(metric=max:Com_update,index=$index,offset=-60s)),.subtract(.es(metric=max:Com_delete,index=$index),.es(metric=max:Com_delete,index=$index,offset=-60s))).divide(60).lines(width=1,fill=2).yaxis(3,label='QueryPerSec').label(label="SQL:",regex='q.*_').label(label=""regex='.$'),$index="system-dstat*",.es(index=$index,metric=max:dstat.total_cpu_usage.wai).bars().yaxis(2,label='cpu iowait %',max=100).label(label='cpu io wait')
```

    * Query解説
.subtract関数内の第二引数のes関数でoffsetを指定することで60秒前のデータを指定することで積算値の差分を描画しています。

        ```bash:
$index="mysql-querycount-*",：検索対象index名を変数化
(
.subtract(.es(metric=max:Com_select,index=$index),.es(metric=max:Com_select,index=$index,offset=-60s)),：SELECT数
.subtract(.es(metric=max:Com_insert,index=$index),.es(metric=max:Com_insert,index=$index,offset=-60s)),：INSERT数
.subtract(.es(metric=max:Com_update,index=$index),.es(metric=max:Com_update,index=$index,offset=-60s)),：UPDATE数
.subtract(.es(metric=max:Com_delete,index=$index),.es(metric=max:Com_delete,index=$index,offset=-60s))：DELETE数
)
.divide(60)：1分間の値を秒間数に修正するため除算
.lines(width=1,fill=2)：widthで線の細さ、fillはグラフを色で塗りつぶす濃度
.yaxis(3,label='QueryPerSec')：QPSの縦軸メモリ、第一引数を数値にすることで複数の縦軸メモリを設定できる
.label(label="SQL:",regex='q.*_')：ラベル名。regexで正規表現することでラベル名を見やすくしている
.label(label=""regex='.$'),
　
$index="system-dstat*",
.es(index=$index,metric=max:dstat.total_cpu_usage.wai)：dstatのiowaitを棒グラフにするためにdstatデータが入っているindexを描画
.bars()
.yaxis(2,label='cpu iowait %',max=100)：第一引数を２にすることで右側にiowait用のメモリを描画
.label(label='cpu io wait')
```

# Twitterでフォローしておいたほうが良い方
* @johtaniさん
* @rashidkpcさん
* @elastic

# その他
日本語の質問が出来るelasticのサイトもあるので何かあれば[こちら](https://discuss.elastic.co/c/in-your-native-tongue/18-category)に聞いてみるのも良いかもしれません。
johtaniさんの回答率が凄い。。。
個人的には英語が出来る方はもちろん、英語ができなくてもGoogle翻訳使いながら英語のコミュニティを見てみるのも結構面白いと思います。
BeatsやKibana辺りは私もたまに見ていて、見たいグラフをtimelionでどういうQueryを書けば実現できるのかとか結構面白いです。

細かいところでは色々ご指摘いただく点も多いかと思いますが、
一番伝えたかったことはtimelionは軽い！ってことと、Queryの自由度が結構高いという点は少しでも伝われば良いなと思っています。
また、各関数はjava scriptで記載されているので、気になる方はぜひぜひ挑戦してみてはいかがでしょうかー
~~ブーメランでかえってきませんように~~
このあたりとか。

```
$ ls /usr/share/kibana/src/core_plugins/timelion/server/series_functions
abs.js   color.js      cusum.js       divide.js  first.js  graphite.js  holt      legend.js  log.js  min.js            movingstd.js  points.js     props.js   range.js           static.js    sum.js    trend    worldbank_indicators.js  yaxis.js
bars.js  condition.js  derivative.js  es
$
```


なんとかまにあってよかた。。。