MySQL Casual Talks vol 6.でNata2について発表してきた

MySQLのスロークエリログを一覧したりサマライズしたり出来るNata2というツールを作ったので、MySQL Casual Talks vol.6で発表させてもらった。

id:oranieさんが早速試してみてくれて、書いてくれたブログエントリにたくさんのブクマがついて大変にありがたい限りです。
MySQLのslow query logを可視化するnata2が大変便利そう - iをgに変えるとorangeになることに気づいたoranieの日記

使い方などをもう少し詳しく

発表当時はドキュメントがまったくなくて大変に雑な感じだったり、発表で説明しきれなかった部分もあったので、ここで改めて少し詳しい解説をします。

Nata2とは

パーズされたスロークエリログをHTTP Postで登録し、件数グラフ、スロークエリログの履歴、mysqldumpslow相当のサマライズなどの表示が出来るウェブアプリケーションです。
スロークエリログを登録するサーバと、パーズやPostを行うクライアントの2つのコンポーネントがあり、クライアントライブラリは2種類の実装があります。
サーバ、クライアントライブラリすべて、Ruby 2.0以降で動作します。

Nata2 Server

https://github.com/studio3104/nata2

インストール、データベース準備、起動

git clone

$ git clone https://github.com/studio3104/nata2.git

bundle install

$ cd nata2
$ bundle install

スロークエリログを記録しておくデータベースの準備

データベースは、特に理由のない限りはMySQLをお使いいただくことを推奨します。
テストではsqlite3を使ったりはしていますが、他のRDBMSでは動作の確認をしていません。

• 設定ファイル

Sequelのdburlをconfig.tomlに記述します。

$ cat ./nata2/config.toml
dburl = "mysql2://nata:password@localhost/nata2"

• MySQLにNata2用のユーザを作成

任意。上述の設定ファイルの通りに接続をしたい場合は、以下の様な感じで。

mysql> GRANT ALL ON nata2.* TO 'nata'@'%' IDENTIFIED BY 'password';

• create database

Nata2用のデータベース、nata2を作成します。

$ mysql -uroot -p -e'CREATE DATABASE `nata2`'

• テーブル作成

テーブル作成スクリプトを実行します。

$ bin/nata2server_init_database

起動

unicornと組み合わせたり、ポートを変えたりなど、環境に合わせて。
以下のコマンドでは、フォアグラウンドで起動し、0.0.0.0:9292で待ち受けます。

$ bundle exec rackup

スロークエリの登録

http://nata2.server/api/1/:sarvice_name/:host_name/:database_nameなURLに、以下のようなパラメタをPostしてあげるだけです。
後述のクライアントライブラリを使うとラクですが、自前でパーズなどしてからPostすることももちろん可能です。

{
  datetime: 1390883951,
  user: 'user',
  host: 'localhost',
  query_time: 2.001227,
  lock_time: 0.0,
  rows_sent: 1,
  rows_examined:0,
  sql: 'SELECT SLEEP(2)'
}

Postパラメタ

userとhost以外は必須です。

• datetime
  • スロークエリログ発生日時
  • エポックタイム
• user
  • クエリの実行ユーザ
  • 文字列
• host
  • どのホストからのクエリか
  • 文字列
• query_time
  • クエリ実行時間
  • 小数
• lock_time
  • ロック時間
  • 小数
• rows_sent
  • 送信された行数
  • 整数
• rows_examined
  • 処理対象になった行数
  • 整数
• sql
  • SQL
  • 文字列
  • 改行文字を含んでも大丈夫

ビュー

• データベース一覧

同じサービスの中に同名のデータベースが登録されている場合、自動的にComplexの表示の下に複合ビューへのリンクが生成されます。
Databaseの表示の下に表示されるリンクは、各データベースの個別のリンクです。

• スロークエリログ履歴

画像は複合ビューです。同一サービス中の同名データベースがマージされて1つのビュー中に表示されています。
グラフはスロークエリログの件数。

• スロークエリログ個別

• mysqldumpslow相当のサマライズ

