MySQL 5.6 Reference Manual :: 21 MySQL Performance Schema :: 21.9 Performance Schema Table Descriptions :: 21.9.8 Performance Schema Summary Tables :: 21.9.8.3 Statement Summary Tables

Section Navigation [Toggle]

21.9.8.3. Statement Summary Tables

The statement summary tables aggregate information about statement events:

events_statements_summary_by_digest: Statement events summarized per schema and digest value
events_statements_summary_by_thread_by_event_name: Statement events summarized per thread and event name
events_statements_summary_global_by_event_name: Statement events summarized per event name

For example:

mysql> SELECT * FROM events_statements_summary_global_by_event_name\G
*************************** 1. row ***************************
                 EVENT_NAME: statement/sql/select
                 COUNT_STAR: 25
             SUM_TIMER_WAIT: 1535983999000
             MIN_TIMER_WAIT: 209823000
             AVG_TIMER_WAIT: 61439359000
             MAX_TIMER_WAIT: 1363397650000
              SUM_LOCK_TIME: 20186000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 388
          SUM_ROWS_EXAMINED: 370
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 6
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 6
     SUM_NO_GOOD_INDEX_USED: 0
...

TRUNCATE TABLE is permitted for statement summary tables. For events_statements_summary_by_digest, it empties the table. For the other statement summary tables, it resets the counters to zero rather than removing rows.

The statement summary tables have these grouping columns to indicate how events are aggregated:

events_statements_summary_by_digest has SCHEMA_NAME and DIGEST column. Each row summarizes events for given schema/digest values. (The DIGEST_TEXT column contains the corresponding normalized statement digest text, but is neither a grouping nor summary column.)
Before MySQL 5.6.9, there is no SCHEMA_NAME column and grouping is based on DIGEST values only.
events_statements_summary_global_by_event_name has an EVENT_NAME column. Each row summarizes events for a given instrument.
events_statements_summary_by_thread_by_event_name has THREAD_ID and EVENT_NAME columns. Each row summarizes events for a given thread instrument instance.

The statement summary tables have these summary columns containing aggregated values:

COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT
These columns are analogous to the columns of the same names in the events_waits_summary_global_by_event_name and events_waits_summary_by_thread_by_event_name tables, except that the statement summary tables aggregate waits from events_statements_current rather than events_waits_current.
SUM_xxx
The aggregate of the corresponding xxx column in the events_statements_current table. For example, the SUM_LOCK_TIME and SUM_ERRORS columns in statement summary tables are the aggregates of the LOCK_TIME and ERRORS columns in events_statements_current table.

The events_statements_summary_by_digest table has these additional summary columns:

FIRST_SEEN_TIMESTAMP, LAST_SEEN_TIMESTAMP
The times at which a statement with the given digest value were first seen and most recently seen.

These tables were added in MySQL 5.6.3, except that events_statements_summary_by_digest was added in 5.6.5.

Statement Digest Aggregation Rules

If the statement_digest consumer is enabled, aggregation into events_statements_summary_by_digest occurs as follows when a statement completes. Aggregation is based on the DIGEST value computed for the statement.

If a events_statements_summary_by_digest row already exists with the digest value for the statement that just completed, statistics for the statement are aggregated to that row. The LAST_SEEN column is updated to the current time.
If no row has the digest value for the statement that just completed, and the table is not full, a new row is created for the statement. The FIRST_SEEN and LAST_SEEN columns are initialized with the current time.
If no row has the statement digest value for the statement that just completed, and the table is full, the statistics for the statement that just completed are added to a special “catch-all” row with DIGEST = NULL, which is created if necessary. If the row is created, the FIRST_SEEN and LAST_SEEN columns are initialized with the current time. Otherwise, the LAST_SEEN column is updated with the current time.

The row with DIGEST = NULL is maintained because Performance Schema tables have a maximum size due to memory constraints. The DIGEST = NULL row permits digests that do not match other rows to be counted even if the summary table is full, using a common “other” bucket. This row helps you estimate whether the digest summary is representative:

A DIGEST = NULL row that has a COUNT_STAR value that represents 5% of all digests shows that the digest summary table is very representative; the other rows cover 95% of the statements seen.
A DIGEST = NULL row that has a COUNT_STAR value that represents 50% of all digests shows that the digest summary table is not very representative; the other rows cover only half the statements seen. Most likely the DBA should increase the maximum table size so that more of the rows counted in the DIGEST = NULL row would be counted using more specific rows instead. To do this, set the performance_schema_digests_size system variable to a larger value at server startup. The default size is 200.

Previous / Next / Up / Table of Contents

User Comments

Add your own comment.

Top / Previous / Next / Up / Table of Contents