25.12 Performance Schema Table Descriptions

Tables in the 'performance_schema' database can be grouped as follows:

 File: manual.info.tmp, Node: performance-schema-table-reference, Next: performance-schema-setup-tables, Prev: performance-schema-table-descriptions, Up: performance-schema-table-descriptions

25.12.1 Performance Schema Table Reference

The following table summarizes all available Performance Schema tables. For greater detail, see the individual table descriptions.

Performance Schema Tables

Table Name Description Deprecated

*note 'accounts': performance-schema-accounts-table.

Connection statistics per client account

*note 'cond_instances': performance-schema-cond-instances-table.

Synchronization object instances

*note 'events_stages_current': performance-schema-events-stages-current-table.

Current stage events

*note 'events_stages_history': performance-schema-events-stages-history-table.

Most recent stage events per thread

*note 'events_stages_history_long': performance-schema-events-stages-history-long-table.

Most recent stage events overall

*note 'events_stages_summary_by_account_by_event_name': performance-schema-stage-summary-tables.

Stage events per account and event name

*note 'events_stages_summary_by_host_by_event_name': performance-schema-stage-summary-tables.

Stage events per host name and event name

*note 'events_stages_summary_by_thread_by_event_name': performance-schema-stage-summary-tables.

Stage waits per thread and event name

*note 'events_stages_summary_by_user_by_event_name': performance-schema-stage-summary-tables.

Stage events per user name and event name

*note 'events_stages_summary_global_by_event_name': performance-schema-stage-summary-tables.

Stage waits per event name

*note 'events_statements_current': performance-schema-events-statements-current-table.

Current statement events

*note 'events_statements_history': performance-schema-events-statements-history-table.

Most recent statement events per thread

*note 'events_statements_history_long': performance-schema-events-statements-history-long-table.

Most recent statement events overall

*note 'events_statements_summary_by_account_by_event_name': performance-schema-statement-summary-tables.

Statement events per account and event name

*note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables.

Statement events per schema and digest value

*note 'events_statements_summary_by_host_by_event_name': performance-schema-statement-summary-tables.

Statement events per host name and event name

*note 'events_statements_summary_by_program': performance-schema-statement-summary-tables.

Statement events per stored program

*note 'events_statements_summary_by_thread_by_event_name': performance-schema-statement-summary-tables.

Statement events per thread and event name

*note 'events_statements_summary_by_user_by_event_name': performance-schema-statement-summary-tables.

Statement events per user name and event name

*note 'events_statements_summary_global_by_event_name': performance-schema-statement-summary-tables.

Statement events per event name

*note 'events_transactions_current': performance-schema-events-transactions-current-table.

Current transaction events

*note 'events_transactions_history': performance-schema-events-transactions-history-table.

Most recent transaction events per thread

*note 'events_transactions_history_long': performance-schema-events-transactions-history-long-table.

Most recent transaction events overall

*note 'events_transactions_summary_by_account_by_event_name': performance-schema-transaction-summary-tables.

Transaction events per account and event name

*note 'events_transactions_summary_by_host_by_event_name': performance-schema-transaction-summary-tables.

Transaction events per host name and event name

*note 'events_transactions_summary_by_thread_by_event_name': performance-schema-transaction-summary-tables.

Transaction events per thread and event name

*note 'events_transactions_summary_by_user_by_event_name': performance-schema-transaction-summary-tables.

Transaction events per user name and event name

*note 'events_transactions_summary_global_by_event_name': performance-schema-transaction-summary-tables.

Transaction events per event name

*note 'events_waits_current': performance-schema-events-waits-current-table.

Current wait events

*note 'events_waits_history': performance-schema-events-waits-history-table.

Most recent wait events per thread

*note 'events_waits_history_long': performance-schema-events-waits-history-long-table.

Most recent wait events overall

*note 'events_waits_summary_by_account_by_event_name': performance-schema-wait-summary-tables.

Wait events per account and event name

*note 'events_waits_summary_by_host_by_event_name': performance-schema-wait-summary-tables.

Wait events per host name and event name

*note 'events_waits_summary_by_instance': performance-schema-wait-summary-tables.

Wait events per instance

*note 'events_waits_summary_by_thread_by_event_name': performance-schema-wait-summary-tables.

Wait events per thread and event name

*note 'events_waits_summary_by_user_by_event_name': performance-schema-wait-summary-tables.

Wait events per user name and event name

*note 'events_waits_summary_global_by_event_name': performance-schema-wait-summary-tables.

Wait events per event name

*note 'file_instances': performance-schema-file-instances-table.

File instances

*note 'file_summary_by_event_name': performance-schema-file-summary-tables.

File events per event name

*note 'file_summary_by_instance': performance-schema-file-summary-tables.

File events per file instance

*note 'global_status': performance-schema-status-variable-tables.

Global status variables

*note 'global_variables': performance-schema-system-variable-tables.

Global system variables

*note 'host_cache': performance-schema-host-cache-table.

Information from internal host cache

*note 'hosts': performance-schema-hosts-table.

Connection statistics per client host name

*note 'memory_summary_by_account_by_event_name': performance-schema-memory-summary-tables.

Memory operations per account and event name

*note 'memory_summary_by_host_by_event_name': performance-schema-memory-summary-tables.

Memory operations per host and event name

*note 'memory_summary_by_thread_by_event_name': performance-schema-memory-summary-tables.

Memory operations per thread and event name

*note 'memory_summary_by_user_by_event_name': performance-schema-memory-summary-tables.

Memory operations per user and event name

*note 'memory_summary_global_by_event_name': performance-schema-memory-summary-tables.

Memory operations globally per event name

*note 'metadata_locks': performance-schema-metadata-locks-table.

Metadata locks and lock requests

*note 'mutex_instances': performance-schema-mutex-instances-table.

Mutex synchronization object instances

*note 'objects_summary_global_by_type': performance-schema-objects-summary-global-by-type-table.

Object summaries

*note 'performance_timers': performance-schema-performance-timers-table.

Which event timers are available

*note 'prepared_statements_instances': performance-schema-prepared-statements-instances-table.

Prepared statement instances and statistics

*note 'replication_applier_configuration': performance-schema-replication-applier-configuration-table.

Configuration parameters for replication applier on replica

*note 'replication_applier_status': performance-schema-replication-applier-status-table.

Current status of replication applier on replica

*note 'replication_applier_status_by_coordinator': performance-schema-replication-applier-status-by-coordinator-table.

SQL or coordinator thread applier status

*note 'replication_applier_status_by_worker': performance-schema-replication-applier-status-by-worker-table.

Worker thread applier status

*note 'replication_connection_configuration': performance-schema-replication-connection-configuration-table.

Configuration parameters for connecting to source

*note 'replication_connection_status': performance-schema-replication-connection-status-table.

Current status of connection to source

*note 'replication_group_member_stats': performance-schema-replication-group-member-stats-table.

Replication group member statistics

*note 'replication_group_members': performance-schema-replication-group-members-table.

Replication group member network and status

*note 'rwlock_instances': performance-schema-rwlock-instances-table.

Lock synchronization object instances

*note 'session_account_connect_attrs': performance-schema-session-account-connect-attrs-table.

Connection attributes per for current session

*note 'session_connect_attrs': performance-schema-session-connect-attrs-table.

Connection attributes for all sessions

*note 'session_status': performance-schema-status-variable-tables.

Status variables for current session

*note 'session_variables': performance-schema-system-variable-tables.

System variables for current session

*note 'setup_actors': performance-schema-setup-actors-table.

How to initialize monitoring for new foreground threads

*note 'setup_consumers': performance-schema-setup-consumers-table.

Consumers for which event information can be stored

*note 'setup_instruments': performance-schema-setup-instruments-table.

Classes of instrumented objects for which events can be collected

*note 'setup_objects': performance-schema-setup-objects-table.

Which objects should be monitored

*note 'setup_timers': performance-schema-setup-timers-table.

Currently 5.7.21 selected event
timers

*note 'socket_instances': performance-schema-socket-instances-table.

Active connection instances

*note 'socket_summary_by_event_name': performance-schema-socket-summary-tables.

Socket waits and I/O per event name

*note 'socket_summary_by_instance': performance-schema-socket-summary-tables.

Socket waits and I/O per instance

*note 'status_by_account': performance-schema-status-variable-summary-tables.

Session status variables per account

*note 'status_by_host': performance-schema-status-variable-summary-tables.

Session status variables per host name

*note 'status_by_thread': performance-schema-status-variable-tables.

Session status variables per session

*note 'status_by_user': performance-schema-status-variable-summary-tables.

Session status variables per user name

*note 'table_handles': performance-schema-table-handles-table.

Table locks and lock requests

*note 'table_io_waits_summary_by_index_usage': performance-schema-table-io-waits-summary-by-index-usage-table.

Table I/O waits per index

*note 'table_io_waits_summary_by_table': performance-schema-table-io-waits-summary-by-table-table.

Table I/O waits per table

*note 'table_lock_waits_summary_by_table': performance-schema-table-lock-waits-summary-by-table-table.

Table lock waits per table

*note 'threads': performance-schema-threads-table.

Information about server threads

*note 'user_variables_by_thread': performance-schema-user-variable-tables.

User-defined variables per thread

*note 'users': performance-schema-users-table.

Connection statistics per client user name

*note 'variables_by_thread': performance-schema-system-variable-tables.

Session system variables per session

 File: manual.info.tmp, Node: performance-schema-setup-tables, Next: performance-schema-instance-tables, Prev: performance-schema-table-reference, Up: performance-schema-table-descriptions

25.12.2 Performance Schema Setup Tables

The setup tables provide information about the current instrumentation and enable the monitoring configuration to be changed. For this reason, some columns in these tables can be changed if you have the 'UPDATE' privilege.

The use of tables rather than individual variables for setup information provides a high degree of flexibility in modifying Performance Schema configuration. For example, you can use a single statement with standard SQL syntax to make multiple simultaneous configuration changes.

These setup tables are available:

 File: manual.info.tmp, Node: performance-schema-setup-actors-table, Next: performance-schema-setup-consumers-table, Prev: performance-schema-setup-tables, Up: performance-schema-setup-tables

25.12.2.1 The setup_actors Table ................................

The *note 'setup_actors': performance-schema-setup-actors-table. table contains information that determines whether to enable monitoring and historical event logging for new foreground server threads (threads associated with client connections). This table has a maximum size of 100 rows by default. To change the table size, modify the 'performance_schema_setup_actors_size' system variable at server startup.

For each new foreground thread, the Performance Schema matches the user and host for the thread against the rows of the note 'setup_actors': performance-schema-setup-actors-table. table. If a row from that table matches, its 'ENABLED' and 'HISTORY' column values are used to set the 'INSTRUMENTED' and 'HISTORY' columns, respectively, of the note 'threads': performance-schema-threads-table. table row for the thread. This enables instrumenting and historical event logging to be applied selectively per host, user, or account (user and host combination). If there is no match, the 'INSTRUMENTED' and 'HISTORY' columns for the thread are set to 'NO'.

For background threads, there is no associated user. 'INSTRUMENTED' and 'HISTORY' are 'YES' by default and *note 'setup_actors': performance-schema-setup-actors-table. is not consulted.

The initial contents of the *note 'setup_actors': performance-schema-setup-actors-table. table match any user and host combination, so monitoring and historical event collection are enabled by default for all foreground threads:

 mysql> SELECT * FROM performance_schema.setup_actors;
 +------+------+------+---------+---------+
 | HOST | USER | ROLE | ENABLED | HISTORY |
 +------+------+------+---------+---------+
 | %    | %    | %    | YES     | YES     |
 +------+------+------+---------+---------+

For information about how to use the note 'setup_actors': performance-schema-setup-actors-table. table to affect event monitoring, see note performance-schema-thread-filtering::.

Modifications to the note 'setup_actors': performance-schema-setup-actors-table. table affect only foreground threads created subsequent to the modification, not existing threads. To affect existing threads, modify the 'INSTRUMENTED' and 'HISTORY' columns of note 'threads': performance-schema-threads-table. table rows.

The *note 'setup_actors': performance-schema-setup-actors-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'setup_actors': performance-schema-setup-actors-table. table. It removes the rows.

 File: manual.info.tmp, Node: performance-schema-setup-consumers-table, Next: performance-schema-setup-instruments-table, Prev: performance-schema-setup-actors-table, Up: performance-schema-setup-tables

25.12.2.2 The setup_consumers Table ...................................

The *note 'setup_consumers': performance-schema-setup-consumers-table. table lists the types of consumers for which event information can be stored and which are enabled:

 mysql> SELECT * FROM performance_schema.setup_consumers;
 +----------------------------------+---------+
 | NAME                             | ENABLED |
 +----------------------------------+---------+
 | events_stages_current            | NO      |
 | events_stages_history            | NO      |
 | events_stages_history_long       | NO      |
 | events_statements_current        | YES     |
 | events_statements_history        | YES     |
 | events_statements_history_long   | NO      |
 | events_transactions_current      | NO      |
 | events_transactions_history      | NO      |
 | events_transactions_history_long | NO      |
 | events_waits_current             | NO      |
 | events_waits_history             | NO      |
 | events_waits_history_long        | NO      |
 | global_instrumentation           | YES     |
 | thread_instrumentation           | YES     |
 | statements_digest                | YES     |
 +----------------------------------+---------+

The consumer settings in the note 'setup_consumers': performance-schema-setup-consumers-table. table form a hierarchy from higher levels to lower. For detailed information about the effect of enabling different consumers, see note performance-schema-consumer-filtering::.

Modifications to the *note 'setup_consumers': performance-schema-setup-consumers-table. table affect monitoring immediately.

The *note 'setup_consumers': performance-schema-setup-consumers-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'setup_consumers': performance-schema-setup-consumers-table. table.

 File: manual.info.tmp, Node: performance-schema-setup-instruments-table, Next: performance-schema-setup-objects-table, Prev: performance-schema-setup-consumers-table, Up: performance-schema-setup-tables

25.12.2.3 The setup_instruments Table .....................................

The *note 'setup_instruments': performance-schema-setup-instruments-table. table lists classes of instrumented objects for which events can be collected:

 mysql> SELECT * FROM performance_schema.setup_instruments;
 +---------------------------------------------------+---------+-------+
 | NAME                                              | ENABLED | TIMED |
 +---------------------------------------------------+---------+-------+
 ...
 | stage/sql/end                                     | NO      | NO    |
 | stage/sql/executing                               | NO      | NO    |
 | stage/sql/init                                    | NO      | NO    |
 | stage/sql/insert                                  | NO      | NO    |
 ...
 | statement/sql/load                                | YES     | YES   |
 | statement/sql/grant                               | YES     | YES   |
 | statement/sql/check                               | YES     | YES   |
 | statement/sql/flush                               | YES     | YES   |
 ...
 | wait/synch/mutex/sql/LOCK_global_read_lock        | YES     | YES   |
 | wait/synch/mutex/sql/LOCK_global_system_variables | YES     | YES   |
 | wait/synch/mutex/sql/LOCK_lock_db                 | YES     | YES   |
 | wait/synch/mutex/sql/LOCK_manager                 | YES     | YES   |
 ...
 | wait/synch/rwlock/sql/LOCK_grant                  | YES     | YES   |
 | wait/synch/rwlock/sql/LOGGER::LOCK_logger         | YES     | YES   |
 | wait/synch/rwlock/sql/LOCK_sys_init_connect       | YES     | YES   |
 | wait/synch/rwlock/sql/LOCK_sys_init_slave         | YES     | YES   |
 ...
 | wait/io/file/sql/binlog                           | YES     | YES   |
 | wait/io/file/sql/binlog_index                     | YES     | YES   |
 | wait/io/file/sql/casetest                         | YES     | YES   |
 | wait/io/file/sql/dbopt                            | YES     | YES   |
 ...

Each instrument added to the source code provides a row for the note 'setup_instruments': performance-schema-setup-instruments-table. table, even when the instrumented code is not executed. When an instrument is enabled and executed, instrumented instances are created, which are visible in the 'XXX_instances' tables, such as note 'file_instances': performance-schema-file-instances-table. or *note 'rwlock_instances': performance-schema-rwlock-instances-table.

Modifications to most *note 'setup_instruments': performance-schema-setup-instruments-table. rows affect monitoring immediately. For some instruments, modifications are effective only at server startup; changing them at runtime has no effect. This affects primarily mutexes, conditions, and rwlocks in the server, although there may be other instruments for which this is true.

For more information about the role of the note 'setup_instruments': performance-schema-setup-instruments-table. table in event filtering, see note performance-schema-pre-filtering::.

The *note 'setup_instruments': performance-schema-setup-instruments-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'setup_instruments': performance-schema-setup-instruments-table. table.

 File: manual.info.tmp, Node: performance-schema-setup-objects-table, Next: performance-schema-setup-timers-table, Prev: performance-schema-setup-instruments-table, Up: performance-schema-setup-tables

