Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Performance Schema Statement Event Tables

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.

These tables store statement events:

The following sections describe those tables. There are also summary tables that aggregate information about statement events; see Section, “Statement Summary Tables”.

Statement Event Configuration

To enable collection of statement events, enable the relevant instruments and consumers.

The setup_instruments table contains instruments with names that begin with statement. These instruments are enabled by default:

mysql> SELECT * FROM setup_instruments WHERE NAME LIKE 'statement/%';
| NAME                                        | ENABLED | TIMED |
| statement/sql/select                        | YES     | YES   |
| statement/sql/create_table                  | YES     | YES   |
| statement/sql/create_index                  | YES     | YES   |
| statement/sp/stmt                           | YES     | YES   |
| statement/sp/set                            | YES     | YES   |
| statement/sp/set_trigger_field              | YES     | YES   |
| statement/scheduler/event                   | YES     | YES   |
| statement/com/Sleep                         | YES     | YES   |
| statement/com/Quit                          | YES     | YES   |
| statement/com/Init DB                       | YES     | YES   |
| statement/abstract/Query                    | YES     | YES   |
| statement/abstract/new_packet               | YES     | YES   |
| statement/abstract/relay_log                | YES     | YES   |

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):

mysql> SELECT * FROM setup_consumers WHERE NAME LIKE '%statements%';
| NAME                           | ENABLED |
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | NO      |
| statements_digest              | YES     |

To enable all statement consumers, do this:

mysql> UPDATE setup_consumers SET ENABLED = 'YES'
    -> WHERE NAME LIKE '%statements%';

The setup_timers table contains a row with a NAME value of statement that indicates the unit for statement event timing. The default unit is NANOSECOND.

mysql> SELECT * FROM setup_timers WHERE NAME = 'statement';
| NAME      | TIMER_NAME |
| statement | NANOSECOND |

To change the timing unit, modify the TIMER_NAME value:

    -> WHERE NAME = 'statement';

For additional information about configuring event collection, see Section 21.2, “Performance Schema Configuration”.

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/ 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:

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

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

  3. 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:

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

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

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.