This section briefly introduces Performance Schema with examples that show how to use it. For additional examples, see Section 20.11, “Using Performance Schema to Diagnose Problems”.

For Performance Schema to be available, support for it must have been configured when MySQL was built. You can verify whether this is the case by checking the server's help output. If Performance Schema is available, the output will mention several variables with names that begin with performance_schema:

shell> mysqld --verbose --help
...
  --performance_schema
                      Enable the performance schema.
  --performance_schema_events_waits_history_long_size=#
                      Number of rows in EVENTS_WAITS_HISTORY_LONG.
...

If such variables do not appear in the output, your server has not been built to support Performance Schema. In this case, see Section 20.2, “Performance Schema Configuration”.

Assuming that Performance Schema is available, it is disabled by default. To enable it, start the server with the performance_schema variable enabled. For example, use these lines in your my.cnf file:

[mysqld]
performance_schema

When the server starts, it sees performance_schema and attempts to initialize Performance Schema. To verify successful initialization, use this statement:

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

A value of ON means that Performance Schema initialized successfully and is ready for use. A value of OFF means that some error occurred. Check the server error log for information about what went wrong.

Performance Schema is implemented as a storage engine. If this engine is available (which you should already have checked earlier), you should see it listed with a SUPPORT value of YES in the output from the INFORMATION_SCHEMA.ENGINES table or the SHOW ENGINES statement:

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES
    -> WHERE ENGINE='PERFORMANCE_SCHEMA'\G
*************************** 1. row ***************************
      ENGINE: PERFORMANCE_SCHEMA
     SUPPORT: YES
     COMMENT: Performance Schema
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO

mysql> SHOW ENGINES\G
...
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
...

The PERFORMANCE_SCHEMA storage engine operates on tables in the performance_schema database. You can make performance_schema the default database so that references to its tables need not be qualified with the database name:

mysql> USE performance_schema;

Many examples in this chapter assume that performance_schema is the default database.

Performance Schema tables are stored in the performance_schema database. Information about the structure of this database and its tables can be obtained, as for any other database, by selecting from the INFORMATION_SCHEMA database or by using SHOW statements. For example, use either of these statements to see what Performance Schema tables exist:

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'performance_schema';
+----------------------------------------------+
| TABLE_NAME                                   |
+----------------------------------------------+
| COND_INSTANCES                               |
| EVENTS_WAITS_CURRENT                         |
| EVENTS_WAITS_HISTORY                         |
| EVENTS_WAITS_HISTORY_LONG                    |
| EVENTS_WAITS_SUMMARY_BY_INSTANCE             |
| EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME |
| EVENTS_WAITS_SUMMARY_GLOBAL_BY_EVENT_NAME    |
| FILE_INSTANCES                               |
| FILE_SUMMARY_BY_EVENT_NAME                   |
| FILE_SUMMARY_BY_INSTANCE                     |
| MUTEX_INSTANCES                              |
| PERFORMANCE_TIMERS                           |
| RWLOCK_INSTANCES                             |
| SETUP_CONSUMERS                              |
| SETUP_INSTRUMENTS                            |
| SETUP_TIMERS                                 |
| THREADS                                      |
+----------------------------------------------+

mysql> SHOW TABLES FROM performance_schema;
+----------------------------------------------+
| Tables_in_performance_schema                 |
+----------------------------------------------+
| COND_INSTANCES                               |
| EVENTS_WAITS_CURRENT                         |
| EVENTS_WAITS_HISTORY                         |
...

The number of Performance Schema tables is expected to increase over time as implementation of additional instrumentation proceeds.

The database name performance_schema is lowercase. The names of tables in the database are uppercase. Normal case sensitivity rules apply, so on systems with case-sensitive file names, the database name and table names must be specified in the lettercase just indicated. This behavior can be modified by setting the lower_case_table_names system variable.

To see the structure of individual tables, use SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE SETUP_TIMERS\G
*************************** 1. row ***************************
       Table: SETUP_TIMERS