25.12.2.4 The setup_objects Table .................................

The *note 'setup_objects': performance-schema-setup-objects-table. table controls whether the Performance Schema monitors particular objects. This table has a maximum size of 100 rows by default. To change the table size, modify the 'performance_schema_setup_objects_size' system variable at server startup.

The initial *note 'setup_objects': performance-schema-setup-objects-table. contents look like this:

 mysql> SELECT * FROM performance_schema.setup_objects;
 +-------------+--------------------+-------------+---------+-------+
 | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
 +-------------+--------------------+-------------+---------+-------+
 | EVENT       | mysql              | %           | NO      | NO    |
 | EVENT       | performance_schema | %           | NO      | NO    |
 | EVENT       | information_schema | %           | NO      | NO    |
 | EVENT       | %                  | %           | YES     | YES   |
 | FUNCTION    | mysql              | %           | NO      | NO    |
 | FUNCTION    | performance_schema | %           | NO      | NO    |
 | FUNCTION    | information_schema | %           | NO      | NO    |
 | FUNCTION    | %                  | %           | YES     | YES   |
 | PROCEDURE   | mysql              | %           | NO      | NO    |
 | PROCEDURE   | performance_schema | %           | NO      | NO    |
 | PROCEDURE   | information_schema | %           | NO      | NO    |
 | PROCEDURE   | %                  | %           | YES     | YES   |
 | TABLE       | mysql              | %           | NO      | NO    |
 | TABLE       | performance_schema | %           | NO      | NO    |
 | TABLE       | information_schema | %           | NO      | NO    |
 | TABLE       | %                  | %           | YES     | YES   |
 | TRIGGER     | mysql              | %           | NO      | NO    |
 | TRIGGER     | performance_schema | %           | NO      | NO    |
 | TRIGGER     | information_schema | %           | NO      | NO    |
 | TRIGGER     | %                  | %           | YES     | YES   |
 +-------------+--------------------+-------------+---------+-------+

Modifications to the *note 'setup_objects': performance-schema-setup-objects-table. table affect object monitoring immediately.

For object types listed in *note 'setup_objects': performance-schema-setup-objects-table, the Performance Schema uses the table to how to monitor them. Object matching is based on the 'OBJECT_SCHEMA' and 'OBJECT_NAME' columns. Objects for which there is no match are not monitored.

The effect of the default object configuration is to instrument all tables except those in the 'mysql', 'INFORMATION_SCHEMA', and 'performance_schema' databases. (Tables in the 'INFORMATION_SCHEMA' database are not instrumented regardless of the contents of *note 'setup_objects': performance-schema-setup-objects-table.; the row for 'information_schema.%' simply makes this default explicit.)

When the Performance Schema checks for a match in note 'setup_objects': performance-schema-setup-objects-table, it tries to find more specific matches first. For example, with a table 'db1.t1', it looks for a match for ''db1'' and ''t1'', then for ''db1'' and ''%'', then for ''%'' and ''%''. The order in which matching occurs matters because different matching note 'setup_objects': performance-schema-setup-objects-table. rows can have different 'ENABLED' and 'TIMED' values.

Rows can be inserted into or deleted from *note 'setup_objects': performance-schema-setup-objects-table. by users with the 'INSERT' or 'DELETE' privilege on the table. For existing rows, only the 'ENABLED' and 'TIMED' columns can be modified, by users with the 'UPDATE' privilege on the table.

For more information about the role of the note 'setup_objects': performance-schema-setup-objects-table. table in event filtering, see note performance-schema-pre-filtering::.

The *note 'setup_objects': performance-schema-setup-objects-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'setup_objects': performance-schema-setup-objects-table. table. It removes the rows.

 File: manual.info.tmp, Node: performance-schema-setup-timers-table, Prev: performance-schema-setup-objects-table, Up: performance-schema-setup-tables

25.12.2.5 The setup_timers Table ................................

The *note 'setup_timers': performance-schema-setup-timers-table. table shows the currently selected event timers:

 mysql> SELECT * FROM performance_schema.setup_timers;
 +-------------+-------------+
 | NAME        | TIMER_NAME  |
 +-------------+-------------+
 | idle        | MICROSECOND |
 | wait        | CYCLE       |
 | stage       | NANOSECOND  |
 | statement   | NANOSECOND  |
 | transaction | NANOSECOND  |
 +-------------+-------------+

Note:

As of MySQL 5.7.21, the Performance Schema note 'setup_timers': performance-schema-setup-timers-table. table is deprecated and is removed in MySQL 8.0, as is the 'TICKS' row in the note 'performance_timers': performance-schema-performance-timers-table. table.

The 'setup_timers.TIMER_NAME' value can be changed to select a different timer. The value can be any of the values in the 'performance_timers.TIMER_NAME' column. For an explanation of how event timing occurs, see *note performance-schema-timing::.

Modifications to the note 'setup_timers': performance-schema-setup-timers-table. table affect monitoring immediately. Events already in progress may use the original timer for the begin time and the new timer for the end time. To avoid unpredictable results after you make timer changes, use note 'TRUNCATE TABLE': truncate-table. to reset Performance Schema statistics.

The *note 'setup_timers': performance-schema-setup-timers-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'setup_timers': performance-schema-setup-timers-table. table.

 File: manual.info.tmp, Node: performance-schema-instance-tables, Next: performance-schema-wait-tables, Prev: performance-schema-setup-tables, Up: performance-schema-table-descriptions

25.12.3 Performance Schema Instance Tables

Instance tables document what types of objects are instrumented. They provide event names and explanatory notes or status information:

These tables list instrumented synchronization objects, files, and connections. There are three types of synchronization objects: 'cond', 'mutex', and 'rwlock'. Each instance table has an 'EVENT_NAME' or 'NAME' column to indicate the instrument associated with each row. Instrument names may have multiple parts and form a hierarchy, as discussed in *note performance-schema-instrument-naming::.

The 'mutex_instances.LOCKED_BY_THREAD_ID' and 'rwlock_instances.WRITE_LOCKED_BY_THREAD_ID' columns are extremely important for investigating performance bottlenecks or deadlocks. For examples of how to use them for this purpose, see *note performance-schema-examples::

 File: manual.info.tmp, Node: performance-schema-cond-instances-table, Next: performance-schema-file-instances-table, Prev: performance-schema-instance-tables, Up: performance-schema-instance-tables

25.12.3.1 The cond_instances Table ..................................

The *note 'cond_instances': performance-schema-cond-instances-table. table lists all the conditions seen by the Performance Schema while the server executes. A condition is a synchronization mechanism used in the code to signal that a specific event has happened, so that a thread waiting for this condition can resume work.

When a thread is waiting for something to happen, the condition name is an indication of what the thread is waiting for, but there is no immediate way to tell which other threads cause the condition to happen.

The *note 'cond_instances': performance-schema-cond-instances-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'cond_instances': performance-schema-cond-instances-table. table.

 File: manual.info.tmp, Node: performance-schema-file-instances-table, Next: performance-schema-mutex-instances-table, Prev: performance-schema-cond-instances-table, Up: performance-schema-instance-tables

25.12.3.2 The file_instances Table ..................................

The note 'file_instances': performance-schema-file-instances-table. table lists all the files seen by the Performance Schema when executing file I/O instrumentation. If a file on disk has never been opened, it is not in note 'file_instances': performance-schema-file-instances-table. When a file is deleted from the disk, it is also removed from the *note 'file_instances': performance-schema-file-instances-table. table.

The *note 'file_instances': performance-schema-file-instances-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'file_instances': performance-schema-file-instances-table. table.

 File: manual.info.tmp, Node: performance-schema-mutex-instances-table, Next: performance-schema-rwlock-instances-table, Prev: performance-schema-file-instances-table, Up: performance-schema-instance-tables

25.12.3.3 The mutex_instances Table ...................................

The *note 'mutex_instances': performance-schema-mutex-instances-table. table lists all the mutexes seen by the Performance Schema while the server executes. A mutex is a synchronization mechanism used in the code to enforce that only one thread at a given time can have access to some common resource. The resource is said to be 'protected' by the mutex.

When two threads executing in the server (for example, two user sessions executing a query simultaneously) do need to access the same resource (a file, a buffer, or some piece of data), these two threads compete against each other, so that the first query to obtain a lock on the mutex causes the other query to wait until the first is done and unlocks the mutex.

The work performed while holding a mutex is said to be in a 'critical section,' and multiple queries do execute this critical section in a serialized way (one at a time), which is a potential bottleneck.

The *note 'mutex_instances': performance-schema-mutex-instances-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'mutex_instances': performance-schema-mutex-instances-table. table.

For every mutex instrumented in the code, the Performance Schema provides the following information.

By performing queries on both of the following tables, a monitoring application or a DBA can detect bottlenecks or deadlocks between threads that involve mutexes:

 File: manual.info.tmp, Node: performance-schema-rwlock-instances-table, Next: performance-schema-socket-instances-table, Prev: performance-schema-mutex-instances-table, Up: performance-schema-instance-tables

25.12.3.4 The rwlock_instances Table ....................................

The *note 'rwlock_instances': performance-schema-rwlock-instances-table. table lists all the rwlock (read write lock) instances seen by the Performance Schema while the server executes. An 'rwlock' is a synchronization mechanism used in the code to enforce that threads at a given time can have access to some common resource following certain rules. The resource is said to be 'protected' by the 'rwlock'. The access is either shared (many threads can have a read lock at the same time), exclusive (only one thread can have a write lock at a given time), or shared-exclusive (a thread can have a write lock while permitting inconsistent reads by other threads). Shared-exclusive access is otherwise known as an 'sxlock' and optimizes concurrency and improves scalability for read-write workloads.

Depending on how many threads are requesting a lock, and the nature of the locks requested, access can be either granted in shared mode, exclusive mode, shared-exclusive mode or not granted at all, waiting for other threads to finish first.

The *note 'rwlock_instances': performance-schema-rwlock-instances-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'rwlock_instances': performance-schema-rwlock-instances-table. table.

By performing queries on both of the following tables, a monitoring application or a DBA may detect some bottlenecks or deadlocks between threads that involve locks:

There is a limitation: The *note 'rwlock_instances': performance-schema-rwlock-instances-table. can be used only to identify the thread holding a write lock, but not the threads holding a read lock.

 File: manual.info.tmp, Node: performance-schema-socket-instances-table, Prev: performance-schema-rwlock-instances-table, Up: performance-schema-instance-tables

25.12.3.5 The socket_instances Table ....................................

The note 'socket_instances': performance-schema-socket-instances-table. table provides a real-time snapshot of the active connections to the MySQL server. The table contains one row per TCP/IP or Unix socket file connection. Information available in this table provides a real-time snapshot of the active connections to the server. (Additional information is available in socket summary tables, including network activity such as socket operations and number of bytes transmitted and received; see note performance-schema-socket-summary-tables::).

 mysql> SELECT * FROM performance_schema.socket_instances\G
 *************************** 1. row ***************************
            EVENT_NAME: wait/io/socket/sql/server_unix_socket
 OBJECT_INSTANCE_BEGIN: 4316619408
             THREAD_ID: 1
             SOCKET_ID: 16
                    IP:
                  PORT: 0
                 STATE: ACTIVE
 *************************** 2. row ***************************
            EVENT_NAME: wait/io/socket/sql/client_connection
 OBJECT_INSTANCE_BEGIN: 4316644608
             THREAD_ID: 21
             SOCKET_ID: 39
                    IP: 127.0.0.1
                  PORT: 55233
                 STATE: ACTIVE
 *************************** 3. row ***************************
            EVENT_NAME: wait/io/socket/sql/server_tcpip_socket
 OBJECT_INSTANCE_BEGIN: 4316699040
             THREAD_ID: 1
             SOCKET_ID: 14
                    IP: 0.0.0.0
                  PORT: 50603
                 STATE: ACTIVE

Socket instruments have names of the form 'wait/io/socket/sql/SOCKET_TYPE' and are used like this:

  1. The server has a listening socket for each network protocol that it supports. The instruments associated with listening sockets for TCP/IP or Unix socket file connections have a SOCKET_TYPE value of 'server_tcpip_socket' or 'server_unix_socket', respectively.

  2. When a listening socket detects a connection, the server transfers the connection to a new socket managed by a separate thread. The instrument for the new connection thread has a SOCKET_TYPE value of 'client_connection'.

  3. When a connection terminates, the row in *note 'socket_instances': performance-schema-socket-instances-table. corresponding to it is deleted.

The *note 'socket_instances': performance-schema-socket-instances-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'socket_instances': performance-schema-socket-instances-table. table.

The 'IP:PORT' column combination value identifies the connection. This combination value is used in the 'OBJECT_NAME' column of the 'events_waits_XXX' tables, to identify the connection from which socket events come:

 File: manual.info.tmp, Node: performance-schema-wait-tables, Next: performance-schema-stage-tables, Prev: performance-schema-instance-tables, Up: performance-schema-table-descriptions

25.12.4 Performance Schema Wait Event Tables

The Performance Schema instruments waits, which are events that take time. Within the event hierarchy, wait events nest within stage events, which nest within statement events, which nest within transaction events.

These tables store wait events:

The following sections describe the wait event tables. There are also summary tables that aggregate information about wait events; see *note performance-schema-wait-summary-tables::.

For more information about the relationship between the three wait event tables, see *note performance-schema-event-tables::.

Configuring Wait Event Collection

To control whether to collect wait events, set the state of the relevant instruments and consumers:

Some wait instruments are enabled by default; others are disabled. For example:

 mysql> SELECT * FROM performance_schema.setup_instruments
        WHERE NAME LIKE 'wait/io/file/innodb%';
 +--------------------------------------+---------+-------+
 | NAME                                 | ENABLED | TIMED |
 +--------------------------------------+---------+-------+
 | wait/io/file/innodb/innodb_data_file | YES     | YES   |
 | wait/io/file/innodb/innodb_log_file  | YES     | YES   |
 | wait/io/file/innodb/innodb_temp_file | YES     | YES   |
 +--------------------------------------+---------+-------+
 mysql> SELECT *
        FROM performance_schema.setup_instruments WHERE
        NAME LIKE 'wait/io/socket/%';
 +----------------------------------------+---------+-------+
 | NAME                                   | ENABLED | TIMED |
 +----------------------------------------+---------+-------+
 | wait/io/socket/sql/server_tcpip_socket | NO      | NO    |
 | wait/io/socket/sql/server_unix_socket  | NO      | NO    |
 | wait/io/socket/sql/client_connection   | NO      | NO    |
 +----------------------------------------+---------+-------+

The wait consumers are disabled by default:

 mysql> SELECT *
        FROM performance_schema.setup_consumers
        WHERE NAME LIKE 'events_waits%';
 +---------------------------+---------+
 | NAME                      | ENABLED |
 +---------------------------+---------+
 | events_waits_current      | NO      |
 | events_waits_history      | NO      |
 | events_waits_history_long | NO      |
 +---------------------------+---------+

To control wait event collection at server startup, use lines like these in your 'my.cnf' file:

To control wait event collection at runtime, update the note 'setup_instruments': performance-schema-setup-instruments-table. and note 'setup_consumers': performance-schema-setup-consumers-table. tables:

To collect only specific wait events, enable only the corresponding wait instruments. To collect wait events only for specific wait event tables, enable the wait instruments but only the wait consumers corresponding to the desired tables.

The *note 'setup_timers': performance-schema-setup-timers-table. table contains a row with a 'NAME' value of 'wait' that indicates the unit for wait event timing. The default unit is 'CYCLE':

 mysql> SELECT *
        FROM performance_schema.setup_timers
        WHERE NAME = 'wait';
 +------+------------+
 | NAME | TIMER_NAME |
 +------+------------+
 | wait | CYCLE      |
 +------+------------+

To change the timing unit, modify the 'TIMER_NAME' value:

 UPDATE performance_schema.setup_timers
 SET TIMER_NAME = 'NANOSECOND'
 WHERE NAME = 'wait';

For additional information about configuring event collection, see note performance-schema-startup-configuration::, and note performance-schema-runtime-configuration::.

 File: manual.info.tmp, Node: performance-schema-events-waits-current-table, Next: performance-schema-events-waits-history-table, Prev: performance-schema-wait-tables, Up: performance-schema-wait-tables

25.12.4.1 The events_waits_current Table ........................................

The *note 'events_waits_current': performance-schema-events-waits-current-table. table contains current wait events. The table stores one row per thread showing the current status of the thread's most recent monitored wait event, so there is no system variable for configuring the table size.

Of the tables that contain wait event rows, note 'events_waits_current': performance-schema-events-waits-current-table. is the most fundamental. Other tables that contain wait event rows are logically derived from the current events. For example, the note 'events_waits_history': performance-schema-events-waits-history-table. and *note 'events_waits_history_long': performance-schema-events-waits-history-long-table. tables are collections of the most recent wait events that have ended, up to a maximum number of rows per thread and globally across all threads, respectively.

