Chapter 22 MySQL Performance Schema

Events are collected by means of instrumentation added to the server source code. Instruments time events, which is how the Performance Schema provides an idea of how long events take. It is also possible to configure instruments not to collect timing information. This section discusses the available timers and their characteristics, and how timing values are represented in events.

Two tables provide timer information:

Each timer row in setup_timers must refer to one of the timers listed in performance_timers.

Timers vary in precision and the amount of overhead they involve. To see what timers are available and their characteristics, check the performance_timers table:

mysql> SELECT * FROM performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2389029850 |                1 |             72 |
| NANOSECOND  |            NULL |             NULL |           NULL |
| MICROSECOND |         1000000 |                1 |            585 |
| MILLISECOND |            1035 |                1 |            738 |
| TICK        |             101 |                1 |            630 |
+-------------+-----------------+------------------+----------------+

The TIMER_NAME column shows the names of the available timers. CYCLE refers to the timer that is based on the CPU (processor) cycle counter. If the values associated with a given timer name are NULL, that timer is not supported on your platform. The rows that do not have NULL indicate which timers you can use in setup_timers.

TIMER_FREQUENCY indicates the number of timer units per second. For a cycle timer, the frequency is generally related to the CPU speed. The value shown was obtained on a system with a 2.4GHz processor. The other timers are based on fixed fractions of seconds. For TICK, the frequency may vary by platform (for example, some use 100 ticks/second, others 1000 ticks/second).

TIMER_RESOLUTION indicates the number of timer units by which timer values increase at a time. If a timer has a resolution of 10, its value increases by 10 each time.

TIMER_OVERHEAD is the minimal number of cycles of overhead to obtain one timing with the given timer. The overhead per event is twice the value displayed because the timer is invoked at the beginning and end of the event.

To see which timer is in effect or to change the timer, access the setup_timers table:

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

mysql> UPDATE setup_timers SET TIMER_NAME = 'MICROSECOND'
    -> WHERE NAME = 'wait';
mysql> SELECT * FROM setup_timers;
+-----------+-------------+
| NAME      | TIMER_NAME  |
+-----------+-------------+
| idle      | MICROSECOND |
| wait      | MICROSECOND |
| stage     | NANOSECOND  |
| statement | NANOSECOND  |
+-----------+-------------+

By default, the Performance Schema uses the best timer available for each instrument type, but you can select a different one.

To time waits, the most important criterion is to reduce overhead, at the possible expense of the timer accuracy, so using the CYCLE timer is the best.

The time a statement (or stage) takes to execute is in general orders of magnitude larger than the time it takes to execute a single wait. To time statements, the most important criterion is to have an accurate measure, which is not affected by changes in processor frequency, so using a timer which is not based on cycles is the best. The default timer for statements is NANOSECOND. The extra “overhead” compared to the cycle timer is not significant, because the overhead caused by calling a timer twice (once when the statement starts, once when it ends) is orders of magnitude less compared to the CPU time used to execute the statement itself. Using the CYCLE timer has no benefit here, only drawbacks.

The precision offered by the cycle counter depends on processor speed. If the processor runs at 1 GHz (one billion cycles/second) or higher, the cycle counter delivers sub-nanosecond precision. Using the cycle counter is much cheaper than getting the actual time of day. For example, the standard gettimeofday() function can take hundreds of cycles, which is an unacceptable overhead for data gathering that may occur thousands or millions of times per second.

Cycle counters also have disadvantages:

Currently, MySQL works with cycle counters on x386 (Windows, OS X, Linux, Solaris, and other Unix flavors), PowerPC, and IA-64.

The setup_instruments table has an ENABLED column to indicate the instruments for which to collect events. The table also has a TIMED column to indicate which instruments are timed. If an instrument is not enabled, it produces no events. If an enabled instrument is not timed, events produced by the instrument have NULL for the TIMER_START, TIMER_END, and TIMER_WAIT timer values. This in turn causes those values to be ignored when calculating the sum, minimum, maximum, and average time values in summary tables.

