Skip navigation links
MySQL 5.5 Reference Manual :: 20 MySQL Performance Schema :: 20.3 Performance Schema Status Monitoring
20.4 Performance Schema Event Timing »
Section Navigation      [Toggle]

20.3. Performance Schema Status Monitoring

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:

  • Performance_schema_xxx_classes_lost indicates how many instruments of type xxx could not be loaded.

  • Performance_schema_xxx_instances_lost indicates how many instances of object type xxx could not be created.

  • Performance_schema_xxx_handles_lost indicates how many instances of object type xxx could not be opened.

  • Performance_schema_locker_lost indicates how many events are “lost” or not recorded.

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 was started with the --performance_schema_max_mutex_classes=200 option and thus has room for 200 mutex instruments.

  • 150 mutex instruments have been loaded already.

  • The plugin named plugin_a contains 40 mutex instruments.

  • The plugin named plugin_b contains 20 mutex instruments.

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:

  • To save a few bytes of memory, you start the server with --performance_schema_max_mutex_classes=N, where N is less than the default value. The default value is chosen to be sufficient to load all the plugins provided in the MySQL distribution, but this can be reduced if some plugins are never loaded. For example, you might choose not to load some of the storage engines in the distribution.

  • You load a third-party plugin that is instrumented for Performance Schema but do not allow for the plugin's instrumentation memory requirements when you start the server. Because it comes from a third party, the instrument memory consumption of this engine is not accounted for in the default value chosen for performance_schema_max_mutex_classes.

    If the server has insufficient resources for the plugin's instruments and you do not explicitly allocate more using --performance_schema_max_mutex_classes=N, loading the plugin leads to starvation of instruments.

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.

To inspect the internal operation of the Performance Schema code, use SHOW ENGINE PERFORMANCE_SCHEMA STATUS: 

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.

Name values consist of two parts, which name an internal buffer and an attribute of the buffer, respectively:

  • Internal buffers that are exposed as a table in the performance_schema are named after the table. Examples: EVENTS_WAITS_HISTORY.ROW_SIZE, MUTEX_INSTANCES.ROW_COUNT.

  • Internal buffers that are not exposed as a table are named within parentheses. Examples: (PFS_COND_CLASS).ROW_SIZE, (PFS_MUTEX_CLASS).MEMORY.

  • Values that apply to Performance Schema as a whole begin with PERFORMANCE_SCHEMA. Example: PERFORMANCE_SCHEMA.MEMORY.

Attributes have these meanings:

  • ROW_SIZE cannot be changed. It is the size of the internal record used by the implementation.

  • ROW_COUNT can be changed depending on the configuration options.

  • For a table, tbl_name.MEMORY is the product of ROW_SIZE multiplied by ROW_COUNT. For Performance Schema as a whole, PERFORMANCE_SCHEMA.MEMORY is the sum of all the memory used (the sum of all other MEMORY values).

In some cases, there is a direct relationship between a configuration parameter and a SHOW ENGINE value. For example, EVENTS_WAITS_HISTORY_LONG.ROW_COUNT corresponds to performance_schema_events_waits_history_long_size. In other cases, the relationship is more complex. For example, EVENTS_WAITS_HISTORY.ROW_COUNT corresponds to performance_schema_events_waits_history_size (the number of rows per thread) multiplied by performance_schema_max_thread_instances ( the number of threads).