For more information about the relationship between the three wait event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect wait events, see *note performance-schema-wait-tables::.

The *note 'events_waits_current': performance-schema-events-waits-current-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_waits_current': performance-schema-events-waits-current-table. table. It removes the rows.

 File: manual.info.tmp, Node: performance-schema-events-waits-history-table, Next: performance-schema-events-waits-history-long-table, Prev: performance-schema-events-waits-current-table, Up: performance-schema-wait-tables

25.12.4.2 The events_waits_history Table ........................................

The *note 'events_waits_history': performance-schema-events-waits-history-table. table contains the N most recent wait events that have ended per thread. Wait events are not added to the table until they have ended. When the table contains the maximum number of rows for a given thread, the oldest thread row is discarded when a new row for that thread is added. When a thread ends, all its rows are discarded.

The Performance Schema autosizes the value of N during server startup. To set the number of rows per thread explicitly, set the 'performance_schema_events_waits_history_size' system variable at server startup.

The note 'events_waits_history': performance-schema-events-waits-history-table. table has the same columns as note 'events_waits_current': performance-schema-events-waits-current-table. See *note performance-schema-events-waits-current-table::.

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_waits_history': performance-schema-events-waits-history-table. table. It removes the rows.

For more information about the relationship between the three wait event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect wait events, see *note performance-schema-wait-tables::.

 File: manual.info.tmp, Node: performance-schema-events-waits-history-long-table, Prev: performance-schema-events-waits-history-table, Up: performance-schema-wait-tables

25.12.4.3 The events_waits_history_long Table .............................................

The *note 'events_waits_history_long': performance-schema-events-waits-history-long-table. table contains N the most recent wait events that have ended globally, across all threads. Wait events are not added to the table until they have ended. When the table becomes full, the oldest row is discarded when a new row is added, regardless of which thread generated either row.

The Performance Schema autosizes the value of N during server startup. To set the table size explicitly, set the 'performance_schema_events_waits_history_long_size' system variable at server startup.

The note 'events_waits_history_long': performance-schema-events-waits-history-long-table. table has the same columns as note 'events_waits_current': performance-schema-events-waits-current-table. See *note performance-schema-events-waits-current-table::.

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_waits_history_long': performance-schema-events-waits-history-long-table. table. It removes the rows.

For more information about the relationship between the three wait event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect wait events, see *note performance-schema-wait-tables::.

 File: manual.info.tmp, Node: performance-schema-stage-tables, Next: performance-schema-statement-tables, Prev: performance-schema-wait-tables, Up: performance-schema-table-descriptions

25.12.5 Performance Schema Stage Event Tables

The Performance Schema instruments stages, which are steps during the statement-execution process, such as parsing a statement, opening a table, or performing a 'filesort' operation. Stages correspond to the thread states displayed by note 'SHOW PROCESSLIST': show-processlist. or that are visible in the Information Schema note 'PROCESSLIST': information-schema-processlist-table. table. Stages begin and end when state values change.

Within the event hierarchy, wait events nest within stage events, which nest within statement events, which nest within transaction events.

These tables store stage events:

The following sections describe the stage event tables. There are also summary tables that aggregate information about stage events; see *note performance-schema-stage-summary-tables::.

For more information about the relationship between the three stage event tables, see *note performance-schema-event-tables::.

Configuring Stage Event Collection

To control whether to collect stage events, set the state of the relevant instruments and consumers:

Other than those instruments that provide statement progress information, the stage instruments are disabled by default. For example:

 mysql> SELECT *
        FROM performance_schema.setup_instruments
        WHERE NAME RLIKE 'stage/sql/[a-c]';
 +----------------------------------------------------+---------+-------+
 | NAME                                               | ENABLED | TIMED |
 +----------------------------------------------------+---------+-------+
 | stage/sql/After create                             | NO      | NO    |
 | stage/sql/allocating local table                   | NO      | NO    |
 | stage/sql/altering table                           | NO      | NO    |
 | stage/sql/committing alter table to storage engine | NO      | NO    |
 | stage/sql/Changing master                          | NO      | NO    |
 | stage/sql/Checking master version                  | NO      | NO    |
 | stage/sql/checking permissions                     | NO      | NO    |
 | stage/sql/checking privileges on cached query      | NO      | NO    |
 | stage/sql/checking query cache for query           | NO      | NO    |
 | stage/sql/cleaning up                              | NO      | NO    |
 | stage/sql/closing tables                           | NO      | NO    |
 | stage/sql/Connecting to master                     | NO      | NO    |
 | stage/sql/converting HEAP to MyISAM                | NO      | NO    |
 | stage/sql/Copying to group table                   | NO      | NO    |
 | stage/sql/Copying to tmp table                     | NO      | NO    |
 | stage/sql/copy to tmp table                        | NO      | NO    |
 | stage/sql/Creating sort index                      | NO      | NO    |
 | stage/sql/creating table                           | NO      | NO    |
 | stage/sql/Creating tmp table                       | NO      | NO    |
 +----------------------------------------------------+---------+-------+

Stage event instruments that provide statement progress information are enabled and timed by default:

 mysql> SELECT *
        FROM performance_schema.setup_instruments
        WHERE ENABLED='YES' AND NAME LIKE "stage/%";
 +------------------------------------------------------+---------+-------+
 | NAME                                                 | ENABLED | TIMED |
 +------------------------------------------------------+---------+-------+
 | stage/sql/copy to tmp table                          | YES     | YES   |
 | stage/innodb/alter table (end)                       | YES     | YES   |
 | stage/innodb/alter table (flush)                     | YES     | YES   |
 | stage/innodb/alter table (insert)                    | YES     | YES   |
 | stage/innodb/alter table (log apply index)           | YES     | YES   |
 | stage/innodb/alter table (log apply table)           | YES     | YES   |
 | stage/innodb/alter table (merge sort)                | YES     | YES   |
 | stage/innodb/alter table (read PK and internal sort) | YES     | YES   |
 | stage/innodb/buffer pool load                        | YES     | YES   |
 +------------------------------------------------------+---------+-------+

The stage consumers are disabled by default:

 mysql> SELECT *
        FROM performance_schema.setup_consumers
        WHERE NAME LIKE 'events_stages%';
 +----------------------------+---------+
 | NAME                       | ENABLED |
 +----------------------------+---------+
 | events_stages_current      | NO      |
 | events_stages_history      | NO      |
 | events_stages_history_long | NO      |
 +----------------------------+---------+

To control stage event collection at server startup, use lines like these in your 'my.cnf' file:

To control stage event collection at runtime, update the note 'setup_instruments': performance-schema-setup-instruments-table. and note 'setup_consumers': performance-schema-setup-consumers-table. tables:

To collect only specific stage events, enable only the corresponding stage instruments. To collect stage events only for specific stage event tables, enable the stage instruments but only the stage consumers corresponding to the desired tables.

The *note 'setup_timers': performance-schema-setup-timers-table. table contains a row with a 'NAME' value of 'stage' that indicates the unit for stage event timing. The default unit is 'NANOSECOND':

 mysql> SELECT *
        FROM performance_schema.setup_timers
        WHERE NAME = 'stage';
 +-------+------------+
 | NAME  | TIMER_NAME |
 +-------+------------+
 | stage | NANOSECOND |
 +-------+------------+

To change the timing unit, modify the 'TIMER_NAME' value:

 UPDATE performance_schema.setup_timers
 SET TIMER_NAME = 'MICROSECOND'
 WHERE NAME = 'stage';

For additional information about configuring event collection, see note performance-schema-startup-configuration::, and note performance-schema-runtime-configuration::.

Stage Event Progress Information

The Performance Schema stage event tables contain two columns that, taken together, provide a stage progress indicator for each row:

Each column is 'NULL' if no progress information is provided for an instrument. Interpretation of the information, if it is available, depends entirely on the instrument implementation. The Performance Schema tables provide a container to store progress data, but make no assumptions about the semantics of the metric itself:

Instrumentation for a stage event progress indicator can implement any of the following behaviors:

The 'stage/sql/copy to tmp table' instrument illustrates how progress indicators work. During execution of an *note 'ALTER TABLE': alter-table. statement, the 'stage/sql/copy to tmp table' stage is used, and this stage can execute potentially for a long time, depending on the size of the data to copy.

The table-copy task has a defined termination (all rows copied), and the 'stage/sql/copy to tmp table' stage is instrumented to provided bounded progress information: The work unit used is number of rows copied, 'WORK_COMPLETED' and 'WORK_ESTIMATED' are both meaningful, and their ratio indicates task percentage complete.

To enable the instrument and the relevant consumers, execute these statements:

 UPDATE performance_schema.setup_instruments
 SET ENABLED='YES'
 WHERE NAME='stage/sql/copy to tmp table';

 UPDATE performance_schema.setup_consumers
 SET ENABLED='YES'
 WHERE NAME LIKE 'events_stages_%';

To see the progress of an ongoing note 'ALTER TABLE': alter-table. statement, select from the note 'events_stages_current': performance-schema-events-stages-current-table. table.

 File: manual.info.tmp, Node: performance-schema-events-stages-current-table, Next: performance-schema-events-stages-history-table, Prev: performance-schema-stage-tables, Up: performance-schema-stage-tables

25.12.5.1 The events_stages_current Table .........................................

The *note 'events_stages_current': performance-schema-events-stages-current-table. table contains current stage events. The table stores one row per thread showing the current status of the thread's most recent monitored stage event, so there is no system variable for configuring the table size.

Of the tables that contain stage event rows, note 'events_stages_current': performance-schema-events-stages-current-table. is the most fundamental. Other tables that contain stage event rows are logically derived from the current events. For example, the note 'events_stages_history': performance-schema-events-stages-history-table. and *note 'events_stages_history_long': performance-schema-events-stages-history-long-table. tables are collections of the most recent stage events that have ended, up to a maximum number of rows per thread and globally across all threads, respectively.

For more information about the relationship between the three stage event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect stage events, see *note performance-schema-stage-tables::.

The *note 'events_stages_current': performance-schema-events-stages-current-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_stages_current': performance-schema-events-stages-current-table. table. It removes the rows.

 File: manual.info.tmp, Node: performance-schema-events-stages-history-table, Next: performance-schema-events-stages-history-long-table, Prev: performance-schema-events-stages-current-table, Up: performance-schema-stage-tables

25.12.5.2 The events_stages_history Table .........................................

The *note 'events_stages_history': performance-schema-events-stages-history-table. table contains the N most recent stage events that have ended per thread. Stage events are not added to the table until they have ended. When the table contains the maximum number of rows for a given thread, the oldest thread row is discarded when a new row for that thread is added. When a thread ends, all its rows are discarded.

The Performance Schema autosizes the value of N during server startup. To set the number of rows per thread explicitly, set the 'performance_schema_events_stages_history_size' system variable at server startup.

The note 'events_stages_history': performance-schema-events-stages-history-table. table has the same columns as note 'events_stages_current': performance-schema-events-stages-current-table. See *note performance-schema-events-stages-current-table::.

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_stages_history': performance-schema-events-stages-history-table. table. It removes the rows.

For more information about the relationship between the three stage event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect stage events, see *note performance-schema-stage-tables::.

 File: manual.info.tmp, Node: performance-schema-events-stages-history-long-table, Prev: performance-schema-events-stages-history-table, Up: performance-schema-stage-tables

25.12.5.3 The events_stages_history_long Table ..............................................

The *note 'events_stages_history_long': performance-schema-events-stages-history-long-table. table contains the N most recent stage events that have ended globally, across all threads. Stage events are not added to the table until they have ended. When the table becomes full, the oldest row is discarded when a new row is added, regardless of which thread generated either row.

The Performance Schema autosizes the value of N during server startup. To set the table size explicitly, set the 'performance_schema_events_stages_history_long_size' system variable at server startup.

The note 'events_stages_history_long': performance-schema-events-stages-history-long-table. table has the same columns as note 'events_stages_current': performance-schema-events-stages-current-table. See *note performance-schema-events-stages-current-table::.

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_stages_history_long': performance-schema-events-stages-history-long-table. table. It removes the rows.

For more information about the relationship between the three stage event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect stage events, see *note performance-schema-stage-tables::.

 File: manual.info.tmp, Node: performance-schema-statement-tables, Next: performance-schema-transaction-tables, Prev: performance-schema-stage-tables, Up: performance-schema-table-descriptions

25.12.6 Performance Schema Statement Event Tables

The Performance Schema instruments statement execution. Statement events occur at a high level of the event hierarchy. Within 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 the statement event tables. There are also summary tables that aggregate information about statement events; see *note performance-schema-statement-summary-tables::.

For more information about the relationship between the three 'events_statements_XXX' event tables, see *note performance-schema-event-tables::.

Configuring Statement Event Collection

To control whether to collect statement events, set the state of the relevant instruments and consumers:

The statement instruments are enabled by default, and the 'events_statements_current', 'events_statements_history', and 'statements_digest' statement consumers are enabled by default:

 mysql> SELECT *
        FROM performance_schema.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   |
 +---------------------------------------------+---------+-------+

 mysql> SELECT *
        FROM performance_schema.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 control statement event collection at server startup, use lines like these in your 'my.cnf' file:

To control statement event collection at runtime, update the note 'setup_instruments': performance-schema-setup-instruments-table. and note 'setup_consumers': performance-schema-setup-consumers-table. tables:

To collect only specific statement events, enable only the corresponding statement instruments. To collect statement events only for specific statement event tables, enable the statement instruments but only the statement consumers corresponding to the desired tables.

The *note 'setup_timers': performance-schema-setup-timers-table. 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 performance_schema.setup_timers
        WHERE NAME = 'statement';
 +-----------+------------+
 | NAME      | TIMER_NAME |
 +-----------+------------+
 | statement | NANOSECOND |
 +-----------+------------+

To change the timing unit, modify the 'TIMER_NAME' value:

 UPDATE performance_schema.setup_timers
 SET TIMER_NAME = 'MICROSECOND'
 WHERE NAME = 'statement';

For additional information about configuring event collection, see note performance-schema-startup-configuration::, and note performance-schema-runtime-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. 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:

Some final instrument names are specific to error handling:

A request can be obtained from any of these sources:

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 an 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 *note 'INSERT': 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 replica:

  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 *note 'INSERT': 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 replica 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 note 'INSERT': insert. statement, execution of the event itself is the parent, instrumented using 'statement/scheduler/event', and the note 'INSERT': 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 *note 'INSERT': 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.

 File: manual.info.tmp, Node: performance-schema-events-statements-current-table, Next: performance-schema-events-statements-history-table, Prev: performance-schema-statement-tables, Up: performance-schema-statement-tables

25.12.6.1 The events_statements_current Table .............................................

The *note 'events_statements_current': performance-schema-events-statements-current-table. table contains current statement events. The table stores one row per thread showing the current status of the thread's most recent monitored statement event, so there is no system variable for configuring the table size.

Of the tables that contain statement event rows, note 'events_statements_current': performance-schema-events-statements-current-table. is the most fundamental. Other tables that contain statement event rows are logically derived from the current events. For example, the note 'events_statements_history': performance-schema-events-statements-history-table. and *note 'events_statements_history_long': performance-schema-events-statements-history-long-table. tables are collections of the most recent statement events that have ended, up to a maximum number of rows per thread and globally across all threads, respectively.

For more information about the relationship between the three 'events_statements_XXX' event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect statement events, see *note performance-schema-statement-tables::.

The *note 'events_statements_current': performance-schema-events-statements-current-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_statements_current': performance-schema-events-statements-current-table. table. It removes the rows.

 File: manual.info.tmp, Node: performance-schema-events-statements-history-table, Next: performance-schema-events-statements-history-long-table, Prev: performance-schema-events-statements-current-table, Up: performance-schema-statement-tables

25.12.6.2 The events_statements_history Table .............................................

The *note 'events_statements_history': performance-schema-events-statements-history-table. table contains the N most recent statement events that have ended per thread. Statement events are not added to the table until they have ended. When the table contains the maximum number of rows for a given thread, the oldest thread row is discarded when a new row for that thread is added. When a thread ends, all its rows are discarded.

The Performance Schema autosizes the value of N during server startup. To set the number of rows per thread explicitly, set the 'performance_schema_events_statements_history_size' system variable at server startup.

The note 'events_statements_history': performance-schema-events-statements-history-table. table has the same columns as note 'events_statements_current': performance-schema-events-statements-current-table. See *note performance-schema-events-statements-current-table::.

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_statements_history': performance-schema-events-statements-history-table. table. It removes the rows.

For more information about the relationship between the three 'events_statements_XXX' event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect statement events, see *note performance-schema-statement-tables::.

 File: manual.info.tmp, Node: performance-schema-events-statements-history-long-table, Next: performance-schema-prepared-statements-instances-table, Prev: performance-schema-events-statements-history-table, Up: performance-schema-statement-tables