Within events, times are stored in units given by the timer in effect when event timing begins. For display, times are shown in picoseconds (trillionths of a second) to normalize them to a standard unit, regardless of which timer is selected.

Modifications to the setup_timers 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, which may lead to unpredictable results. If you make timer changes, you may want to use TRUNCATE TABLE to reset Performance Schema statistics.

The timer baseline (“time zero”) occurs at Performance Schema initialization during server startup. TIMER_START and TIMER_END values in events represent picoseconds since the baseline. TIMER_WAIT values are durations in picoseconds.

Picosecond values in events are approximate. Their accuracy is subject to the usual forms of error associated with conversion from one unit to another. If the CYCLE timer is used and the processor rate varies, there might be drift. For these reasons, it is not reasonable to look at the TIMER_START value for an event as an accurate measure of time elapsed since server startup. On the other hand, it is reasonable to use TIMER_START or TIMER_WAIT values in ORDER BY clauses to order events by start time or duration.

The choice of picoseconds in events rather than a value such as microseconds has a performance basis. One implementation goal was to show results in a uniform time unit, regardless of the timer. In an ideal world this time unit would look like a wall-clock unit and be reasonably precise; in other words, microseconds. But to convert cycles or nanoseconds to microseconds, it would be necessary to perform a division for every instrumentation. Division is expensive on many platforms. Multiplication is not expensive, so that is what is used. Therefore, the time unit is an integer multiple of the highest possible TIMER_FREQUENCY value, using a multiplier large enough to ensure that there is no major precision loss. The result is that the time unit is “picoseconds.” This precision is spurious, but the decision enables overhead to be minimized.

Events are processed in a producer/consumer fashion:

Filtering can be done at different stages of performance monitoring:

The following sections provide more detail about pre-filtering and provide guidelines for naming instruments or consumers in filtering operations. For information about writing queries to retrieve information (post-filtering), see Section 22.3, “Performance Schema Queries”.

Pre-filtering is done by the Performance Schema and has a global effect that applies to all users. Pre-filtering can be applied to either the producer or consumer stage of event processing:

Modifications to any of these tables affect monitoring immediately, with some exceptions:

When you change the monitoring configuration, the Performance Schema does not flush the history tables. Events already collected remain in the current-events and history tables until displaced by newer events. If you disable instruments, you might need to wait a while before events for them are displaced by newer events of interest. Alternatively, use TRUNCATE TABLE to empty the history tables.

After making instrumentation changes, you might want to truncate the summary tables to clear aggregate information for previously collected events. Except for events_statements_summary_by_digest, the effect of TRUNCATE TABLE for summary tables is to reset the summary columns to 0 or NULL, not to remove rows.

The following sections describe how to use specific tables to control Performance Schema pre-filtering.

mysql> SELECT * FROM setup_instruments;
+------------------------------------------------------------+---------+-------+
| NAME                                                       | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
...
| 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   |
...

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

+-------------+---------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+-------------+---------------+-------------+---------+-------+
| TABLE       | db1           | t1          | YES     | YES   |
| TABLE       | db1           | t2          | NO      | NO    |
| TABLE       | db2           | %           | YES     | YES   |
| TABLE       | db3           | %           | NO      | NO    |
| TABLE       | %             | %           | YES     | YES   |
+-------------+---------------+-------------+---------+-------+

mysql> SELECT * FROM setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| %    | %    | %    |
+------+------+------+

TRUNCATE TABLE setup_actors;

INSERT INTO setup_actors (HOST,USER,ROLE) VALUES('localhost','joe','%');
INSERT INTO setup_actors (HOST,USER,ROLE) VALUES('%','sam','%');

mysql> SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_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     |
+--------------------------------+---------+

mysql> UPDATE setup_consumers
    -> SET ENABLED = 'NO' WHERE NAME LIKE '%history%';

global_instrumentation
 thread_instrumentation
   events_waits_current
     events_waits_history
     events_waits_history_long
   events_stages_current
     events_stages_history
     events_stages_history_long
   events_statements_current
     events_statements_history
     events_statements_history_long
 statements_digest

