gihyo.jpサイトのロゴ

MySQL道普請便り

第85回sysスキーマの便利なストアドプロシージャやストアドファンクション［その2］

2018-11-27

前回（第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ビューなどを提供しています。

mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest \G
*************************** 1. row ***************************
                SCHEMA_NAME: test
                     DIGEST: 496988680ff6fdbc042a2f62f2892a6a
                DIGEST_TEXT: SELECT * FROM `t0` WHERE `id` = ?
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 764207000
             MIN_TIMER_WAIT: 764207000
             AVG_TIMER_WAIT: 764207000
             MAX_TIMER_WAIT: 764207000
              SUM_LOCK_TIME: 424000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1
          SUM_ROWS_EXAMINED: 1
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-11-13 16:48:26
                  LAST_SEEN: 2018-11-13 16:48:26

SQLステートメントはDIGEST_TEXTカラムに表示されているように正規化され、同じテーブルおよび同じWHERE句のようなSQLステートメントであれば、グループ化します。正規化されたものはプリペアドステートメントのような形式になります。DIGESTカラムはDIGEST_TEXTカラムのSQLステートメントのハッシュ値が入ります。

正規化の例
mysql> SELECT /* comment */ * FROM t0 WHERE col1 = 1 AND col2 > 100
mysql> SELECT * FROM t0 where col1=99 AND col1  >  999

余分な空白やコメントは排除、リテラル文字列は?で丸めて、正規化します。

mysql> SELECT * FROM t0 WHERE col1 = ? AND col2 > ?

結果として正規化後が同じ形式になるので、同じSQLステートメントと判断され同一行で集計されます。

そして、このプロシージャはevents_statements_summary_by_digestテーブルからスナップショットを取得して、それを基に分析します。また、テーブルは累積で集計されるため、2点間でスナップショットを取得して比較することで分析するということも可能です。OracleでいうStatspackのようなものと考えることができると思います。

実行方法

まずは、簡単にstatement_analysis ビューをベースとした2点間比較を取得してみます。

①ベースとなるスナップショットを保存するテンポラリテーブル（ベーステーブル）作成

sys.baseテンポラリテーブルが作成されます。

CALL sys.statement_performance_analyzer('create_tmp', 'sys.base', NULL);

②初期のスナップショット作成

events_statements_summary_by_digestのデータがsys.tmp_digestsというテンポラリテーブルにコピーされます。tmp_digestsテーブルはハードコードされたテンポラリテーブルです。

CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);

③ベーステーブルに初期スナップショット保存

②で取得されたsys.tmp_digestsのデータがsys.baseへコピーされます。

CALL sys.statement_performance_analyzer('save', 'sys.base', NULL);

④待機

ここで数秒待機します。

⑤スナップショット作成

events_statements_summary_by_digestのデータがsys.tmp_digestsというテンポラリテーブルにコピーされます。

CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);

⑥分析

⑤で取得したsys.tmp_digestsと③で取得したsys.baseの差分を表示します。

CALL sys.statement_performance_analyzer('delta', 'sys.base', 'analysis')\G
*************************** 1. row ***************************
Next Output: Top 100 Queries Ordered by Total Latency
1 row in set (0.03 sec)

*************************** 1. row ***************************
            query: SELECT * FROM `t0` LIMIT ?
               db: test
        full_scan:
       exec_count: 14
        err_count: 0
       warn_count: 0
    total_latency: 2.42 ms
      max_latency: 284.70 us
      avg_latency: 172.73 us
     lock_latency: 1.11 ms
        rows_sent: 14
    rows_sent_avg: 1
    rows_examined: 14
rows_examined_avg: 1
    rows_affected: 0
rows_affected_avg: 0
       tmp_tables: 0
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 0fa9052a072b228c4bbc5363d21719b5
       first_seen: 2018-11-13 19:53:31
        last_seen: 2018-11-13 19:56:10
〈以下割愛〉

上記の場合は③と⑤の間での、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します。

CALL sys.statement_performance_analyzer('delta', 'sys.base', 'with_sorting')

まとめ

このプロシージャでさまざまな分析が可能です。前述のin_viewsに書いてあるビューを確認して、いろいろ試してもらえればと思います。また、customを利用することで独自の分析もできます。SET @sys.debug='ON'とすることで、デバッグ情報が表示されるので、独自で作成する場合はONにすると良いと思います。

詳しく知りたい方は、26.4.4.25 The statement_performance_analyzer() Procedureをご参照ください。

おすすめ記事

記事・ニュース一覧