25.12.6.3 The events_statements_history_long Table ..................................................

The *note 'events_statements_history_long': performance-schema-events-statements-history-long-table. table contains the N most recent statement events that have ended globally, across all threads. Statement events are not added to the table until they have ended. When the table becomes full, the oldest row is discarded when a new row is added, regardless of which thread generated either row.

The value of N is autosized at server startup. To set the table size explicitly, set the 'performance_schema_events_statements_history_long_size' system variable at server startup.

The note 'events_statements_history_long': performance-schema-events-statements-history-long-table. table has the same columns as note 'events_statements_current': performance-schema-events-statements-current-table. See *note performance-schema-events-statements-current-table::.

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_statements_history_long': performance-schema-events-statements-history-long-table. table. It removes the rows.

For more information about the relationship between the three 'events_statements_XXX' event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect statement events, see *note performance-schema-statement-tables::.

 File: manual.info.tmp, Node: performance-schema-prepared-statements-instances-table, Prev: performance-schema-events-statements-history-long-table, Up: performance-schema-statement-tables

25.12.6.4 The prepared_statements_instances Table .................................................

The Performance Schema provides instrumentation for prepared statements, for which there are two protocols:

Performance Schema prepared statement instrumentation covers both protocols. The following discussion refers to the server commands rather than the C API functions or SQL statements.

Information about prepared statements is available in the *note 'prepared_statements_instances': performance-schema-prepared-statements-instances-table. table. This table enables inspection of prepared statements used in the server and provides aggregated statistics about them. To control the size of this table, set the 'performance_schema_max_prepared_statements_instances' system variable at server startup.

Collection of prepared statement information depends on the statement instruments shown in the following table. These instruments are enabled by default. To modify them, update the *note 'setup_instruments': performance-schema-setup-instruments-table. table.

Instrument Server Command

'statement/com/Prepare' 'COM_STMT_PREPARE'

'statement/com/Execute' 'COM_STMT_EXECUTE'

'statement/sql/prepare_sql' 'SQLCOM_PREPARE'

'statement/sql/execute_sql' 'SQLCOM_EXECUTE'

The Performance Schema manages the contents of the *note 'prepared_statements_instances': performance-schema-prepared-statements-instances-table. table as follows:

The *note 'prepared_statements_instances': performance-schema-prepared-statements-instances-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. resets the statistics columns of the note 'prepared_statements_instances': performance-schema-prepared-statements-instances-table. table.

 File: manual.info.tmp, Node: performance-schema-transaction-tables, Next: performance-schema-connection-tables, Prev: performance-schema-statement-tables, Up: performance-schema-table-descriptions

25.12.7 Performance Schema Transaction Tables

The Performance Schema instruments transactions. Within the event hierarchy, wait events nest within stage events, which nest within statement events, which nest within transaction events.

These tables store transaction events:

The following sections describe the transaction event tables. There are also summary tables that aggregate information about transaction events; see *note performance-schema-transaction-summary-tables::.

For more information about the relationship between the three transaction event tables, see *note performance-schema-event-tables::.

Configuring Transaction Event Collection

To control whether to collect transaction events, set the state of the relevant instruments and consumers:

The 'transaction' instrument and the transaction consumers are disabled by default:

 mysql> SELECT *
        FROM performance_schema.setup_instruments
        WHERE NAME = 'transaction';
 +-------------+---------+-------+
 | NAME        | ENABLED | TIMED |
 +-------------+---------+-------+
 | transaction | NO      | NO    |
 +-------------+---------+-------+
 mysql> SELECT *
        FROM performance_schema.setup_consumers
        WHERE NAME LIKE 'events_transactions%';
 +----------------------------------+---------+
 | NAME                             | ENABLED |
 +----------------------------------+---------+
 | events_transactions_current      | NO      |
 | events_transactions_history      | NO      |
 | events_transactions_history_long | NO      |
 +----------------------------------+---------+

To control transaction event collection at server startup, use lines like these in your 'my.cnf' file:

To control transaction event collection at runtime, update the note 'setup_instruments': performance-schema-setup-instruments-table. and note 'setup_consumers': performance-schema-setup-consumers-table. tables:

To collect transaction events only for specific transaction event tables, enable the 'transaction' instrument but only the transaction consumers corresponding to the desired tables.

The *note 'setup_timers': performance-schema-setup-timers-table. table contains a row with a 'NAME' value of 'transaction' that indicates the unit for transaction event timing. The default unit is 'NANOSECOND':

 mysql> SELECT *
        FROM performance_schema.setup_timers
        WHERE NAME = 'transaction';
 +-------------+------------+
 | NAME        | TIMER_NAME |
 +-------------+------------+
 | transaction | NANOSECOND |
 +-------------+------------+

To change the timing unit, modify the 'TIMER_NAME' value:

 UPDATE performance_schema.setup_timers
 SET TIMER_NAME = 'MICROSECOND'
 WHERE NAME = 'transaction';

For additional information about configuring event collection, see note performance-schema-startup-configuration::, and note performance-schema-runtime-configuration::.

Transaction Boundaries

In MySQL Server, transactions start explicitly with these statements:

 START TRANSACTION | BEGIN | XA START | XA BEGIN

Transactions also start implicitly. For example, when the 'autocommit' system variable is enabled, the start of each statement starts a new transaction.

When 'autocommit' is disabled, the first statement following a committed transaction marks the start of a new transaction. Subsequent statements are part of the transaction until it is committed.

Transactions explicitly end with these statements:

 COMMIT | ROLLBACK | XA COMMIT | XA ROLLBACK

Transactions also end implicitly, by execution of DDL statements, locking statements, and server administration statements.

In the following discussion, references to note 'START TRANSACTION': commit. also apply to note 'BEGIN': commit, note 'XA START': xa-statements, and note 'XA BEGIN': xa-statements. Similarly, references to note 'COMMIT': commit. and note 'ROLLBACK': commit. apply to note 'XA COMMIT': xa-statements. and note 'XA ROLLBACK': xa-statements, respectively.

The Performance Schema defines transaction boundaries similarly to that of the server. The start and end of a transaction event closely match the corresponding state transitions in the server:

There are subtle implications to this approach:

To illustrate, consider the following scenario:

 1. SET autocommit = OFF;
 2. CREATE TABLE t1 (a INT) ENGINE = InnoDB;
 3. START TRANSACTION;                       -- Transaction 1 START
 4. INSERT INTO t1 VALUES (1), (2), (3);
 5. CREATE TABLE t2 (a INT) ENGINE = MyISAM; -- Transaction 1 COMMIT
                                             -- (implicit; DDL forces commit)
 6. INSERT INTO t2 VALUES (1), (2), (3);     -- Update nontransactional table
 7. UPDATE t2 SET a = a + 1;                 -- ... and again
 8. INSERT INTO t1 VALUES (4), (5), (6);     -- Write to transactional table
                                             -- Transaction 2 START (implicit)
 9. COMMIT;                                  -- Transaction 2 COMMIT

From the perspective of the server, Transaction 1 ends when table 't2' is created. Transaction 2 does not start until a transactional table is accessed, despite the intervening updates to nontransactional tables.

From the perspective of the Performance Schema, Transaction 2 starts when the server transitions into an active transaction state. Statements 6 and 7 are not included within the boundaries of Transaction 2, which is consistent with how the server writes transactions to the binary log.

Transaction Instrumentation

Three attributes define transactions:

To reduce complexity of the transaction instrumentation and to ensure that the collected transaction data provides complete, meaningful results, all transactions are instrumented independently of access mode, isolation level, or autocommit mode.

To selectively examine transaction history, use the attribute columns in the transaction event tables: 'ACCESS_MODE', 'ISOLATION_LEVEL', and 'AUTOCOMMIT'.

The cost of transaction instrumentation can be reduced various ways, such as enabling or disabling transaction instrumentation according to user, account, host, or thread (client connection).

Transactions and Nested Events

The parent of a transaction event is the event that initiated the transaction. For an explicitly started transaction, this includes the note 'START TRANSACTION': commit. and note 'COMMIT AND CHAIN': commit. statements. For an implicitly started transaction, it is the first statement that uses a transactional engine after the previous transaction ends.

In general, a transaction is the top-level parent to all events initiated during the transaction, including statements that explicitly end the transaction such as note 'COMMIT': commit. and note 'ROLLBACK': commit. Exceptions are statements that implicitly end a transaction, such as DDL statements, in which case the current transaction must be committed before the new statement is executed.

Transactions and Stored Programs

Transactions and stored program events are related as follows:

Transactions and Savepoints

Savepoint statements are recorded as separate statement events. Transaction events include separate counters for note 'SAVEPOINT': savepoint, note 'ROLLBACK TO SAVEPOINT': savepoint, and *note 'RELEASE SAVEPOINT': savepoint. statements issued during the transaction.

Transactions and Errors

Errors and warnings that occur within a transaction are recorded in statement events, but not in the corresponding transaction event. This includes transaction-specific errors and warnings, such as a rollback on a nontransactional table or GTID consistency errors.

 File: manual.info.tmp, Node: performance-schema-events-transactions-current-table, Next: performance-schema-events-transactions-history-table, Prev: performance-schema-transaction-tables, Up: performance-schema-transaction-tables

25.12.7.1 The events_transactions_current Table ...............................................

The *note 'events_transactions_current': performance-schema-events-transactions-current-table. table contains current transaction events. The table stores one row per thread showing the current status of the thread's most recent monitored transaction event, so there is no system variable for configuring the table size. For example:

 mysql> SELECT *
        FROM performance_schema.events_transactions_current LIMIT 1\G
 *************************** 1. row ***************************
                       THREAD_ID: 26
                        EVENT_ID: 7
                    END_EVENT_ID: NULL
                      EVENT_NAME: transaction
                           STATE: ACTIVE
                          TRX_ID: NULL
                            GTID: 3E11FA47-71CA-11E1-9E33-C80AA9429562:56
                             XID: NULL
                        XA_STATE: NULL
                          SOURCE: transaction.cc:150
                     TIMER_START: 420833537900000
                       TIMER_END: NULL
                      TIMER_WAIT: NULL
                     ACCESS_MODE: READ WRITE
                 ISOLATION_LEVEL: REPEATABLE READ
                      AUTOCOMMIT: NO
            NUMBER_OF_SAVEPOINTS: 0
 NUMBER_OF_ROLLBACK_TO_SAVEPOINT: 0
     NUMBER_OF_RELEASE_SAVEPOINT: 0
           OBJECT_INSTANCE_BEGIN: NULL
                NESTING_EVENT_ID: 6
              NESTING_EVENT_TYPE: STATEMENT

Of the tables that contain transaction event rows, note 'events_transactions_current': performance-schema-events-transactions-current-table. is the most fundamental. Other tables that contain transaction event rows are logically derived from the current events. For example, the note 'events_transactions_history': performance-schema-events-transactions-history-table. and *note 'events_transactions_history_long': performance-schema-events-transactions-history-long-table. tables are collections of the most recent transaction events that have ended, up to a maximum number of rows per thread and globally across all threads, respectively.

For more information about the relationship between the three transaction event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect transaction events, see *note performance-schema-transaction-tables::.

The *note 'events_transactions_current': performance-schema-events-transactions-current-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_transactions_current': performance-schema-events-transactions-current-table. table. It removes the rows.

 File: manual.info.tmp, Node: performance-schema-events-transactions-history-table, Next: performance-schema-events-transactions-history-long-table, Prev: performance-schema-events-transactions-current-table, Up: performance-schema-transaction-tables

25.12.7.2 The events_transactions_history Table ...............................................

The *note 'events_transactions_history': performance-schema-events-transactions-history-table. table contains the N most recent transaction events that have ended per thread. Transaction events are not added to the table until they have ended. When the table contains the maximum number of rows for a given thread, the oldest thread row is discarded when a new row for that thread is added. When a thread ends, all its rows are discarded.

The Performance Schema autosizes the value of N during server startup. To set the number of rows per thread explicitly, set the 'performance_schema_events_transactions_history_size' system variable at server startup.

The note 'events_transactions_history': performance-schema-events-transactions-history-table. table has the same columns as note 'events_transactions_current': performance-schema-events-transactions-current-table. See *note performance-schema-events-transactions-current-table::.

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_transactions_history': performance-schema-events-transactions-history-table. table. It removes the rows.

For more information about the relationship between the three transaction event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect transaction events, see *note performance-schema-transaction-tables::.

 File: manual.info.tmp, Node: performance-schema-events-transactions-history-long-table, Prev: performance-schema-events-transactions-history-table, Up: performance-schema-transaction-tables

25.12.7.3 The events_transactions_history_long Table ....................................................

The *note 'events_transactions_history_long': performance-schema-events-transactions-history-long-table. table contains the N most recent transaction events that have ended globally, across all threads. Transaction events are not added to the table until they have ended. When the table becomes full, the oldest row is discarded when a new row is added, regardless of which thread generated either row.

The Performance Schema autosizes the value of N is autosized at server startup. To set the table size explicitly, set the 'performance_schema_events_transactions_history_long_size' system variable at server startup.

The note 'events_transactions_history_long': performance-schema-events-transactions-history-long-table. table has the same columns as note 'events_transactions_current': performance-schema-events-transactions-current-table. See *note performance-schema-events-transactions-current-table::.

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'events_transactions_history_long': performance-schema-events-transactions-history-long-table. table. It removes the rows.

For more information about the relationship between the three transaction event tables, see *note performance-schema-event-tables::.

For information about configuring whether to collect transaction events, see *note performance-schema-transaction-tables::.

 File: manual.info.tmp, Node: performance-schema-connection-tables, Next: performance-schema-connection-attribute-tables, Prev: performance-schema-transaction-tables, Up: performance-schema-table-descriptions

25.12.8 Performance Schema Connection Tables

When a client connects to the MySQL server, it does so under a particular user name and from a particular host. The Performance Schema provides statistics about these connections, tracking them per account (user and host combination) as well as separately per user name and host name, using these tables:

The meaning of 'account' in the connection tables is similar to its meaning in the MySQL grant tables in the 'mysql' system database, in the sense that the term refers to a combination of user and host values. They differ in that, for grant tables, the host part of an account can be a pattern, whereas for Performance Schema tables, the host value is always a specific nonpattern host name.

Each connection table has 'CURRENT_CONNECTIONS' and 'TOTAL_CONNECTIONS' columns to track the current and total number of connections per 'tracking value' on which its statistics are based. The tables differ in what they use for the tracking value. The note 'accounts': performance-schema-accounts-table. table has 'USER' and 'HOST' columns to track connections per user and host combination. The note 'users': performance-schema-users-table. and *note 'hosts': performance-schema-hosts-table. tables have a 'USER' and 'HOST' column, respectively, to track connections per user name and host name.

The Performance Schema also counts internal threads and threads for user sessions that failed to authenticate, using rows with 'USER' and 'HOST' column values of 'NULL'.

Suppose that clients named 'user1' and 'user2' each connect one time from 'hosta' and 'hostb'. The Performance Schema tracks the connections as follows:

When a client connects, the Performance Schema determines which row in each connection table applies, using the tracking value appropriate to each table. If there is no such row, one is added. Then the Performance Schema increments by one the 'CURRENT_CONNECTIONS' and 'TOTAL_CONNECTIONS' columns in that row.

When a client disconnects, the Performance Schema decrements by one the 'CURRENT_CONNECTIONS' column in the row and leaves the 'TOTAL_CONNECTIONS' column unchanged.

*note 'TRUNCATE TABLE': truncate-table. is permitted for connection tables. It has these effects:

The Performance Schema maintains summary tables that aggregate connection statistics for various event types by account, host, or user. These tables have '_summary_by_account', '_summary_by_host', or '_summary_by_user' in the name. To identify them, use this query:

 mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'performance_schema'
        AND TABLE_NAME REGEXP '_summary_by_(account|host|user)'
        ORDER BY TABLE_NAME;
 +------------------------------------------------------+
 | TABLE_NAME                                           |
 +------------------------------------------------------+
 | events_stages_summary_by_account_by_event_name       |
 | events_stages_summary_by_host_by_event_name          |
 | events_stages_summary_by_user_by_event_name          |
 | events_statements_summary_by_account_by_event_name   |
 | events_statements_summary_by_host_by_event_name      |
 | events_statements_summary_by_user_by_event_name      |
 | events_transactions_summary_by_account_by_event_name |
 | events_transactions_summary_by_host_by_event_name    |
 | events_transactions_summary_by_user_by_event_name    |
 | events_waits_summary_by_account_by_event_name        |
 | events_waits_summary_by_host_by_event_name           |
 | events_waits_summary_by_user_by_event_name           |
 | memory_summary_by_account_by_event_name              |
 | memory_summary_by_host_by_event_name                 |
 | memory_summary_by_user_by_event_name                 |
 +------------------------------------------------------+

