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を超えたらサンプルを更新する
- “Resampling based on wait times” で、
performance_schema_max_digest_sample_age > 0の時- 最後に
QUERY_SAMPLE_*を保管した時刻からperformance_schema_max_digest_sample_age秒以上経ってまた同じダイジェストが記録された時にサンプルを更新する
- 最後に
という動きなようす。
サンプルは
というわけでPKを変えながら10万回SELECTを流しながら、このテーブルを覗いてみた。
サンプルは
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 件のコメント :
コメントを投稿