画面右上で選択された期間で登録されているスロークエリログをサマライズし、クエリの種類ごとに、合計回数順、合計クエリ実行時間順、合計ロック時間順、合計フェッチ行数順、平均クエリ実行時間順、平均ロック時間順、平均フェッチ行数順に表示します。
画像は合計回数順。

クライアントの実装について

MySQLのスロークエリログは、出力されたuse句以降は、そのデータベースでのスロークエリログだということがわかるが、どのデータベースのスロークエリだったかということをクエリごとに記録してくれない。
そのため、最後のスロークエリログが発生したデータベースを記録しながらファイルをナメていく実装をしています。
なお、Percona Serverはその限りにあらず、schemaというパラメタをクエリごとに記録してくれる。(設定次第？)

nata2-client

https://github.com/studio3104/nata2-client

• 2つのクライアントライブラリのうちの1つ
• SSHとmysql clientで情報を取得
• スロークエリログ収集対象サーバと、SSH、mysqldの接続情報は設定ファイルに記述
• 処理済み行数と、最後のスロークエリログのデータベース名をsqlite3に記録しながら実行する
• メモリをギリギリまで使いたいMySQLのサーバでfluentdを使うのが不安な場合に使う

インストール

$ git clone https://github.com/studio3104/nata2-client.git
$ cd nata2-client
$ bundle install

設定

config.tomlに以下のように記述します。

# スロークエリログ収集対象ホストとサービス名を指定
[targets]
service1 = [ "host1", "host2" ]
service2 = [ "host3", "host4" ]

# Nata2 ServerのFQDNと待ち受けポートを指定
[nataserver]
fqdn = "nata2.server"
port = 9292

[default]
# 一度の実行で何行処理するか指定
fetch_lines_limit = 10000

# 個別に指定がないホストのSSH接続情報
[default.ssh]
username = "root"
password = "password"
keys = [ "/home/studio3104/.ssh/id_rsa" ]
passphrase = ""
port = 20022

# 個別に指定がないホストのmysqld接続情報
[default.mysql]
username = "nata"
password = "nata"
port = 13306

# ホストごとの個別のSSH接続情報
# host1の実行時にdefault.sshを上書きする
[host1.ssh]
username = "satoshi"
keys = [ "/home/satoshi/.ssh/id_rsa" ]
passphrase = ""

実行

$ bundle exec bin/nata2-client start

fluent-plugin-nata2

https://github.com/studio3104/fluent-plugin-nata2
http://rubygems.org/gems/fluent-plugin-nata2

• 2つのクライアントライブラリのうちの1つ
• fluentdのプラグイン
  • 2つのプラグインを内包
    • パーズされたスロークエリログをNata2にPostするout_nata2
    • スロークエリログをtail&パーズして吐き出すin_mysqlslowquery_ex

out_nata2