mysql> SELECT * FROM setup_consumers;
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| global_instrumentation    | NO      |
...
+---------------------------+---------+

mysql> SELECT * FROM setup_consumers;
+---------------------------+---------+
| NAME                      | ENABLED |
+---------------------------+---------+
| global_instrumentation    | YES     |
| thread_instrumentation    | NO      |
...
+---------------------------+---------+

mysql> SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| events_waits_current           | NO      |
...
| events_stages_current          | NO      |
...
| events_statements_current      | YES     |
...
+--------------------------------+---------+

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

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

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

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

Names given for filtering operations can be as specific or general as required. To indicate a single instrument or consumer, specify its name in full:

mysql> UPDATE setup_instruments
    -> SET ENABLED = 'NO'
    -> WHERE NAME = 'wait/synch/mutex/myisammrg/MYRG_INFO::mutex';

mysql> UPDATE setup_consumers
    -> SET ENABLED = 'NO' WHERE NAME = 'events_waits_current';

To specify a group of instruments or consumers, use a pattern that matches the group members:

mysql> UPDATE setup_instruments
    -> SET ENABLED = 'NO'
    -> WHERE NAME LIKE 'wait/synch/mutex/%';

mysql> UPDATE setup_consumers
    -> SET ENABLED = 'NO' WHERE NAME LIKE '%history%';

If you use a pattern, it should be chosen so that it matches all the items of interest and no others. For example, to select all file I/O instruments, it is better to use a pattern that includes the entire instrument name prefix:

... WHERE NAME LIKE 'wait/io/file/%';

A pattern of '%/file/%' will match other instruments that have a component of '/file/' anywhere in the name. Even less suitable is the pattern '%file%' because it will match instruments with 'file' anywhere in the name, such as wait/synch/mutex/sql/LOCK_des_key_file.

To check which instrument or consumer names a pattern matches, perform a simple test:

mysql> SELECT NAME FROM setup_instruments WHERE NAME LIKE 'pattern';

mysql> SELECT NAME FROM setup_consumers WHERE NAME LIKE 'pattern';

For information about the types of names that are supported, see Section 22.4, “Performance Schema Instrument Naming Conventions”.

It is always possible to determine what instruments the Performance Schema includes by checking the setup_instruments table. For example, to see what file-related events are instrumented for the InnoDB storage engine, use this query:

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

An exhaustive description of precisely what is instrumented is not given in this documentation, for several reasons:

The setup_actors table contains information that determines whether to enable monitoring 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 setup_actors table. If a row from that table matches, its ENABLED column value is used to set the the INSTRUMENTED column of the threads table row for the thread. This enables instrumenting to be applied selectively per host, user, or account (combination of host and user). If there is no match, the INSTRUMENTED column for the thread is set to NO.

For background threads, there is no associated user. INSTRUMENTED is YES by default and setup_actors is not consulted.

The initial contents of the setup_actors table match any user and host combination, so monitoring is enabled by default for all foreground threads:

mysql> SELECT * FROM setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| %    | %    | %    |
+------+------+------+

For information about how to use the setup_actors table to affect event monitoring, see Section 22.2.3.3.3, “Pre-Filtering by Thread”.

Modifications to the setup_actors table affect only foreground threads created subsequent to the modification, not existing threads. To affect existing threads, modify the INSTRUMENTED column of threads table rows.

The setup_actors table has these columns:

The setup_consumers table lists the types of consumers for which event information can be stored and which are enabled:

mysql> SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | NO      |
| events_stages_history          | NO      |
| events_stages_history_long     | NO      |
| events_statements_current      | YES     |
| events_statements_history      | NO      |
| events_statements_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 setup_consumers table form a hierarchy from higher levels to lower. For detailed information about the effect of enabling different consumers, see Section 22.2.3.3.4, “Pre-Filtering by Consumer”.

Modifications to the setup_consumers table affect monitoring immediately.

The setup_consumers table has these columns:

The setup_instruments table lists classes of instrumented objects for which events can be collected:

