2018年5月30日水曜日

MySQL 8.0にはperformance_schema.events_statements_summary_by_digest にQUERY_SAMPLE_TEXTカラムが追加された

MySQL 5.7からMySQL 8.0でのevents_statements_summary_by_digestのカラム変更。
$ diff -y --suppress-common-lines <(mysql57 -sse "DESC p_s.events_statements_summary_by_digest")
 <(mysql80 -sse "DESC p_s.events_statements_summary_by_digest")
DIGEST  varchar(32)     YES             NULL                  | DIGEST  varchar(64)     YES             NULL
FIRST_SEEN      timestamp       NO              0000-00-00 00 | FIRST_SEEN      timestamp(6)    NO              0000-00-00 00
LAST_SEEN       timestamp       NO              0000-00-00 00 | LAST_SEEN       timestamp(6)    NO              0000-00-00 00
                                                              > QUANTILE_95     bigint(20) unsigned     NO              NULL
                                                              > QUANTILE_99     bigint(20) unsigned     NO              NULL
                                                              > QUANTILE_999    bigint(20) unsigned     NO              NULL
                                                              > QUERY_SAMPLE_TEXT       longtext        YES             NULL
                                                              > QUERY_SAMPLE_SEEN       timestamp(6)    NO              0000-
                                                              > QUERY_SAMPLE_TIMER_WAIT bigint(20) unsigned     NO
  • FISRST_SEEN, LAST_SEEN のマイクロ秒対応
  • QUANTILE_*, QUERY_SAMPLE_* カラムの追加
そのうち、 QUERY_SAMPLE_* の動作に関わる performance_schema_max_digest_sample_age のはなし。

を読む感じ、
  • performance_schema_max_digest_sample_age = 0 の時
    • “Resampling based on wait times” で、 TIMER_WAIT が現在の QUERY_SAMPLE_TIMER_WAIT を超えたらサンプルを更新する
  • performance_schema_max_digest_sample_age > 0 の時
    • 最後に QUERY_SAMPLE_* を保管した時刻から performance_schema_max_digest_sample_age 秒以上経ってまた同じダイジェストが記録された時にサンプルを更新する
という動きなようす。
サンプルは STATEMENT_DIGEST みたいなものでノーマライズされる前の生のSQL。
というわけでPKを変えながら10万回SELECTを流しながら、このテーブルを覗いてみた。
$ for n in $(seq 1 100000) ; do
> mysql80 -e "SELECT * FROM d1.t1 WHERE num = $n" > /dev/null
> done

mysql80 100022> SELECT * FROM p_s.events_statements_summary_by_digest WHERE count_star > 10\G...
*************************** 3. row ***************************
                SCHEMA_NAME: NULL
                     DIGEST: d214d5d8f31ce686d36be01a22bc7cfff76dd8b7b131644c7fcad28e76f78489
                DIGEST_TEXT: SELECT * FROM `d1` . `t1` WHERE `num` = ?
                 COUNT_STAR: 100000
             SUM_TIMER_WAIT: 18943715535000
             MIN_TIMER_WAIT: 121014000
             AVG_TIMER_WAIT: 189437000
             MAX_TIMER_WAIT: 5925412000
              SUM_LOCK_TIME: 8737613000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 100000
          SUM_ROWS_EXAMINED: 100000
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-05-30 14:44:59.376251
                  LAST_SEEN: 2018-05-30 15:00:46.526876
                QUANTILE_95: 251188643
                QUANTILE_99: 363078054
               QUANTILE_999: 660693448
          QUERY_SAMPLE_TEXT: SELECT * FROM d1.t1 WHERE num = 96806
          QUERY_SAMPLE_SEEN: 2018-05-30 15:00:17.487788
    QUERY_SAMPLE_TIMER_WAIT: 1973581000
3 rows in set (0.01 sec)
ちょくちょく QUERY_SAMPLE_* が変わったり変わらなかったりする。
個人的には performance_schema_max_digest_sample_age = 0 にして「一番時間がかかった時のサンプル」を残しておくのがいいかなと思ったり思わなかったり。
ちなみに、ビューに対するクエリーの場合、サンプルはビューに対するアクセスのクエリーで DIGEST_TEXT はビューを展開した後のクエリーをノーマライズするので一瞬「んっ?」となった。
慣れれば平気。
mysql80 100022> SELECT * FROM p_s.events_statements_summary_by_digest WHERE count_star > 10\G
*************************** 1. row ***************************
                SCHEMA_NAME: p_s
                     DIGEST: 9cbd44d9fdf48860a2f21597fd1d543130319eb800204c6ed51491e68ff55dcd
                DIGEST_TEXT: SELECT `performance_schema` . `events_statements_summary_by_digest` . `SCHEMA_NAME` AS `SCHEMA_NAME` , `performance_schema` . `events_statements_summary_by_digest` . `DIGEST` AS `DIGEST` , `performance_schema` . `events_statements_summary_by_digest` . `DIGEST_TEXT` AS `DIGEST_TEXT` , `performance_schema` . `events_statements_summary_by_digest` . `COUNT_STAR` AS `COUNT_STAR` , `performance_schema` . `events_statements_summary_by_digest` . `SUM_TIMER_WAIT` AS `SUM_TIMER_WAIT` , `performance_schema` . `events_statements_summary_by_digest` . `MIN_TIMER_WAIT` AS `MIN_TIMER_WAIT` , `performance_schema` . `events_statements_summary_by_digest` . `AVG_TIMER_WAIT` AS `AVG_TIMER_WAIT` , `performance_schema` . `events_statements_summary_by_digest` . `MAX_TIMER_WAIT` AS `MAX_TIMER_WAIT` , `performance_schema` . `events_statements_summary_by_digest` . `SUM_LOCK_TIME` AS `SUM_LOCK_TIME` , `performance_schema` . `events_statements_summary_by_digest` . `SUM_ERRORS` AS `SUM_ERRORS` ,
                 COUNT_STAR: 12
             SUM_TIMER_WAIT: 24253861000
             MIN_TIMER_WAIT: 925197000
             AVG_TIMER_WAIT: 2021155000
             MAX_TIMER_WAIT: 6172994000
              SUM_LOCK_TIME: 6760000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 25
          SUM_ROWS_EXAMINED: 64
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: 12
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 12
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-05-30 17:20:00.342417
                  LAST_SEEN: 2018-05-30 17:21:16.207898
                QUANTILE_95: 6309573444
                QUANTILE_99: 6309573444
               QUANTILE_999: 6309573444
          QUERY_SAMPLE_TEXT: SELECT * FROM p_s.events_statements_summary_by_digest WHERE count_star > 10
          QUERY_SAMPLE_SEEN: 2018-05-30 17:21:11.760156
    QUERY_SAMPLE_TIMER_WAIT: 1947630000

0 件のコメント :

コメントを投稿