In this post, we will see how to configure the Performance Schema and avoid it becomes a performance bottleneck. The default settings are a good starting point. Those have been extensively tested to ensure that in the vast majority of cases, they will not cause a severe overhead. It can be worth considering to enable some additional consumers and instruments.
While the below suggestions usually only causes a small or no overhead, it is worth testing whether the overhead of additional instruments and consumers is acceptable, and it is worth having a monitoring baseline before making the changes so it is clear what the change is when changing the configuration.
To enable all of the below suggestions, use (requires MySQL 5.7or later):
[mysqld] performance_schema_instrument = transaction=ON performance_schema_instrument = wait/lock/metadata/sql/mdl=ON performance_schema_instrument = memory/%=COUNTED performance_schema_consumer_events_statements_history_long = ON performance_schema_consumer_events_transactions_current = ON performance_schema_consumer_events_transactions_history = ON All butperformance_schema_consumer_events_statements_history_long are enabled by default in MySQL 8.0.
MySQL Server 5.6 and Later
If you use the Query Analyzer in MySQL Enterprise Monitor and want example queries and explain plans for the queries collected using the Performance Schema, it requires to keep a history of the latest queries executed:
performance_schema_consumer_events_statements_history_long = ON
This will keep (by default) the last 10000 statements executed. The overhead is in general small.
MySQL Server 5.7 and Later
In MySQL 5.7 it can be worth considering enabling the consumers and instruments discussed in the following sections.
Transactions
You can enable transaction monitoring with (in the MySQL configuration file):
performance_schema_instrument = transaction=ON performance_schema_consumer_events_transactions_current = ON
This will enable monitoring of current transaction. If you want to keep defailts of the the last 10 transactions for each connection, additional include:
performance_schema_consumer_events_transactions_history = ON
The overhead of this is expected to be very small.
Metadata Locks
To enable monitoring of metadata locks, add to the MySQL configuration file:
performance_schema_instrument = wait/lock/metadata/sql/mdl=ON
The overhead is expected to be small.
Memory
To track what is using memory, add to the MySQL configuration file:
performance_schema_instrument = memory/%=COUNTED
The overhead is in general small, but for connect/disconnect the overhead can increase to around 6% in a worst-case scenario.