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!
