WL#9830: PERFORMANCE_SCHEMA DIGEST QUERY SAMPLE

Add a column QUERY_SAMPLE_TEXT in table PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST to capture a query sample so that users can run EXPLAIN on a real query and to get a query plan.

Add a column QUERY_SAMPLE_SEEN in table PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST to capture the query sample timestamp.

Add a column QUERY_SAMPLE_TIMER_WAIT in table PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST to capture the query sample execution time.

Modify columns FIRST_SEEN and LAST_SEEN in table PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST to use fractional seconds.

NOTE:

Right now, Enterprise Monitor uses the history_long tables to get a sample, which implies that the history long consumer is kept enabled causing overhead. If the query sample is captured directly in the digest table, overhead in production can be reduced as history tables will not be needed.

CURRENT-VERSION = 8.0.3
PREVIOUS-VERSION = 5.7

F-1 A fresh MySQL installation of CURRENT-VERSION must create the EVENTS_STATEMENTS_SUMMARY_BY_DIGEST table as specified below.

F-2 An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must recreate the EVENTS_STATEMENTS_SUMMARY_BY_DIGEST table with the QUERY_SAMPLE_TEXT, QUERY_SAMPLE_SEEN and QUERY_SAMPLE_TIMER_WAIT columns.

F-3 The size of the EVENTS_STATEMENTS_SUMMARY_BY_DIGEST.QUERY_SAMPLE_TEXT column is defined by the system variable performance_schema_max_sql_text_length.

F-4 The EVENTS_STATEMENTS_SUMMARY_BY_DIGEST.QUERY_SAMPLE_TEXT field for a given digest is updated when the wait time of the corresponding query exceeds the previous QUERY_SAMPLE_TIMER_WAIT, or when the QUERY_SAMPLE_SEEN timestamp is older than performance_schema_max_digest_sample_age seconds.

F-5 The EVENTS_STATEMENTS_SUMMARY_BY_DIGEST.QUERY_SAMPLE_SEEN field for a given digest is updated when the QUERY_SAMPLE_TEXT field is updated.

F-6 The EVENTS_STATEMENTS_SUMMARY_BY_DIGEST.QUERY_SAMPLE_TIMER_WAIT field for a given digest is updated when the QUERY_SAMPLE_TEXT field is updated.

Existing tables are changed as follows:

performance_schema.events_summary_by_digest

CREATE TABLE `events_summary_by_digest` (
  `SCHEMA_NAME` varchar(64),
  `DIGEST` varchar(32),
  `DIGEST_TEXT` longtext,
  `COUNT_STAR` bigint unsigned NOT NULL,
  `SUM_TIMER_WAIT` bigint unsigned NOT NULL,
  `MIN_TIMER_WAIT` bigint unsigned NOT NULL,
  `AVG_TIMER_WAIT` bigint unsigned NOT NULL,
  `MAX_TIMER_WAIT` bigint unsigned NOT NULL,
  `SUM_LOCK_TIME` bigint unsigned NOT NULL,
  `SUM_ERRORS` bigint unsigned NOT NULL,
  `SUM_WARNINGS` bigint unsigned NOT NULL,
  `SUM_ROWS_AFFECTED` bigint unsigned NOT NULL,
  `SUM_ROWS_SENT` bigint unsigned NOT NULL,
  `SUM_ROWS_EXAMINED` bigint unsigned NOT NULL,
  `SUM_CREATED_TMP_DISK_TABLES` bigint unsigned NOT NULL,
  `SUM_CREATED_TMP_TABLES` bigint unsigned NOT NULL,
  `SUM_SELECT_FULL_JOIN` bigint unsigned NOT NULL,
  `SUM_SELECT_FULL_RANGE_JOIN` bigint unsigned NOT NULL,
  `SUM_SELECT_RANGE` bigint unsigned NOT NULL,
  `SUM_SELECT_RANGE_CHECK` bigint unsigned NOT NULL,
  `SUM_SELECT_SCAN` bigint unsigned NOT NULL,
  `SUM_SORT_MERGE_PASSES` bigint unsigned NOT NULL,
  `SUM_SORT_RANGE` bigint unsigned NOT NULL,
  `SUM_SORT_ROWS` bigint unsigned NOT NULL,
  `SUM_SORT_SCAN` bigint unsigned NOT NULL,
  `SUM_NO_INDEX_USED` bigint unsigned NOT NULL,
  `SUM_NO_GOOD_INDEX_USED` bigint unsigned NOT NULL,

  Modified columns:
  `FIRST_SEEN` timestamp(6) NOT NULL default 0,
  `LAST_SEEN` timestamp(6) NOT NULL default 0,

  `QUANTILE_95` bigint unsigned NOT NULL,
  `QUANTILE_99` bigint unsigned NOT NULL,
  `QUANTILE_999` bigint unsigned NOT NULL,

  New columns:
  `QUERY_SAMPLE_TEXT` longtext,
  `QUERY_SAMPLE_SEEN` timestamp(6) NOT NULL default 0,
  `QUERY_SAMPLE_TIMER_WAIT` bigint unsigned NOT NULL,
  
  `UNIQUE KEY (SCHEMA_NAME, DIGEST) USING HASH,
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

System variables are changed as follows:

system variable performance_schema_max_digest_sample_age

New system variable:

• name = performance_schema_max_digest_sample_age
• type = integer
• min value = 0
• max value = unbounded (max int)
• default value = 60
• global variable
• dynamic

The variable defines, in seconds, the time after which a previous query sample is considered old. When the value is 0, queries are not resampled due to age. When the value is greater than zero, queries are resampled if the last sample is more that performance_schema_max_digest_sample_age seconds old.

Query Sample Criteria

A query sample is taken when:

• It is the first query, or
• The wait time exceeds query_sample_timer_wait, or
• The QUERY_SAMPLE_SEEN timestamp of the previous query sample exceeds the maximum query age.

For other than the first query, the wait time is always examined. The age criteria forces a query sample refresh when the wait time remains below QUERY_SAMPLE_TIMER_WAIT.