BigQueryのテーブルを一定期間経過後削除する defaultTableExpirationMs
Google BigQuery 便利ですね。
テーブルを日毎に分割してデータを貯めていっているのですが、ある程度経過したらまぁ消してもよいかなと思うデータもあります。
そんなときに、GCSやS3のObject Lifecycle Management的なものがあると、自前でcronまわして削除するという手間とか何らかのリソースが不要になって嬉しいなということで、調べてやってみた話です。
答えはちゃんと公式ドキュメントに書いてあって、 defaultTableExpirationMs というプロパティを設定すれば良いようです。
GitHub - GoogleCloudPlatform/gcloud-ruby: Google Cloud Client Library for Ruby 使って設定するコードは以下のような感じです。
require "gcloud" gcloud = Gcloud.new "project-id", "/path/to/keyfile.json" bigquery = gcloud.bigquery dataset = bigquery.dataset "mydataset" dataset.default_expiration = 3600000
ドキュメントの通り、単位はmsecで、設定以降に作られたテーブルは、その作成した時間から上記設定時間経過後に自動的に削除されます。作成済みのテーブルには反映されませんし、作成済みのテーブル個別に対して削除期限を変更する設定はないようです(これこれで欲しいのですが)。また、1時間より短い設定はできません。
あと注意として、Datasetに対してオーナーの権限(Is owner)を持ったキー(上記のコードでいうkeyfile.json)でないと設定できませんでした。編集権限(Can edit)では設定できませんでした。
現状ではUIからは1日で削除するか否かの設定はできますが、細かい指定をするには上記のようにAPIを使って設定するしかなさそうです。
が、これでテーブルの管理の一手間が減って大変助かりますね。
エンジニアサポートクロス2016にゴールドスポンサー( Hamee )として参加してきた
2/5に横浜大桟橋で開催されたエンジニアサポートクロス2016にゴールドスポンサーのHamee
の一員として参加してきました。
ISUCON5で惨敗し、その悔しさのエネルギーがあるときに、CROSS2016スポンサー募集を発見して、そのエネルギーで会社も説得し、なんとか会社として初のエンジニア系イベントのスポンサーというところにこぎつけました。
最初はシルバーくらいのつもりだったのが、結局はゴールドスポンサーでエントリー。
ゴールドなのでランチセッションもできるということで、ブースの準備はあるは、ランチの手配、セッションスピーカーの手配など、初めてづくしで、みんなでアイデア出して協力してと大変だったけどよい経験でした。もっともほとんどのことは仲間がうまくやってくれて私は大したことせず状態でしたが。仲間に感謝ですね。
ともあれ、当日はがらぽんでMacBook Airがあたるという、太っ腹!?企画でそれなりにブースも盛況になるし、twitterも賑わってくれたました。
MacBook Airはなんとしても当てて欲しかったので、結構最後の方まで出なかったのはハラハラでした。当たりでてよかったー。
知り合いが当てた!#cross2016 #Hamee pic.twitter.com/33f1p2Sbgr
— ひらひら (@hirahira_mb_) February 5, 2016
ランチセッションのまとめは、こちら。まとめ、ありがとうございます!
とにかく、スポンサーやってみて良かったです。
いろんな方と出会いがありましたし、今まで参加するだけだったのと違って数倍楽しめました。
また、来年もなんとか会社を丸め込んで説得してスポンサーで参加したいですね。
最後に、当日一緒にHameeスタッフとして参加した皆様に感謝します。
そして、エンジニアサポートクロス2016のイベントスタッフの皆様、いろいろ手配・ご協力いただきありがとうございました。
もちろん、ブースに来てくれた参加者の皆様、本当にありがとうございました!!
ランチは小田原のはなまるキッチンさんにご用意いただきました。
マジでおいしいので、小田原きたときにはちょっと歩くけど、超オススメです。
続:Consul 0.6でユーザのいるshardを探す(Prepared Queryを使う)
この記事は HashiCorp Advent Calendar 2015 - Qiita の23日目です。(1時間ほど早めに公開したのでブログの日付は22ですが)
前回の記事 Consul 0.6でユーザのいるshardを探す(Prepared Queryを使う) - kikumotoのメモ帳 の続編です。
id:fujiwara さんの
Consul 0.6でユーザのいるshardを探す(Prepared Queryを使う) - kikumotoのメモ帳b.hatena.ne.jp というコメントを受けて、Prepared Queryを100万件突っ込んでみた結果をまとめます。
- [consul]
数百万のPQがconsulに登録されても大丈夫なものだろうか
2015/12/21 07:25
結果
先に結果を。
100万件PQを突っ込みましたが、DNSの応答は10件だけ登録のときも、100万件登録後も、ほぼ2msecとなり特に性能面での変化は見られませんでした。
ただ、PQの登録については若干劣化(最初は0.9msくらいあったのが、1.5msくらいになった)するようです。ただし、環境が安定しているかどうかは確証がもてないので、参考程度に。
あと、
というコメントもいただき、突っ込んだデータを削除しました。
確かに、Disk使用量が減ることはなかったです。
一方で、メモリの方は解放されているようです(Consul0.6のため?0.5では未検証)。
環境
項目 | 内容 |
---|---|
Consul version | 0.6 |
Consul server | 5ノード |
Consul client | 4ノード |
Consul データパス | /dev/shm/consul_data(tmpfs) |
メモリ(全ノード共通) | 16GB |
CPU | Xeon(R) CPU E5-2407(4コア) |
OS | CentOS5,6,7混在 |
という感じで、特にデータは tmpfs 上において試しました。(これは各ノードのDisk性能が一律ではないため、今回の評価用にこの設定にしてあります。)
データの登録
データの登録は下記のようなrubyコードで行いました。Keep-Aliveしながら、一件ごとの登録にかかった時間を記録しています。
#!/usr/bin/env ruby require 'net/https' require 'uri' require 'benchmark' base_uri = "http://localhost:8500/v1/query" uri = URI.parse(base_uri) c = Net::HTTP.new(uri.host, uri.port) req = Net::HTTP::Post.new(uri.path) c.start do |x| 11.upto(1000000) { |i| id = sprintf("%07d", i) req.body = <<-EOS {"Name":"mdb-#{id}","Service":{"Service":"db01","Tags":["master"]},"DNS":{"TTL":"30s"}} EOS res = nil time = Benchmark.realtime do res = x.request(req) end puts "#{id}, #{time}, #{res.code}, #{res.body}" } end
登録は、leaderノード上で実行しました。
登録前後、削除後の状況
登録前は厳密には、10件だけPQを登録した状態です。
DNS応答(ms) | データサイズ(byte) | メモリ空容量(byte) | 1件登録時間(ms) | |
---|---|---|---|---|
登録前 | 2 | 262144 | 6523371520 | 0.9 〜 1.0(10件登録した時) |
登録後 | 2 | 134217728 | 5202753454 | 1.3 〜 1.6(100万件付近) |
削除後 | - | 134217728 | 6361238449 | - |
◼︎ 測定方法について
- DNSはdigの結果に表示される
Query time
を取得(CacheにのっていないURLをqueryしてます)。 - データサイズは
/dev/shm/consul_data/raft/raft.db
のサイズ。ls より。 - メモリ空容量は、freeコマンでのfeeの値。
- 1件登録時間は、登録スクリプトの出力より、目でみておおよその値を抽出。
メモリの状況は下記グラフも載せておきます。下がり始めたところが、100万件データを入れているところ。回復しているところが削除しているタイミングなので、メモリは回復する感じです。
まとめ
- 100万件PQ入れてもDNS引きに関して性能面での劣化はない。
- データ登録については性能劣化がある。
- データ削除しても、Disk容量は減らない。
- データ削除するとメモリは解放される。
実際は各自の用法・設定に合わせて確認してもらうのが一番なので、以上の結果は参考程度にどうぞ。
また、利用用途としても多分ソシャゲのような数百万ユーザが対象なものについて、この方法を採るのはちょっと微妙かなという印象です。DNS引きに影響はないにしても、登録や削除が頻繁なものにはちょっとという気がします。(障害時のデータ復元も大変そう)
自分の想定環境では多くて10万ユーザ、多分1万ユーザくらいの登録で、変動も頻繁でなく、PQのデータはすべてDBから復元可能なので、DNS引きだけでshardを解決できるという利点をとって採用方向で、今後本番環境内でConsulの動作試験していくかなぁという感じです。
何がご要望がいただければ、お正月に試すかもしれません。(ただ寝てるかもしれません)
Consul 0.6でユーザのいるshardを探す(Prepared Queryを使う)
この記事は HashiCorp Advent Calendar 2015 - Qiita の第21日目です。(大人な事情により Hamee Advent Calendar 2015 - Qiita の第20日目でもあります。なので微妙な時間に公開)
概略
ユーザIDをベースにDBを分割(sharding)しているのですが、プログラムからユーザのいるshardを探すのにConsulを使えないかなぁと思って試している話。
Consul0.6のPrepared Queryを使って解決してみました。
目次
ユーザIDをベースにDBを分割(sharding)していると、プログラムからユーザのいるshardを探す必要があります。 これをどこかのメタDBサーバにするってのも良いんだけど、Consul使ってDNSで引っ張ってこれると使う側にとって扱いやすいかなと思って調べてます。 ・Node設定 各DBではconsulのServiceが下記JSONの定義のように登録されています。 なので以下のような感じでDNSでひける状態です。
ちなみに試している環境にはdnsmasqを入れています。 Consul0.6で導入されたPrepared Query(以下PQ)を使う方法です。 PQに下記のような情報を登録します。(user1.jsonとします) これを以下のように登録します。 IDが発行されるので、これは後で必要になります。もしくは /v1/query にGETでリクエストすれば一覧が取れるので、そこから必要ものを探せばOKです。 このようにすると、以下のようにDNSでひけて、ユーザが収容されているmaster DBのIPが取得できます。 ・slave昇格 db01-aが死んでdb01-bがmasterになるときは、db01-bで のようにmasterのタグをもたせて これで、同じようにDNSをひくと、昇格したノードのIPが取得できます。 なお、db01-aがmasterのタグを持って起動しないようにする必要はあります。今回は、マシンブート時にconsulを自動起動させないようにして逃げてます。 ・shard移動 user1のいるshardがdb02になれば、 というJSONを用意して、このPQのIDを指定してPUTします。 これで、同じようにDNSをひくと、今度はdb02のmasterのIPが取得できます。 Consul0.6より前だとPQがないので、外部サービス(ノード)を登録することで同じようにことできると思います。 この場合、外部サービスとして以下のようなJSONを登録します。 こうすると、以下のようにDNSがひけます。 外部サービス(ノード)なので、CNAMEで解決されて、その後Aレコードを得るような形となります。 shardの移動は、Nodeの定義を更新することで対応することとなります。 Consul0.6より前ではこの方法かなぁとは思うのですが、Consul0.6だとPQによりCNAMEを介さずにひけるので方法1を採用予定です。 ・タグだけで のようにタグだけで解決することも可能は可能かと。 ただ、shard移動するときとか、userの追加・削除時には必要なタグをして指定して更新するのでちょっと大変そう。 ・サービスだけで みたいなサービスをユーザ数分だけ登録することでもできそう。 この場合、slave昇格でユーザ数分だけServiceを更新・登録とかしないといけなのでこれも大変そう。 ユーザIDをベースにDBを分割(sharding)しているような場合に、それをDNSでひけるようにするには、Consul0.6のPrepared Queryを使うとなんとかなりそうでした。 ちなみに、PQを1万ほど突っ込んでみた範囲では、DNSをひくことにパフォーマンス的な影響は特に見られませんでした。 Consulでやる場合のもっと良い方法とか、そもそもConsul使うべきじゃないとかありましたら教えてください。背景
前提
{
"Service": {
"name": "db01",
"tags": [ "master" ]
}
}
# dig master.db01.service.consul.
; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.37.rc1.el6_7.4 <<>> master.db01.service.consul.
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 53317
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0
;; QUESTION SECTION:
;master.db01.service.consul. IN A
;; ANSWER SECTION:
master.db01.service.consul. 30 IN A 192.168.0.11
;; Query time: 1 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Fri Dec 18 14:11:33 2015
;; MSG SIZE rcvd: 94
方法1(今回採用予定)
{
"Name": "master-db-user1",
"Service": {
"Service": "db01",
"Tags": ["master"]
},
"DNS": {
"TTL": "30s"
}
}
$ curl -X POST http://localhost:8500/v1/query --data-binary @user1.json
{"ID":"66804426-8e5f-427b-caa7-d896c9328910"}
$ dig master-db-user1.query.consul.
; <<>> DiG 9.3.6-P1-RedHat-9.3.6-25.P1.el5_11.2 <<>> master-db-user1.query.consul.
;; global options: printcmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 14375
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0
;; QUESTION SECTION:
;master-db-user1.query.consul. IN A
;; ANSWER SECTION:
master-db-user1.query.consul. 30 IN A 192.168.0.11
;; Query time: 1 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Fri Dec 18 14:44:21 2015
;; MSG SIZE rcvd: 94
{
"Service": {
"name": "db01",
"tags": [ "master" ]
}
}
consul reload
すればよいです。$ dig master-db-user1.query.consul. +short
192.168.0.12
{
"Name": "master-db-user1",
"Service": {
"Service": "db02",
"Tags": ["master"]
},
"DNS": {
"TTL": "30s"
}
}
$ curl -X PUT http://localhost:8500/v1/query/66804426-8e5f-427b-caa7-d896c9328910 --data-binary @user1.json
$ dig master-db-user1.query.consul. +short
192.168.0.21
方法2
{
"Node": "master-db-user1",
"Address": "master.db01.service.consul"
}
$ curl -X PUT http://localhost:8500/v1/catalog/register --data-binary @user1-node.json
$ dig master-db-user1.node.consul.
; <<>> DiG 9.3.6-P1-RedHat-9.3.6-25.P1.el5_11.2 <<>> master-db-user1.node.consul.
;; global options: printcmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 29792
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 0
;; QUESTION SECTION:
;master-db-user1.node.consul. IN A
;; ANSWER SECTION:
master-db-user1.node.consul. 30 IN CNAME master.db01.service.consul.
master.db01.service.consul. 30 IN A 192.168.0.11
;; Query time: 5 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Fri Dec 18 15:20:49 2015
;; MSG SIZE rcvd: 166
その他の方法
{
"Service": {
"name": "db01",
"tags": [ "master", "user1", "user2", ・・・ ]
}
}
{
"Service": {
"name": "user1",
"tags": [ "master"]
}
}
まとめ
fluentdのプルリク#696へのフィードバックを求めています
下記は、https://github.com/fluent/fluentd/pull/775 で2016/01/18にマージされたました!
Fluentd Advent Calendar 2015 - Qiita の10日目がまだ空いていたので、入れさせてもらいました!
今、in_tailに関して下記PRを出させてもらっています。
fluentd v0.12.18 が出ましたが、これはまだ取り込まれていません(想定内)。
な状況です。
in_tailのmultilineモードでは、Fluentdのin_tailプラグインで複数行のログをよむ方法 - Boost Your Programming! で説明されているように
つまり、メッセージがログに出力されてもすぐにそのログがFluentdのメッセージとして送信されるわけではなく、次のログが出てくるまで遅延することになります。
という問題があります。
頻繁にログが流れるような状況であると問題にはならないのですが、ログをfluentdでとりあえず集めているとそんなに頻繁に出力されるないようなログも対象としいたりして、その際にmultilineで取り込んでいるとこの仕様では困ることがあります。
そこで、上記PRを出している感じです。
とりあえず、同じことができるものを in_tail_multiex.rb · GitHub に起きました。
これを /etc/td-agent/plugin 配下に置いて conf に
<source> type tail_multiex path /path/to/logfile tag my.tag pos_file /var/log/td-agent/my.log.pos format multiline format_firstline /^\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d / format1 /^(?<time>\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d) (?<message>.*)$/ multiline_flush_interval 1s </source>
のようにmultiline_flush_interval
を指定してもらえればよいです。この指定した期間、ログに出力がないと、fluentdのメッセージとして送信されます。
問題や、コメントなどありましたら、上記PRなり、そもそもの issue
などにご意見をいただければ幸いです。
実際問題としてはとりあえず、gistにあげたような独自プラグインで当面のニーズは満たしていますが、本体に取り込まれるとより嬉しいかな、ということで。
pixzでケチケチバックアップ(もしくはGCS Nearlineで節約バックアップ)
この記事は qiita.com の7日目です。
TL;DR
pixzでCPUマルチコアを使って高速かつ、高圧縮することで、バックアップの保存料金節約をしました。
GCS Nearlineなら、GB単価も安いし、取り出すときも高圧縮してあれば取り出し料金、データ転送料金も安く抑えられて一石二鳥でした。
目次
日々多数あるオンプレミスなサーバ内の種々なデータを gzip 圧縮して AWS S3($0.0330/GB/Month)に保存しているのですが、1TBを超えてきたのもあるし所詮バックアップで取り出すことはたまにしかないので、より安い GCS Nearline($0.01/GB/Month)に移行することにしました。 ついでに、gzip圧縮するのと同じ実時間程度でより高圧縮にすると、保存料金も取り出し料金も安くできるので、圧縮方法について簡単に評価することにしました。 すでに最初に書いているように、最終的には pixz を選択したのですが、評価対象・内容を書いておきます。圧縮対象や用いるサーバ(CPU)によってこのあたりは判断が分かれることになるので、参考程度にしてもらえると良いです。 ・圧縮対象 を使用しました。 ・環境 ファイルを一度readして、キャッシュにのせての評価です。 Writeについても各コマンドが終了するまでの時間で、Diskへの同期までは含んでいません(のはず)。 ・結果 結果は下記のとおりです。time コマンでの計測です。 logrotate で pixz を使いたい場合は logrotate.d 配下に作るファイルで のような記述を入れればよいです。 CentOS5/6 で使う必要があったので、そのビルド方法を下記に記しておきます。 pixzをビルドするには liblzmaやlibarchiveが必要となる。特に liblzma については xz-devel パッケージでインストールされたものではpixzのビルドが通らない。
そこで、liblzmaおよblibarchiveについてもソースからビルドし、pixzはそれらを static link するようにする。 ・ビルド用ディレクトリの準備 ビルド作業用にディレクトリを作成しておく。 ・libarchiveビルド http://www.libarchive.org/downloads/ より最新のソースを取得し、ビルドする。 ・liblzmaビルド http://tukaani.org/xz/ より最新のソースを取得し、ビルドする。 ・pixzビルド https://github.com/vasi/pixz/releases より最新のソースを取得し、ビルドする。 libarchiveを認識するための設定 configure make これで、ldd でダイナミックリンクしているライブラリを確認し、liblzmaとlibarchiveがないことを確認する。 こうして出来上がった pixz を /usr/bin なり /usr/local/bin なりにコピーして使えばよい。 結局、バックアップに必要な領域にかかる費用は 1/5 くらいになって幸せです! pixzとGCS Nearlineでバックアップ保存料を大幅に削減できました。 注意1:pixzはデフォルトでは全Coreを全力で使うので適宜パラメータを調製しましょう。 注意2:AWS EC2にあるデータなんかはS3に保存しています(pixzは使ってます)。AWSからGCS Nearlineに転送するとその料金が高いので。背景
評価
MySQLのダンプファイル:サイズ 1863439430 byte
CPU E5-2407 2.20GHz(4Core)
Memory 16GB
software
time real
time user
圧縮ファイルサイズ
gzip
41.381s
40.158s
132994340
bzip2
9m7.662s
9m6.532s
86384870
pbzip2
2m23.181s
9m18.850s
86384870
pbzip2 -1
1m36.440s
6m22.965s
139260027
xz -1
1m17.306s
1m15.626s
105005636
pixz -2
26.569s
1m43.460s
87629204
pixz -3
38.820s
2m30.649s
84229748
pixz -2
が圧縮率や処理時間からするとベターに見えるので、これを採用した。logrotate
compress
compresscmd /usr/local/bin/pixz
compressoptions -2
compressext .xz
pixzのビルド
$ mkdir $HOME/pixz_build
$ cd $HOME/pixz_build
$ wget http://www.libarchive.org/downloads/libarchive-3.1.2.tar.gz
$ tar xzf libarchive-3.1.2.tar.gz
$ cd libarchive-3.1.2
$ ./configure --prefix="$HOME/pixz_build"
$ make
$ make install
$ cd $HOME/pixz_build
$ wget http://tukaani.org/xz/xz-5.2.2.tar.gz
$ tar xzf xz-5.2.2.tar.gz
$ cd xz-5.2.2
$ ./configure --prefix="$HOME/pixz_build"
$ make
$ make install
$ export PKG_CONFIG_PATH=$HOME/pixz_build/lib/pkgconfig
$ pkg-config --exists --print-errors "libarchive"
$ cd $HOME/pixz_build
$ wget -O pixz-1.0.5.tar.gz https://github.com/vasi/pixz/releases/download/v1.0.5/pixz-1.0.5.tar.gz
$ tar xzf pixz-1.0.5.tar.gz
$ cd pixz-1.0.5
$ LIBARCHIVE_LIBS=$HOME/pixz_build/lib/libarchive.a LZMA_LIBS=$HOME/pixz_build/lib/liblzma.a ./configure --prefix="$HOME/pixz_build"
$ make CFLAGS="-I$HOME/pixz_build/include/"
$ ldd src/pixz
linux-gate.so.1 => (0x00302000)
libm.so.6 => /lib/libm.so.6 (0x00ca9000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00c8d000)
libc.so.6 => /lib/libc.so.6 (0x00b28000)
/lib/ld-linux.so.2 (0x00b09000)
結果・まとめ
カジュアルにMySQLスローログ可視化with Fluentd+Elasticsearch+Kibana
この記事は
- MySQL Casual Advent Calendar 2015 - Qiita
- Elasticsearch Advent Calendar 2015 - Qiita
- Hamee Advent Calendar 2015 - Qiita
の第4日目です。
TL;DR
開発者の皆さんに、CasualにMySQLスローログを分析しもらうために、Fluentd + Elasticsearch + Kibana でMySQLスロークエリを下図のようにビジュアライズしました。(Kibana上で EXPLAIN の結果も確認できるようにしてあります)
ついでに、以下の Fluentd の filter plugin を作成しました。
目次
MySQLのスローログの一覧をくれ、と言われたのがきっかけですが、せっかくなので EXPLAIN の情報も渡したいし、発生状況を随時確認できるほうがうれしいかなということで、可視化する方向にしました。 最初は nata2 を使えばいいやと思っていたのですが、スロークエリとなっているSQLが巨大過ぎて nata2 が作るURIが長大になりすぎる問題に当たったたり、EXPLAINがないしってところでそのまま使うことは諦めました。 他には、 というやり方もありましたが、nata2のようにSQLのfingerprintでまとめるようになってないし、やはりEXPLAIN情報もないしってことで、結局、以上の情報をMIXして Fluentd(+自作プラグイン) + Elasticsearch + Kibana で可視化することにしました。
その方法をこのエントリーで書きます。 環境、バージョンは下記の通りです。 Java, Elasticsearch は yum でインストールしています。
Kibana は kibana4セットアップ - Qiita を参考にしました。 Elasicsearch では文字列の解析をされると困るので、下記のコマンドで文字列を解析しないように設定してあります。 fluentdでの処理の流れとしては下記のようなイメージとなっています。 実際には、filter_record_transformer より後、out_elasticsearch 前で、out_foward, in_forward を使ってデータを1箇所に集約する感しています。 以下に、使用している一部のプラグインについてコメントします。 MySQL のスローログファイルをfluentdに取り込むプラグインには yuku-t/fluent-plugin-mysqlslowquery · GitHub もあるのですが、 studio3104/fluent-plugin-nata2 · GitHub の方が できれば、nata2のプラグインに同梱ではなくて、昔のように分離しておいてくれるとPRとか出しやすいかなぁと思っています。 自作プラグイン kikumoto/fluent-plugin-mysql_explain · GitHub です。 このプラグインは、in_mysqlslowquery_ex で取得されたJSONの 自作プラグイン kikumoto/fluent-plugin-sql_fingerprint · GitHub です。 このプラグインは、in_mysqlslowquery_ex で取得されたJSONの 標準入力にSQLを受け取り、標準出力に抽象化された SQL (fingerprint) を出力する外部ツールに依存しています。実際のところは Percona Toolkitに含まれる pt-fingerprint を利用していますが、条件を満たすものであればなんでもOKです。 以下、td-agent.conf の設定例です。 これで、Elasticsearch に EXPLAIN もついたデータが登録されるようになります。 初回 Kibana にアクセスすると Index の設定を求められるので、上記の設定であるなら、 して、 グラフを出すには、
あとは、グラフ幾つかつくって適当にDashboardに登録してお好みにレイアウトすれば、冒頭に貼ってあるようなものができあがります。 Fluentd(+自作フィルタープラグイン) + Elasticsearch + Kibana でMySQLスロークエリを可視化しました。 ログをカジュアルに取り込める Fluentd、データをカジュアルに放り込める Elasticsearch、カジュアルにグラフを作ることのできる Kibana、どれも大変便利! 世の中から背景
Elasticsearch + Kibana 環境
名前
バージョン
OS
CentOS 6
Java
Open JDK 1.8
Elasticsearch
1.7.3
Kibana
4.1.2
curl -XPOST 127.0.0.1:9200/_template/strig_not_analyzed_template -d '{
"template": "*",
"mappings": {
"_default_": {
"dynamic_templates": [
{
"string_template": {
"mapping": {
"index": "not_analyzed",
"type": "string"
},
"match_mapping_type": "string",
"match": "*"
}
}
]
}
}
}'
Fluentd
in_mysqlslowquery_ex (fluent-plugin-nata2同梱)で、スロークエリログファイルをparse
↓
filter_record_transformer でホスト名設定
↓
filter_mysql_explain (自作プラグイン)で、EXPLAIN 結果取得
↓
filter_sql_fingerprint (自作プラグイン)で、sql の fingerprintを取得
↓
out_elasticsearch で Elasticsearch にデータ登録
in_mysqlslowquery_ex
SET timestamp
をあらかじめ除外してくれたりとか、アクセスしているDB情報も保持してくれたりして、すぐ使うに便利だったのでこちらを選択しました。filter_mysql_explain
sql
属性に保持されている SQL に対して EXPLAIN を実行して、その結果を explain
属性に保持する filter plugin となっています。filter_sql_fingerprint
sql
属性に保持されている SQL 文のパラメータ部分を抽象化するものです。抽象化されたSQLは fingeprint
属性に保持されます。設定例
<source>
type mysqlslowquery_ex
path /var/log/mysql/mysql-slow.log
tag mysqlslowquery.myapplication
pos_file /var/log/td-agent/mysql-slow.log.pos
last_dbname_file /var/log/td-agent/mysql-slow.log.lastdb
</source>
<filter mysqlslowquery.**>
type record_transformer
<record>
hostname ${hostname}
</record>
</filter>
<filter mysqlslowquery.**>
type mysql_explain
host 127.0.0.1
port 3306
database mydb
username dbuser
</filter>
<filter mysqlslowquery.**>
type sql_fingerprint
fingerprint_tool_path /usr/bin/pt-fingerprint
</filter>
<match mysqlslowquery.**>
type elasticsearch
type_name myapp-mysqlslowquery
host 127.0.0.1
port 9200
logstash_format true
logstash_prefix mysqlslowquery
include_tag_key true
</match>
Kibanaのグラフ作成例
Index contains time-based events
にチェックIndex name or pattern
に mysqlslowquery-*
を入力Create
すれば OK です。Visualize
で例えば、Vertical bar chart を選んで、下図のようのな設定を入れれば、fingerprint 単位で各ホストごとに色分けされたグラフが出力できます。まとめ
クソスロークエリがなくなることに貢献できれば幸いです!