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_EVENT_NAME           |
| EVENTS_WAITS_SUMMARY_BY_INSTANCE             |
| EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME |
| FILE_INSTANCES                               |
| FILE_SUMMARY_BY_EVENT_NAME                   |
| FILE_SUMMARY_BY_INSTANCE                     |
| MUTEX_INSTANCES                              |
| PERFORMANCE_TIMERS                           |
| PROCESSLIST                                  |
| RWLOCK_INSTANCES                             |
| SETUP_CONSUMERS                              |
| SETUP_INSTRUMENTS                            |
| SETUP_OBJECTS                                |
| SETUP_TIMERS                                 |
+----------------------------------------------+

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