• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Recommended Configuration of the MySQL Performance Schema

By admin

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.

Filed Under: mysql

Some more articles you might also be interested in …

  1. What are the various types of locking used in the MySQL Server
  2. How to Configure Multiple MySQL Servers On One System Using mysqld_multi
  3. How To Create a Local Yum Repository for MySQL Enterprise Packages
  4. Understanding MySQL Pluggable Authentication
  5. MySQL : Stored Procedure For Rotating the Audit Log
  6. How to List Tables Per Tablespace In MySQL Cluster
  7. How to gather information on the MySQL 8 Roles and Privileges assigned to a user without using SHOW GRANTS
  8. MySQL ‘show processlist’ statement
  9. Beginners Guide to Management of MySQL Cluster Log Files
  10. “expect” script to provide password to mysql_config_editor

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary