MySQL Performance Schema to identify SQL Statements which the execution duration exceeds average timing by 30%

 

Statement Digest in MySQL 

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-digests.html

The parser is also used by the STATEMENT_DIGEST_TEXT() and STATEMENT_DIGEST() functions, which applications can call to compute a normalized statement digest and a digest hash value, respectively, from an SQL statement.

events_statement_summary_by_digest in Performance Schema contains information

+-----------------------------+-----------------+------+-----+---------+-------+

| Field                       | Type            | Null | Key | Default | Extra |

+-----------------------------+-----------------+------+-----+---------+-------+

| SCHEMA_NAME                 | varchar(64)     | YES  | MUL | NULL    |       |

| DIGEST                      | varchar(64)     | YES  |     | NULL    |       |

| DIGEST_TEXT                 | longtext        | YES  |     | NULL    |       |

| COUNT_STAR                  | bigint unsigned | NO   |     | NULL    |       |

| SUM_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| MIN_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| AVG_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| MAX_TIMER_WAIT              | bigint unsigned | NO   |     | NULL    |       |

| SUM_LOCK_TIME               | bigint unsigned | NO   |     | NULL    |       |

| SUM_ERRORS                  | bigint unsigned | NO   |     | NULL    |       |

| SUM_WARNINGS                | bigint unsigned | NO   |     | NULL    |       |

| SUM_ROWS_AFFECTED           | bigint unsigned | NO   |     | NULL    |       |

| SUM_ROWS_SENT               | bigint unsigned | NO   |     | NULL    |       |

| SUM_ROWS_EXAMINED           | bigint unsigned | NO   |     | NULL    |       |

| SUM_CREATED_TMP_DISK_TABLES | bigint unsigned | NO   |     | NULL    |       |

| SUM_CREATED_TMP_TABLES      | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_FULL_JOIN        | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_FULL_RANGE_JOIN  | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_RANGE            | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_RANGE_CHECK      | bigint unsigned | NO   |     | NULL    |       |

| SUM_SELECT_SCAN             | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_MERGE_PASSES       | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_RANGE              | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_ROWS               | bigint unsigned | NO   |     | NULL    |       |

| SUM_SORT_SCAN               | bigint unsigned | NO   |     | NULL    |       |

| SUM_NO_INDEX_USED           | bigint unsigned | NO   |     | NULL    |       |

| SUM_NO_GOOD_INDEX_USED      | bigint unsigned | NO   |     | NULL    |       |

| SUM_CPU_TIME                | bigint unsigned | NO   |     | NULL    |       |

| COUNT_SECONDARY             | bigint unsigned | NO   |     | NULL    |       |

| FIRST_SEEN                  | timestamp(6)    | NO   |     | NULL    |       |

| LAST_SEEN                   | timestamp(6)    | NO   |     | NULL    |       |

| QUANTILE_95                 | bigint unsigned | NO   |     | NULL    |       |

| QUANTILE_99                 | bigint unsigned | NO   |     | NULL    |       |

| QUANTILE_999                | bigint unsigned | NO   |     | NULL    |       |

| QUERY_SAMPLE_TEXT           | longtext        | YES  |     | NULL    |       |

| QUERY_SAMPLE_SEEN           | timestamp(6)    | NO   |     | NULL    |       |

| QUERY_SAMPLE_TIMER_WAIT     | bigint unsigned | NO   |     | NULL    |       |

+-----------------------------+-----------------+------+-----+---------+-------+

The events_statements_history_long[events_statement_current / events_statement_history] contains information about a SQL statement and Digest together with the execution time.  With referencing the events_statement_history_long, we can track the execution timing compared to the average execution time from table "events_statements_summary_by_digest".

1. To enable 'performance_schema" to collect events_statements_history_long, we need to enable the consumer (for the *history_long).  The default value is OFF which means the events_statements_history_long is empty without any data.   Enabling the events_statements_history_long allows more SQL statements to be captured and compared with the average execution time.

mysql > update setup_consumers set enabled='YES' where name = 'events_statements_history_long';

2. Retrieving the list of SQL Statements where the digest is the same and the execution time is having 30% over the average timing.

mysql > select a.sql_text,  a.digest, (a.timer_end - a.timer_start) duration, b.avg_timer_wait 

          > from events_statements_history_long a, events_statements_summary_by_digest b 

          > where a.digest = b.digest and b.avg_timer_wait > (a.timer_end - a.timer_start) *1.3

Performance schema / sys schema contains valuable information which we can use them to track / tune our system.  It can be the SQL.  It can be storage or files.  

You can share more with others about how you can reuse the performance_schema tables to improve your application.

Enjoy reading!