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 :

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

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

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 😀