Skip navigation links
MySQL Performance Schema :: 7 Performance Schema Table Descriptions :: 7.5 Performance Schema Instance Tables
7.6 Performance Schema Miscellaneous Tables »
Section Navigation      [Toggle]

7.5. Performance Schema Instance Tables

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

This group contains tables with names that match the pattern '%INSTANCES' (plural). It does not include tables with '_BY_INSTANCE' in their name; those are summary tables, not instance tables. 

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

These tables list instrumented synchronization objects and files. Each table has an EVENT_NAME or NAME column to indicate the instrument associated with each row. Instrument names have multiple parts and form a hierarchy, as discussed in Section 5, “Performance Schema Event Instrument Naming Conventions”.

There are three types of synchronization objects: cond, mutex, and rwlock. These objects are described in Section 5, “Performance Schema Event Instrument Naming Conventions”.

The COND_INSTANCES table has these columns:

  • NAME

    The instrument name associated with the condition.

  • OBJECT_INSTANCE_BEGIN

    The address in memory of the condition that was instrumented.

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:

  • FILE_NAME

    The file name.

  • EVENT_NAME

    The instrument name associated with the file.

  • OPEN_COUNT

    The count of open handles on the file. If a file was opened and then closed, it was opened 1 time, but OPEN_COUNT will be 0. To list all the files currently opened by the server, use WHERE OPEN_COUNT > 0.

The MUTEX_INSTANCES table has these columns:

  • NAME

    The instrument name associated with the mutex.

  • OBJECT_INSTANCE_BEGIN

    The address in memory of the mutex that was instrumented.

  • LOCKED_BY_THREAD_ID

    When a thread currently has a mutex locked, LOCKED_BY_THREAD_ID is the THREAD_ID of the locking thread, otherwise it is NULL.

The RWLOCK_INSTANCES table has these columns:

  • NAME

    The instrument name associated with the lock.

  • OBJECT_INSTANCE_BEGIN

    The address in memory of the lock that was instrumented.

  • WRITE_LOCKED_BY_THREAD_ID

    When a thread currently has an rwlock locked in exclusive (write) mode, WRITE_LOCKED_BY_THREAD_ID is the THREAD_ID of the locking thread, otherwise it is NULL.

  • READ_LOCKED_BY_COUNT

    When a thread currently has an rwlock locked in shared (read) mode, READ_LOCKED_BY_COUNT is incremented by 1. This is a counter only, so it cannot be used directly to find which thread holds a read lock, but it can be used to see whether there is a read contention on an rwlock, and see how many readers are currently active.

The MUTEX_INSTANCES.LOCKED_BY_THREAD_ID and RWLOCK_INSTANCES.WRITE_LOCKED_BY_THREAD_ID columns are extremely important for investigating performance bottlenecks or deadlocks. For examples of how to use them for this purpose, see Section 11, “Using Performance Schema to Diagnose Problems”