前回(第82回 sysスキーマの便利なストアドプロシージャやストアドファンクション[その1])で、いくつかのプロシージャやファンクションを紹介しました。今回はその続きでsysスキーマのstatement_performance_analyzer()
プロシージャを紹介します。使用しているMySQLのバージョンは5.7.23です。
statement_performance_analyzer() プロシージャ
概要
このプロシージャは、MySQL上で実行中のSQLステートメントを分析したレポートを作成します。MySQL5.7.9から導入されました。実行するにはSUPER
権限が必要になります。これは、更新をバイナリーログに出力しないためのオプションSET sql_log_bin = 0
を内部で実行するためです。
events_statements_summary_by_digestテーブル
performance_schema.events_statements_summary_by_digest
テーブルを基に分析します。プロシージャの説明の前に、このテーブルについて簡単に説明します。
このテーブルはMySQLで実行されたSQLステートメントのさまざま統計情報を格納します。たとえば、レイテンシー、実行された回数やロックした時間などの情報を累積で集計します。メモリ上のテーブルのため、MySQL再起動するとデータは初期化されます。詳しくは25.10 Performance Schema Statement Digestsをご参照ください。
また、sysスキーマにはこのテーブルから時間の単位など付与して、人間が読みやすい形式のstatement_analysis
ビューなどを提供しています。
SQLステートメントはDIGEST_TEXT
カラムに表示されているように正規化され、同じテーブルおよび同じWHERE句のようなSQLステートメントであれば、グループ化します。正規化されたものはプリペアドステートメントのような形式になります。DIGEST
カラムはDIGEST_TEXT
カラムのSQLステートメントのハッシュ値が入ります。
余分な空白やコメントは排除、リテラル文字列は?
で丸めて、正規化します。
結果として正規化後が同じ形式になるので、同じSQLステートメントと判断され同一行で集計されます。
そして、このプロシージャはevents_statements_summary_by_digest
テーブルからスナップショットを取得して、それを基に分析します。また、テーブルは累積で集計されるため、2点間でスナップショットを取得して比較することで分析するということも可能です。OracleでいうStatspackのようなものと考えることができると思います。
実行方法
まずは、簡単にstatement_analysis ビューをベースとした2点間比較を取得してみます。
①ベースとなるスナップショットを保存するテンポラリテーブル(ベーステーブル)作成
sys.base
テンポラリテーブルが作成されます。
②初期のスナップショット作成
events_statements_summary_by_digest
のデータがsys.tmp_digests
というテンポラリテーブルにコピーされます。tmp_digests
テーブルはハードコードされたテンポラリテーブルです。
③ベーステーブルに初期スナップショット保存
②で取得されたsys.tmp_digests
のデータがsys.base
へコピーされます。
④待機
ここで数秒待機します。
⑤スナップショット作成
events_statements_summary_by_digest
のデータがsys.tmp_digests
というテンポラリテーブルにコピーされます。
⑥分析
⑤で取得したsys.tmp_digests
と③で取得したsys.base
の差分を表示します。
上記の場合は③と⑤の間での、SELECT * FROM t0 LIMIT ?
のクエリの分析結果が表示されています。このような形で分析できるようになります。
パラメータの紹介
statement_performance_analyzer(in_action,in_table,in_views)
というように3つの引数をとります。
- in_action
- アクションを指定します。以下の中から選択できます。
-
- snapshot…in_tableに指定されたテーブルからtmp_digestsテーブルへスナップショットを保存します。in_tableにNULLを指定すると
events_statements_summary_by_digest
テーブルからスナップショットを作成します
- save…tmp_digestsテーブルからin_tableで指定されたテーブルにスナップショットを作成します
- overall…in_tableで指定されたテーブルの内容に基づいて分析します。in_tableにNULLを指定するとtmp_digestsテーブルから分析します。
- delta…差分解析します。in_tableに指定されたテーブルとtmp_digestsテーブルの間で計算されます。
- create_tmp…スナップショットを保存する一時テーブルを作成します。
- cleanup…スナップショットおよびデルタに使用される一時テーブルを削除します。
- in_table
- in_actionに一部で使用されるテーブル名を指定します。
- in_views
- どのビューをベースとするかを指定します。
-
- analysis…
statement_analysis
ビュー
- with_runtimes_in_95th_percentile…
statements_with_runtimes_in_95th_percentile
ビュー
- with_errors_or_warnings…
statements_with_errors_or_warnings
ビュー
- with_full_table_scans…
statements_with_full_table_scans
ビュー
- with_sorting…
statements_with_sorting
ビュー
- with_temp_tables…
statements_with_temp_tables
ビュー
- custom …独自で作成したビュー
これらのcustom以外のビューはすべてsysスキーマ上に存在していて、ベースとなっているテーブルはevents_statements_summary_by_digest
テーブルです。詳しく説明しませんが、プロシージャでの分析結果がそれらのビューと同じ構成になります。
先ほどの例で、ソートの情報を分析する場合は、以下のようにCALLします。
まとめ
このプロシージャでさまざまな分析が可能です。前述のin_viewsに書いてあるビューを確認して、いろいろ試してもらえればと思います。また、custom
を利用することで独自の分析もできます。SET @sys.debug='ON'
とすることで、デバッグ情報が表示されるので、独自で作成する場合はONにすると良いと思います。
詳しく知りたい方は、26.4.4.25 The statement_performance_analyzer() Procedureをご参照ください。