For details about individual connection summary tables, consult the section that describes tables for the summarized event type:

*note 'TRUNCATE TABLE': truncate-table. is permitted for connection summary tables. It removes rows for accounts, hosts, or users with no connections, and resets the summary columns to zero for the remaining rows. In addition, each summary table that is aggregated by account, host, user, or thread is implicitly truncated by truncation of the connection table on which it depends. The following table describes the relationship between connection table truncation and implicitly truncated tables.

Implicit Effects of Connection Table Truncation

Truncated Connection Table Implicitly Truncated Summary Tables

'accounts' Tables with names containing '_summary_by_account', '_summary_by_thread'

'hosts' Tables with names containing '_summary_by_account', '_summary_by_host', '_summary_by_thread'

'users' Tables with names containing '_summary_by_account', '_summary_by_user', '_summary_by_thread'

Truncating a '_summary_global' summary table also implicitly truncates its corresponding connection and thread summary tables. For example, truncating *note 'events_waits_summary_global_by_event_name': performance-schema-wait-summary-tables. implicitly truncates the wait event summary tables that are aggregated by account, host, user, or thread.

 File: manual.info.tmp, Node: performance-schema-accounts-table, Next: performance-schema-hosts-table, Prev: performance-schema-connection-tables, Up: performance-schema-connection-tables

25.12.8.1 The accounts Table ............................

The *note 'accounts': performance-schema-accounts-table. table contains a row for each account that has connected to the MySQL server. For each account, the table counts the current and total number of connections. The table size is autosized at server startup. To set the table size explicitly, set the 'performance_schema_accounts_size' system variable at server startup. To disable account statistics, set this variable to 0.

The note 'accounts': performance-schema-accounts-table. table has the following columns. For a description of how the Performance Schema maintains rows in this table, including the effect of note 'TRUNCATE TABLE': truncate-table, see *note performance-schema-connection-tables::.

 File: manual.info.tmp, Node: performance-schema-hosts-table, Next: performance-schema-users-table, Prev: performance-schema-accounts-table, Up: performance-schema-connection-tables

25.12.8.2 The hosts Table .........................

The *note 'hosts': performance-schema-hosts-table. table contains a row for each host from which clients have connected to the MySQL server. For each host name, the table counts the current and total number of connections. The table size is autosized at server startup. To set the table size explicitly, set the 'performance_schema_hosts_size' system variable at server startup. To disable host statistics, set this variable to 0.

The note 'hosts': performance-schema-hosts-table. table has the following columns. For a description of how the Performance Schema maintains rows in this table, including the effect of note 'TRUNCATE TABLE': truncate-table, see *note performance-schema-connection-tables::.

 File: manual.info.tmp, Node: performance-schema-users-table, Prev: performance-schema-hosts-table, Up: performance-schema-connection-tables

25.12.8.3 The users Table .........................

The *note 'users': performance-schema-users-table. table contains a row for each user who has connected to the MySQL server. For each user name, the table counts the current and total number of connections. The table size is autosized at server startup. To set the table size explicitly, set the 'performance_schema_users_size' system variable at server startup. To disable user statistics, set this variable to 0.

The note 'users': performance-schema-users-table. table has the following columns. For a description of how the Performance Schema maintains rows in this table, including the effect of note 'TRUNCATE TABLE': truncate-table, see *note performance-schema-connection-tables::.

 File: manual.info.tmp, Node: performance-schema-connection-attribute-tables, Next: performance-schema-user-variable-tables, Prev: performance-schema-connection-tables, Up: performance-schema-table-descriptions

25.12.9 Performance Schema Connection Attribute Tables

Connection attributes are key-value pairs that application programs can pass to the server at connect time. For applications based on the C API implemented by the 'libmysqlclient' client library, the 'mysql_options()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-options.html) and 'mysql_options4()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-options4.html) functions define the connection attribute set. Other MySQL Connectors may provide their own attribute-definition methods.

These Performance Schema tables expose attribute information:

Attribute names that begin with an underscore ('_') are reserved for internal use and should not be created by application programs. This convention permits new attributes to be introduced by MySQL without colliding with application attributes, and enables application programs to define their own attributes that do not collide with internal attributes.

Available Connection Atrributes

The set of connection attributes visible within a given connection varies depending on factors such as your platform, MySQL Connector used to establish the connection, or client program.

The 'libmysqlclient' client library sets these attributes:

Other MySQL Connectors may define their own connection attributes.

MySQL Connector/J defines these attributes:

MySQL Connector/NET defines these attributes:

PHP defines attributes that depend on how it was compiled:

Many MySQL client programs set a 'program_name' attribute with a value equal to the client name. For example, note 'mysqladmin': mysqladmin. and note 'mysqldump': mysqldump. set 'program_name' to 'mysqladmin' and 'mysqldump', respectively.

Some MySQL client programs define additional attributes:

Connection Atrribute Limits

There are limits on the amount of connection attribute data transmitted from client to server:

