MySQL statement level tracing

We already have seen how to gather overall MySQL server diagnostics information and how to trace a specific thread activity. Let’s go to more granular level this time ! What about tracing a specific statement. Has MySQL a built-in way to do that ?

The “ps_trace_statement_digest” introduced in MySQL 5.7 allow tracing a specific statement digest (As explained in the doc : The digesting process converts each SQL statement to normalized form (the statement digest) and computes a SHA-256 hash value (the digest hash value) from the normalized result.Normalization permits statements that are similar to be grouped and summarized to expose information about the types of statements the server is executing and how often they occur. )

Ok let’s give it a try :

Looking for which statement digest to trace :

Capture 01

Start tracing : Monitor the specified statement digest for 10 seconds and pool the event tables every  0.1 second to captures statistics

Capture 02

Capture 03

It will also attempt to generate an EXPLAIN for the longest running statement :

Capture 04

As indicated the EXPLAIN may fail, as:

  • Performance Schema truncates long SQL_TEXT values (and hence the EXPLAIN will fail due to parse errors)
  • The default schema is sys (so tables that are not fully qualified in the query may not be found)
  • Some queries such as SHOW are not supported in EXPLAIN.

That’s it 😀

One thought on “MySQL statement level tracing

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s