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.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

3 Comments

  1. […] events_statements_summary_by_digestというperformance schemaテーブルに対して、クエリサンプルの完全なクエリ例と重要な情報を得られるよう、変更が行われました。ユーザーが実際のクエリにEXPLAINを実行して実行計画を得られるようにするため、クエリサンプルを格納するQUERY_SAMPLE_TEXT列が追加されました。QUERY_SAMPLE_SEEN列が、クエリサンプルのタイムスタンプを確認するために追加されました。QUERY_SAMPLE_TIMER_WAIT列が、クエリサンプルの実行所要時間を確認するために追加されました。FIRST_SEEN列とLAST_SEEN列が、秒の小数部分を表示するよう変更されました。こちらのFrederic Descampsのブログ記事をご覧ください。 […]

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.