mysql> SELECT * FROM setup_instruments;
+------------------------------------------------------------+---------+-------+
| NAME                                                       | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
...
| 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 this 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 *_instances tables.

Modifications to most setup_instruments 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 setup_instruments table in event filtering, see Section 22.2.3.3, “Event Pre-Filtering”.

The setup_instruments table has these columns:

The setup_objects 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 setup_objects contents look like this:

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

Modifications to the setup_objects table affect object monitoring immediately.

For object types listed in setup_objects, 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 setup_objects; the row for information_schema.% simply makes this default explicit.)

When the Performance Schema checks for a match in setup_objects, 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 setup_objects rows can have different ENABLED and TIMED values.

Rows can be inserted into or deleted from setup_objects 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 setup_objects table in event filtering, see Section 22.2.3.3, “Event Pre-Filtering”.

The setup_objects table has these columns:

The setup_timers table shows the currently selected event timers:

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

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 Section 22.2.3.1, “Performance Schema Event Timing”.

Modifications to the setup_timers table affect monitoring immediately. Events already measured are stored using the original timer unit, and events in progress may use the original timer for the begin time and the new timer for the end time. To avoid unpredictable results if you make timer changes, use TRUNCATE TABLE to reset Performance Schema statistics.

The setup_timers table has these columns:

The cond_instances 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 thread, or threads, will cause the condition to happen.

The cond_instances table has these columns:

The file_instances 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 will not be in file_instances. When a file is deleted from the disk, it is also removed from the file_instances table.

The file_instances table has these columns:

The mutex_instances 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 will compete against each other, so that the first query to obtain a lock on the mutex will cause 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 mutex_instances table has these columns:

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:

The rwlock_instances table lists all the rwlock instances (read write locks) 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) or exclusive (only one thread can have a write lock at a given time).

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

The rwlock_instances table has these columns:

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 rwlock_instances can be used only to identify the thread holding a write lock, but not the threads holding a read lock.

The socket_instances 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 Section 22.9.9.8, “Socket Summary Tables”).

mysql> SELECT * FROM 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:

The socket_instances table has these columns:

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:

The socket_instances table was added in MySQL 5.6.3.

The events_waits_current table contains current wait events, one row per thread showing the current status of the thread's most recent monitored wait event.

The events_waits_current table can be truncated with TRUNCATE TABLE.

Of the tables that contain wait event rows, events_waits_current is the most fundamental. Other tables that contain wait event rows are logically derived from the current events. For example, the events_waits_history and events_waits_history_long tables are collections of the most recent wait events, up to a fixed number of rows.

For information about configuration of wait event collection, see Section 22.9.4, “Performance Schema Wait Event Tables”.

The events_waits_current table has these columns:

The events_waits_history table contains the most recent N wait events per thread. The value of N is autosized at server startup. To set the table size explicitly, set the performance_schema_events_waits_history_size system variable at server startup. Wait events are not added to the table until they have ended. As new events are added, older events are discarded if the table is full.

The events_waits_history table has the same structure as events_waits_current. See Section 22.9.4.1, “The events_waits_current Table”.

The events_waits_history table can be truncated with TRUNCATE TABLE.

For information about configuration of wait event collection, see Section 22.9.4, “Performance Schema Wait Event Tables”.

The events_waits_history_long table contains the most recent N wait events. The value of N is autosized at server startup. To set the table size explicitly, set the performance_schema_events_waits_history_long_size system variable at server startup. Wait events are not added to the table until they have ended. As new events are added, older events are discarded if the table is full. When a thread ends, its rows are removed from the table.

The events_waits_history_long table has the same structure as events_waits_current. See Section 22.9.4.1, “The events_waits_current Table”.

The events_waits_history_long table can be truncated with TRUNCATE TABLE.

For information about configuration of wait event collection, see Section 22.9.4, “Performance Schema Wait Event Tables”.

The events_stages_current table contains current stage events, one row per thread showing the current status of the thread's most recent monitored stage event.