パーズされたスロークエリログをNata2にポストするプラグインです。
登録API(http://nata2.server/api/1/:sarvice_name/:host_name/:database_name)の:service_nameと:host_nameはtagから決定されるので、このプラグインに処理が渡るときにはtagの末尾がservicename.hostnameのようになるように設定して、サービス名とホスト名を指定しておく必要があります。

主な設定項目

• server
  • 必須
  • Nata2 ServerのFQDNを指定します
• port
  • 必須
  • Nata2 Serverの待ち受けポートを指定します

in_mysqlslowquery_ex

fluent-plugin-mysqlslowqueryというプラグインが既に存在しているが、

• SQLの連続する空白文字と改行文字を省いてしまう
• Fluent::TailInputを継承しているため、最近のfluentdのtailプラグインで使える機能が使えない
  • どのデータベースのスロークエリログかわからなくなってしまう問題の対応として、ログを先頭から読むread_from_headを有効にして使いたい

の理由から、Fluent::NewTailInputを継承した別のプラグインを書きました。

主な設定項目

• pos_file

  • オプション(有効推奨)
  • ログファイルをどこまで読んだか記録しておくファイル
  • last_dbname_fileとは異なるパスを指定

• last_dbname_file

  • オプション(有効推奨)
  • 最後に出力したスロークエリログが発生したデータベースを記録しておくファイルパスを指定
  • pos_fileとは異なるパスを指定

• dbname_if_missing_dbname_in_log

  • オプション
  • スロークエリログからも、last_dbname_fileからもどのデータベースでのスロークエリログだったかわからなかった場合に使われるデータベース名

• format

  • 何を指定しても内部でnoneに書き換えられます

• read_from_head

  • オプション(有効推奨)
  • ログを先頭から読みこむ

• path

  • 必須
  • スロークエリログのパス

• tag

  • 必須
  • out_nataとの組み合わせで使う場合、tagの末尾をservicename.hostnameのようにして、サービス名とホスト名を指定しておく必要がある

設定例

とりあえずこんな感じで設定しておけばNata2にスロークエリログが登録されていきます。
ご利用の環境に合わせて適宜変更してご利用ください。

<source>
  type mysqlslowquery_ex
  read_from_head
  path /path/to/slowquery.log
  tag slowquery.servicename.hostname
  pos_file /tmp/slowquery.log.pos
  last_dbname_file /tmp/slowquery.log.lastdb
</source>

<match slowquery.**>
  type nata2
  remove_tag_prefix slowquery.
  server nata2.server
  port 9292
</match>

今後追加したい機能

• EXPLAINの取得、登録、参照
• SHOW CREATE TABLEの取得、登録、参照

など

おわりに

自分がはじめて参加したMySQL Casual Talksはvol3.でした。
登壇されていた皆さんがすごく輝いて見えたのを今でも覚えているし、まさか回を重ねた同会で自分が登壇するだなんて、当時の自分はまったく考えていなかっただろうなーと思います。
機会をくださったid:myfinderさん、会場を提供してくださった日本オラクル様、本当にありがとうございました。

YAPC::Asia Tokyo 2014 で 40 分間話をする権利をいただきました

トーク採択していただきました。インフラエンジニア(狭義)は死んだ
ソーシャルメディアでの拡散などしてくださった皆様有難うございます！！！１

初日の朝イチでだいぶ早いですが、早起きしてお越しいただけると大変うれしいです。
2014-08-29 10:20:00 に 慶應義塾日吉キャンパス内 協生館 2F 多目的教室2 でお待ちしております。よろしくお願い致します。

HRForecast の EMBED でヘッダとラベルを非表示に出来るようになったよ

HRForecast には EMBED の機能があり、iframe で別のページ(別のアプリケーション)にグラフを埋め込むことが出来ます。

ただ、埋め込む先のアプリケーションによっては、グラフ名と 1週間,1ヶ月,1年 などのレンジ指定、CSVエクスポートが置かれているヘッダ領域、グラフ名と値が表示される右側のラベル領域が不要な場合もあるかなと思います。
ので、pull-request して取り込んでもらいました。

suppress graph headers or labels when specified respective options #26

指定するクエリストリングと参考画像

iframe の中の URL のクエリストリングによって表示が変わります。

• ヘッダもラベルも消したい場合

graphheader=0&graphlabel=0

• ヘッダだけ消したい場合

graphheader=0 or graphheader=0&graphlabel=1

• ラベルだけ消したい場合

graphlabel=0 or graphheader=1&graphlabel=0

• ヘッダもラベルも消さず、従来どおりにしたい場合

graphlabel, graphheader を指定しない or graphheader=1&graphlabel=1

HRForecast についてご存じない方はこちらをどうぞ

HRForecast - もうひとつのデータビジュアライズツール
kazeburo/HRForecast

fluent-plugin-graphite を書いたよ

graphite にメトリクスをポストする fluent-plugin を書きました
先に github で公開されていた fluent-plugin-graphite がありましたが、イチから書いて gem release いたしました

https://github.com/studio3104/fluent-plugin-graphite
http://rubygems.org/gems/fluent-plugin-graphite

なぜイチから書いたのか

以下のような箇所に懸念があり、修正だと結局まるっと書き直すのと変わらないと思いイチから書いてしまいました

• 先行プラグインは、
  • Fluent::BufferedOutput を継承し、内部でサンプリングやカウントなどの計算をしていたが、そういうのは他のプラグインに任せて、来た値をそのまま投げてあげればいいのではないかと思った
  • レコードの key というフィールドと、設定の key_prefix からグラフ名を生成し、 count, gauge というフィールドに値を入れなければならず、少し扱いにくさを感じた
  • メトリクスを送信するたびに TCP コネクションの切断、切断をしていた

新しく書いたプラグインの機能

詳しくは README を見ていただきたく

• メトリクスを持つフィールドを指定して出力対象とする
• グラフ名はタグと出力対象のフィールドのキーから生成
• TCP コネクションを可能な限り使いまわす (graphite-api.gem による機能)
• バッファリングしておいてある程度まとめてメトリクスを送信 (graphite-api.gem による機能)

たとえば dstat からグラフを生成したい場合

こんな感じの設定をすると、

<source>
  type dstat
  tag dstat.__HOSTNAME__
  option -lnc
  delay 3
</source>

<match dstat.**>
  type flatten_hash
  add_tag_prefix graphite.
  separator .
</match>

<match graphite.dstat.**>
  type graphite
  host localhost
  port 2003
  tag_for prefix
  remove_tag_prefix graphite.dstat.
  name_key_pattern ^((?!hostname).)*$
</match>

こんな感じのグラフが出来る

先行プラグインとの互換性

設定ファイルを頑張って書きなおしてもらえれば同じグラフに対してメトリクスを送るようには出来ます
先日の このエントリ を例にすると、以下の様に設定ファイルを書き換えてあげる必要があります

<source>
  type dstat
  tag dstat
  option -lcn
  delay 5
</source>
 
<match dstat>
  type copy
  <store>
    type map
    tag  "map.dstat.gauges." + record["hostname"]
    time time
    record { "loadavg-short" => record["dstat"]["load avg"]["1m"] }
  </store>
  <store>
    type map
    tag  "map.dstat.gauges." + record["hostname"]
    time time
    record { "cpu-usr" => record["dstat"]["total cpu usage"]["usr"] }
  </store>
  <store>
    type map
    tag  "map.dstat.gauges." + record["hostname"]
    time time
    record { "cpu-sys" => record["dstat"]["total cpu usage"]["sys"] }
  </store>
  <store>
    type map
    tag  "map.dstat.gauges." + record["hostname"]
    time time
    record { "cpu-hiq" => record["dstat"]["total cpu usage"]["hiq"] }
  </store>
  <store>
    type map
    tag  "map.dstat.gauges." + record["hostname"]
    time time
    record { "cpu-siq" => record["dstat"]["total cpu usage"]["siq"] }
  </store>
  <store>
    type map
    tag  "map.dstat.gauges." + record["hostname"]
    time time
    record { "net-recv" => record["dstat"]["net/total"]["recv"] }
  </store>  
  <store>
    type map
    tag  "map.dstat.gauges." + record["hostname"]
    time time
    record { "net-send" => record["dstat"]["net/total"]["send"] }
  </store>  
</match>
 
<match map.dstat.**>
  type              graphite
  host              localhost
  port              2003
  remove_tag_prefix map
  name_key_pattern  .+
</match>

#CROSS2014 で #ぶつかり稽古 やります！

ぶつかり稽古って？

@__kan こんにちは、ペパボです。YAPC::ASIA参加者スペシャル特典にご応募いただき、ありがとうございます！ @kentaro とのぶつかりげいこをぜひ開催したく思います。ご都合のよろしい日をいくつかご連絡下さい！ URL

2013-10-02 11:08:11 via TweetList! to @__kan

オリジナルは昨年11月に行われたこちらのイベントです。エンジニアとエンジニアとの魂のぶつかり愛。

企画について

オリジナルのほうはペアプロでしたが、今回は趣向を変えて、コードレビューをテーマとしました。
自分はオリジナルのぶつかり稽古の関係者ではないのですが、許可を賜り、今回の企画名に冠しております。

実行委員長の @muddydixon さんがご自身のブログで仰っておられますが、今年の CROSS はこのようなテーマのもと各セッションが準備されてきました。

今年はテーマとして「クロスでススム、クロスで変わる」を掲げました。 「交流」「議論」を通して、明日への知識・人脈をひとつでも得て、変わる機会になってくだされば幸いです。

自分がセッションオーナーを務めさせていただくことになったセッションでは、このようなモチベーションのもと企画しました。

• 普段コードレビュをしてもらっていて、よそのあの人達はどんな感じでやっているのだろうと気になる
• 普段の勉強会の発表やセッションは最適化された手法を人に話す用にブラッシュアップされたものになっていることが多いので、現場感ライブ感のあるセッションを見たい

どんなセッション？

さて肝心の内容ですが、このような感じでお届けする予定です。

• コードレビュをテーマとしたパネルディスカッションを軸として進行
  • 気をつけていること
  • 使用しているツールについて
  • など！など！！
• 実際にレビュー、修正を行いながら進行
  • レビュー、修正は各ペアで予め何往復かしておいてもらっているので、普段の開発の一部分を切り取ってお見せする、という感じ

詳しくは実際にセッションにいらっしゃってご覧くださればと存じます。

各弟子に実装していただいた WEB アプリケーションは事前にデプロイし聴衆の方が見られるようにします。 また、各リポジトリの URL も公表しますので、勇気のあるあなたはレビュアーとしてライブで参加することも出来てしまうかもしれません！！！

課題の WEB アプリケーション

仕様はこんな感じです。
コレを各弟子に実装していただきました。

• アイドルグループの人気楽曲投票WEBアプリケーション
  • 要件
    • 661曲の候補の中から好きな1曲に投票
    • 1シリアルナンバーにつき1回投票可能
    • 最低でも1,000,000個のシリアルナンバーが発行されている
    • 会員登録は不要
  • 使用するフレームワーク、データストアなどは自由
  • スキーマと楽曲データの定義もお願いします

お待ちしております

会場でお会いしましょう！！！！！！

http://www.cross-party.com/programs/butsukari/

HRForecast に値を投げた時に一緒に複合グラフも作っちゃいたい！

HRForecast の API で値を投げた時に返ってくる JSON に、これまではエラーの有無とエラーメッセージだけでしたが、POST 成功時にはグラフの情報が含まれるようになりました。

{
    :metricses => [
        [0] {
            :section_name => "test_section",
                      :id => "1",
                  :colors => "[\"#99cc33\"]",
              :updated_at => nil,
            :service_name => "test_service",
              :graph_name => "test_graph",
                   :color => "#99cc33",
                    :meta => "{\"color\":\"#99cc33\"}",
              :created_at => nil,
                    :sort => "0"
        }
    ],
        :error => 0
}

他にもいくつかの JSON API が追加され、ますます便利になってしまっています。 https://github.com/kazeburo/HRForecast/commit/a5b28908a0fe53c45f9b576687df6a58af385b17

値を投げた時にグラフの情報が返ってくれると例えば何が嬉しいのか

複合グラフを作成する API 自体はないのですが、これまででも POST してあげれば WEB UI からでなくても複合グラフを作成することは出来ました。 しかし複合グラフを作成するには、複合する対象のグラフの ID を送ってあげる必要があります。 ちょっと変な実装をすればグラフ ID を HTTP リクエストで取得することは出来ますが、トリッキーなことをせずとも複合グラフの作成が出来るようになります。

こんな感じでどうでしょうか

ふたつのグラフ(test_1, test_2)に値を投げて、複合グラフ(test_complex)がなければ作成します。
ちょっと工夫すれば test_complex がすでに作られていて、そこに test_3 をつっこみたい、なんてこともカンタンに出来るでしょう。

require "net/http"
require "json"

class HRForecastRequestError < StandardError; end
class HRForecast
  def initialize(fqdn, bind_port, enable_https = false)
    @base_url = enable_https ? "https://" + fqdn : "http://" + fqdn
    @bind_port = bind_port
  end

  # datetime のサポートするフォーマットはドキュメント参照: @base_url/docs
  def post_value(service_name, section_name, graph_name, post_bodies)
    value = post_bodies[:value]
    datetime = post_bodies[:datetime] ? post_bodies[:datetime] : Time.now

    graph_path = [service_name, section_name, graph_name].join("/")
    result = post_request("/api/" + graph_path, number: value, datetime: datetime)

    # 失敗すると、200 で body にエラーメッセージの入った JSON が返るので成否はそこを見て判断
    if result[:error] == 1
      raise HRForecastRequestError, "could not post value to #{graph_path} (#{result[:messages].to_s.chomp})"
    end

    result[:metricses].first[:id].to_i
  end

  def create_complex_graph(service_name, section_name, graph_name, path_data, graph_options = {})
    description = graph_options[:description] ? graph_options[:description] : ""
    stack = graph_options[:stack] ? graph_options[:stack] : 0
    sort = graph_options[:sort] ? graph_options[:sort] : 19

    result = post_request("/add_complex", {
      service_name: service_name,
      section_name: section_name,
      graph_name: graph_name,
      description: description,
      stack: stack, # 0:積み上げないグラフ, 1:積み上げるグラフ
      sort: sort, # 値の大きいモノ順で list に表示される (0..19)
      :"path-data" => path_data # 複合グラフに突っ込むグラフの ID を配列で渡す
    })

    # 失敗すると、200 で body にエラーメッセージの入った JSON が返るので成否はそこを見て判断
    if result[:error] == 1
      graph_path = [service_name, section_name, graph_name].join("/")
      raise HRForecastRequestError, "could not create complex graph #{graph_path} (#{result[:messages].to_s.chomp})"
    end
  end

  def complex_graph_exist?(service_name, section_name, graph_name)
    url = URI.parse(@base_url + ":" + @bind_port.to_s + ["/json_complex", service_name, section_name, graph_name].join("/"))
    http = Net::HTTP.new(url.host, url.port)
    http.get(url.path).is_a?(Net::HTTPSuccess)
  end

  private
  def post_request(request_path, post_data)
    url = @base_url + ":" + @bind_port.to_s + request_path
    response = Net::HTTP.post_form(URI.parse(url), post_data)

    raise HRForecastRequestError, "post request was not success" unless  response.is_a?(Net::HTTPSuccess)
    JSON.parse(response.body, symbolize_names: true)
  end
end

hf = HRForecast.new("test.hrforecast.com", 5127)
service_name = "test_service"
section_name = "test_section"
graph_name_prefix = "test"

test1_graph_id = hf.post_value(service_name, section_name, graph_name_prefix + "_1", value: rand(100) + 1)
test2_graph_id = hf.post_value(service_name, section_name, graph_name_prefix + "_2", value: rand(100) + 1)

complex_graph_name = graph_name_prefix + "_complex"
if !hf.complex_graph_exist?(service_name, section_name, complex_graph_name) && test1_graph_id && test2_graph_id
  hf.create_complex_graph(
    service_name, section_name, complex_graph_name,
    [ test1_graph_id, test2_graph_id ]
  )
end

余談

前述のとおり、このような変な実装によってグラフ ID を取得することは出来ます。

def get_graph_id(service_name, section_name, graph_name)
  # HTTP リクエストでグラフ ID を取得出来るクチが csv ダウンロードリンクしかなかったぽい
  url = URI.parse(@base_url + ":" + @bind_port.to_s + ["/csv", service_name, section_name, graph_name].join("/"))
  http = Net::HTTP.new(url.host, url.port)

  # "d=1" をつけてリクエストすると content-disposition の csv のファイル名からグラフ ID が取得できる
  response = http.head(url.path + "?d=1")
  return $1.to_i if response["content-disposition"] =~ %r{attachment; filename=\"metrics_(\d+).csv\"}
end

自分で書いてて違和感があったので、「複合グラフを作成するために、値が POSTされたときにグラフ ID を返すようにって出来ますか？」って kazeburo さんに相談したら、数分で実装してくれました。
はじめからお願いすれば良かった感が強いですけど、自分はこの変な実装をするために結構な時間を費やしてしまったのですが HRForecast のコードを読んでみたりだとか色々勉強になったこともあったのでまぁそれはそれで良かったと考えます。前向きに。

はてなブログ2周年おめでとう！

はてなブログ2周年おめでとう！> id:hatenablog