スマートスタイル TECH BLOG

データベース&クラウド技術情報

pt-query-digestを使用したクエリログの変換について

pt-query-digestを使用したクエリログの変換について

Percona Toolkitに含まれるpt-query-digestをスロークエリのレポーティングに使用している方は多いと思いますが、pt-query-digestは様々なクエリソースを受け取り、様々なフォーマットに変換できることをご存知でしょうか。

今回は、pt-query-digestを使用して、様々なクエリソースをスロークエリログ形式に変換する方法をご紹介します。

pt-query-digestが対応するクエリソースの形式

pt-query-digestは以下のクエリログ形式に対応しています。

  • slowlog | スロークエリログ
  • genlog | 一般ログ
  • binlog | バイナリログ
  • tcpdump | tcpdump
  • rawlog | テキストログ
  • –processlist | SHOW PROCESSLIST

入力するクエリログの形式は、 --processlistを除き–type オプションで指定します。

--processlist はログからの読み取りを行うものではなく、–processlist オプションにMySQLへの接続のためのDSNを指定することで、一定間隔でポーリングした結果を入力とすることができるというものです。

rawlogは、1行、1SQL形式の以下のようなテキストログを意味します。

pt-query-digestが対応する出力形式

出力形式は以下のとおりです。

  • report | テキストレポート形式
  • slowlog | スロークエリログ形式
  • secure-slowlog | セキュアスロークエリログ形式
  • json | JSONレポート形式
  • json-anon | セキュアJSONレポート形式

pt-query-digestの機能としては、スロークエリログをサマリしたレポートを作成するということが主ですが、レポート出力を行わず(--no-report)、あるクエリログをスロークエリログ等の形式に変換するということが可能です。

それぞれのクエリログ形式の、スロークエリログへの変換はベストエフォートで行われます。

例えば、バイナリログをスロークエリログに変換する場合、 binlog_format=ROW で記録されたDMLイベントはSQL形式ではなくBASE64エンコードされた文字列になりますので、スロークエリ形式で正常に出力されません。
一方で、DDLや、binlog_format=STATEMENTで記録されたDMLイベントはスロークエリ形式で出力されます。

一方でtcpdumpのログはLock_time, Rows_sent, Rows_examined等の統計は無いものの、クエリのフルテキスト、及び実行時間という基本的な内容が確認できます。

--processlist を指定した場合のログも同様に、詳細なメトリックは無いものの基本的な情報が確認できます。

tcpdumpやprocesslistのメリットは、long_query_time=0のようにインスタンスレベルでの全クエリの取得を有効化せずに実行されているクエリをロギングできるという点です。

tcpdumpについては、long_query_time = 0にするよりもMySQLのパフォーマンスに影響を与えないという計測結果があります。

https://www.percona.com/blog/2015/04/10/measuring-impact-tcpdump-busy-hosts/

processlistは定点観測になりますので高速なクエリは記録されない可能性がありますが、より影響を与えないロギングが可能でしょう。

secure-slowlogjson-anon では、出力されるSQLの 条件値数値 等が?に変換されます。
ベンダへの調査依頼の際に、センシティブなデータをマスクすることに役立つでしょう。

スロークエリログからJSONへ

--output json を使用することで、スロークエリログのレポートをJSON形式で出力することが可能です。

他のプログラムにスロークエリログを連携する場合には、JSON形式が適しています。

例えば、日々のスロークエリレポートをJSON形式で他のデータストアにインポートし、RedashやGrafana、Kibana等で可視化するということも考えられます。

以下のように実行時間を抽出できます。

また、--group-by arg の指定により同じテキストであるクエリをまとめることも可能です。

例えばある環境に実行されているクエリを、別のバージョンの環境に実行してテストしたい、というようなニーズに活用できます。
ここで注意したいのは、JSONレポート形式の場合、classes[].example.query に格納される文字数は10000文字に制限されていることです。
クエリが部分的にならないよう、pt-query-digestのmax_query_lengthの値を増やすことを推奨します。

JSON形式のレポートから、以下のようにクエリを抽出し、別環境で実行することも可能です。

なお、取得元の環境と同じ状態でクエリが実行可能かという点は、条件値のレコードの有無によります。
実行環境のある時点のバックアップから検証環境を作成し、いつでも実行前の状態に戻せるようにファイルシステムのスナップショットを取得しておくことが望ましいでしょう。
また、この場合DMLを実行する際の時系列は考慮されませんのでご注意ください。

まとめ

今回紹介したJSON形式、スロークエリログ形式など、出力フォーマットが決まっていれば、自作のパース用スクリプトも作成しやすくなるのではと思います。

pt-query-digestはその歴史も長く、ユーザ数も多いこともあってアイデア次第で色々な使い方も考えられます。

たまにドキュメントを眺めてみてお気に入りの使用方法を見つけてみてはいかがでしょうか。

Return Top