The events_stages_current table can be truncated with TRUNCATE TABLE.

Of the tables that contain stage event rows, events_stages_current is the most fundamental. Other tables that contain stage event rows are logically derived from the current events. For example, the events_stages_history and events_stages_history_long tables are collections of the most recent stage events, up to a fixed number of rows.

For information about configuration of stage event collection, see Section 22.9.5, “Performance Schema Stage Event Tables”.

The events_stages_current table has these columns:

The events_stages_current table was added in MySQL 5.6.3.

The events_stages_history table contains the most recent N stage events per thread. The value of N is autosized at server startup. To set the table size explicitly, set the performance_schema_events_stages_history_size system variable at server startup. Stage events are not added to the table until they have ended. As new events are added, older events are discarded if the table is full.

The events_stages_history table has the same structure as events_stages_current. See Section 22.9.5.1, “The events_stages_current Table”.

The events_stages_history table can be truncated with TRUNCATE TABLE.

The events_stages_history table was added in MySQL 5.6.3.

For information about configuration of stage event collection, see Section 22.9.5, “Performance Schema Stage Event Tables”.

The events_stages_history_long table contains the most recent N stage events. The value of N is autosized at server startup. To set the table size explicitly, set the performance_schema_events_stages_history_long_size system variable at server startup. Stage events are not added to the table until they have ended. As new events are added, older events are discarded if the table is full. When a thread ends, its rows are removed from the table.

The events_stages_history_long table has the same structure as events_stages_current. See Section 22.9.5.1, “The events_stages_current Table”.

The events_stages_history_long table can be truncated with TRUNCATE TABLE.

The events_stages_history_long table was added in MySQL 5.6.3.

For information about configuration of stage event collection, see Section 22.9.5, “Performance Schema Stage Event Tables”.

The events_statements_current table contains current statement events, one row per thread showing the current status of the thread's most recent monitored statement event.

The events_statements_current table can be truncated with TRUNCATE TABLE.

Of the tables that contain statement event rows, events_statements_current is the most fundamental. Other tables that contain statement event rows are logically derived from the current events. For example, the events_statements_history and events_statements_history_long tables are collections of the most recent statement events, up to a fixed number of rows.

For information about configuration of statement event collection, see Section 22.9.6, “Performance Schema Statement Event Tables”.

The events_statements_current table has these columns:

The events_statements_current table was added in MySQL 5.6.3.

The events_statements_history table contains the most recent N statement events per thread. The value of N is autosized at server startup. To set the table size explicitly, set the performance_schema_events_statements_history_size system variable at server startup. Statement events are not added to the table until they have ended. As new events are added, older events are discarded if the table is full.

The events_statements_history table has the same structure as events_statements_current. See Section 22.9.6.1, “The events_statements_current Table”.

The events_statements_history table can be truncated with TRUNCATE TABLE.

The events_statements_history table was added in MySQL 5.6.3.

For information about configuration of statement event collection, see Section 22.9.6, “Performance Schema Statement Event Tables”.

The events_statements_history_long table contains the most recent N statement events. 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. Statement events are not added to the table until they have ended. As new events are added, older events are discarded if the table is full. When a thread ends, its rows are removed from the table.

The events_statements_history_long table has the same structure as events_statements_current. See Section 22.9.6.1, “The events_statements_current Table”.

The events_statements_history_long table can be truncated with TRUNCATE TABLE.

The events_statements_history_long table was added in MySQL 5.6.3.

For information about configuration of statement event collection, see Section 22.9.6, “Performance Schema Statement Event Tables”.

The accounts 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 accounts table has the following columns. For a description of how the Performance Schema maintains rows in this table, including the effect of TRUNCATE TABLE, see Section 22.9.7, “Performance Schema Connection Tables”.

The accounts table was added in MySQL 5.6.3.

The hosts 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 hosts table has the following columns. For a description of how the Performance Schema maintains rows in this table, including the effect of TRUNCATE TABLE, see Section 22.9.7, “Performance Schema Connection Tables”.

The hosts table was added in MySQL 5.6.3.

