21.9.6 Performance Schema Statement Event Tables

The Performance Schema instruments statement execution.
Statement events occur at a high level of the event hierarchy:
Wait events nest within stage events, which nest within
statement events, which nest within transaction events.

To modify collection of statement events, change the
ENABLED and TIMING columns
of the relevant instruments. For example:

mysql> UPDATE setup_instruments SET ENABLED = 'NO'
-> WHERE NAME LIKE 'statement/com/%';

The setup_consumers table contains
consumer values with names corresponding to the current and
recent statement event table names, and the statement digest
consumer. These consumers may be used to filter collection of
statement events and statement digesting.
events_statements_current,
events_statements_history, and
statements_digest are enabled by default
(before MySQL 5.7.5,
events_statements_history is disabled by
default):

Statement Monitoring

Statement monitoring begins from the moment the server sees that
activity is requested on a thread, to the moment when all
activity has ceased. Typically, this means from the time the
server gets the first packet from the client to the time the
server has finished sending the response. Before MySQL 5.7.2,
monitoring occurs only for top-level statements. Statements
within stored programs and subqueries are not seen separately.
As of 5.7.2, statements within stored programs are monitored
like other statements.

When the Performance Schema instruments a request (server
command or SQL statement), it uses instrument names that proceed
in stages from more general (or “abstract”) to more
specific until it arrives at a final instrument name.

Final instrument names correspond to server commands and SQL
statements:

Server commands correspond to the
COM_xxx codes
defined in the mysql_com.h header file
and processed in sql/sql_parse.cc.
Examples are COM_PING and
COM_QUIT. Instruments for commands have
names that begin with statement/com, such
as statement/com/Ping and
statement/com/Quit.

SQL statements are expressed as text, such as
DELETE FROM t1 or SELECT * FROM
t2. Instruments for SQL statements have names that
begin with statement/sql, such as
statement/sql/delete and
statement/sql/select.

Some final instrument names are specific to error handling:

statement/com/Error accounts for messages
received by the server that are out of band. It can be used
to detect commands sent by clients that the server does not
understand. This may be helpful for purposes such as
identifying clients that are misconfigured or using a
version of MySQL more recent than that of the server, or
clients that are attempting to attack the server.

statement/sql/error accounts for SQL
statements that fail to parse. It can be used to detect
malformed queries sent by clients. A query that fails to
parse differs from a query that parses but fails due to an
error during execution. For example, SELECT *
FROM is malformed, and the
statement/sql/error instrument is used.
By contrast, SELECT * parses but fails
with a No tables used error. In this
case, statement/sql/select is used and
the statement event contains information to indicate the
nature of the error.

A request can be obtained from any of these sources:

As a command or statement request from a client, which sends
the request as packets

As a statement string read from the relay log on a
replication slave (as of MySQL 5.7.2)

As an event from the Event Scheduler (as of MySQL 5.7.2)

The details for a request are not initially known and the
Performance Schema proceeds from abstract to specific instrument
names in a sequence that depends on the source of the request.

For a request received from a client:

When the server detects a new packet at the socket level, a
new statement is started with an abstract instrument name of
statement/abstract/new_packet.

When the server reads the packet number, it knows more about
the type of request received, and the Performance Schema
refines the instrument name. For example, if the request is
a COM_PING packet, the instrument name
becomes statement/com/Ping and that is
the final name. If the request is a
COM_QUERY packet, it is known to
correspond to a SQL statement but not the particular type of
statement. In this case, the instrument changes from one
abstract name to a more specific but still abstract name,
statement/abstract/Query, and the request
requires further classification.

If the request is a statement, the statement text is read
and given to the parser. After parsing, the exact statement
type is known. If the request is, for example, an
INSERT statement, the
Performance Schema refines the instrument name from
statement/abstract/Query to
statement/sql/insert, which is the final
name.

For a request read as a statement from the relay log on a
replication slave:

Statements in the relay log are stored as text and are read
as such. There is no network protocol, so the
statement/abstract/new_packet instrument
is not used. Instead, the initial instrument is
statement/abstract/relay_log.

When the statement is parsed, the exact statement type is
known. If the request is, for example, an
INSERT statement, the
Performance Schema refines the instrument name from
statement/abstract/Query to
statement/sql/insert, which is the final
name.

The preceding description applies only for statement-based
replication. For row-based replication, table I/O done on the
slave as it processes row changes can be instrumented, but row
events in the relay log do not appear as discrete statements.

For a request received from the Event Scheduler:

The event execution is instrumented using the name
statement/scheduler/event. This is the final
name.

Statements executed within the event body are instrumented using
statement/sql/* names, without use of any
preceding abstract instrument. An event is a stored program, and
stored programs are precompiled in memory before execution.
Consequently, there is no parsing at runtime and the type of
each statement is known by the time it executes.

Statements executed within the event body are child statements.
For example, if an event executes an
INSERT statement, execution of
the event itself is the parent, instrumented using
statement/scheduler/event, and the
INSERT is the child, instrumented
using statement/sql/insert. The parent/child
relationship holds between separate
instrumented operations. This differs from the sequence of
refinement that occurs within a single
instrumented operation, from abstract to final instrument names.

For statistics to be collected for statements, it is not
sufficient to enable only the final
statement/sql/* instruments used for
individual statement types. The abtract
statement/abstract/* instruments must be
enabled as well. This should not normally be an issue because
all statement instruments are enabled by default. However, an
application that enables or disables statement instruments
selectively must take into account that disabling abstract
instruments also disables statistics collection for the
individual statement instruments. For example, to collect
statistics for INSERT statements,
statement/sql/insert must be enabled, but
also statement/abstract/new_packet and
statement/abstract/Query. Similarly, for
replicated statements to be instrumented,
statement/abstract/relay_log must be enabled.

No statistics are aggregated for abstract instruments such as
statement/abstract/Query because no statement
is ever classified with an abstract instrument as the final
statement name.

The abstract instrument names in the preceding discussion are as
of MySQL 5.7.3. In earlier 5.7 versions, there was some renaming
before those names were settled on:

statement/abstract/new_packet was
statement/com/ before MySQL 5.7.3.

statement/abstract/Query was
statement/com/Query before MySQL 5.7.3.

statement/abstract/relay_log was
statement/rpl/relay_log in MySQL 5.7.2
and did not exist before that.