How to Enable Performance Schema in MySQL: Step-by-Step Guide

To enable performance_schema in MySQL, set performance_schema=ON in the MySQL configuration file (my.cnf) under the [mysqld] section and restart the MySQL server. You can verify it is enabled by running SHOW VARIABLES LIKE 'performance_schema';.

Syntax

To enable Performance Schema, add the following line to your MySQL configuration file (my.cnf or my.ini):

• performance_schema=ON: Turns on the Performance Schema feature.

This setting must be placed under the [mysqld] section and requires a server restart to take effect.

ini

[mysqld]
performance_schema=ON

Example

This example shows how to enable Performance Schema, restart MySQL, and verify it is active.

bash

# Step 1: Edit my.cnf (Linux) or my.ini (Windows) and add:
[mysqld]
performance_schema=ON

# Step 2: Restart MySQL server
sudo systemctl restart mysql

# Step 3: Connect to MySQL and check status
mysql> SHOW VARIABLES LIKE 'performance_schema';

Output

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

Common Pitfalls

Common mistakes when enabling Performance Schema include:

• Not placing performance_schema=ON under the [mysqld] section, so it is ignored.
• Forgetting to restart the MySQL server after changing the configuration.
• Trying to enable Performance Schema dynamically at runtime, which is not supported.
• Using an older MySQL version that does not support Performance Schema (introduced in MySQL 5.5).

ini

[client]
# Wrong: placed under [client] section, will not enable
performance_schema=ON

# Correct:
[mysqld]
performance_schema=ON

Quick Reference

Step	Action	Notes
1	Edit MySQL config file	Add 'performance_schema=ON' under [mysqld]
2	Restart MySQL server	Required for changes to apply
3	Verify status	Run SHOW VARIABLES LIKE 'performance_schema';
4	Use Performance Schema tables	Query tables like performance_schema.events_statements_summary_by_digest

Key Takeaways

Enable Performance Schema by adding 'performance_schema=ON' under [mysqld] in the MySQL config file.

Restart the MySQL server after changing the configuration to activate Performance Schema.

Verify Performance Schema is enabled by running SHOW VARIABLES LIKE 'performance_schema';

Performance Schema cannot be enabled dynamically at runtime; it requires a server restart.

Ensure your MySQL version is 5.5 or newer to use Performance Schema.