For connections initiated using the C API, the 'libmysqlclient' library imposes a limit of 64KB on the aggregate size of connection attribute data on the client side: Calls to 'mysql_options()' (https://dev.mysql.com/doc/c-api/5.7/en/mysql-options.html) that cause this limit to be exceeded produce a 'CR_INVALID_PARAMETER_NO' (https://dev.mysql.com/doc/mysql-errors/5.7/en/client-error-reference.html#error_cr_invalid_parameter_no) error. Other MySQL Connectors may impose their own client-side limits on how much connection attribute data can be transmitted to the server.

On the server side, these size checks on connection attribute data occur:

 File: manual.info.tmp, Node: performance-schema-session-account-connect-attrs-table, Next: performance-schema-session-connect-attrs-table, Prev: performance-schema-connection-attribute-tables, Up: performance-schema-connection-attribute-tables

25.12.9.1 The session_account_connect_attrs Table .................................................

Application programs can provide key-value connection attributes to be passed to the server at connect time. For descriptions of common attributes, see *note performance-schema-connection-attribute-tables::.

The note 'session_account_connect_attrs': performance-schema-session-account-connect-attrs-table. table contains connection attributes only for the current session, and other sessions associated with the session account. To see connection attributes for all sessions, use the note 'session_connect_attrs': performance-schema-session-connect-attrs-table. table.

The *note 'session_account_connect_attrs': performance-schema-session-account-connect-attrs-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'session_account_connect_attrs': performance-schema-session-account-connect-attrs-table. table.

 File: manual.info.tmp, Node: performance-schema-session-connect-attrs-table, Prev: performance-schema-session-account-connect-attrs-table, Up: performance-schema-connection-attribute-tables

25.12.9.2 The session_connect_attrs Table .........................................

Application programs can provide key-value connection attributes to be passed to the server at connect time. For descriptions of common attributes, see *note performance-schema-connection-attribute-tables::.

The note 'session_connect_attrs': performance-schema-session-connect-attrs-table. table contains connection attributes for all sessions. To see connection attributes only for the current session, and other sessions associated with the session account, use the note 'session_account_connect_attrs': performance-schema-session-account-connect-attrs-table. table.

The *note 'session_connect_attrs': performance-schema-session-connect-attrs-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'session_connect_attrs': performance-schema-session-connect-attrs-table. table.

 File: manual.info.tmp, Node: performance-schema-user-variable-tables, Next: performance-schema-replication-tables, Prev: performance-schema-connection-attribute-tables, Up: performance-schema-table-descriptions

25.12.10 Performance Schema User-Defined Variable Tables

The Performance Schema provides a note 'user_variables_by_thread': performance-schema-user-variable-tables. table that exposes user-defined variables. These are variables defined within a specific session and include a '@' character preceding the name; see note user-variables::.

The *note 'user_variables_by_thread': performance-schema-user-variable-tables. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'user_variables_by_thread': performance-schema-user-variable-tables. table.

 File: manual.info.tmp, Node: performance-schema-replication-tables, Next: performance-schema-lock-tables, Prev: performance-schema-user-variable-tables, Up: performance-schema-table-descriptions

25.12.11 Performance Schema Replication Tables

The Performance Schema provides tables that expose replication information. This is similar to the information available from the *note 'SHOW SLAVE STATUS': show-slave-status. statement, but representation in table form is more accessible and has usability benefits:

Replication Table Descriptions

The Performance Schema provides the following replication-related tables:

The following sections describe each replication table in more detail, including the correspondence between the columns produced by *note 'SHOW SLAVE STATUS': show-slave-status. and the replication table columns in which the same information appears.

The remainder of this introduction to the replication tables describes how the Performance Schema populates them and which fields from *note 'SHOW SLAVE STATUS': show-slave-status. are not represented in the tables.

Replication Table Life Cycle

The Performance Schema populates the replication tables as follows:

**note 'SHOW SLAVE STATUS': show-slave-status. Information Not In the Replication Tables*

The information in the Performance Schema replication tables differs somewhat from the information available from note 'SHOW SLAVE STATUS': show-slave-status. because the tables are oriented toward use of global transaction identifiers (GTIDs), not file names and positions, and they represent server UUID values, not server ID values. Due to these differences, several note 'SHOW SLAVE STATUS': show-slave-status. columns are not preserved in the Performance Schema replication tables, or are represented a different way:

Status Variables Moved to Replication Tables

As of MySQL version 5.7.5, the following status variables (previously monitored using *note 'SHOW STATUS': show-status.) were moved to the Perfomance Schema replication tables:

These status variables are now only relevant when a single replication channel is being used because they only report the status of the default replication channel. When multiple replication channels exist, use the Performance Schema replication tables described in this section, which report these variables for each existing replication channel.

Replication Channels

The first column of the replication Performance Schema tables is 'CHANNEL_NAME'. This enables the tables to be viewed per replication channel. In a non-multisource replication setup there is a single default replication channel. When you are using multiple replication channels on a replica, you can filter the tables per replication channel to monitor a specific replication channel. See note replication-channels:: and note replication-multi-source-monitoring:: for more information.

 File: manual.info.tmp, Node: performance-schema-replication-connection-configuration-table, Next: performance-schema-replication-connection-status-table, Prev: performance-schema-replication-tables, Up: performance-schema-replication-tables

25.12.11.1 The replication_connection_configuration Table .........................................................

This table shows the configuration parameters used by the replica for connecting to the source. Parameters stored in the table can be changed at runtime with the *note 'CHANGE MASTER TO': change-master-to. statement, as indicated in the column descriptions.

Compared to the note 'replication_connection_status': performance-schema-replication-connection-status-table. table, note 'replication_connection_configuration': performance-schema-replication-connection-configuration-table. changes less frequently. It contains values that define how the replica connects to the source and that remain constant during the connection, whereas *note 'replication_connection_status': performance-schema-replication-connection-status-table. contains values that change during the connection.

The note 'replication_connection_configuration': performance-schema-replication-connection-configuration-table. table has the following columns. The column descriptions indicate the corresponding 'CHANGE MASTER TO' options from which the column values are taken, and the table given later in this section shows the correspondence between note 'replication_connection_configuration': performance-schema-replication-connection-configuration-table. columns and *note 'SHOW SLAVE STATUS': show-slave-status. columns.

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'replication_connection_configuration': performance-schema-replication-connection-configuration-table. table.

The following table shows the correspondence between note 'replication_connection_configuration': performance-schema-replication-connection-configuration-table. columns and note 'SHOW SLAVE STATUS': show-slave-status. columns.

'replication_connection_configuration' 'SHOW SLAVE STATUS' Column Column

'CHANNEL_NAME' 'Channel_name'

'HOST' 'Master_Host'

'PORT' 'Master_Port'

'USER' 'Master_User'

'NETWORK_INTERFACE' 'Master_Bind'

'AUTO_POSITION' 'Auto_Position'

'SSL_ALLOWED' 'Master_SSL_Allowed'

'SSL_CA_FILE' 'Master_SSL_CA_File'

'SSL_CA_PATH' 'Master_SSL_CA_Path'

'SSL_CERTIFICATE' 'Master_SSL_Cert'

'SSL_CIPHER' 'Master_SSL_Cipher'

'SSL_KEY' 'Master_SSL_Key'

'SSL_VERIFY_SERVER_CERTIFICATE' 'Master_SSL_Verify_Server_Cert'

'SSL_CRL_FILE' 'Master_SSL_Crl'

'SSL_CRL_PATH' 'Master_SSL_Crlpath'

'CONNECTION_RETRY_INTERVAL' 'Connect_Retry'

'CONNECTION_RETRY_COUNT' 'Master_Retry_Count'

'HEARTBEAT_INTERVAL' None

'TLS_VERSION' 'Master_TLS_Version'

 File: manual.info.tmp, Node: performance-schema-replication-connection-status-table, Next: performance-schema-replication-applier-configuration-table, Prev: performance-schema-replication-connection-configuration-table, Up: performance-schema-replication-tables

25.12.11.2 The replication_connection_status Table ..................................................

This table shows the current status of the replication I/O thread that handles the replica's connection to the source.

Compared to the note 'replication_connection_configuration': performance-schema-replication-connection-configuration-table. table, note 'replication_connection_status': performance-schema-replication-connection-status-table. changes more frequently. It contains values that change during the connection, whereas *note 'replication_connection_configuration': performance-schema-replication-connection-configuration-table. contains values which define how the replica connects to the source and that remain constant during the connection.

The *note 'replication_connection_status': performance-schema-replication-connection-status-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'replication_connection_status': performance-schema-replication-connection-status-table. table.

The following table shows the correspondence between note 'replication_connection_status': performance-schema-replication-connection-status-table. columns and note 'SHOW SLAVE STATUS': show-slave-status. columns.

'replication_connection_status' Column 'SHOW SLAVE STATUS' Column

'SOURCE_UUID' 'Master_UUID'

'THREAD_ID' None

'SERVICE_STATE' 'Slave_IO_Running'

'RECEIVED_TRANSACTION_SET' 'Retrieved_Gtid_Set'

'LAST_ERROR_NUMBER' 'Last_IO_Errno'

'LAST_ERROR_MESSAGE' 'Last_IO_Error'

'LAST_ERROR_TIMESTAMP' 'Last_IO_Error_Timestamp'

 File: manual.info.tmp, Node: performance-schema-replication-applier-configuration-table, Next: performance-schema-replication-applier-status-table, Prev: performance-schema-replication-connection-status-table, Up: performance-schema-replication-tables

25.12.11.3 The replication_applier_configuration Table ......................................................

This table shows the configuration parameters that affect transactions applied by the replica. Parameters stored in the table can be changed at runtime with the *note 'CHANGE MASTER TO': change-master-to. statement, as indicated in the column descriptions.

The *note 'replication_applier_configuration': performance-schema-replication-applier-configuration-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'replication_applier_configuration': performance-schema-replication-applier-configuration-table. table.

The following table shows the correspondence between note 'replication_applier_configuration ': performance-schema-replication-applier-configuration-table. columns and note 'SHOW SLAVE STATUS': show-slave-status. columns.

'replication_applier_configuration' 'SHOW SLAVE STATUS' Column Column

'DESIRED_DELAY' 'SQL_Delay'

 File: manual.info.tmp, Node: performance-schema-replication-applier-status-table, Next: performance-schema-replication-applier-status-by-coordinator-table, Prev: performance-schema-replication-applier-configuration-table, Up: performance-schema-replication-tables

25.12.11.4 The replication_applier_status Table ...............................................

This table shows the current general transaction execution status on the replica. The table provides information about general aspects of transaction applier status that are not specific to any thread involved. Thread-specific status information is available in the note 'replication_applier_status_by_coordinator': performance-schema-replication-applier-status-by-coordinator-table. table (and note 'replication_applier_status_by_worker': performance-schema-replication-applier-status-by-worker-table. if the replica is multithreaded).

The *note 'replication_applier_status': performance-schema-replication-applier-status-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'replication_applier_status': performance-schema-replication-applier-status-table. table.

The following table shows the correspondence between note 'replication_applier_status': performance-schema-replication-applier-status-table. columns and note 'SHOW SLAVE STATUS': show-slave-status. columns.

'replication_applier_status' Column 'SHOW SLAVE STATUS' Column

'SERVICE_STATE' None

'REMAINING_DELAY' 'SQL_Remaining_Delay'

 File: manual.info.tmp, Node: performance-schema-replication-applier-status-by-coordinator-table, Next: performance-schema-replication-applier-status-by-worker-table, Prev: performance-schema-replication-applier-status-table, Up: performance-schema-replication-tables

25.12.11.5 The replication_applier_status_by_coordinator Table ..............................................................

For a multithreaded replica, the replica uses multiple worker threads and a coordinator thread to manage them, and this table shows the status of the coordinator thread. For a single-threaded replica, this table is empty. For a multithreaded replica, the *note 'replication_applier_status_by_worker': performance-schema-replication-applier-status-by-worker-table. table shows the status of the worker threads.

The *note 'replication_applier_status_by_coordinator': performance-schema-replication-applier-status-by-coordinator-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'replication_applier_status_by_coordinator': performance-schema-replication-applier-status-by-coordinator-table. table.

The following table shows the correspondence between note 'replication_applier_status_by_coordinator': performance-schema-replication-applier-status-by-coordinator-table. columns and note 'SHOW SLAVE STATUS': show-slave-status. columns.

'replication_applier_status_by_coordinator' 'SHOW SLAVE STATUS' Column Column

'THREAD_ID' None

'SERVICE_STATE' 'Slave_SQL_Running'

'LAST_ERROR_NUMBER' 'Last_SQL_Errno'

'LAST_ERROR_MESSAGE' 'Last_SQL_Error'

'LAST_ERROR_TIMESTAMP' 'Last_SQL_Error_Timestamp'

 File: manual.info.tmp, Node: performance-schema-replication-applier-status-by-worker-table, Next: performance-schema-replication-group-member-stats-table, Prev: performance-schema-replication-applier-status-by-coordinator-table, Up: performance-schema-replication-tables

25.12.11.6 The replication_applier_status_by_worker Table .........................................................

If the replica is not multithreaded, this table shows the status of the applier thread. Otherwise, the replica uses multiple worker threads and a coordinator thread to manage them, and this table shows the status of the worker threads. For a multithreaded replica, the *note 'replication_applier_status_by_coordinator': performance-schema-replication-applier-status-by-coordinator-table. table shows the status of the coordinator thread.

The 'replication_applier_status_by_worker' table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'replication_applier_status_by_worker': performance-schema-replication-applier-status-by-worker-table. table.

The following table shows the correspondence between 'replication_applier_status_by_worker' columns and *note 'SHOW SLAVE STATUS': show-slave-status. columns.

'replication_applier_status_by_worker' 'SHOW SLAVE STATUS' Column Column

'WORKER_ID' None

'THREAD_ID' None

'SERVICE_STATE' None

'LAST_SEEN_TRANSACTION' None

'LAST_ERROR_NUMBER' 'Last_SQL_Errno'

'LAST_ERROR_MESSAGE' 'Last_SQL_Error'

'LAST_ERROR_TIMESTAMP' 'Last_SQL_Error_Timestamp'

 File: manual.info.tmp, Node: performance-schema-replication-group-member-stats-table, Next: performance-schema-replication-group-members-table, Prev: performance-schema-replication-applier-status-by-worker-table, Up: performance-schema-replication-tables

25.12.11.7 The replication_group_member_stats Table ...................................................

This table shows statistical information for MySQL Group Replication members. It is populated only when Group Replication is running.

The 'replication_group_member_stats' table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'replication_group_member_stats': performance-schema-replication-group-member-stats-table. table.

 File: manual.info.tmp, Node: performance-schema-replication-group-members-table, Prev: performance-schema-replication-group-member-stats-table, Up: performance-schema-replication-tables

25.12.11.8 The replication_group_members Table ..............................................

This table shows network and status information for replication group members. The network addresses shown are the addresses used to connect clients to the group, and should not be confused with the member's internal group communication address specified by 'group_replication_local_address'.

The 'replication_group_members' table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'replication_group_members': performance-schema-replication-group-members-table. table.

 File: manual.info.tmp, Node: performance-schema-lock-tables, Next: performance-schema-system-variable-tables, Prev: performance-schema-replication-tables, Up: performance-schema-table-descriptions

25.12.12 Performance Schema Lock Tables

The Performance Schema exposes lock information through these tables:

The following sections describe these tables in more detail.

 File: manual.info.tmp, Node: performance-schema-metadata-locks-table, Next: performance-schema-table-handles-table, Prev: performance-schema-lock-tables, Up: performance-schema-lock-tables

25.12.12.1 The metadata_locks Table ...................................

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency; see note metadata-locking::. Metadata locking applies not just to tables, but also to schemas, stored programs (procedures, functions, triggers, scheduled events), tablespaces, user locks acquired with the 'GET_LOCK()' function (see note locking-functions::), and locks acquired with the locking service described in *note locking-service::.

The Performance Schema exposes metadata lock information through the *note 'metadata_locks': performance-schema-metadata-locks-table. table:

This information enables you to understand metadata lock dependencies between sessions. You can see not only which lock a session is waiting for, but which session currently holds that lock.

The *note 'metadata_locks': performance-schema-metadata-locks-table. table is read only and cannot be updated. It is autosized by default; to configure the table size, set the 'performance_schema_max_metadata_locks' system variable at server startup.

Metadata lock instrumentation uses the 'wait/lock/metadata/sql/mdl' instrument, which is disabled by default.

To control metadata lock instrumentation state at server startup, use lines like these in your 'my.cnf' file:

To control metadata lock instrumentation state at runtime, update the *note 'setup_instruments': performance-schema-setup-instruments-table. table:

The Performance Schema maintains *note 'metadata_locks': performance-schema-metadata-locks-table. table content as follows, using the 'LOCK_STATUS' column to indicate the status of each lock:

The *note 'metadata_locks': performance-schema-metadata-locks-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'metadata_locks': performance-schema-metadata-locks-table. table.

 File: manual.info.tmp, Node: performance-schema-table-handles-table, Prev: performance-schema-metadata-locks-table, Up: performance-schema-lock-tables

25.12.12.2 The table_handles Table ..................................

The Performance Schema exposes table lock information through the note 'table_handles': performance-schema-table-handles-table. table to show the table locks currently in effect for each opened table handle. note 'table_handles': performance-schema-table-handles-table. reports what is recorded by the table lock instrumentation. This information shows which table handles the server has open, how they are locked, and by which sessions.

The *note 'table_handles': performance-schema-table-handles-table. table is read only and cannot be updated. It is autosized by default; to configure the table size, set the 'performance_schema_max_table_handles' system variable at server startup.

Table lock instrumentation uses the 'wait/lock/table/sql/handler' instrument, which is enabled by default.

To control table lock instrumentation state at server startup, use lines like these in your 'my.cnf' file:

To control table lock instrumentation state at runtime, update the *note 'setup_instruments': performance-schema-setup-instruments-table. table:

The *note 'table_handles': performance-schema-table-handles-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'table_handles': performance-schema-table-handles-table. table.

 File: manual.info.tmp, Node: performance-schema-system-variable-tables, Next: performance-schema-status-variable-tables, Prev: performance-schema-lock-tables, Up: performance-schema-table-descriptions

25.12.13 Performance Schema System Variable Tables

Note:

The value of the 'show_compatibility_56' system variable affects the information available from the tables described here. For details, see the description of that variable in *note server-system-variables::.

The MySQL server maintains many system variables that indicate how it is configured (see *note server-system-variables::). System variable information is available in these Performance Schema tables:

The session variable tables (note 'session_variables': performance-schema-system-variable-tables, note 'variables_by_thread': performance-schema-system-variable-tables.) contain information only for active sessions, not terminated sessions.

The note 'global_variables': performance-schema-system-variable-tables. and note 'session_variables': performance-schema-system-variable-tables. tables have these columns:

The *note 'variables_by_thread': performance-schema-system-variable-tables. table has these columns:

The *note 'variables_by_thread': performance-schema-system-variable-tables. table contains system variable information only about foreground threads. If not all threads are instrumented by the Performance Schema, this table may miss some rows. In this case, the 'Performance_schema_thread_instances_lost' status variable is greater than zero.

*note 'TRUNCATE TABLE': truncate-table. is not supported for Performance Schema system variable tables.

 File: manual.info.tmp, Node: performance-schema-status-variable-tables, Next: performance-schema-summary-tables, Prev: performance-schema-system-variable-tables, Up: performance-schema-table-descriptions

25.12.14 Performance Schema Status Variable Tables

Note:

The value of the 'show_compatibility_56' system variable affects the information available from the tables described here. For details, see the description of that variable in *note server-system-variables::.

The MySQL server maintains many status variables that provide information about its operation (see *note server-status-variables::). Status variable information is available in these Performance Schema tables:

There are also summary tables that provide status variable information aggregated by account, host name, and user name. See *note performance-schema-status-variable-summary-tables::.

The session variable tables (note 'session_status': performance-schema-status-variable-tables, note 'status_by_thread': performance-schema-status-variable-tables.) contain information only for active sessions, not terminated sessions.

The Performance Schema collects statistics for global status variables only for threads for which the 'INSTRUMENTED' value is 'YES' in the *note 'threads': performance-schema-threads-table. table. Statistics for session status variables are always collected, regardless of the 'INSTRUMENTED' value.

The Performance Schema does not collect statistics for 'Com_XXX' status variables in the status variable tables. To obtain global and per-session statement execution counts, use the note 'events_statements_summary_global_by_event_name': performance-schema-statement-summary-tables. and note 'events_statements_summary_by_thread_by_event_name': performance-schema-statement-summary-tables. tables, respectively. For example:

 SELECT EVENT_NAME, COUNT_STAR
 FROM performance_schema.events_statements_summary_global_by_event_name
 WHERE EVENT_NAME LIKE 'statement/sql/%';

The note 'global_status': performance-schema-status-variable-tables. and note 'session_status': performance-schema-status-variable-tables. tables have these columns:

The *note 'status_by_thread': performance-schema-status-variable-tables. table contains the status of each active thread. It has these columns:

The *note 'status_by_thread': performance-schema-status-variable-tables. table contains status variable information only about foreground threads. If the 'performance_schema_max_thread_instances' system variable is not autoscaled (signified by a value of −1) and the maximum permitted number of instrumented thread objects is not greater than the number of background threads, the table is empty.

The Performance Schema supports *note 'TRUNCATE TABLE': truncate-table. for status variable tables as follows:

'FLUSH STATUS' adds the session status from all active sessions to the global status variables, resets the status of all active sessions, and resets account, host, and user status values aggregated from disconnected sessions.

 File: manual.info.tmp, Node: performance-schema-summary-tables, Next: performance-schema-miscellaneous-tables, Prev: performance-schema-status-variable-tables, Up: performance-schema-table-descriptions

25.12.15 Performance Schema Summary Tables

Summary tables provide aggregated information for terminated events over time. The tables in this group summarize event data in different ways.

Each summary table has grouping columns that determine how to group the data to be aggregated, and summary columns that contain the aggregated values. Tables that summarize events in similar ways often have similar sets of summary columns and differ only in the grouping columns used to determine how events are aggregated.

Summary tables can be truncated with *note 'TRUNCATE TABLE': truncate-table. Generally, the effect is to reset the summary columns to 0 or 'NULL', not to remove rows. This enables you to clear collected values and restart aggregation. That might be useful, for example, after you have made a runtime configuration change. Exceptions to this truncation behavior are noted in individual summary table sections.

Wait Event Summaries

Performance Schema Wait Event Summary Tables

Table Name Description

*note 'events_waits_summary_by_account_by_event_name': performance-schema-wait-summary-tables.Wait events per account and event name

*note 'events_waits_summary_by_host_by_event_name': performance-schema-wait-summary-tables.Wait events per host name and event name

*note 'events_waits_summary_by_instance': performance-schema-wait-summary-tables.Wait events per instance

*note 'events_waits_summary_by_thread_by_event_name': performance-schema-wait-summary-tables.Wait events per thread and event name

*note 'events_waits_summary_by_user_by_event_name': performance-schema-wait-summary-tables.Wait events per user name and event name

*note 'events_waits_summary_global_by_event_name': performance-schema-wait-summary-tables.Wait events per event name

Stage Summaries

Performance Schema Stage Event Summary Tables

Table Name Description

*note 'events_stages_summary_by_account_by_event_name': performance-schema-stage-summary-tables.Stage events per account and event name

*note 'events_stages_summary_by_host_by_event_name': performance-schema-stage-summary-tables.Stage events per host name and event name

*note 'events_stages_summary_by_thread_by_event_name': performance-schema-stage-summary-tables.Stage waits per thread and event name

*note 'events_stages_summary_by_user_by_event_name': performance-schema-stage-summary-tables.Stage events per user name and event name

*note 'events_stages_summary_global_by_event_name': performance-schema-stage-summary-tables.Stage waits per event name

Statement Summaries

Performance Schema Statement Event Summary Tables

Table Name Description

*note 'events_statements_summary_by_account_by_event_name': performance-schema-statement-summary-tables.Statement events per account and event name

*note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables.Statement events per schema and digest value

*note 'events_statements_summary_by_host_by_event_name': performance-schema-statement-summary-tables.Statement events per host name and event name

*note 'events_statements_summary_by_program': performance-schema-statement-summary-tables.Statement events per stored program

*note 'events_statements_summary_by_thread_by_event_name': performance-schema-statement-summary-tables.Statement events per thread and event name

*note 'events_statements_summary_by_user_by_event_name': performance-schema-statement-summary-tables.Statement events per user name and event name

*note 'events_statements_summary_global_by_event_name': performance-schema-statement-summary-tables.Statement events per event name

*note 'prepared_statements_instances': performance-schema-prepared-statements-instances-table.Prepared statement instances and statistics

Transaction Summaries

Performance Schema Transaction Event Summary Tables

Table Name Description

*note 'events_transactions_summary_by_account_by_event_name': performance-schema-transaction-summary-tables.Transaction events per account and event name

*note 'events_transactions_summary_by_host_by_event_name': performance-schema-transaction-summary-tables.Transaction events per host name and event name

*note 'events_transactions_summary_by_thread_by_event_name': performance-schema-transaction-summary-tables.Transaction events per thread and event name

*note 'events_transactions_summary_by_user_by_event_name': performance-schema-transaction-summary-tables.Transaction events per user name and event name

*note 'events_transactions_summary_global_by_event_name': performance-schema-transaction-summary-tables.Transaction events per event name

Object Wait Summaries

Performance Schema Object Event Summary Tables

Table Name Description

*note 'objects_summary_global_by_type': performance-schema-objects-summary-global-by-type-table.Object summaries

File I/O Summaries

Performance Schema File I/O Event Summary Tables

Table Name Description

*note 'file_summary_by_event_name': performance-schema-file-summary-tables.File events per event name

*note 'file_summary_by_instance': performance-schema-file-summary-tables.File events per file instance

Table I/O and Lock Wait Summaries

Performance Schema Table I/O and Lock Wait Event Summary Tables

Table Name Description

*note 'table_io_waits_summary_by_index_usage': performance-schema-table-io-waits-summary-by-index-usage-table.Table I/O waits per index

*note 'table_io_waits_summary_by_table': performance-schema-table-io-waits-summary-by-table-table.Table I/O waits per table

*note 'table_lock_waits_summary_by_table': performance-schema-table-lock-waits-summary-by-table-table.Table lock waits per table

Socket Summaries

Performance Schema Socket Event Summary Tables

Table Name Description

*note 'socket_summary_by_event_name': performance-schema-socket-summary-tables.Socket waits and I/O per event name

*note 'socket_summary_by_instance': performance-schema-socket-summary-tables.Socket waits and I/O per instance

Memory Summaries

Performance Schema Memory Operation Summary Tables

Table Name Description

*note 'memory_summary_by_account_by_event_name': performance-schema-memory-summary-tables.Memory operations per account and event name

*note 'memory_summary_by_host_by_event_name': performance-schema-memory-summary-tables.Memory operations per host and event name

*note 'memory_summary_by_thread_by_event_name': performance-schema-memory-summary-tables.Memory operations per thread and event name

*note 'memory_summary_by_user_by_event_name': performance-schema-memory-summary-tables.Memory operations per user and event name

*note 'memory_summary_global_by_event_name': performance-schema-memory-summary-tables.Memory operations globally per event name

Status Variable Summaries

Performance Schema Error Status Variable Summary Tables

Table Name Description

*note 'status_by_account': performance-schema-status-variable-summary-tables.Session status variables per account

*note 'status_by_host': performance-schema-status-variable-summary-tables.Session status variables per host name

*note 'status_by_user': performance-schema-status-variable-summary-tables.Session status variables per user name

 File: manual.info.tmp, Node: performance-schema-wait-summary-tables, Next: performance-schema-stage-summary-tables, Prev: performance-schema-summary-tables, Up: performance-schema-summary-tables

25.12.15.1 Wait Event Summary Tables ....................................

The Performance Schema maintains tables for collecting current and recent wait events, and aggregates that information in summary tables. *note performance-schema-wait-tables:: describes the events on which wait summaries are based. See that discussion for information about the content of wait events, the current and recent wait event tables, and how to control wait event collection, which is disabled by default.

Example wait event summary information:

 mysql> SELECT *
        FROM performance_schema.events_waits_summary_global_by_event_name\G
 ...
 *************************** 6. row ***************************
     EVENT_NAME: wait/synch/mutex/sql/BINARY_LOG::LOCK_index
     COUNT_STAR: 8
 SUM_TIMER_WAIT: 2119302
 MIN_TIMER_WAIT: 196092
 AVG_TIMER_WAIT: 264912
 MAX_TIMER_WAIT: 569421
 ...
 *************************** 9. row ***************************
     EVENT_NAME: wait/synch/mutex/sql/hash_filo::lock
     COUNT_STAR: 69
 SUM_TIMER_WAIT: 16848828
 MIN_TIMER_WAIT: 0
 AVG_TIMER_WAIT: 244185
 MAX_TIMER_WAIT: 735345
 ...

Each wait event 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 *note 'setup_instruments': performance-schema-setup-instruments-table. table:

Each wait event summary table has these summary columns containing aggregated values:

*note 'TRUNCATE TABLE': truncate-table. is permitted for wait summary tables. It has these effects:

In addition, each wait 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 note 'events_waits_summary_global_by_event_name': performance-schema-wait-summary-tables. For details, see note performance-schema-connection-tables::.

 File: manual.info.tmp, Node: performance-schema-stage-summary-tables, Next: performance-schema-statement-summary-tables, Prev: performance-schema-wait-summary-tables, Up: performance-schema-summary-tables

25.12.15.2 Stage Summary Tables ...............................

The Performance Schema maintains tables for collecting current and recent stage events, and aggregates that information in summary tables. *note performance-schema-stage-tables:: describes the events on which stage summaries are based. See that discussion for information about the content of stage events, the current and historical stage event tables, and how to control stage event collection, which is disabled by default.

Example stage event summary information:

 mysql> SELECT *
        FROM performance_schema.events_stages_summary_global_by_event_name\G
 ...
 *************************** 5. row ***************************
     EVENT_NAME: stage/sql/checking permissions
     COUNT_STAR: 57
 SUM_TIMER_WAIT: 26501888880
 MIN_TIMER_WAIT: 7317456
 AVG_TIMER_WAIT: 464945295
 MAX_TIMER_WAIT: 12858936792
 ...
 *************************** 9. row ***************************
     EVENT_NAME: stage/sql/closing tables
     COUNT_STAR: 37
 SUM_TIMER_WAIT: 662606568
 MIN_TIMER_WAIT: 1593864
 AVG_TIMER_WAIT: 17907891
 MAX_TIMER_WAIT: 437977248
 ...

Each stage 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 *note 'setup_instruments': performance-schema-setup-instruments-table. table:

Each stage summary table has these summary columns containing aggregated values: 'COUNT_STAR', 'SUM_TIMER_WAIT', 'MIN_TIMER_WAIT', 'AVG_TIMER_WAIT', and 'MAX_TIMER_WAIT'. These columns are analogous to the columns of the same names in the wait event summary tables (see note performance-schema-wait-summary-tables::), except that the stage summary tables aggregate events from note 'events_stages_current': performance-schema-events-stages-current-table. rather than *note 'events_waits_current': performance-schema-events-waits-current-table.

*note 'TRUNCATE TABLE': truncate-table. is permitted for stage summary tables. It has these effects:

In addition, each stage 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 note 'events_stages_summary_global_by_event_name': performance-schema-stage-summary-tables. For details, see note performance-schema-connection-tables::.

 File: manual.info.tmp, Node: performance-schema-statement-summary-tables, Next: performance-schema-transaction-summary-tables, Prev: performance-schema-stage-summary-tables, Up: performance-schema-summary-tables

25.12.15.3 Statement Summary Tables ...................................

The Performance Schema maintains tables for collecting current and recent statement events, and aggregates that information in summary tables. *note performance-schema-statement-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 *note 'setup_instruments': performance-schema-setup-instruments-table. table:

Each statement summary table has these summary columns containing aggregated values (with exceptions as noted):

The *note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables. table has these additional summary columns:

The *note 'events_statements_summary_by_program': performance-schema-statement-summary-tables. table has these additional summary columns:

The *note 'prepared_statements_instances': performance-schema-prepared-statements-instances-table. table has these additional summary columns:

*note 'TRUNCATE TABLE': truncate-table. is permitted for statement summary tables. It has these effects:

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 note 'events_statements_summary_global_by_event_name': performance-schema-statement-summary-tables. For details, see note performance-schema-connection-tables::.

Statement Digest Aggregation Rules

If the 'statements_digest' consumer is enabled, aggregation into *note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables. occurs as follows when a statement completes. Aggregation is based on the 'DIGEST' value computed for the statement.

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:

Stored Program Instrumentation Behavior

For stored program types for which instrumentation is enabled in the note 'setup_objects': performance-schema-setup-objects-table. table, note 'events_statements_summary_by_program': performance-schema-statement-summary-tables. maintains statistics for stored programs as follows:

See also *note performance-schema-pre-filtering::.

 File: manual.info.tmp, Node: performance-schema-transaction-summary-tables, Next: performance-schema-objects-summary-global-by-type-table, Prev: performance-schema-statement-summary-tables, Up: performance-schema-summary-tables

25.12.15.4 Transaction Summary Tables .....................................

The Performance Schema maintains tables for collecting current and recent transaction events, and aggregates that information in summary tables. *note performance-schema-transaction-tables:: describes the events on which transaction summaries are based. See that discussion for information about the content of transaction events, the current and historical transaction event tables, and how to control transaction event collection, which is disabled by default.

Example transaction event summary information:

 mysql> SELECT *
        FROM performance_schema.events_transactions_summary_global_by_event_name
        LIMIT 1\G
 *************************** 1. row ***************************
           EVENT_NAME: transaction
           COUNT_STAR: 5
       SUM_TIMER_WAIT: 19550092000
       MIN_TIMER_WAIT: 2954148000
       AVG_TIMER_WAIT: 3910018000
       MAX_TIMER_WAIT: 5486275000
     COUNT_READ_WRITE: 5
 SUM_TIMER_READ_WRITE: 19550092000
 MIN_TIMER_READ_WRITE: 2954148000
 AVG_TIMER_READ_WRITE: 3910018000
 MAX_TIMER_READ_WRITE: 5486275000
      COUNT_READ_ONLY: 0
  SUM_TIMER_READ_ONLY: 0
  MIN_TIMER_READ_ONLY: 0
  AVG_TIMER_READ_ONLY: 0
  MAX_TIMER_READ_ONLY: 0

Each transaction 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 *note 'setup_instruments': performance-schema-setup-instruments-table. table:

Each transaction summary table has these summary columns containing aggregated values:

*note 'TRUNCATE TABLE': truncate-table. is permitted for transaction summary tables. It has these effects:

In addition, each transaction 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 note 'events_transactions_summary_global_by_event_name': performance-schema-transaction-summary-tables. For details, see note performance-schema-connection-tables::.

Transaction Aggregation Rules

Transaction event collection occurs without regard to isolation level, access mode, or autocommit mode.

Transaction event collection occurs for all non-aborted transactions initiated by the server, including empty transactions.

Read-write transactions are generally more resource intensive than read-only transactions, therefore transaction summary tables include separate aggregate columns for read-write and read-only transactions.

Resource requirements may also vary with transaction isolation level. However, presuming that only one isolation level would be used per server, aggregation by isolation level is not provided.

 File: manual.info.tmp, Node: performance-schema-objects-summary-global-by-type-table, Next: performance-schema-file-summary-tables, Prev: performance-schema-transaction-summary-tables, Up: performance-schema-summary-tables

25.12.15.5 Object Wait Summary Table ....................................

The Performance Schema maintains the *note 'objects_summary_global_by_type': performance-schema-objects-summary-global-by-type-table. table for aggregating object wait events.

Example object wait event summary information:

 mysql> SELECT * FROM performance_schema.objects_summary_global_by_type\G
 ...
 *************************** 3. row ***************************
    OBJECT_TYPE: TABLE
  OBJECT_SCHEMA: test
    OBJECT_NAME: t
     COUNT_STAR: 3
 SUM_TIMER_WAIT: 263126976
 MIN_TIMER_WAIT: 1522272
 AVG_TIMER_WAIT: 87708678
 MAX_TIMER_WAIT: 258428280
 ...
 *************************** 10. row ***************************
    OBJECT_TYPE: TABLE
  OBJECT_SCHEMA: mysql
    OBJECT_NAME: user
     COUNT_STAR: 14
 SUM_TIMER_WAIT: 365567592
 MIN_TIMER_WAIT: 1141704
 AVG_TIMER_WAIT: 26111769
 MAX_TIMER_WAIT: 334783032
 ...

The *note 'objects_summary_global_by_type': performance-schema-objects-summary-global-by-type-table. table has these grouping columns to indicate how the table aggregates events: 'OBJECT_TYPE', 'OBJECT_SCHEMA', and 'OBJECT_NAME'. Each row summarizes events for the given object.

note 'objects_summary_global_by_type': performance-schema-objects-summary-global-by-type-table. has the same summary columns as the 'events_waits_summary_by_XXX' tables. See note performance-schema-wait-summary-tables::.

*note 'TRUNCATE TABLE': truncate-table. is permitted for the object summary table. It resets the summary columns to zero rather than removing rows.

 File: manual.info.tmp, Node: performance-schema-file-summary-tables, Next: performance-schema-table-wait-summary-tables, Prev: performance-schema-objects-summary-global-by-type-table, Up: performance-schema-summary-tables

25.12.15.6 File I/O Summary Tables ..................................

The Performance Schema maintains file I/O summary tables that aggregate information about I/O operations.

Example file I/O event summary information:

 mysql> SELECT * FROM performance_schema.file_summary_by_event_name\G
 ...
 *************************** 2. row ***************************
                EVENT_NAME: wait/io/file/sql/binlog
                COUNT_STAR: 31
            SUM_TIMER_WAIT: 8243784888
            MIN_TIMER_WAIT: 0
            AVG_TIMER_WAIT: 265928484
            MAX_TIMER_WAIT: 6490658832
 ...
 mysql> SELECT * FROM performance_schema.file_summary_by_instance\G
 ...
 *************************** 2. row ***************************
                 FILE_NAME: /var/mysql/share/english/errmsg.sys
                EVENT_NAME: wait/io/file/sql/ERRMSG
                EVENT_NAME: wait/io/file/sql/ERRMSG
     OBJECT_INSTANCE_BEGIN: 4686193384
                COUNT_STAR: 5
            SUM_TIMER_WAIT: 13990154448
            MIN_TIMER_WAIT: 26349624
            AVG_TIMER_WAIT: 2798030607
            MAX_TIMER_WAIT: 8150662536
 ...

Each file I/O 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 *note 'setup_instruments': performance-schema-setup-instruments-table. table:

Each file I/O summary table has the following summary columns containing aggregated values. Some columns are more general and have values that are the same as the sum of the values of more fine-grained columns. In this way, aggregations at higher levels are available directly without the need for user-defined views that sum lower-level columns.

*note 'TRUNCATE TABLE': truncate-table. is permitted for file I/O summary tables. It resets the summary columns to zero rather than removing rows.

The MySQL server uses several techniques to avoid I/O operations by caching information read from files, so it is possible that statements you might expect to result in I/O events do not do so. You may be able to ensure that I/O does occur by flushing caches or restarting the server to reset its state.

 File: manual.info.tmp, Node: performance-schema-table-wait-summary-tables, Next: performance-schema-socket-summary-tables, Prev: performance-schema-file-summary-tables, Up: performance-schema-summary-tables

25.12.15.7 Table I/O and Lock Wait Summary Tables .................................................

The following sections describe the table I/O and lock wait summary tables:

 File: manual.info.tmp, Node: performance-schema-table-io-waits-summary-by-table-table, Next: performance-schema-table-io-waits-summary-by-index-usage-table, Prev: performance-schema-table-wait-summary-tables, Up: performance-schema-table-wait-summary-tables

25.12.15.8 The table_io_waits_summary_by_table Table ....................................................

The *note 'table_io_waits_summary_by_table': performance-schema-table-io-waits-summary-by-table-table. table aggregates all table I/O wait events, as generated by the 'wait/io/table/sql/handler' instrument. The grouping is by table.

The note 'table_io_waits_summary_by_table': performance-schema-table-io-waits-summary-by-table-table. table has these grouping columns to indicate how the table aggregates events: 'OBJECT_TYPE', 'OBJECT_SCHEMA', and 'OBJECT_NAME'. These columns have the same meaning as in the note 'events_waits_current': performance-schema-events-waits-current-table. table. They identify the table to which the row applies.

*note 'table_io_waits_summary_by_table': performance-schema-table-io-waits-summary-by-table-table. has the following summary columns containing aggregated values. As indicated in the column descriptions, some columns are more general and have values that are the same as the sum of the values of more fine-grained columns. For example, columns that aggregate all writes hold the sum of the corresponding columns that aggregate inserts, updates, and deletes. In this way, aggregations at higher levels are available directly without the need for user-defined views that sum lower-level columns.

note 'TRUNCATE TABLE': truncate-table. is permitted for table I/O summary tables. It resets the summary columns to zero rather than removing rows. Truncating this table also truncates the note 'table_io_waits_summary_by_index_usage': performance-schema-table-io-waits-summary-by-index-usage-table. table.

 File: manual.info.tmp, Node: performance-schema-table-io-waits-summary-by-index-usage-table, Next: performance-schema-table-lock-waits-summary-by-table-table, Prev: performance-schema-table-io-waits-summary-by-table-table, Up: performance-schema-table-wait-summary-tables

25.12.15.9 The table_io_waits_summary_by_index_usage Table ..........................................................

The *note 'table_io_waits_summary_by_index_usage': performance-schema-table-io-waits-summary-by-index-usage-table. table aggregates all table index I/O wait events, as generated by the 'wait/io/table/sql/handler' instrument. The grouping is by table index.

The columns of note 'table_io_waits_summary_by_index_usage': performance-schema-table-io-waits-summary-by-index-usage-table. are nearly identical to note 'table_io_waits_summary_by_table': performance-schema-table-io-waits-summary-by-table-table. The only difference is the additional group column, 'INDEX_NAME', which corresponds to the name of the index that was used when the table I/O wait event was recorded:

note 'TRUNCATE TABLE': truncate-table. is permitted for table I/O summary tables. It resets the summary columns to zero rather than removing rows. This table is also truncated by truncation of the note 'table_io_waits_summary_by_table': performance-schema-table-io-waits-summary-by-table-table. table. A DDL operation that changes the index structure of a table may cause the per-index statistics to be reset.

 File: manual.info.tmp, Node: performance-schema-table-lock-waits-summary-by-table-table, Prev: performance-schema-table-io-waits-summary-by-index-usage-table, Up: performance-schema-table-wait-summary-tables

25.12.15.10 The table_lock_waits_summary_by_table Table .......................................................

The *note 'table_lock_waits_summary_by_table': performance-schema-table-lock-waits-summary-by-table-table. table aggregates all table lock wait events, as generated by the 'wait/lock/table/sql/handler' instrument. The grouping is by table.

This table contains information about internal and external locks:

The note 'table_lock_waits_summary_by_table': performance-schema-table-lock-waits-summary-by-table-table. table has these grouping columns to indicate how the table aggregates events: 'OBJECT_TYPE', 'OBJECT_SCHEMA', and 'OBJECT_NAME'. These columns have the same meaning as in the note 'events_waits_current': performance-schema-events-waits-current-table. table. They identify the table to which the row applies.

*note 'table_lock_waits_summary_by_table': performance-schema-table-lock-waits-summary-by-table-table. has the following summary columns containing aggregated values. As indicated in the column descriptions, some columns are more general and have values that are the same as the sum of the values of more fine-grained columns. For example, columns that aggregate all locks hold the sum of the corresponding columns that aggregate read and write locks. In this way, aggregations at higher levels are available directly without the need for user-defined views that sum lower-level columns.

*note 'TRUNCATE TABLE': truncate-table. is permitted for table lock summary tables. It resets the summary columns to zero rather than removing rows.

 File: manual.info.tmp, Node: performance-schema-socket-summary-tables, Next: performance-schema-memory-summary-tables, Prev: performance-schema-table-wait-summary-tables, Up: performance-schema-summary-tables

25.12.15.11 Socket Summary Tables .................................

These socket summary tables aggregate timer and byte count information for socket operations:

The socket summary tables do not aggregate waits generated by 'idle' events while sockets are waiting for the next request from the client. For 'idle' event aggregations, use the wait-event summary tables; see *note performance-schema-wait-summary-tables::.

Each socket 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 *note 'setup_instruments': performance-schema-setup-instruments-table. table:

Each socket summary table has these summary columns containing aggregated values:

The *note 'socket_summary_by_instance': performance-schema-socket-summary-tables. table also has an 'EVENT_NAME' column that indicates the class of the socket: 'client_connection', 'server_tcpip_socket', 'server_unix_socket'. This column can be grouped on to isolate, for example, client activity from that of the server listening sockets.

note 'TRUNCATE TABLE': truncate-table. is permitted for socket summary tables. Except for note 'events_statements_summary_by_digest': performance-schema-statement-summary-tables, tt resets the summary columns to zero rather than removing rows.

 File: manual.info.tmp, Node: performance-schema-memory-summary-tables, Next: performance-schema-status-variable-summary-tables, Prev: performance-schema-socket-summary-tables, Up: performance-schema-summary-tables

25.12.15.12 Memory Summary Tables .................................

The Performance Schema instruments memory usage and aggregates memory usage statistics, detailed by these factors:

The Performance Schema instruments the following aspects of memory use

Memory sizes help to understand or tune the memory consumption of the server.

Operation counts help to understand or tune the overall pressure the server is putting on the memory allocator, which has an impact on performance. Allocating a single byte one million times is not the same as allocating one million bytes a single time; tracking both sizes and counts can expose the difference.

Low and high water marks are critical to detect workload spikes, overall workload stability, and possible memory leaks.

Memory summary tables do not contain timing information because memory events are not timed.

For information about collecting memory usage data, see *note memory-instrumentation-behavior::.

Example memory event summary information:

 mysql> SELECT *
        FROM performance_schema.memory_summary_global_by_event_name
        WHERE EVENT_NAME = 'memory/sql/TABLE'\G
 *************************** 1. row ***************************
                   EVENT_NAME: memory/sql/TABLE
                  COUNT_ALLOC: 1381
                   COUNT_FREE: 924
    SUM_NUMBER_OF_BYTES_ALLOC: 2059873
     SUM_NUMBER_OF_BYTES_FREE: 1407432
               LOW_COUNT_USED: 0
           CURRENT_COUNT_USED: 457
              HIGH_COUNT_USED: 461
     LOW_NUMBER_OF_BYTES_USED: 0
 CURRENT_NUMBER_OF_BYTES_USED: 652441
    HIGH_NUMBER_OF_BYTES_USED: 669269

Each memory 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 *note 'setup_instruments': performance-schema-setup-instruments-table. table:

Each memory summary table has these summary columns containing aggregated values:

*note 'TRUNCATE TABLE': truncate-table. is permitted for memory summary tables. It has these effects:

In addition, each memory 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 note 'memory_summary_global_by_event_name': performance-schema-memory-summary-tables. For details, see note performance-schema-connection-tables::.

Memory Instrumentation Behavior

Memory instruments are listed in the *note 'setup_instruments': performance-schema-setup-instruments-table. table and have names of the form 'memory/CODE_AREA/INSTRUMENT_NAME'. Most memory instrumentation is disabled by default.

Instruments named with the prefix 'memory/performance_schema/' expose how much memory is allocated for internal buffers in the Performance Schema itself. The 'memory/performance_schema/' instruments are built in, always enabled, and cannot be disabled at startup or runtime. Built-in memory instruments are displayed only in the *note 'memory_summary_global_by_event_name': performance-schema-memory-summary-tables. table.

To control memory instrumentation state at server startup, use lines like these in your 'my.cnf' file:

To control memory instrumentation state at runtime, update the 'ENABLED' column of the relevant instruments in the *note 'setup_instruments': performance-schema-setup-instruments-table. table:

For memory instruments, the 'TIMED' column in *note 'setup_instruments': performance-schema-setup-instruments-table. is ignored because memory operations are not timed.

When a thread in the server executes a memory allocation that has been instrumented, these rules apply:

For deallocation, these rules apply:

For the per-thread statistics, the following rules apply.

When an instrumented memory block of size N is allocated, the Performance Schema makes these updates to memory summary table columns:

When an instrumented memory block is deallocated, the Performance Schema makes these updates to memory summary table columns:

For higher-level aggregates (global, by account, by user, by host), the same rules apply as expected for low and high water marks.

For lower estimates in summary tables other than *note 'memory_summary_global_by_event_name': performance-schema-memory-summary-tables, it is possible for values to go negative if memory ownership is transferred between threads.

Here is an example of estimate computation; but note that estimate implementation is subject to change:

Thread 1 uses memory in the range from 1MB to 2MB during execution, as reported by the 'LOW_NUMBER_OF_BYTES_USED' and 'HIGH_NUMBER_OF_BYTES_USED' columns of the *note 'memory_summary_by_thread_by_event_name': performance-schema-memory-summary-tables. table.

Thread 2 uses memory in the range from 10MB to 12MB during execution, as reported likewise.

When these two threads belong to the same user account, the per-account summary estimates that this account used memory in the range from 11MB to 14MB. That is, the 'LOW_NUMBER_OF_BYTES_USED' for the higher level aggregate is the sum of each 'LOW_NUMBER_OF_BYTES_USED' (assuming the worst case). Likewise, the 'HIGH_NUMBER_OF_BYTES_USED' for the higher level aggregate is the sum of each 'HIGH_NUMBER_OF_BYTES_USED' (assuming the worst case).

11MB is a lower estimate that can occur only if both threads hit the low usage mark at the same time.

14MB is a higher estimate that can occur only if both threads hit the high usage mark at the same time.

The real memory usage for this account could have been in the range from 11.5MB to 13.5MB.

For capacity planning, reporting the worst case is actually the desired behavior, as it shows what can potentially happen when sessions are uncorrelated, which is typically the case.

 File: manual.info.tmp, Node: performance-schema-status-variable-summary-tables, Prev: performance-schema-memory-summary-tables, Up: performance-schema-summary-tables

25.12.15.13 Status Variable Summary Tables ..........................................

Note:

The value of the 'show_compatibility_56' system variable affects the information available from the tables described here. For details, see the description of that variable in *note server-system-variables::.

The Performance Schema makes status variable information available in the tables described in *note performance-schema-status-variable-tables::. It also makes aggregated status variable information available in summary tables, described here. Each status variable summary table has one or more grouping columns to indicate how the table aggregates status values:

Each status variable summary table has this summary column containing aggregated values:

The meaning of 'account' in these tables is similar to its meaning in the MySQL grant tables in the 'mysql' system database, in the sense that the term refers to a combination of user and host values. They differ in that, for grant tables, the host part of an account can be a pattern, whereas for Performance Schema tables, the host value is always a specific nonpattern host name.

Account status is collected when sessions terminate. The session status counters are added to the global status counters and the corresponding account status counters. If account statistics are not collected, the session status is added to host and user status, if host and user status are collected.

Account, host, and user statistics are not collected if the 'performance_schema_accounts_size', 'performance_schema_hosts_size', and 'performance_schema_users_size' system variables, respectively, are set to 0.

The Performance Schema supports *note 'TRUNCATE TABLE': truncate-table. for status variable summary tables as follows; in all cases, status for active sessions is unaffected:

'FLUSH STATUS' adds the session status from all active sessions to the global status variables, resets the status of all active sessions, and resets account, host, and user status values aggregated from disconnected sessions.

 File: manual.info.tmp, Node: performance-schema-miscellaneous-tables, Prev: performance-schema-summary-tables, Up: performance-schema-table-descriptions

25.12.16 Performance Schema Miscellaneous Tables

The following sections describe tables that do not fall into the table categories discussed in the preceding sections:

 File: manual.info.tmp, Node: performance-schema-host-cache-table, Next: performance-schema-performance-timers-table, Prev: performance-schema-miscellaneous-tables, Up: performance-schema-miscellaneous-tables

25.12.16.1 The host_cache Table ...............................

The MySQL server maintains an in-memory host cache that contains client host name and IP address information and is used to avoid Domain Name System (DNS) lookups. The note 'host_cache': performance-schema-host-cache-table. table exposes the contents of this cache. The 'host_cache_size' system variable controls the size of the host cache, as well as the size of the note 'host_cache': performance-schema-host-cache-table. table. For operational and configuration information about the host cache, see *note host-cache::.

Because the note 'host_cache': performance-schema-host-cache-table. table exposes the contents of the host cache, it can be examined using note 'SELECT': select. statements. This may help you diagnose the causes of connection problems. The Performance Schema must be enabled or this table is empty.

The *note 'host_cache': performance-schema-host-cache-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is permitted for the note 'host_cache': performance-schema-host-cache-table. table. It requires the 'DROP' privilege for the table. Truncating the table flushes the host cache, which has the effects described in *note host-cache-flushing::.

 File: manual.info.tmp, Node: performance-schema-performance-timers-table, Next: performance-schema-processlist-table, Prev: performance-schema-host-cache-table, Up: performance-schema-miscellaneous-tables

25.12.16.2 The performance_timers Table .......................................

The *note 'performance_timers': performance-schema-performance-timers-table. table shows which event timers are available:

 mysql> SELECT * FROM performance_schema.performance_timers;
 +-------------+-----------------+------------------+----------------+
 | TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
 +-------------+-----------------+------------------+----------------+
 | CYCLE       |      2389029850 |                1 |             72 |
 | NANOSECOND  |      1000000000 |                1 |            112 |
 | MICROSECOND |         1000000 |                1 |            136 |
 | MILLISECOND |            1036 |                1 |            168 |
 | TICK        |             105 |                1 |           2416 |
 +-------------+-----------------+------------------+----------------+

If the values associated with a given timer name are 'NULL', that timer is not supported on your platform. The rows that do not contain 'NULL' indicate which timers you can use in note 'setup_timers': performance-schema-setup-timers-table. For an explanation of how event timing occurs, see note performance-schema-timing::.

Note:

As of MySQL 5.7.21, the Performance Schema note 'setup_timers': performance-schema-setup-timers-table. table is deprecated and is removed in MySQL 8.0, as is the 'TICKS' row in the note 'performance_timers': performance-schema-performance-timers-table. table.

The *note 'performance_timers': performance-schema-performance-timers-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'performance_timers': performance-schema-performance-timers-table. table.

 File: manual.info.tmp, Node: performance-schema-processlist-table, Next: performance-schema-threads-table, Prev: performance-schema-performance-timers-table, Up: performance-schema-miscellaneous-tables

25.12.16.3 The processlist Table ................................

Note:

The *note 'processlist': performance-schema-processlist-table. table is automatically created in the Performance Schema for new installations of MySQL 5.7.39, or higher. It is also created automatically by an upgrade.

The MySQL process list indicates the operations currently being performed by the set of threads executing within the server. The note 'processlist': performance-schema-processlist-table. table is one source of process information. For a comparison of this table with other sources, see note processlist-sources::.

The *note 'processlist': performance-schema-processlist-table. table can be queried directly. If you have the 'PROCESS' privilege, you can see all threads, even those belonging to other users. Otherwise (without the 'PROCESS' privilege), nonanonymous users have access to information about their own threads but not threads for other users, and anonymous users have no access to thread information.

Note:

If the 'performance_schema_show_processlist' system variable is enabled, the note 'processlist': performance-schema-processlist-table. table also serves as the basis for an alternative implementation underlying the note 'SHOW PROCESSLIST': show-processlist. statement. For details, see later in this section.

The *note 'processlist': performance-schema-processlist-table. table contains a row for each server process:

 mysql> SELECT * FROM performance_schema.processlist\G
 *************************** 1. row ***************************
      ID: 5
    USER: event_scheduler
    HOST: localhost
      DB: NULL
 COMMAND: Daemon
    TIME: 137
   STATE: Waiting on empty queue
    INFO: NULL
 *************************** 2. row ***************************
      ID: 9
    USER: me
    HOST: localhost:58812
      DB: NULL
 COMMAND: Sleep
    TIME: 95
   STATE:
    INFO: NULL
 *************************** 3. row ***************************
      ID: 10
    USER: me
    HOST: localhost:58834
      DB: test
 COMMAND: Query
    TIME: 0
   STATE: executing
    INFO: SELECT * FROM performance_schema.processlist
 ...

The *note 'processlist': performance-schema-processlist-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'processlist': performance-schema-processlist-table. table.

As mentioned previously, if the 'performance_schema_show_processlist' system variable is enabled, the *note 'processlist': performance-schema-processlist-table. table serves as the basis for an alternative implementation of other process information sources:

The default note 'SHOW PROCESSLIST': show-processlist. implementation iterates across active threads from within the thread manager while holding a global mutex. This has negative performance consequences, particularly on busy systems. The alternative note 'SHOW PROCESSLIST': show-processlist. implementation is based on the Performance Schema *note 'processlist': performance-schema-processlist-table. table. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex.

MySQL configuration affects *note 'processlist': performance-schema-processlist-table. table contents as follows:

The preceding configuration parameters affect the contents of the 'processlist' table. For a given configuration, however, the *note 'processlist': performance-schema-processlist-table. contents are unaffected by the 'performance_schema_show_processlist' setting.

The alternative process list implementation does not apply to the 'INFORMATION_SCHEMA' *note 'PROCESSLIST': information-schema-processlist-table. table or the 'COM_PROCESS_INFO' command of the MySQL client/server protocol.

 File: manual.info.tmp, Node: performance-schema-threads-table, Prev: performance-schema-processlist-table, Up: performance-schema-miscellaneous-tables

25.12.16.4 The threads Table ............................

The *note 'threads': performance-schema-threads-table. table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring and historical event logging are enabled for it:

 mysql> SELECT * FROM performance_schema.threads\G
 *************************** 1. row ***************************
           THREAD_ID: 1
                NAME: thread/sql/main
                TYPE: BACKGROUND
      PROCESSLIST_ID: NULL
    PROCESSLIST_USER: NULL
    PROCESSLIST_HOST: NULL
      PROCESSLIST_DB: NULL
 PROCESSLIST_COMMAND: NULL
    PROCESSLIST_TIME: 80284
   PROCESSLIST_STATE: NULL
    PROCESSLIST_INFO: NULL
    PARENT_THREAD_ID: NULL
                ROLE: NULL
        INSTRUMENTED: YES
             HISTORY: YES
     CONNECTION_TYPE: NULL
        THREAD_OS_ID: 489803
 ...
 *************************** 4. row ***************************
           THREAD_ID: 51
                NAME: thread/sql/one_connection
                TYPE: FOREGROUND
      PROCESSLIST_ID: 34
    PROCESSLIST_USER: isabella
    PROCESSLIST_HOST: localhost
      PROCESSLIST_DB: performance_schema
 PROCESSLIST_COMMAND: Query
    PROCESSLIST_TIME: 0
   PROCESSLIST_STATE: Sending data
    PROCESSLIST_INFO: SELECT * FROM performance_schema.threads
    PARENT_THREAD_ID: 1
                ROLE: NULL
        INSTRUMENTED: YES
             HISTORY: YES
     CONNECTION_TYPE: SSL/TLS
        THREAD_OS_ID: 755399
 ...

When the Performance Schema initializes, it populates the *note 'threads': performance-schema-threads-table. table based on the threads in existence then. Thereafter, a new row is added each time the server creates a thread.

The 'INSTRUMENTED' and 'HISTORY' column values for new threads are determined by the contents of the note 'setup_actors': performance-schema-setup-actors-table. table. For information about how to use the note 'setup_actors': performance-schema-setup-actors-table. table to control these columns, see *note performance-schema-thread-filtering::.

Removal of rows from the note 'threads': performance-schema-threads-table. table occurs when threads end. For a thread associated with a client session, removal occurs when the session ends. If a client has auto-reconnect enabled and the session reconnects after a disconnect, the session becomes associated with a new row in the note 'threads': performance-schema-threads-table. table that has a different 'PROCESSLIST_ID' value. The initial 'INSTRUMENTED' and 'HISTORY' values for the new thread may be different from those of the original thread: The *note 'setup_actors': performance-schema-setup-actors-table. table may have changed in the meantime, and if the 'INSTRUMENTED' or 'HISTORY' value for the original thread was changed after the row was initialized, the change does not carry over to the new thread.

You can enable or disable thread monitoring (that is, whether events executed by the thread are instrumented) and historical event logging. To control the initial 'INSTRUMENTED' and 'HISTORY' values for new foreground threads, use the note 'setup_actors': performance-schema-setup-actors-table. table. To control these aspects of existing threads, set the 'INSTRUMENTED' and 'HISTORY' columns of note 'threads': performance-schema-threads-table. table rows. (For more information about the conditions under which thread monitoring and historical event logging occur, see the descriptions of the 'INSTRUMENTED' and 'HISTORY' columns.)

For a comparison of the note 'threads': performance-schema-threads-table. table columns with names having a prefix of 'PROCESSLIST_' to other process information sources, see note processlist-sources::.

Important:

For thread information sources other than the note 'threads': performance-schema-threads-table. table, information about threads for other users is shown only if the current user has the 'PROCESS' privilege. That is not true of the note 'threads': performance-schema-threads-table. table; all rows are shown to any user who has the 'SELECT' privilege for the table. Users who should not be able to see threads for other users by accessing the *note 'threads': performance-schema-threads-table. table should not be given the 'SELECT' privilege for it.

The *note 'threads': performance-schema-threads-table. table has these columns:

note 'TRUNCATE TABLE': truncate-table. is not permitted for the note 'threads': performance-schema-threads-table. table.

 File: manual.info.tmp, Node: performance-schema-option-variable-reference, Next: performance-schema-options, Prev: performance-schema-table-descriptions, Up: performance-schema