WL#5461: PERFORMANCE SCHEMA MY.CNF DEFAULT SETUP
Affects: Server-Prototype Only — Status: Complete — Priority: Medium
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='<instrument name>=<on|off|true|false|1|0|counted>' 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_<name>=<on|off|true|false|1|0>' 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_<name>=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/<category>/<name>' --performance_schema_instrument='wait/synch/rwlock/<category>/<name>' --performance_schema_instrument='wait/synch/cond/<category>/<name>' --performance_schema_instrument='wait/synch/mutex/<category>' --performance_schema_instrument='wait/synch/rwlock/<category>' --performance_schema_instrument='wait/synch/cond/<category>' ... 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".
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.