MySQL
Elasticsearch
Kibana

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

More than 1 year has passed since last update.

みなさまおはようございます。

朝から仕事というなの趣味に走っている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の良いところ


  • 動作が軽い

  • 複数の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を1つのグラフにしたものだけにしぼってます。。。(´・ω・`)

スクリーンショット 2016-12-05 20.13.26.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



    • 集計する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

* jdk

* Elasticsearch

* Kibana

* MySQL


dstatの可視化

dstatについては既に記載されている方がいらっしゃいますのでこちらを参照いただければ。

http://qiita.com/ymtszw/items/debbc6d6063c85531fd1


MySQL関連の可視化



  • MySQL Query数取得スクリプト

    $ 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
    
    <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の登録

    スクリーンショット 2016-12-05 20.32.30.png



  • timelionでQueryの試しうち

    スクリーンショット 2016-12-05 20.33.47.png



  • 詳細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秒前のデータを指定することで積算値の差分を描画しています。

      $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):第一引数を2にすることで右側に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
$

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