29.10 Performance Schema Statement Digests and Sampling (original) (raw)

The MySQL server is capable of maintaining statement digest information. 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. For each digest, a representative statement that produces the digest is stored as a sample. This section describes how statement digesting and sampling occur and how they can be useful.

Digesting occurs in the parser regardless of whether the Performance Schema is available, so that other features such as MySQL Enterprise Firewall and query rewrite plugins have access to statement digests.

Statement Digest General Concepts

When the parser receives an SQL statement, it computes a statement digest if that digest is needed, which is true if any of the following conditions are true:

The parser is also used by theSTATEMENT_DIGEST_TEXT() andSTATEMENT_DIGEST() functions, which applications can call to compute a normalized statement digest and a digest hash value, respectively, from an SQL statement.

The max_digest_length system variable value determines the maximum number of bytes available per session for computation of normalized statement digests. Once that amount of space is used during digest computation, truncation occurs: no further tokens from a parsed statement are collected or figure into its digest value. Statements that differ only after that many bytes of parsed tokens produce the same normalized statement digest and are considered identical if compared or if aggregated for digest statistics.

Warning

Setting the max_digest_length system variable to zero disables digest production, which also disables server functionality that requires digests.

After the normalized statement has been computed, a SHA-256 hash value is computed from it. In addition:

Statement normalization 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:

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 parser 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 data 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 normalized statements differ because the object identifiers differ:

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 (as determined bymax_digest_length), truncation occurs and the text ends with “...”. Long normalized statements that differ only in the part that occurs following the “...” are considered 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 happens to be right after theAND, both statements 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.

Statement Digests in the Performance Schema

In the Performance Schema, statement digesting involves these elements:

Some Performance Tables have a column that stores original SQL statements from which digests are computed:

The maximum space available for statement display is 1024 bytes by default. To change this value, set theperformance_schema_max_sql_text_length system variable at server startup. Changes affect the storage required for all the columns just named.

Theperformance_schema_max_digest_length system variable determines the maximum number of bytes available per statement for digest value storage in the Performance Schema. However, the display length of statement digests may be longer than the available buffer size due to internal encoding of statement elements such as keywords and literal values. Consequently, values selected from theDIGEST_TEXT column of statement event tables may appear to exceed theperformance_schema_max_digest_length value.

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

Theevents_statements_summary_by_digest summary table has a fixed size. By default the Performance Schema estimates the size to use at startup. To specify the table size explicitly, set theperformance_schema_digests_size system variable at server startup. If the table becomes full, the Performance Schema groups statements that haveSCHEMA_NAME and DIGEST values not matching existing values in the table in a special row with SCHEMA_NAME andDIGEST 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 summary table size by increasingperformance_schema_digests_size.

Statement Digest Memory Use

For applications that generate very long statements that differ only at the end, increasingmax_digest_length enables computation of digests that distinguish statements that would otherwise aggregate to the same digest. Conversely, decreasingmax_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 (the server allocatesmax_digest_length bytes per session).

As described previously, normalized statement digests as computed by the parser are constrained to a maximum ofmax_digest_length bytes, whereas normalized statement digests stored in the Performance Schema useperformance_schema_max_digest_length bytes. The following memory-use considerations apply regarding the relative values ofmax_digest_length andperformance_schema_max_digest_length:

Because the Performance Schema statement event tables might store many digests, settingperformance_schema_max_digest_length smaller than max_digest_length enables administrators to balance these factors:

Theperformance_schema_max_digest_length setting is not per session, it is per statement, and a session can store multiple statements in theevents_statements_history table. A typical number of statements in this table is 10 per session, so each session consumes 10 times the memory indicated by theperformance_schema_max_digest_length value, for this table alone.

Also, there are many statements (and digests) collected globally, most notably in theevents_statements_history_long table. Here, too, N statements stored consumes N times the memory indicated by theperformance_schema_max_digest_length value.

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

mysql> SELECT NAME
       FROM performance_schema.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 performance_schema.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      |
+----------------------------------------------------------------------+

Statement Sampling

The Performance Schema uses statement sampling to collect representative statements that produce each digest value in theevents_statements_summary_by_digest table. These columns store sample statement information:QUERY_SAMPLE_TEXT (the text of the statement), QUERY_SAMPLE_SEEN (when the statement was seen), andQUERY_SAMPLE_TIMER_WAIT (the statement wait or execution time). The Performance Schema updates all three columns each time it chooses a sample statement.

When a new table row is inserted, the statement that produced the row digest value is stored as the current sample statement associated with the digest. Thereafter, when the server sees other statements with the same digest value, it determines whether to use the new statement to replace the current sample statement (that is, whether to resample). Resampling policy is based on the comparative wait times of the current sample statement and new statement and, optionally, the age of the current sample statement:

By default,performance_schema_max_digest_sample_age is 60 seconds (1 minute). To change how quickly sample statements “expire” due to age, increase or decrease the value. To disable the age-based part of the resampling policy, setperformance_schema_max_digest_sample_age to 0.