WL#7801: PERFORMANCE SCHEMA, SETUP_INSTRUMENT METADATA

Add metadata about instruments in setup_instruments:
- static vs dynamic instrumented objects
- singleton vs multiple
- stages with progress indicator
etc

Requirements
============

CURRENT-VERSION = 8.0.3
PREVIOUS-VERSION = 5.7

(1) Table performance_schema.setup_instruments structure.
---------------------------------------------------------

Func-Req (1.1): A fresh MySQL installation of CURRENT-VERSION must create the
table performance_schema.setup_instruments with the proper structure.

Func-Req (1.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create
the table performance_schema.setup_instruments with the proper structure.

Func-Req (1.3): Column PROPERTIES read-only, by users with
the proper grants.

Func-Req (1.4): Column VOLATILITY read-only, by users with
the proper grants.

Func-Req (1.5): Column DOCUMENTATION read-only, by users with
the proper grants.

(2) Table performance_schema.setup_threads structure.
-----------------------------------------------------

Func-Req (2.1): A fresh MySQL installation of CURRENT-VERSION must create the
table performance_schema.setup_threads with the proper structure.

Func-Req (2.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create
the table performance_schema.setup_threads with the proper structure.

Func-Req (2.3): Column ENABLED is updatable, by users with
the proper grants.

Func-Req (2.4): Column HISTORY is updatable, by users with
the proper grants.

(3) Threads instruments
-----------------------

Func-Req (3.1): Instrumented thread classes are displayed in table
setup_threads, with a prefix "thread/".

(4) properties
--------------

Func-Req (4.1): Code instrumented with a given flag is displayed
in table setup_instruments in column
properties with the corresponding set, as follows:
- (4.1.1) C PSI_FLAG_SINGLETON is displayed as SQL SET "singleton".
- (4.1.2) C PSI_FLAG_MUTABLE is displayed as SQL SET "mutable".
- (4.1.3) C PSI_FLAG_PROGRESS is displayed as SQL SET "progress".
- (4.1.4) C PSI_FLAG_USER is displayed as SQL SET "user".
- (4.1.5) C PSI_FLAG_ONLY_GLOBAL_STAT is displayed as SQL SET "global_statistics".

Func-Req (4.2): Threads instrumented with a given flag is displayed
in table setup_threads in column
properties with the corresponding set, as follows:
- (4.2.1) C PSI_FLAG_SINGLETON is displayed as SQL SET "singleton".
- (4.2.2) C PSI_FLAG_USER is displayed as SQL SET "user".

(5) Volatility
--------------

Func-Req (5.1): The instrument volatility value instrumented in the code is
displayed in column setup_instruments.volatility.

Func-Req (5.2): The thread volatility value instrumented in the code is
displayed in column setup_threads.volatility.

(6) Documentation
-----------------

Func-Req (6.1): The instrument documentation text instrumented in the code is
displayed in column setup_instruments.documentation.
Func-Req (6.2): The thread documentation text instrumented in the code is
displayed in column setup_threads.documentation.

TABLE performance_schema.setup_instruments
==========================================

Schema changes
--------------

CREATE TABLE `setup_instruments` (
  `NAME` varchar(128) NOT NULL,
  `ENABLED` enum('YES','NO') NOT NULL,

Modified column:
  `TIMED` enum('YES','NO') DEFAULT NULL,

New column:
  `PROPERTIES` set('singleton',
    'progress',
    'user',
    'global_statistics',
    'mutable') NOT NULL,

New column:
  `VOLATILITY` int(11) NOT NULL,

New column:
  `DOCUMENTATION` longtext,

  PRIMARY KEY (`NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

Modified column TIMED:
This column is now NULL-able.

Privileges
----------

Column PROPERTIES is read only.
Column VOLATILITY is read only.
Column DOCUMENTATION is read only.

Column TIMED semantic
---------------------

For instruments which do not support timing:
- the TIMED column is always displayed as NULL
- updating the TIMED column has no effect, the update is ignored.
This is for clarity and ease of use.

Column PROPERTIES semantic
---------------------------

This column is a set: multiple flags can be set for each instruments.

Property SINGLETON:
The singleton property indicates the instrument has only one instance.
Most global mutex locks in the server are singletons.

Property PROGRESS:
The progress property indicates the instrument is capable
of reporting progress data.
This only applies to stages.

Property USER:
The user property indicates the instrument is directly related
to the user workload.
For example, threads execution a user session
like "thread/sql/one_connection" have the user property,
to differentiate them from system threads.

Property GLOBAL_STATISTICS:
The global_statistics property indicates that,
for this instrument,
only global summaries are available.
No per thread/account/user/host summaries are available.
Most of the performance schema memory instrumentation
produce only global statistics.

Property MUTABLE:
This property indicate the instrument can "mutate" into a more specific one.
This only applies to statement instruments.

Column VOLATILITY semantic
--------------------------

Instrumented code contains a "volatility" attribute,
now exposed in table setup_instruments.

Volatility is currently defined as:
  #define PSI_VOLATILITY_UNKNOWN 0
  #define PSI_VOLATILITY_PERMANENT 1
  #define PSI_VOLATILITY_PROVISIONING 2
  #define PSI_VOLATILITY_DDL 3
  #define PSI_VOLATILITY_CACHE 4
  #define PSI_VOLATILITY_SESSION 5
  #define PSI_VOLATILITY_TRANSACTION 6
  #define PSI_VOLATILITY_QUERY 7
  #define PSI_VOLATILITY_INTRA_QUERY 8

The volatility attribute is only a hint given to the performance schema.
It is used to optimize memory allocations, to group instruments with
different volatility into different pools, to improve efficiency.

For example, instruments flagged with a "session" volatility are:
mysql> select NAME, VOLATILITY from setup_instruments where volatility = 5;
+---------------------------------------------+------------+
| NAME                                        | VOLATILITY |
+---------------------------------------------+------------+
| wait/synch/mutex/sql/THD::LOCK_thd_data     |          5 |
| wait/synch/mutex/sql/THD::LOCK_thd_query    |          5 |
| wait/synch/mutex/sql/THD::LOCK_thd_sysvar   |          5 |
| wait/synch/mutex/sql/THD::LOCK_query_plan   |          5 |
| wait/synch/mutex/sql/THD::LOCK_current_cond |          5 |
+---------------------------------------------+------------+

The "volatility" column is purely informational,
to give the user (and the performance schema code) some hint about the
instrument runtime behavior.

Instruments with a low volatility index (PERMANENT = 1)
are created once at server startup, and never destroyed / re created
during the server normal operation (they are only destroyed during shutdown).

For example, the mutex "wait/synch/mutex/pfs/LOCK_pfs_share_list"
is defined with a volatility of 1,
which means this mutex is created once.
Possible overhead from the instrumentation itself (namely, mysql_mutex_init())
will have no effect for this instrument then.
Runtime overhead will happen only when locking / unlocking the mutex.

Instrument with a higher volatility index (say SESSION = 5)
are created and destroyed for every user session.
For example, the mutex "wait/synch/mutex/sql/THD::LOCK_query_plan" itself
is created when a session connects, and destroyed when the session disconnects.

This mutex will be more sensitive to performance schema overhead,
because overhead will come not only from the lock / unlock instrumentation,
but also from the mysql_mutex_create() and mysql_mutex_destroy()
instrumentation, which will be executed more often.

Another aspect of volatility concerns when an update in
setup_instruments.ENABLED actually has some effect.

The ENABLED column in table setup_instrument affects the subsequent instrumented
objects created, but has no effect on instruments already created.

For example,
  update setup_instruments set ENABLED=xxx
  where NAME = "wait/synch/mutex/pfs/LOCK_pfs_share_list"
has actually *no* effect: this mutex is permanent, and was created already
before the update was executed.
This mutex will never be created again, so the ENABLED value in table
setup_instrument will never be used.
To enable of disable this mutex, use table mutex_instances instead.

By contrast,
  update setup_instruments set ENABLED=xxx
  where NAME = "wait/synch/mutex/sql/THD::LOCK_query_plan"
will still not affect the LOCK_query_plan mutex for existing sessions,
but this will have an effect on new sessions connecting to the database.

Instruments that are more "volatile" will use new settings from table
setup_instruments sooner.

Column DOCUMENTATION semantic
-----------------------------

This column is documentation provided with the instrumentation, if any.

Note that this task provide the new DOCUMENTATION column,
not the content.
Providing documentation for every instrumentation points is out of scope for
this task.

TABLE performance_schema.setup_threads
======================================

New table.

CREATE TABLE `setup_threads` (
  `NAME` varchar(128) NOT NULL,
  `ENABLED` enum('YES','NO') NOT NULL,
  `HISTORY` enum('YES','NO') NOT NULL,
  `PROPERTIES` set('singleton', 'user') NOT NULL,
  `VOLATILITY` int(11) NOT NULL,
  `DOCUMENTATION` longtext,
  PRIMARY KEY (`NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

Privileges
----------

Column ENABLED is updatable.
Column HISTORY is updatable.
All other columns are read only.

Thread instruments
------------------

Instrumented threads are displayed in table setup_threads.
This allows to expose existing thread names, and attributes.

Thread instruments are prefixed with "thread",
for example "thread/sql/main".

Column ENABLED semantic
-----------------------

For background threads, setting the ENABLED value in table
setup_threads control whether threads have INSTRUMENTED='YES' or 'NO'
when subsequent threads for this instrument are created.

Changing the ENABLED value has no effect on threads already executing.

Column HISTORY semantic
-----------------------

For background threads, setting the HISTORY value in table
setup_threads control whether threads have HISTORY='YES' or 'NO'
when the subsequent threads for this instrument are created.

Changing the HISTORY value has no effect on threads already executing.

Column PROPERTIES semantic
---------------------------

This column is a set: multiple flags can be set for each instruments.

Property SINGLETON:
The singleton property indicates the thread has only one instance.
For example, there is only one "thread/sql/main" thread.

Property USER:
The user property indicates the instrument is directly related
to the user workload.
For example, threads execution a user session
like "thread/sql/one_connection" have the user property,
to differentiate them from system threads.

Column VOLATILITY semantic
--------------------------

Same as setup_instruments.

Column DOCUMENTATION semantic
-----------------------------

Same as setup_instruments.