RDS の general log を logstash ( Kibana, ElasticSearch ) で グラフ化してみた メモ

AWS




logstash は非常に簡単なインストール・操作できれいなグラフが出せるのでおススメです。


手順
1. logstash のインストール

jar をダウンロードするのみ

% wget https://download.elasticsearch.org/logstash/logstash/logstash-1.3.3-flatjar.jar


2. MySQL generallog 用 logstash 設定ファイルの作成

$ vi mysql-generallog-elasticsearch.conf

input {
  tcp {
    type => "mysql-generallog"
    port => 3333
  }
}

filter {
  if [type] == "mysql-generallog" {
    grok {
      match => { "message" => "%{TIMESTAMP_ISO8601:timestamp}\t%{DATA:log_type}\
t%{DATA:command_type}\t%{DATA:query_type}\t%{GREEDYDATA:query}\t%{DATA:user_host
}\t%{DATA:thread_id}\t%{DATA:server_id}" }
    }

    date {
      match => [ "timestamp", "yyyy-MM-dd HH:mm:ss" ]
    }
  }
}

output {
  elasticsearch {
    embedded => true
  }

  #stdout { debug => true }
}


3. logstash の起動

% java -jar logstash-1.3.3-flatjar.jar agent -f mysql-generallog-elasticsearch.conf -- web

# logstash には elasticsearch が含まれているので、別にelasticsearch が起動されていると
エラーになるので注意


4. フィード用スクリプトの作成

$ vi normalize_feed.bash

#!/bin/bash -x
# MySQL generallog のノーマライズの内容
# 1) ヘッダ行を除く
# 2) アルファベットを小文字に統一する
# 3) 改行、タブ以外の制御コード等を削除
# 4) \\nを削除
# 5) SQLコマンドの分類のため文等のselect, insert等を抽出
# 6) 時刻を UTC から JST に変換
# 
# 出力順
# timestamp log_type command_type query_type query user_host thread_id server_id
#
# 実行方法 例
# ./normalize_feed.bash mysql_general_log201407060459.tar.gz
# ./normalize_feed.bash *.tar.gz

date
while [ -n "$1" ]
do
 # ログファイルの解凍
 tar -O -xzvf $1 |
 # ヘッダ行を除く
 tail -n +2 |
 # アルファベットは小文字に正規化
 tr "[A-Z]" "[a-z]" |
 # 改行、タブ以外の制御コード等を削除
 tr  -d '\000-\010\013-\037\177-\377' |
 # \\nを削除
 sed -e 's/\\n/ /g' |
 # 出力フォーマット変更
 awk -F"        " 'BEGIN{OFS="\t"}
 {
  # SQLコマンドの分類のため文等のselect, insert等を抽出
  split ( $6, sql, " " )

  # 時刻を UTC から JST に変換
  split($1,datetime," ")
  split(datetime[1],date,"-")
  split(datetime[2],time,":")
  timestamp = mktime(sprintf("%d %d %d %d %s %s",date[1],date[2],date[3],time[1]
,time[2],time[3]) )+9*3600

  # 出力
  print strftime("%Y-%m-%d %H:%M:%S",timestamp), "MYSQL_GENERAL_LOG", $5, sql[1]
, $6, $2, $3, $4
 }' |
 # Elastic Search にフィード
 nc localhost 3333

 shift
done


5. フィード

% normalize_feed.bash *.tar.gz


6. 表示

ブラウザから http://localhost:9292/ (Logstash Dashboard)をクリック
ページ上部で表示期間を設定する
検索式は Lucene の文法で書く。Googleとだいたい同じと思ってOK
グラフの線を増やすには検索ボックスの＋をクリックする
検索ボックスが増えるので新たな検索式を書く

緑 : query_type:select
黄 : query_type:insert
水 : query_type:delete
橙 : query_type:update
赤 : log_type:MYSQL_GENERAL_LOG NOT (query_type:select OR query_type:insert OR query_type:delete OR query_type:update) <- select, insert, delete, update 以外