Create Table: CREATE TABLE `SETUP_TIMERS` (
  `NAME` varchar(64) NOT NULL,
  `TIMER_NAME` enum('CYCLE','NANOSECOND','MICROSECOND','MILLISECOND','TICK')
   NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

Table structure is also available by selecting from tables such as INFORMATION_SCHEMA.COLUMNS or by using statements such as SHOW COLUMNS.

Tables in the performance_schema database can be grouped according to the type of information in them: Current events, event histories and summaries, object instances, and setup (configuration) information. The following examples illustrate a few uses for these tables. For detailed information about the tables in each group, see Section 20.7, “Performance Schema Table Descriptions”.

To see what the server is doing at the moment, examine the EVENTS_WAITS_CURRENT table. It contains one row per thread showing each thread's most recent monitored event:

mysql> SELECT * FROM EVENTS_WAITS_CURRENT\G
*************************** 1. row ***************************
            THREAD_ID: 0
             EVENT_ID: 5523
           EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK::mutex
               SOURCE: thr_lock.c:525
          TIMER_START: 201660494489586
            TIMER_END: 201660494576112
           TIMER_WAIT: 86526
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 142270668
     NESTING_EVENT_ID: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: 0
...

This event indicates that thread 0 was waiting for 86,526 picoseconds to acquire a lock on THR_LOCK::mutex, a mutex in the mysys subsystem. The first few columns provide the following information:

The history tables contain the same kind of rows as the current-events table but have more rows and show what the server has been doing “recently” rather than “currently.” The EVENTS_WAITS_HISTORY and EVENTS_WAITS_HISTORY_LONG tables contain the most recent 10 events per thread and most recent 10,000 events, respectively. For example, to see information for recent events produced by thread 13, do this:

mysql> SELECT EVENT_ID, EVENT_NAME, TIMER_WAIT
    -> FROM EVENTS_WAITS_HISTORY WHERE THREAD_ID = 13
    -> ORDER BY EVENT_ID;
+----------+-----------------------------------------+------------+
| EVENT_ID | EVENT_NAME                              | TIMER_WAIT |
+----------+-----------------------------------------+------------+
|       86 | wait/synch/mutex/mysys/THR_LOCK::mutex  |     686322 |
|       87 | wait/synch/mutex/mysys/THR_LOCK_malloc  |     320535 |
|       88 | wait/synch/mutex/mysys/THR_LOCK_malloc  |     339390 |
|       89 | wait/synch/mutex/mysys/THR_LOCK_malloc  |     377100 |
|       90 | wait/synch/mutex/sql/LOCK_plugin        |     614673 |
|       91 | wait/synch/mutex/sql/LOCK_open          |     659925 |
|       92 | wait/synch/mutex/sql/THD::LOCK_thd_data |     494001 |
|       93 | wait/synch/mutex/mysys/THR_LOCK_malloc  |     222489 |
|       94 | wait/synch/mutex/mysys/THR_LOCK_malloc  |     214947 |
|       95 | wait/synch/mutex/mysys/LOCK_alarm       |     312993 |
+----------+-----------------------------------------+------------+

As new events are added to a history table, older events are discarded if the table is full.

Summary tables provide aggregate information for all events over time. The tables in this group summarize event data in different ways. To see which instruments have been executed the most times or have taken the most wait time, sort the EVENTS_WAITS_SUMMARY_GLOBAL_BY_EVENT_NAME table on the COUNT_STAR or SUM_TIMER_WAIT column, which correspond to a COUNT(*) or SUM(TIMER_WAIT) value, respectively, calculated over all events:

mysql> SELECT EVENT_NAME, COUNT_STAR
    -> FROM EVENTS_WAITS_SUMMARY_GLOBAL_BY_EVENT_NAME
    -> ORDER BY COUNT_STAR DESC LIMIT 10;
+---------------------------------------------------+------------+
| EVENT_NAME                                        | COUNT_STAR |
+---------------------------------------------------+------------+
| wait/synch/mutex/mysys/THR_LOCK_malloc            |       6419 |
| wait/io/file/sql/FRM                              |        452 |
| wait/synch/mutex/sql/LOCK_plugin                  |        337 |
| wait/synch/mutex/mysys/THR_LOCK_open              |        187 |
| wait/synch/mutex/mysys/LOCK_alarm                 |        147 |
| wait/synch/mutex/sql/THD::LOCK_thd_data           |        115 |
| wait/io/file/myisam/kfile                         |        102 |
| wait/synch/mutex/sql/LOCK_global_system_variables |         89 |
| wait/synch/mutex/mysys/THR_LOCK::mutex            |         89 |
| wait/synch/mutex/sql/LOCK_open                    |         88 |
+---------------------------------------------------+------------+

mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT
    -> FROM EVENTS_WAITS_SUMMARY_GLOBAL_BY_EVENT_NAME
    -> ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
+----------------------------------------+----------------+
| EVENT_NAME                             | SUM_TIMER_WAIT |
+----------------------------------------+----------------+
| wait/io/file/sql/MYSQL_LOG             |     1599816582 |
| wait/synch/mutex/mysys/THR_LOCK_malloc |     1530083250 |
| wait/io/file/sql/binlog_index          |     1385291934 |
| wait/io/file/sql/FRM                   |     1292823243 |
| wait/io/file/myisam/kfile              |      411193611 |
| wait/io/file/myisam/dfile              |      322401645 |
| wait/synch/mutex/mysys/LOCK_alarm      |      145126935 |
| wait/io/file/sql/casetest              |      104324715 |
| wait/synch/mutex/sql/LOCK_plugin       |       86027823 |
| wait/io/file/sql/pid                   |       72591750 |
+----------------------------------------+----------------+

These results show that the THR_LOCK_malloc mutex is “hot,” both in terms of how often it is used and amount of time that threads wait attempting to acquire it.

Instance tables document what types of objects are instrumented. An instrumented object, when used by the server, produces an event. These tables provide event names and explanatory notes or status information. For example, the FILE_INSTANCES table lists instances of instruments for file I/O operations and their associated files:

mysql> SELECT * FROM FILE_INSTANCES\G
*************************** 1. row ***************************
 FILE_NAME: /opt/mysql-log/60500/binlog.000007
EVENT_NAME: wait/io/file/sql/binlog
OPEN_COUNT: 0
*************************** 2. row ***************************
 FILE_NAME: /opt/mysql/60500/data/mysql/tables_priv.MYI
EVENT_NAME: wait/io/file/myisam/kfile
OPEN_COUNT: 1
*************************** 3. row ***************************
 FILE_NAME: /opt/mysql/60500/data/mysql/columns_priv.MYI
EVENT_NAME: wait/io/file/myisam/kfile
OPEN_COUNT: 1
...

Setup tables are used to configure and display monitoring characteristics. For example, to see which event timer is selected, query the SETUP_TIMERS tables:

mysql> SELECT * FROM SETUP_TIMERS;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | CYCLE      |
+------+------------+

SETUP_INSTRUMENTS lists the set of instruments for which events can be collected and shows which of them are enabled:

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

To understand how to interpret instrument names, see Section 20.5, “Performance Schema Event Instrument Naming Conventions”.

To control whether events are collected for an instrument, set its ENABLED value to YES or NO. For example:

mysql> UPDATE SETUP_INSTRUMENTS SET ENABLED = 'NO'
    -> WHERE NAME = 'wait/synch/mutex/sql/LOCK_mysql_create_db';

Performance Schema uses collected events to update tables in the performance_schema database, which act as “consumers” of event information. The SETUP_CONSUMERS table lists the available consumers and shows which of them are enabled:

mysql> SELECT * FROM SETUP_CONSUMERS;
+----------------------------------------------+---------+
| NAME                                         | ENABLED |
+----------------------------------------------+---------+
| events_waits_current                         | YES     |
| events_waits_history                         | YES     |
| events_waits_history_long                    | YES     |
| events_waits_summary_by_thread_by_event_name | YES     |
| events_waits_summary_by_event_name           | YES     |
| events_waits_summary_by_instance             | YES     |
| file_summary_by_event_name                   | YES     |
| file_summary_by_instance                     | YES     |
+----------------------------------------------+---------+

To control whether Performance Schema maintains a consumer as a destination for event information, set its ENABLED value.

For more information about the setup tables and how to use them to control event collection, see Section 20.2.3, “Event Collection Pre-Filtering and Post-Filtering”.

There are some miscellaneous tables that do not fall into any of the previous groups. For example, PERFORMANCE_TIMERS lists the available event timers and their characteristics. For information about timers, see Section 20.4, “Performance Schema Event Timing”.

To use MySQL Performance Schema, these configuration considerations apply:

shell> ./configure --with-plugins=innobase,perfschema

[ERROR] Native table 'performance_schema'.'EVENTS_WAITS_HISTORY'
has the wrong structure
[ERROR] Native table 'performance_schema'.'EVENTS_WAITS_HISTORY_LONG'
has the wrong structure
...

shell> mysqld --verbose --help
...
  --performance_schema
                      Enable the performance schema.
  --performance_schema_events_waits_history_long_size=#
                      Number of rows in EVENTS_WAITS_HISTORY_LONG.
...

mysql> SHOW ENGINES\G
...
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
...

[mysqld]
performance_schema

mysql> SHOW VARIABLES LIKE 'perf%';
+---------------------------------------------------+---------+
| Variable_name                                     | Value   |
+---------------------------------------------------+---------+
| performance_schema                                | ON      |
| performance_schema_events_waits_history_long_size | 10000   |
| performance_schema_events_waits_history_size      | 10      |
| performance_schema_max_cond_classes               | 80      |
| performance_schema_max_cond_instances             | 1000    |
| performance_schema_max_file_classes               | 50      |
| performance_schema_max_file_handles               | 32768   |
| performance_schema_max_file_instances             | 10000   |
| performance_schema_max_mutex_classes              | 200     |
| performance_schema_max_mutex_instances            | 1000000 |
| performance_schema_max_rwlock_classes             | 30      |
| performance_schema_max_rwlock_instances           | 1000000 |
| performance_schema_max_table_handles              | 100000  |
| performance_schema_max_table_instances            | 50000   |
| performance_schema_max_thread_classes             | 50      |
| performance_schema_max_thread_instances           | 1000    |
+---------------------------------------------------+---------+

[mysqld]
performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'performance_schema'
    -> AND TABLE_NAME LIKE 'SETUP%';
+-------------------+
| TABLE_NAME        |
+-------------------+
| SETUP_CONSUMERS   |
| SETUP_INSTRUMENTS |
| SETUP_TIMERS      |
+-------------------+

mysql> SELECT * FROM SETUP_TIMERS;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | CYCLE      |
+------+------------+

mysql> UPDATE SETUP_TIMERS SET TIMER_NAME = 'NANOSECOND';

mysql> SELECT * FROM SETUP_TIMERS;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | NANOSECOND |
+------+------------+

mysql> UPDATE SETUP_INSTRUMENTS SET ENABLED = 'NO';

mysql> UPDATE SETUP_INSTRUMENTS SET ENABLED = 'NO'
    -> WHERE NAME LIKE 'wait/io/file/%';

mysql> UPDATE SETUP_INSTRUMENTS
    -> SET ENABLED = IF(NAME LIKE 'wait/io/file/%', 'NO', 'YES');

mysql> UPDATE SETUP_INSTRUMENTS
    -> SET ENABLED = CASE WHEN NAME LIKE '%/mysys/%' THEN 'YES' ELSE 'NO' END;

mysql> UPDATE SETUP_INSTRUMENTS SET ENABLED = 'NO'
    -> WHERE NAME = 'wait/synch/mutex/mysys/TMPDIR_mutex';

mysql> UPDATE SETUP_INSTRUMENTS
    -> SET ENABLED = IF(ENABLED = 'YES', 'NO', 'YES')
    -> WHERE NAME = 'wait/synch/mutex/mysys/TMPDIR_mutex';

mysql> UPDATE SETUP_INSTRUMENTS SET TIMED = 'NO';

mysql> UPDATE SETUP_CONSUMERS
    -> SET ENABLED = 'NO' WHERE NAME LIKE '%summary%';

mysql> SELECT THREAD_ID, NUMBER_OF_BYTES
    -> FROM EVENTS_WAITS_HISTORY
    -> WHERE EVENT_NAME LIKE 'wait/io/file/%'
    -> AND NUMBER_OF_BYTES IS NOT NULL;
+-----------+-----------------+
| THREAD_ID | NUMBER_OF_BYTES |
+-----------+-----------------+
|        11 |              66 |
|        11 |              47 |
|        11 |             139 |
|         5 |              24 |
|         5 |             834 |
+-----------+-----------------+

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 = 'file_summary_by_instance';

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

mysql> UPDATE SETUP_CONSUMERS
    -> SET ENABLED = 'NO' WHERE NAME LIKE '%summary%';

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

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

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

There are several status variables associated with Performance Schema:

mysql> SHOW STATUS LIKE 'perf%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Performance_schema_cond_classes_lost     | 0     |
| Performance_schema_cond_instances_lost   | 0     |
| Performance_schema_file_classes_lost     | 0     |
| Performance_schema_file_handles_lost     | 0     |
| Performance_schema_file_instances_lost   | 0     |
| Performance_schema_locker_lost           | 0     |
| Performance_schema_mutex_classes_lost    | 0     |
| Performance_schema_mutex_instances_lost  | 0     |
| Performance_schema_rwlock_classes_lost   | 0     |
| Performance_schema_rwlock_instances_lost | 0     |
| Performance_schema_table_handles_lost    | 0     |
| Performance_schema_table_instances_lost  | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
+------------------------------------------+-------+

The Performance Schema status variables provide information about instrumentation that could not be loaded or created due to memory constraints. Names for these variables have several forms:

For example, if a mutex is instrumented in the server source but the server cannot allocate memory for the instrumentation at runtime, it increments Performance_schema_mutex_classes_lost. The mutex still functions as a synchronization object (that is, the server continues to function normally), but performance data for it will not be collected. If the instrument can be allocated, it can be used for initializing instrumented mutex instances. For a singleton mutex such as a global mutex, there will be only one instance. Other mutexes have an instance per connection, or per page in various caches and data buffers, so the number of instances varies over time. Increasing the maximum number of connections or the maximum size of some buffers will increase the maximum number of instances that might be allocated at once. If the server cannot create a given instrumented mutex instance, it increments Performance_schema_mutex_instances_lost.

Suppose that the following conditions hold:

The server allocates mutex instruments for the plugins depending on how many they need and how many are available, as illustrated by the following sequence of statements:

INSTALL PLUGIN plugin_a

The server now has 150+40 = 190 mutex instruments.

UNINSTALL PLUGIN plugin_a;

The server still has 190 instruments. All the historical data generated by the plugin code is still available, but new events for the instruments are not collected.

INSTALL PLUGIN plugin_a;

The server detects that the 40 instruments are already defined, so no new instruments are created, and previously assigned internal memory buffers are reused. The server still has 190 instruments.

INSTALL PLUGIN plugin_b;

The server has room for 200-190 = 10 instruments (in this case, mutex classes), and sees that the plugin contains 20 new instruments. 10 instruments are loaded, and 10 are discarded or “lost.” The Performance_schema_mutex_classes_lost indicates the number of instruments (mutex classes) lost:

mysql> SHOW STATUS LIKE "perf%mutex_classes_lost";
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Performance_schema_mutex_classes_lost | 10    |
+---------------------------------------+-------+
1 row in set (0.10 sec)

The instrumentation still works and collects (partial) data for plugin_b.

When the server cannot create a mutex instrument, these results occur:

The pattern just described applies to all types of instruments, not just mutexes.

A value of Performance_schema_mutex_classes_lost greater than 0 can happen in two cases:

If the value chosen for performance_schema_max_mutex_classes is too small, no error is reported in the error log and there is no failure at runtime. However, the content of the tables in the performance_schema database will miss events. The Performance_schema_mutex_classes_lost status variable is the only visible sign to indicate that some events were dropped internally due to failure to create instruments.

If an instrument is not lost, it is known to the Performance Schema, and is used when instrumenting instances. For example, wait/synch/mutex/sql/LOCK_delete is the name of a mutex instrument in the SETUP_INSTRUMENTS table. This single instrument is used when creating in the code (in THD::LOCK_delete) however many instances of the mutex are needed as the server runs. In this case, LOCK_delete is a mutex that is per connection (THD), so if a server has 1000 connections, there are 1000 threads, and 1000 instrumented LOCK_delete mutex instances (THD::LOCK_delete).

If the server does not have room for all these 1000 instrumented mutexes (instances), some mutexes are created with instrumentation, and some are created without instrumentation. If the server can create only 800 instances, 200 instances are lost. The server continues to run, but increments Performance_schema_mutex_instances_lost by 200 to indicate that instances could not be created.

A value of Performance_schema_mutex_instances_lost greater than 0 can happen when the code initializes more mutexes at runtime than were allocated for --performance_schema_max_mutex_instances=N.

The bottom line is that if SHOW STATUS LIKE 'perf%' says that nothing was lost (all values are zero), the Performance Schema data is accurate and can be relied upon. If something was lost, the data is incomplete, and the Performance Schema could not record everything given the insufficient amount of memory it was given to use. In this case, the specific Performance_schema_xxx_lost variable indicates the problem area.

It might be appropriate in some cases to cause deliberate instrument starvation. For example, if you do not care about performance data for file I/O, you can start the server with all Performance Schema parameters related to file I/O set to 0. No memory will be allocated for file-related classes, instances, or handles, and all file events will be lost.

Use SHOW ENGINE PERFORMANCE_SCHEMA STATUS to inspect the internal operation of the Performance Schema code:

mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G
...
*************************** 3. row ***************************
  Type: performance_schema
  Name: EVENTS_WAITS_HISTORY.ROW_SIZE
Status: 76
*************************** 4. row ***************************
  Type: performance_schema
  Name: EVENTS_WAITS_HISTORY.ROW_COUNT
Status: 10000
*************************** 5. row ***************************
  Type: performance_schema
  Name: EVENTS_WAITS_HISTORY.MEMORY
Status: 760000
...
*************************** 57. row ***************************
  Type: performance_schema
  Name: PERFORMANCE_SCHEMA.MEMORY
Status: 26459600
...

The intent of this statement is to help the DBA to understand the effects that different options have on memory requirements. For a description of the field meanings, see Section 12.4.5.16, “SHOW ENGINE Syntax”.

Events are collected by means of instrumentation added to the server source code. Instruments time events, which is how 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.

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.

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, which has a single row:

mysql> SELECT * FROM SETUP_TIMERS;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | CYCLE      |
+------+------------+

mysql> UPDATE SETUP_TIMERS SET TIMER_NAME = 'MICROSECOND';

mysql> SELECT * FROM SETUP_TIMERS;
+------+-------------+
| NAME | TIMER_NAME  |
+------+-------------+
| wait | MICROSECOND |
+------+-------------+

Performance Schema uses the best timer available by default, but you can select a different one. Generally the best timer is CYCLE, which uses the CPU cycle counter whenever possible to provide high precision and low overhead.

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 if data gathering occurs thousands or millions of times per second.

Cycle counters also have disadvantages:

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

Within events, times are stored in picoseconds (trillionths of a second) so that they all use a standard unit, regardless of which timer is selected. The timer used for an event is the one in effect when the event is timed. This timer is used to convert start and end values to picoseconds for storage in the event. If a different timer is selected, that affects only events that start afterward, not those already in progress.

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. If this decision turns out to be impractical in some way, we will revisit it.

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.

An instrument name consists of a sequence of components separated by '/' characters. Example names:

wait/io/file/myisam/log
wait/io/file/mysys/charset
wait/synch/cond/mysys/COND_alarm
wait/synch/cond/sql/BINLOG::update_cond
wait/synch/mutex/mysys/BITMAP_mutex
wait/synch/mutex/sql/Event_scheduler::LOCK_scheduler_state
wait/synch/mutex/sql/LOCK_delete
wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock
wait/synch/rwlock/sql/Query_cache_query::lock

The instrument name space has a tree-like structure. The components of an instrument name from left to right provide a progression from more general to more specific. The number of components a name has depends on the type of instrument.

The interpretation of a given component in a name depends on the components to the left of it. For example, myisam appears in both of the following names, but myisam in the first name is related to file I/O, whereas in the second it is related to a synchronization instrument:

wait/io/file/myisam/log
wait/synch/cond/myisam/MI_SORT_INFO::cond

Instrument names consist of a prefix with a structure defined by the Performance Schema implementation and a suffix defined by the developer implementing the instrument code. For the prefix part of instrument names, the top level indicates the type of instrument. Currently this is always wait, so the naming tree has this structure:

The suffix part of instrument names comes from the code for the instruments themselves. For example, following the first three levels just described, the next levels are:

The database name performance_schema is lowercase. The names of tables in the database are uppercase. Normal case sensitivity rules apply, so on systems with case-sensitive file names, the database name and table names must be specified in the lettercase just indicated. This behavior can be modified by setting the lower_case_table_names system variable.

Most tables in the performance_schema database are read only and cannot be modified. Some of the setup tables have columns that can be modified to affect Performance Schema operation. Truncation is permitted to clear collected events or aggregation counters, so TRUNCATE TABLE can be used on tables containing those kinds of information, such as tables named with a prefix of EVENTS_WAITS.

Privileges are as for other databases and tables:

Tables in the performance_schema database can be grouped as follows:

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'performance_schema'
    -> AND TABLE_NAME LIKE 'SETUP%';
+-------------------+
| TABLE_NAME        |
+-------------------+
| SETUP_CONSUMERS   |
| SETUP_INSTRUMENTS |
| SETUP_TIMERS      |
+-------------------+

mysql> SELECT * FROM SETUP_CONSUMERS;
+----------------------------------------------+---------+
| NAME                                         | ENABLED |
+----------------------------------------------+---------+
| events_waits_current                         | YES     |
| events_waits_history                         | YES     |
| events_waits_history_long                    | YES     |
| events_waits_summary_by_thread_by_event_name | YES     |
| events_waits_summary_by_event_name           | YES     |
| events_waits_summary_by_instance             | YES     |
| file_summary_by_event_name                   | YES     |
| file_summary_by_instance                     | YES     |
+----------------------------------------------+---------+

mysql> SELECT * FROM SETUP_INSTRUMENTS;
+------------------------------------------------------------+---------+-------+
| NAME                                                       | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock                            | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync                | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active              | YES     | YES   |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool                | YES     | YES   |
| wait/synch/mutex/sql/LOCK_des_key_file                     | YES     | YES   |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index             | YES     | YES   |
...

mysql> SELECT * FROM SETUP_TIMERS;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | CYCLE      |
+------+------------+

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'performance_schema'
    -> AND TABLE_NAME LIKE '%CURRENT';
+----------------------+
| TABLE_NAME           |
+----------------------+
| EVENTS_WAITS_CURRENT |
+----------------------+

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'performance_schema'
    -> AND (TABLE_NAME LIKE '%HISTORY' OR TABLE_NAME LIKE '%HISTORY_LONG');
+---------------------------+
| TABLE_NAME                |
+---------------------------+
| EVENTS_WAITS_HISTORY      |
| EVENTS_WAITS_HISTORY_LONG |
+---------------------------+

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'performance_schema'
    -> AND TABLE_NAME LIKE '%SUMMARY%';
+----------------------------------------------+
| TABLE_NAME                                   |
+----------------------------------------------+
| EVENTS_WAITS_SUMMARY_BY_INSTANCE             |
| EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME |
| EVENTS_WAITS_SUMMARY_GLOBAL_BY_EVENT_NAME    |
| FILE_SUMMARY_BY_EVENT_NAME                   |
| FILE_SUMMARY_BY_INSTANCE                     |
+----------------------------------------------+

mysql> SELECT * FROM EVENTS_WAITS_SUMMARY_GLOBAL_BY_EVENT_NAME\G
...
*************************** 6. row ***************************
    EVENT_NAME: wait/synch/mutex/sql/MYSQL_BIN_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
...

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'performance_schema'
    -> AND TABLE_NAME LIKE '%INSTANCES';
+------------------+
| TABLE_NAME       |
+------------------+
| COND_INSTANCES   |
| FILE_INSTANCES   |
| MUTEX_INSTANCES  |
| RWLOCK_INSTANCES |
+------------------+

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 |
+-------------+-----------------+------------------+----------------+

Removing a plugin with UNINSTALL PLUGIN does not affect information already collected for code in that plugin. Time spent executing the code while the plugin was loaded was still spent even if the plugin is unloaded later. The associated event information, including aggregate information, remains readable in performance_schema database tables. For additional information about the effect of plugin installation and removal, see Section 20.3, “Performance Schema Status Monitoring”.

A plugin implementor who instruments plugin code should document its instrumentation characteristics to enable those who load the plugin to account for its requirements. For example, a third-party storage engine should include in its documentation how much memory the engine needs for mutex and other instruments.

Performance Schema implements several system variables that provide configuration information:

mysql> SHOW VARIABLES LIKE 'perf%';
+---------------------------------------------------+---------+
| Variable_name                                     | Value   |
+---------------------------------------------------+---------+
| performance_schema                                | ON      |
| performance_schema_events_waits_history_long_size | 10000   |
| performance_schema_events_waits_history_size      | 10      |
| performance_schema_max_cond_classes               | 80      |
| performance_schema_max_cond_instances             | 1000    |
| performance_schema_max_file_classes               | 50      |
| performance_schema_max_file_handles               | 32768   |
| performance_schema_max_file_instances             | 10000   |
| performance_schema_max_mutex_classes              | 200     |
| performance_schema_max_mutex_instances            | 1000000 |
| performance_schema_max_rwlock_classes             | 30      |
| performance_schema_max_rwlock_instances           | 1000000 |
| performance_schema_max_table_handles              | 100000  |
| performance_schema_max_table_instances            | 50000   |
| performance_schema_max_thread_classes             | 50      |
| performance_schema_max_thread_instances           | 1000    |
+---------------------------------------------------+---------+

The variables have the following meanings:

Performance Schema implements several status variables that provide information about instrumentation that could not be loaded or created due to memory constraints:

mysql> SHOW STATUS LIKE 'perf%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Performance_schema_cond_classes_lost     | 0     |
| Performance_schema_cond_instances_lost   | 0     |
| Performance_schema_file_classes_lost     | 0     |
| Performance_schema_file_handles_lost     | 0     |
| Performance_schema_file_instances_lost   | 0     |
| Performance_schema_locker_lost           | 0     |
| Performance_schema_mutex_classes_lost    | 0     |
| Performance_schema_mutex_instances_lost  | 0     |
| Performance_schema_rwlock_classes_lost   | 0     |
| Performance_schema_rwlock_instances_lost | 0     |
| Performance_schema_table_handles_lost    | 0     |
| Performance_schema_table_instances_lost  | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
+------------------------------------------+-------+

Names for these variables have several forms:

For information on using these variables to check Performance Schema status, see Section 20.3, “Performance Schema Status Monitoring”.

The Performance Schema is a tool to help a DBA do performance tuning by taking real measurements instead of “wild guesses.” This section demonstrates some ways to use Performance Schema for this purpose. The discussion here relies on the use of event filtering, which is described in Section 20.2.3, “Event Collection Pre-Filtering and Post-Filtering”.

The following example provides one methodology that you can use to analyze a repeatable problem, such as investigating a performance bottleneck. To begin, you should have a repeatable use case where performance is deemed “too slow” and needs optimization, and you should enable all instrumentation (no pre-filtering at all).

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. This is made possible by Performance Schema instrumentation as follows: