Today, yet another blog post about improvements in MySQL 8.0 related to
Performance_Schema. Before MySQL 8.0 it was not always easy to get an example of the queries you could find in
Performance_Schema when looking for statements summaries. You had to link several tables (even from
sys) to achieve this goal as I explained it in this post.
Now in MySQL 8.0, we have changed the table
<a href="https://dev.mysql.com/doc/refman/8.0/en/statement-summary-tables.html">events_statements_summary_by_digest</a>. This table now contains 6 extra columns:
QUANTILE_95: stores the 95th percentile of the statement latency, in picoseconds.
QUANTILE_99: stores the 99th percentile of the statement latency, in picoseconds.
QUANTILE_999: stores the 99.9th percentile of the statement latency, in picoseconds.
QUERY_SAMPLE_TEXT: captures a query sample that can be used with EXPLAIN to get a query plan.
QUERY_SAMPLE_SEEN: stores the timestamp of the query.
QUERY_SAMPLE_TIMER_WAIT: stores the query sample execution time.
LAST_SEEN have also been modified to use fractional seconds. The previous definition was:
Field: LAST_SEEN Type: timestamp Null: NO Key: Default: 0000-00-00 00:00:00 Extra:
Field: LAST_SEEN Type: timestamp(6) Null: NO Key: Default: 0000-00-00 00:00:00.000000 Extra:
The main goal is to capture a full example query like it was made in production with some key information about this query example and to make it easily accessible.
Indeed, you can control the sampling for the queries in
events_statements_summary_by_digest table. By default if the sample statement is for the same query digest is older than 60 seconds (default value of
performance_schema_max_digest_sample_age) then it gets replaced by the new statement. Even if the new statement wait time is less than the one already in the table. If the old statement is not too old, it gets replaced only if the new statement as a greater