Upgrade to Pro — share decks privately, control downloads, hide ads and more …

pt-query-digestをリアルタイムに取りたい!

Genta Kamitani
November 26, 2019
180

 pt-query-digestをリアルタイムに取りたい!

社内LTで発表したスライドです。

Genta Kamitani

November 26, 2019
Tweet

Transcript

  1. 大量のSQLクエリを SELECT * FROM hoge WHERE foo = 123 SELECT

    * FROM hoge WHERE foo = 456 SELECT * FROM hoge WHERE foo = 789 INSERT INTO hoge (fuga, foo) VALUES (1, 2), (3, 4) INSERT INTO hoge (fuga, foo) VALUES (5, 6), (7, 8), (9, 10) SELECT * FROM bar WHERE 0 <= baz AND baz <= 5 ORDER BY id ASC SELECT * FROM bar WHERE 10 <= baz AND baz <= 15 ORDER BY id SELECT * FROM bar WHERE 20 <= baz AND baz <= 25 ORDER BY id
  2. こうして SELECT * FROM hoge WHERE foo = 123 SELECT

    * FROM hoge WHERE foo = 456 SELECT * FROM hoge WHERE foo = 789 INSERT INTO hoge (fuga, foo) VALUES (1, 2), (3, 4) INSERT INTO hoge (fuga, foo) VALUES (5, 6), (7, 8), (9, 10) SELECT * FROM bar WHERE 0 <= baz AND baz <= 5 ORDER BY id ASC SELECT * FROM bar WHERE 10 <= baz AND baz <= 15 ORDER BY id SELECT * FROM bar WHERE 20 <= baz AND baz <= 25 ORDER BY id
  3. こう SELECT * FROM hoge WHERE foo = 123 SELECT

    * FROM hoge WHERE foo = 456 SELECT * FROM hoge WHERE foo = 789 INSERT INTO hoge (fuga, foo) VALUES (1, 2), (3, 4) INSERT INTO hoge (fuga, foo) VALUES (5, 6), (7, 8), (9, 10) SELECT * FROM bar WHERE 0 <= baz AND baz <= 5 ORDER BY id ASC SELECT * FROM bar WHERE 10 <= baz AND baz <= 15 ORDER BY id SELECT * FROM bar WHERE 20 <= baz AND baz <= 25 ORDER BY id QPS: 100 Avg Time: 200us Total Time: 200ms QPS: 50 Avg Time: 10us Total Time: 500us QPS: 1000 Avg Time: 2ms Total Time: 2s
  4. 代替になりそうなソフトウェア • ProxySQL ◦ 現在検討はしてるけどスッと入るようなものではない • InfluxDB, Elasticsearch, etc. ◦

    現在モンストで使ってるし、こいつらで集計できると楽 • pt-fingerprint ◦ シンプルなコマンドすぎて使い勝手が悪い ◦ 大量のクエリを変換するのに向いてない
  5. 起動 $ fluentd -c example.conf -p lib/fluent/plugin … 2019-11-26 12:37:39

    +0900 [info]: #0 fluentd worker is now running worker=0
  6. ログを入れてみる echo -e 'query:SELECT * FROM hoge where fuga =

    1' >> /path/to/query.log echo -e 'query:SELECT * FROM hoge where fuga = 2' >> /path/to/query.log echo -e 'query:SELECT * FROM hoge where fuga = 3' >> /path/to/query.log
  7. fluentdの出力 2019-11-26 12:38:03.060290000 +0900 example.input: {"query":"select * from hoge where

    fuga = ?","fingerprint":"select * from hoge where fuga = ?"} 2019-11-26 12:40:21.063140000 +0900 example.input: {"query":"select * from hoge where fuga = ?","fingerprint":"select * from hoge where fuga = ?"} 2019-11-26 12:40:27.060001000 +0900 example.input: {"query":"select * from hoge where fuga = ?","fingerprint":"select * from hoge where fuga = ?"}
  8. fluentdの出力 2019-11-26 12:38:03.060290000 +0900 example.input: {"query":"select * from hoge where

    fuga = ?","fingerprint":"select * from hoge where fuga = ?"} 2019-11-26 12:40:21.063140000 +0900 example.input: {"query":"select * from hoge where fuga = ?","fingerprint":"select * from hoge where fuga = ?"} 2019-11-26 12:40:27.060001000 +0900 example.input: {"query":"select * from hoge where fuga = ?","fingerprint":"select * from hoge where fuga = ?"}