Configure, configure and configure
The performance schema is affected by three distinct set of configuration parameters, that take effect at compile time, server startup, or runtime.
Compile time options
When building from the source code, make sure the cmake flag 'WITH_PERFSCHEMA_STORAGE_ENGINE' is set to 'ON' (it is by default).
For packages built by Oracle, the performance schema is included by default.
Server startup options
Simply add the 'performance_schema' option to your my.cnf configuration file.
The effect of this option seems mis understood, leading people to think that the performance schema can only be enabled or disabled at server startup time, which is not the case.
The role of this startup option, with the related sizing parameters, is to initialize the performance schema internal memory buffers, and hook the performance schema instrumentation to the server and or plugins, so that everything is 'ready to go'. Note that 'ready to go' does not means 'enabled', it just means that things can be enabled or disabled later at runtime.
Runtime configuration
When the performance schema component is active in the server, it is listening to all events. Performance schema 'consumers' will decide, based on the content of the various setup tables in the performance_schema database, what to do with the event that just happened in the server code.
A table, performance_schema.setup_consumers, shows all the consumers available. This table has a ENABLED column that can be updated, giving the DBA full control on the internal performance schema execution flow.
There is a consumer which effectively controls globally all the data collection, so by turning this consumer ON or OFF, the entire performance schema instrumentation can be turned on or off, dynamically.
In 5.5, this consumer is named 'events_waits_current'. Note that this consumer in fact has two roles, as it controls both the overall data collection, and also the output table performance_schema.events_waits_current. This is a limitation, as there is no way to collect performance schema data independently of table events_waits_current.
In 5.6, this limitation has been lifted, so that the consumer which effectively control data collection is 'global_instrumentation', while the consumer named 'events_waits_current' controls only the table of the same name. The new consumer 'global_instrumentation' in 5.6 is the result of significant performance optimizations implemented in the code, made possible by decoupling the overall data collection from table events_waits_current.
Cheat sheet for mysql-5.5
To enable the performance schema at runtime (5.5), use the following query:
mysql> update performance_schema.setup_consumers set enabled='YES' where name='events_waits_current'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from performance_schema.setup_consumers; +----------------------------------------------+---------+ | NAME | ENABLED | +----------------------------------------------+---------+ | events_waits_current | YES | | ... ... | +----------------------------------------------+---------+ 8 rows in set (0.01 sec)
To disable the performance schema at runtime (5.5), use the following query:
mysql> update performance_schema.setup_consumers set enabled='NO' where name='events_waits_current'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from performance_schema.setup_consumers; +----------------------------------------------+---------+ | NAME | ENABLED | +----------------------------------------------+---------+ | events_waits_current | NO | | ... ... | +----------------------------------------------+---------+ 8 rows in set (0.01 sec)
Cheat sheet for mysql-5.6
To disable the performance schema at runtime (5.6), use the following query:
update performance_schema.setup_consumers set enabled='NO' where name='global_instrumentation';
To enable the performance schema at runtime (5.6), use the following query:
mysql> update performance_schema.setup_consumers set enabled='YES' where name='global_instrumentation';