The users 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 users table has the following columns. For a description of how the Performance Schema maintains rows in this table, including the effect of TRUNCATE TABLE, see Section 22.9.7, “Performance Schema Connection Tables”.

The users table was added in MySQL 5.6.3.

As of MySQL 5.6.6, application programs can provide key/value connection attributes to be passed to the server at connect time, using the mysql_options() and mysql_options4() C API functions.

The session_account_connect_attrs table contains connection attributes only for sessions open for your own account. To see connection attributes for all sessions, look in the session_connect_attrs table.

The session_account_connect_attrs table contains these columns:

As of MySQL 5.6.6, application programs can provide key/value connection attributes to be passed to the server at connect time, using the mysql_options() and mysql_options4() C API functions.

The session_connect_attrs table contains connection attributes for all sessions. To see connection attributes only for sessions open for your own account, look in the session_account_connect_attrs table.

The session_connect_attrs table contains these columns:

The Performance Schema maintains tables for collecting current and recent wait events, and aggregates that information in summary tables. Section 22.9.4, “Performance Schema Wait Event 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.

Each event waits summary table has one or more grouping columns to indicate how the table aggregates events. Event names refer to names of event instruments in the setup_instruments table.

All event waits summary tables have these summary columns containing aggregated values:

Example wait event summary information:

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

TRUNCATE TABLE is permitted for wait summary tables. It resets the summary columns to zero rather than removing rows.

As of MySQL 5.6.3, the Performance Schema maintains tables for collecting current and recent stage events, and aggregates that information in summary tables. Section 22.9.5, “Performance Schema Stage Event Tables” describes the events on which stage summaries are based. See that discussion for information about the content of stage events, the current and recent stage event tables, and how to control stage event collection.

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 setup_instruments table.

All stage summary tables have 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 event wait summary tables (see Section 22.9.9.1, “Event Wait Summary Tables”), except that the stage summary tables aggregate waits from events_stages_current rather than events_waits_current.

Example stage event summary information:

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

TRUNCATE TABLE is permitted for stage summary tables. It resets the summary columns to zero rather than removing rows.

As of MySQL 5.6.3, the Performance Schema maintains tables for collecting current and recent statement events, and aggregates that information in summary tables. Section 22.9.6, “Performance Schema Statement Event Tables” describes the events on which statement summaries are based. See that discussion for information about the content of statement events, the current and recent statement event tables, and how to control statement event collection.

Each statement summary table has one or more grouping columns to indicate how the table aggregates events. Event names refer to names of event instruments in the setup_instruments table.

All statement summary tables have these summary columns containing aggregated values:

The events_statements_summary_by_digest table has these additional summary columns:

Example statement event summary information:

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

TRUNCATE TABLE is permitted for statement summary tables. For events_statements_summary_by_digest, it empties the table. For the other statement summary tables, it resets the summary columns to zero rather than removing rows.

Statement Digest Aggregation Rules

If the statement_digest consumer is enabled, aggregation into events_statements_summary_by_digest occurs as follows when a statement completes. Aggregation is based on the DIGEST value computed for the statement.

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:

The objects_summary_global_by_type table aggregates object wait events. It 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.

objects_summary_global_by_type has the same summary columns as the events_waits_summary_by_xxx tables. See Section 22.9.9.1, “Event Wait Summary Tables”.

Example object wait event summary information:

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

TRUNCATE TABLE is permitted for the object summary table. It resets the summary columns to zero rather than removing rows.

The file I/O summary tables aggregate information about I/O operations.

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 setup_instruments table.

All file I/O summary tables have the following summary columns containing aggregated values. (Before MySQL 5.6.4, the tables contain only the COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ, and SUM_NUMBER_OF_BYTES_WRITE aggregation columns.) 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.

Example file I/O event summary information:

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

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 will not. You may be able to ensure that I/O does occur by flushing caches or restarting the server to reset its state.

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

The connection summary tables are similar to the corresponding events_xxx_summary_by_thread_by_event_name tables, except that aggregation occurs per account, user, or host, rather than by thread.

