MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
The Performance Schema maintains tables for collecting current and recent statement events, and aggregates that information in summary tables. Section 29.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: 54 SUM_TIMER_WAIT: 38860400000 MIN_TIMER_WAIT: 52400000 AVG_TIMER_WAIT: 719600000 MAX_TIMER_WAIT: 12631800000 SUM_LOCK_TIME: 88000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 60 SUM_ROWS_EXAMINED: 120 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 21 SUM_SELECT_FULL_JOIN: 16 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 41 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 22 SUM_NO_GOOD_INDEX_USED: 0 SUM_CPU_TIME: 0 MAX_CONTROLLED_MEMORY: 2028360 MAX_TOTAL_MEMORY: 2853429 COUNT_SECONDARY: 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
setup_instruments
table:
events_statements_summary_by_account_by_event_name
has EVENT_NAME
,
USER
, and HOST
columns. Each row summarizes events for a given account
(user and host combination) and event name.
events_statements_summary_by_digest
has SCHEMA_NAME
and
DIGEST
columns. Each row summarizes
events per schema and digest value. (The
DIGEST_TEXT
column contains the
corresponding normalized statement digest text, but is
neither a grouping nor a summary column. The
QUERY_SAMPLE_TEXT
,
QUERY_SAMPLE_SEEN
, and
QUERY_SAMPLE_TIMER_WAIT
columns also
are neither grouping nor summary columns; they support
statement sampling.)
The maximum number of rows in the table is autosized at
server startup. To set this maximum explicitly, set the
performance_schema_digests_size
system variable at server startup.
events_statements_summary_by_host_by_event_name
has EVENT_NAME
and
HOST
columns. Each row summarizes
events for a given host and event name.
events_statements_summary_by_program
has OBJECT_TYPE
,
OBJECT_SCHEMA
, and
OBJECT_NAME
columns. Each row
summarizes events for a given stored program (stored
procedure or function, trigger, or event).
events_statements_summary_by_thread_by_event_name
has THREAD_ID
and
EVENT_NAME
columns. Each row summarizes
events for a given thread and event name.
events_statements_summary_by_user_by_event_name
has EVENT_NAME
and
USER
columns. Each row summarizes
events for a given user and event name.
events_statements_summary_global_by_event_name
has an EVENT_NAME
column. Each row
summarizes events for a given event name.
prepared_statements_instances
has an OBJECT_INSTANCE_BEGIN
column.
Each row summarizes events for a given prepared statement.
Each statement summary table has these summary columns containing aggregated values (with exceptions as noted):
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 wait event summary tables (see
Section 29.12.20.1, “Wait Event Summary Tables”),
except that the statement summary tables aggregate events
from
events_statements_current
rather than
events_waits_current
.
The
prepared_statements_instances
table does not have these columns.
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.
MAX_CONTROLLED_MEMORY
Reports the maximum amount of controlled memory used by a statement during execution.
This column was added in MySQL 8.0.31.
MAX_TOTAL_MEMORY
Reports the maximum amount of memory used by a statement during execution.
This column was added in MySQL 8.0.31.
COUNT_SECONDARY
The number of times a query was processed on the
SECONDARY
engine. For use with HeatWave Service
and HeatWave, where the PRIMARY
engine
is InnoDB
and the
SECONDARY
engine is HeatWave
(RAPID
). For MySQL Community Edition Server, MySQL Enterprise Edition Server
(on-premise), and HeatWave Service without HeatWave, queries are
always processed on the PRIMARY
engine,
which means the value is always 0 on these MySQL Servers.
The COUNT_SECONDARY
column was added in
MySQL 8.0.29.
The
events_statements_summary_by_digest
table has these additional summary columns:
FIRST_SEEN
,
LAST_SEEN
Timestamps indicating when statements with the given digest value were first seen and most recently seen.
QUANTILE_95
: The 95th percentile of the
statement latency, in picoseconds. This percentile is a
high estimate, computed from the histogram data collected.
In other words, for a given digest, 95% of the statements
measured have a latency lower than
QUANTILE_95
.
For access to the histogram data, use the tables described in Section 29.12.20.4, “Statement Histogram Summary Tables”.
QUANTILE_99
: Similar to
QUANTILE_95
, but for the 99th
percentile.
QUANTILE_999
: Similar to
QUANTILE_95
, but for the 99.9th
percentile.
The
events_statements_summary_by_digest
table contains the following columns. These are neither
grouping nor summary columns; they support statement sampling:
QUERY_SAMPLE_TEXT
A sample SQL statement that produces the digest value in
the row. This column enables applications to access, for a
given digest value, a statement actually seen by the
server that produces that digest. One use for this might
be to run EXPLAIN
on the
statement to examine the execution plan for a
representative statement associated with a frequently
occurring digest.
When the QUERY_SAMPLE_TEXT
column is
assigned a value, the QUERY_SAMPLE_SEEN
and QUERY_SAMPLE_TIMER_WAIT
columns are
assigned values as well.
The maximum space available for statement display is 1024
bytes by default. To change this value, set the
performance_schema_max_sql_text_length
system variable at server startup. (Changing this value
affects columns in other Performance Schema tables as
well. See
Section 29.10, “Performance Schema Statement Digests and Sampling”.)
For information about statement sampling, see Section 29.10, “Performance Schema Statement Digests and Sampling”.
QUERY_SAMPLE_SEEN
A timestamp indicating when the statement in the
QUERY_SAMPLE_TEXT
column was seen.
QUERY_SAMPLE_TIMER_WAIT
The wait time for the sample statement in the
QUERY_SAMPLE_TEXT
column.
The
events_statements_summary_by_program
table has these additional summary columns:
COUNT_STATEMENTS
,
SUM_STATEMENTS_WAIT
,
MIN_STATEMENTS_WAIT
,
AVG_STATEMENTS_WAIT
,
MAX_STATEMENTS_WAIT
Statistics about nested statements invoked during stored program execution.
The prepared_statements_instances
table has these additional summary columns:
COUNT_EXECUTE
,
SUM_TIMER_EXECUTE
,
MIN_TIMER_EXECUTE
,
AVG_TIMER_EXECUTE
,
MAX_TIMER_EXECUTE
Aggregated statistics for executions of the prepared statement.
The statement summary tables have these indexes:
events_transactions_summary_by_account_by_event_name
:
Primary key on (USER
,
HOST
,
EVENT_NAME
)
events_statements_summary_by_digest
:
Primary key on (SCHEMA_NAME
,
DIGEST
)
events_transactions_summary_by_host_by_event_name
:
Primary key on (HOST
,
EVENT_NAME
)
events_statements_summary_by_program
:
Primary key on (OBJECT_TYPE
,
OBJECT_SCHEMA
,
OBJECT_NAME
)
events_statements_summary_by_thread_by_event_name
:
Primary key on (THREAD_ID
,
EVENT_NAME
)
events_transactions_summary_by_user_by_event_name
:
Primary key on (USER
,
EVENT_NAME
)
events_statements_summary_global_by_event_name
:
Primary key on (EVENT_NAME
)
TRUNCATE TABLE
is permitted for
statement summary tables. It has these effects:
For
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
truncation of
events_statements_summary_global_by_event_name
.
For details, see
Section 29.12.8, “Performance Schema Connection Tables”.
In addition, truncating
events_statements_summary_by_digest
implicitly truncates
events_statements_histogram_by_digest
,
and truncating
events_statements_summary_global_by_event_name
implicitly truncates
events_statements_histogram_global
.
If the statements_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. By default, the table is
autosized, but if this size is too small, set the
performance_schema_digests_size
system variable to a larger value at server startup.
For stored program types for which instrumentation is
enabled in the setup_objects
table,
events_statements_summary_by_program
maintains statistics for stored programs as follows:
A row is added for an object when it is first used in the server.
The row for an object is removed when the object is dropped.
Statistics are aggregated in the row for an object as it executes.
See also Section 29.4.3, “Event Pre-Filtering”.