The Performance Schema maintains tables for collecting current and recent statement events, and aggregates that information in summary tables. Section 22.12.6, “Performance Schema Statement Event Tables” describes the events on which statement summaries are based. See that discussion for information about the content of statement events, the current and historical statement event tables, and how to control statement event collection, which is partially disabled by default.
Example statement event summary information:
mysql> SELECT * FROM performance_schema.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 ...
Each statement summary table has one or more grouping columns
to indicate how the table aggregates events. Event names refer
to names of event instruments in the
HOSTcolumns. Each row summarizes events for a given account (user and host combination) and event name.
DIGESTcolumns. Each row summarizes events per schema and digest value. (The
DIGEST_TEXTcolumn contains the corresponding normalized statement digest text, but is neither a grouping nor a summary column.)
The maximum number of rows in the table is autosized at server startup. To set this maximum explicitly, set the
performance_schema_digests_sizesystem variable at server startup.
HOSTcolumns. Each row summarizes events for a given host and event name.
EVENT_NAMEcolumns. Each row summarizes events for a given thread and event name.
USERcolumns. Each row summarizes events for a given user and event name.
EVENT_NAMEcolumn. Each row summarizes events for a given event name.
Each statement summary table has these summary columns containing aggregated values:
These columns are analogous to the columns of the same names in the wait event summary tables (see Section 18.104.22.168, “Wait Event Summary Tables”), except that the statement summary tables aggregate events from
The aggregate of the corresponding
xxxcolumn in the
events_statements_currenttable. For example, the
SUM_ERRORScolumns in statement summary tables are the aggregates of the
table has these additional summary columns:
Timestamps indicating when statements with the given digest value were first seen and most recently seen.
TRUNCATE TABLE is permitted for
statement summary tables. It has these effects:
events_statements_summary_by_digest, it removes the rows.
For other summary tables not aggregated by account, host, or user, truncation resets the summary columns to zero rather than removing rows.
For other summary tables aggregated by account, host, or user, truncation removes rows for accounts, hosts, or users with no connections, and resets the summary columns to zero for the remaining rows.
In addition, each statement summary table that is aggregated
by account, host, user, or thread is implicitly truncated by
truncation of the connection table on which it depends, or
For details, see
Section 22.12.7, “Performance Schema Connection Tables”.
statements_digest consumer is
enabled, aggregation into
occurs as follows when a statement completes. Aggregation is
based on the
DIGEST value computed for
events_statements_summary_by_digestrow already exists with the digest value for the statement that just completed, statistics for the statement are aggregated to that row. The
LAST_SEENcolumn 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
LAST_SEENcolumns 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
NULL, which is created if necessary. If the row is created, the
LAST_SEENcolumns are initialized with the current time. Otherwise, the
LAST_SEENcolumn is updated with the current time.
The row with
NULL is maintained because Performance
Schema tables have a maximum size due to memory constraints.
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:
NULLrow that has a
COUNT_STARvalue that represents 5% of all digests shows that the digest summary table is very representative; the other rows cover 95% of the statements seen.
NULLrow that has a
COUNT_STARvalue 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
NULLrow would be counted using more specific rows instead. To do this, set the
performance_schema_digests_sizesystem variable to a larger value at server startup. The default size is 200.