MySQL 8.0 : meta-data added to Performance_Schema’s Instruments

In MySQL 8.0, the engineers have added useful meta-data to the table SETUP_INSTRUMENT. This table lists the classes of instrumented objects for which events can be collected.

To the NAME, ENABLES and TIMED columns, were added PROPERTIES, VOLATILITY and DOCUMENTATION.

Let’s have a quick look at these new columns:

PROPERTIES can have the following values

• global_statistics: only global summaries are available for this instrument. Example: memory/performance_schema/metadata_locks that return the memory used for table performance_schema.metadata_locks
• mutable: only applicable for statement instruments as they can “mutate” into a more specific one. Example: statement/abstract/relay_log that returns the new event just read from the relay log.
• progress: applied only to stage instruments, it reports progress data. Example: stage/sql/copy to tmp table
• singleton: instruments having a single instance. Example: wait/synch/mutex/sql/LOCK_error_log, like most global mutex locks this lock on error log is a singleton.
• user: instrument related to user workload. Example: the instrument on idle

VOLATILITY

This define the life or creation occurrence of the instrument. The possible values from low to high are:

• 0 : unknown
• 1 : permanent
• 2 : provisioning
• 3 : ddl
• 4 : cache
• 5 : session
• 6 : transaction
• 7 : query
• 8 : intra_query

For example, wait/synch/mutex/sql/THD::LOCK_thd_query as a volatility of 5, which means this mutex is created each time a session connects and destroyed when the session disconnects.

There is no point then to enable an instrument for an object already created.

DOCUMENTATION

Finally, now there is a documentation column describing the purpose of the instrument. Currently there are 80 instruments documented with the help of that column.

This is an example:

         NAME: memory/performance_schema/prepared_statements_instances
      ENABLED: YES
        TIMED: NULL
   PROPERTIES: global_statistics
   VOLATILITY: 1
DOCUMENTATION: Memory used for table performance_schema.prepared_statements_instances

All this is explained in details in the MySQL’s manual.

Enjoy MySQL 8.0 and I wish you a pleasant discovery of all the new features !