WL#5461: PERFORMANCE SCHEMA MY.CNF DEFAULT SETUP

INSTRUMENTS:

Provide default values for SETUP_INSTRUMENT.ENABLED in:
- the server command line options
- the my.cnf configuration file.

In some cases, a client connection issuing a statement like:
  UPDATE performance_schema.SETUP_INSTRUMENT set ...
has no effect on core server instrumented objects,
because such objects are initialized before the first client connection is accepted.

This interface is to configure default values in SETUP_INSTRUMENT,
so that a DBA effectively can control how core server code initialized only
during the server startup is to be instrumented.

CONSUMERS:

Provide default values for SETUP_CONSUMERS.ENABLED in:
- the server command line options
- the my.cnf configuration file.

Definitions
===========

States
------

An instrumented object can be in several states / sub states,
as documented below.

States defined at build time:
- instrumentation not compiled in
- instrumentation compiled in

States defined at deployment or runtime (sub states of "instrumentation compiled
in")

- None (the instance does not exist)
- Not instrumented
- Instrumented
  - Disabled (SETUP_INSTRUMENT.ENABLED= 'NO')
  - Enabled (SETUP_INSTRUMENT.ENABLED= 'YES')
    - Not timed (SETUP_INSTRUMENT.TIMED= 'NO')
    - Timed (SETUP_INSTRUMENT.TIMED= 'YES')

Note that there is a subtle difference between "Not instrumented" and
"Instrumented, Disabled", in terms of implementation and code path.

"Not instrumented" corresponds to
  m_psi == NULL
"Instrumented, Disabled" corresponds to
  m_psi->m_klass->m_enabled == false

Transitions
-----------

Obviously, there are no transitions between:
- instrumentation not compiled in
- instrumentation compiled in

The following applies to code compiled with instrumentation.

Transitions within the "Instrumented" state are dynamic and effectively executed
in real time, on every UPDATE on the SETUP_INSTRUMENT table.

Transitions between the "Instrumented" and "Not instrumented" state, however,
to not take immediately effect after an update on table SETUP_INSTRUMENT.
These transitions will take effect only after the instance is destroyed and
initialized again.

For some instrumented code, this happens as part of the nominal server execution:
for example, "wait/synch/mutex/sql/Delayed_insert::mutex" is a mutex initialized
during the execution of an INSERT DELAYED, and will be initialized according to
the content of SETUP_INSTRUMENTS when a new INSERT DELAYED statement is executed.

On the other hand, a mutex like "wait/synch/mutex/sql/LOCK_open" is only
initialized once at server startup, so any subsequent update on SETUP_INSTRUMENT
will only change in which substate of "Instrumented" the mutex is in, but will
never cause the mutex to transition to an "Not instrumented" state.

Overhead
--------

The instrumentation cost of an object depends on the state of this object.
Instrumentation cost do vary based on optimizations in the code, but no matter
how many optimizations are implemented, the following relative order is likely
to be always observed.

In increasing order, the cost of instrumentation depends on an instance internal
state, as follows:
- Not compiled in,
- Compiled in, not instrumented
- Compiled in, Instrumented, Disabled,
- Compiled in, Instrumented, Enabled, Not timed,
- Compiled in, Instrumented, Enabled, Timed.

By definition, the cost of the "Not compiled in" instrumentation is zero.

Problem with SETUP_INSTRUMENTS
==============================

Compiling options
-----------------

When there is a difference of overhead between:
- Not compiled in,
- Compiled in, Instrumented, Disabled,
and when the state
- Compiled in, not instrumented
is in practice unreachable, a work around to limit overhead is to force the code
to be not instrumented, with compiling options used at build time.

For example, #define PFS_SKIP_BUFFER_MUTEX_RWLOCK falls into this category.

This solution however does not scale: with 1 compiling option to statically
enable/disable some instrumentation, and with 200 instrumentation, having 200
different compiling options with the associated combinatorial complexity simply
does not scale.

The goal is to make the state:
- Compiled in, not instrumented
effectively reachable at runtime, and the overhead of this state sufficiently
low so that there is no compelling reason to use compile time flags for each
instrument.

Instrumentation states
----------------------

For core server objects, for example like LOCK_open, there is no way for a DBA
to control in which state the instrumentation is in.

Currently, by default, the content of SETUP_INSTRUMENT is always ENABLED, TIMED
by default, so that LOCK_open will always be in an "Instrumented" state. The
state "Not instrumented" is unreachable.

Changing the factory default configuration of SETUP_INSTRUMENT to ENABLED='NO'
will not resolve the problem, since the only reachable state will then be "Not
instrumented", with no way to instrument the object later.

Solution for SETUP_INSTRUMENTS
==============================

The default value of SETUP_INSTRUMENT to use when initializing an object is read
from the server startup parameters / the server configuration file.

Using this interface, a DBA can decide if a core server instrumented code is to
be in the "Instrumented" or "Not instrumented" state, and therefore can avoid
the instrumentation overhead for objects that have no value to the DBA.

The fundamental difference between:
- the static my.cnf interface
- the dynamic TABLE performance_schema.SETUP_INSTRUMENTS interface
is that the former can be used to configure objects initialized *before* the
server accepts the first connection, while the later only affects objects
initialized *after* an update statement in table SETUP_INSTRUMENTS.

These two interface are not redundant.
There is no need to specify the "TIMED='YES' / 'NO' parameter in my.cnf,
since this parameter can effectively be changed at runtime later.

Problem with SETUP_CONSUMERS
============================

Consumers have an impact on overhead, so typically only a few critical consumers
should be enabled in production.
The choice of which consumer to enable / disable by default is really dependent
on deployments, and up to the DBA.
To disable a consumer, an explicit UPDATE statement must be executed, which can
typically be given in a server startup init SQL file. (mysqld --init-file)

For complex deployments, this is not an issue since starting the server with an
init file is most likely used already.

For simple deployments, where only a my.cnf file is used, having to use and
administer a new server init file on top of my.cnf is an extra burden.

Solution for SETUP_CONSUMERS
============================

Allow to specify the initial value of consumers in the server my.cnf file, so
that the server will start with the desired performance schema consumers.

This solution improves the ease of use of the performance schema.

Configuration options
=====================

Instruments:

When initializing an object of a given instrumented name,
the performance schema code searches the server startup options / my.cnf file
for a default value instead of using a hard coded default.

The name of the option derives from the name of the instrument.

For example, to disable the instrument "wait/synch/mutex/sql/LOCK_open",
the user can specify in the my.cnf file:

--performance_schema_instrument='wait/synch/mutex/sql/LOCK_open=false'

The option syntax is the option name followed by the instrument name
and default instrument state:

--performance_schema_instrument='='

where
  on  = timed, counted
  off = not timed, not counted
  counted = not timed, counted

For example:
--performance_schema_instrument='wait/synch/mutex/sql/PAGE::lock=off'
--performance_schema_instrument='statement/sql/alter_table=enabled'
--performance_schema_instrument='wait/io/file/sql/send_file=counted'

(See the "States" section above for the difference between 'enabled', 'disabled'
and 'counted'.)

Note that only one instrument per option instance is supported. Valid but
otherwise unrecognized instrument names are retained in order to accommodate
plugin components that register instruments after server startup.

Other examples:

At deployment time, use
--performance_schema_instrument='wait/synch/mutex/innodb/buffer/block/mutex=false'
--performance_schema_instrument='wait/synch/rwlock/innodb/buf/block/lock=false'

instead of defining at build time:
# define PFS_SKIP_BUFFER_MUTEX_RWLOCK

Consumers:

When initializing a consumer, the performance schema code searches the server
startup options / my.cnf file for a default value instead of using a hard coded
default.

The option syntax is:

--performance_schema_consumer_='

where
  on  = events for this consumer will be collected
  off = events for this consumer will not be collected

The name of the option derives from the name of the consumer.
For example, to disable the instrument "events_waits_history_long",
the user can specify in the my.cnf file:

--performance_schema_consumer_events_waits_history_long=false


Group configuration options:
============================

For instrument names, a wildcard may be used to specify groups of instruments.
For example, 
--performance_schema_instrument='wait/synch/mutex/sql/LOCK%=false'
--performance_schema_instrument='wait/synch/rwlock/sql/%=false'
--performance_schema_instrument='wait/synch/cond/%=true'

Specificity determines precedence, so longer name strings override shorter
matching pattern regardless of order. For example, the instrument
'LOCK_thread_count' will remain enabled even though all other instruments within
the same category are subsequently disabled:
--performance_schema_instrument='wait/synch/mutex/sql/LOCK_thread_count=true'
--performance_schema_instrument='wait/synch/mutex/sql/%=false'

To enable all instruments:
--performance_schema_instrument='%=true'

To disable all instruments:
--performance_schema_instrument='%=false'

To count but not time all events:
--performance_schema_instrument='%=counted'

For event consumers, the format for specifying individual options is:
--performance_schema_consumer_=false

Note that wildcards are not supported for consumer options.

Default values:
===============

By default after server startup:
- all statement instruments are enabled
- all stage instruments are disabled
- all waits/synch/mutex instruments are disabled
- all waits/synch/rwlock instruments are disabled
- all waits/synch/cond instruments are disabled
- all waits/io/file instruments are enabled
- all waits/io/table instruments are enabled
- all waits/io/socket instruments are disabled
- the idle instrument is enabled
- consumer global_instrumentation is enabled
- consumer thread_instrumentation is enabled
- consumer events_waits_current is disabled
- consumer events_waits_history is disabled
- consumer events_waits_history_long is disabled
- consumer events_stages_current is disabled
- consumer events_stages_history is disabled
- consumer events_stages_history_long is disabled
- consumer events_statement_current is enabled
- consumer events_statement_history is disabled
- consumer events_statement_history_long is disabled

Limitations:
============

For implementation reasons, options of this form:
--performance_schema_instrument='wait/synch/mutex//'
--performance_schema_instrument='wait/synch/rwlock//'
--performance_schema_instrument='wait/synch/cond//'
--performance_schema_instrument='wait/synch/mutex/'
--performance_schema_instrument='wait/synch/rwlock/'
--performance_schema_instrument='wait/synch/cond/'
...
are not displayed in:
- SHOW VARIABLES
- INFORMATION_SCHEMA.VARIABLES

Note that these options doe not control whether an instrument is enabled or
disabled, they only control whether an instrument if enabled or disabled *by
default* after server startup: displaying a value in SHOW VARIABLE serves no
purpose and does not give any reliable information about the current server
configuration. To know what is the current configuration for instruments, the
proper command to use is "SELECT * from performance_schema.setup_instruments".