Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  MySQL Performance Schema  /  Performance Schema Statement Digests

21.7 Performance Schema Statement Digests

The MySQL server is capable of maintaining statement digest information. The digesting process converts a SQL statement to normalized form and computes a hash value for the 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. This section describes how statement normalizing occurs and how it can be useful.


Before MySQL 5.7.4, statement digesting was a function of the Performance Schema. As of 5.7.4, digesting occurs at the SQL level regardless of whether the Performance Schema is available, so that other server functions such as query rewrite plugins have access to statement digests.

In the Performance Schema, statement digesting involves these components:

  • A statement_digest consumer in the setup_consumers table controls whether the Performance Schema maintains digest information.

  • The statement event tables (events_statements_current, events_statements_history, and events_statements_history_long) have columns that contain digests and the corresponding digest hash values:

    • DIGEST_TEXT is the text of the normalized statement digest.

    • DIGEST is the digest MD5 hash value.

    The maximum space available for digest computation is 1024 bytes by default. As of MySQL 5.7.8, this value can be changed at server startup by setting the performance_schema_max_digest_length system variable. In MySQL 5.7.6 and 5.7.7, use max_digest_length instead. Before 5.7.6, the value cannot be changed.

  • The statement event tables also have a SQL_TEXT column that contains the original SQL statement. The maximum space available for statement display is 1024 bytes by default. As of MySQL 5.7.6, this value can be changed at server startup by setting the performance_schema_max_sql_text_length system variable. Before 5.7.6, the value cannot be changed.

  • An events_statements_summary_by_digest table provides aggregated statement digest information.

Normalizing a statement transforms the statement text to a more standardized digest string representation that preserves the general statement structure while removing information not essential to the structure:

  • Object identifiers such as database and table names are preserved.

  • Literal values are converted to parameter markers. A normalized statement does not retain information such as names, passwords, dates, and so forth.

  • Comments are removed and whitespace is adjusted.

Consider these statements:

SELECT * FROM orders WHERE customer_id=10 AND quantity>20
SELECT * FROM orders WHERE customer_id = 20 AND quantity > 100

To normalize these statements, the Performance Schema replaces data values by ? and adjusts whitespace. Both statements yield the same normalized form and thus are considered the same:

SELECT * FROM orders WHERE customer_id = ? AND quantity > ?

The normalized statement contains less information but is still representative of the original statement. Other similar statements that have different comparison values have the same normalized form.

Now consider these statements:

SELECT * FROM customers WHERE customer_id = 1000
SELECT * FROM orders WHERE customer_id = 1000

In this case, the statements are not the same. The object identifiers differ, so the statements yield different normalized forms:

SELECT * FROM customers WHERE customer_id = ?
SELECT * FROM orders WHERE customer_id = ?

If normalization produces a statement that exceeds the space available in the digest buffer, the text ends with .... Long statements that differ only in the part that occurs following the ... are considered to be the same. Consider these statements:

SELECT * FROM mytable WHERE cola = 10 AND colb = 20
SELECT * FROM mytable WHERE cola = 10 AND colc = 20

If the cutoff happened to be right after the AND, both statements would have this normalized form:

SELECT * FROM mytable WHERE cola = ? AND ...

In this case, the difference in the second column name is lost and both statements are considered the same.

For each normalized statement, the Performance Schema computes a hash digest value and stores the statement and its MD5 hash value in the DIGEST_TEXT and DIGEST columns of the statement event tables (events_statements_current, events_statements_history, and events_statements_history_long). In addition, information for statements with the same SCHEMA_NAME and DIGEST values are aggregated in the events_statements_summary_by_digest summary table. The Performance Schema uses MD5 hash values because they are fast to compute and have a favorable statistical distribution that minimizes collisions.

The statement digest summary table provides a profile of the statements executed by the server. It shows what kinds of statements an application is executing and how often. An application developer can use this information together with other information in the table to assess the application's performance characteristics. For example, table columns that show wait times, lock times, or index use may highlight types of queries that are inefficient. This gives the developer insight into which parts of the application need attention.

The events_statements_summary_by_digest summary table has a fixed size, so when it becomes full, statements that have SCHEMA_NAME and DIGEST values not matching existing values in the table are grouped in a special row with SCHEMA_NAME and DIGEST set to NULL. This permits all statements to be counted. However, if the special row accounts for a significant percentage of the statements executed, it might be desirable to increase the size of the summary table by setting the performance_schema_digests_size system variable to a larger value at server startup. If no performance_schema_digests_size value is given, the Performance Schema estimates the value to use at startup.

The performance_schema_max_digest_length system variable determines the maximum number of bytes available in the digest buffer for digest computation. However, the display length of statement digests may be longer than the available buffer size due to encoding of statement components such as keywords and literal values in digest buffer. Consequently, values selected from the DIGEST_TEXT column of statement event tables may appear to exceed the performance_schema_max_digest_length value.

For applications that generate very long statements that differ only at the end, increasing performance_schema_max_digest_length enables computation of digests that distinguish statements that would otherwise aggregate to the same digest. Conversely, decreasing performance_schema_max_digest_length causes the server to devote less memory to digest storage but increases the likelihood of longer statements aggregating to the same digest. Administrators should keep in mind that larger values result in correspondingly increased memory requirements, particularly for workloads that involve large numbers of simultaneous sessions (performance_schema_max_digest_length bytes are allocated per session).

To assess the amount of memory used for SQL statement storage and digest computation, use the SHOW ENGINE PERFORMANCE_SCHEMA STATUS statement, or monitor these instruments:

mysql> SELECT NAME FROM setup_instruments
    -> WHERE NAME LIKE '%.sqltext';
| NAME                                                             |
| memory/performance_schema/events_statements_history.sqltext      |
| memory/performance_schema/events_statements_current.sqltext      |
| memory/performance_schema/events_statements_history_long.sqltext |

mysql> SELECT NAME FROM setup_instruments
    -> WHERE NAME LIKE 'memory/performance_schema/%.tokens';
| NAME                                                                 |
| memory/performance_schema/events_statements_history.tokens           |
| memory/performance_schema/events_statements_current.tokens           |
| memory/performance_schema/events_statements_summary_by_digest.tokens |
| memory/performance_schema/events_statements_history_long.tokens      |

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.