The Performance Schema maintains summary tables that aggregate connection statistics by event name and account, user, or host. Separate groups of tables are available that aggregate wait, stage, and statement events, which results in this set of connection summary tables:

In other words, the connection summary tables have names of the form events_xxx_summary_yyy_by_event_name, where xxx is waits, stages, or statements, and yyy is account, user, or host.

The connection summary tables provide an intermediate aggregation level:

Each connection summary table has one or more grouping columns to indicate how the table aggregates events. Event names refer to names of event instruments in the setup_instruments table.

All connection summary tables have these summary columns containing aggregated values: COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, and MAX_TIMER_WAIT. These are similar to the columns of the same names in the events_waits_summary_by_instance table. Connection summary tables for statements have additional SUM_xxx columns that aggregate statement types.

The connection summary tables were added in MySQL 5.6.3.

TRUNCATE TABLE is permitted for connection summary tables. It resets the summary columns to zero rather than removing rows. In addition, connection summary tables are implicitly truncated if a connection table on which they depend is truncated. Table 22.2, “Effect of Implicit Table Truncation”, describes the relationship between connection table truncation and implicitly truncated 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 Section 22.9.9.1, “Event 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 setup_instruments table.

All socket summary tables have these summary columns containing aggregated values:

The socket_summary_by_instance 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.

These tables were added in MySQL 5.6.3.

TRUNCATE TABLE is permitted for socket summary tables. Except for events_statements_summary_by_digest, tt resets the summary columns to zero rather than removing rows.

The host_cache table provides access to the contents of the host cache, which contains client host name and IP address information and is used to avoid DNS lookups. (See Section 8.12.6.2, “DNS Lookup Optimization and the Host Cache”.) The host_cache table exposes the contents of the host cache so that it can be examined using SELECT statements. The Performance Schema must be enabled or this table is empty.

FLUSH HOSTS and TRUNCATE TABLE host_cache have the same effect: They clear the host cache. This also empties the host_cache table (because it is the visible representation of the cache) and unblocks any blocked hosts (see Section B.5.2.6, “Host 'host_name' is blocked”.) FLUSH HOSTS requires the RELOAD privilege. TRUNCATE TABLE requires the DROP privilege for the host_cache table.

The host_cache table has these columns:

The host_cache table was added in MySQL 5.6.5.

The performance_timers table shows which event timers are available:

mysql> SELECT * FROM performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      2389029850 |                1 |             72 |
| NANOSECOND  |            NULL |             NULL |           NULL |
| MICROSECOND |         1000000 |                1 |            585 |
| MILLISECOND |            1035 |                1 |            738 |
| TICK        |             101 |                1 |            630 |
+-------------+-----------------+------------------+----------------+

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

The performance_timers table has these columns:

The threads table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring is enabled for it:

mysql> SELECT * FROM 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
...
*************************** 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 threads
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
...

When the Performance Schema initializes, it populates the threads table based on the threads in existence then. Thereafter, a new row is added each time the server creates a thread.

The INSTRUMENTED column value for new threads is determined by the contents of the setup_actors table. For information about how to use the setup_actors table to control this column, see Section 22.2.3.3.3, “Pre-Filtering by Thread”.

Removal of rows from the threads 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 threads table that has a different PROCESSLIST_ID value. The initial INSTRUMENTED value for the new thread may be different from that of the original thread: The setup_actors table may have changed in the meantime, and if the INSTRUMENTED value for the original thread was changed after it was initialized, that change does not carry over to the new thread.

The threads table columns with names having a prefix of PROCESSLIST_ provide information similar to that available from the INFORMATION_SCHEMA.PROCESSLIST table or the SHOW PROCESSLIST statement. Thus, all three sources provide thread-monitoring information. Use of threads differs from use of the other two sources in these ways:

For these reasons, DBAs who perform server monitoring using INFORMATION_SCHEMA.PROCESSLIST or SHOW PROCESSLIST may wish to monitor using the threads table instead.

The threads table has these columns: