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 21.5, “Performance Schema Event Instrument Naming Conventions”.
There are three types of synchronization objects:
cond, mutex, and
rwlock. These objects are described in
Section 21.5, “Performance Schema Event Instrument Naming Conventions”.
The cond_instances table has these columns:
NAMEThe instrument name associated with the condition.
OBJECT_INSTANCE_BEGINThe 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_NAMEThe file name.
EVENT_NAMEThe instrument name associated with the file.
OPEN_COUNTThe count of open handles on the file. If a file was opened and then closed, it was opened 1 time, but
OPEN_COUNTwill be 0. To list all the files currently opened by the server, useWHERE OPEN_COUNT > 0.
The mutex_instances table has these columns:
NAMEThe instrument name associated with the mutex.
OBJECT_INSTANCE_BEGINThe address in memory of the mutex that was instrumented.
LOCKED_BY_THREAD_IDWhen a thread currently has a mutex locked,
LOCKED_BY_THREAD_IDis theTHREAD_IDof the locking thread, otherwise it isNULL.
The rwlock_instances table has these columns:
NAMEThe instrument name associated with the lock.
OBJECT_INSTANCE_BEGINThe address in memory of the lock that was instrumented.
WRITE_LOCKED_BY_THREAD_IDWhen a thread currently has an
rwlocklocked in exclusive (write) mode,WRITE_LOCKED_BY_THREAD_IDis theTHREAD_IDof the locking thread, otherwise it isNULL.READ_LOCKED_BY_COUNTWhen a thread currently has an
rwlocklocked in shared (read) mode,READ_LOCKED_BY_COUNTis 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 anrwlock, 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 21.11, “Using Performance Schema to Diagnose Problems”