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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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. How To Create a Local Yum Repository for MySQL Enterprise Packages
  2. Understanding MySQL Privileges
  3. How to use mysqldump without CREATE TABLE statements
  4. How to configure resource groups for MySQL Server running on Linux
  5. MySQL Server Error – “Can’t Create A New Thread (errno 11)”
  6. How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server
  7. What is the MySQL Enterprise Monitor?
  8. Configure MySQL Router to Auto Restart of Failure using systemd
  9. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP
  10. MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE

You May Also Like

Primary Sidebar

Recent Posts

  • pw-cat Command Examples in Linux
  • pvs: command not found
  • pulseaudio: command not found
  • pulseaudio Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright