MySQL 8.0 : Digest Query Samples in Performance_Schema

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 events_statements_summary_by_digest. 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.

FIRST_SEEN and 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: 

Now it’s

  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.

Edit:

I forgot something important that the famous daddy of SYS and Performance_Schema expert reported to me: performance_schema_max_digest_sample_age

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 wait_time.