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

Posted at 2016-12-05

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

スク![アップロード中スクリーンショット 2016-12-05 9.54.07.png]()リーンショット 2016-12-05 9.54.07.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で可視化してみた

timelionの良いところ

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

可視化してみたい内容

dstat

$ 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を１つのグラフにしたものだけにしぼってます。。。(´・ω・｀)

ハマりどころ

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の数が多すぎてエラーが出る
- 集計する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の設定
/etc/td-agent/td-agent.conf

for mysql query count

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 　 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 ```

KibanaでIndexPatternsの登録
timelionでQueryの試しうち

詳細Query

$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でフォローしておいたほうが良い方

その他

日本語の質問が出来るelasticのサイトもあるので何かあればこちらに聞いてみるのも良いかもしれません。
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
$

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up