Check performance schema is enabled or disabled in Mysql or MariaDB

Leave a reply

The Performance Schema is:

  • Enabled by default in most recent versions of MySQL (5.6 and later) and MariaDB (10.1 and later).
  • However, it may be disabled by default in some older versions or depending on how the server was configured.

Check if the Performance Schema is Enabled

Run the following query:

SHOW VARIABLES LIKE 'performance_schema';

Output Example:

Variable_nameValue
performance_schemaON
  • ON: Performance Schema is enabled.
  • OFF: Performance Schema is disabled.

How to Enable the Performance Schema

  1. Temporary Activation (Without Restart)
    Run the following SQL command to enable it for the current session:
SET GLOBAL performance_schema = ON;

Permanent Activation (With Restart)
Add the following line to your MySQL/MariaDB configuration file (my.cnf or my.ini), typically located in /etc/mysql/ or /etc/my.cnf:

[mysqld]
performance_schema = ON

Then restart the database server:

sudo systemctl restart mysql

Considerations

  • Overhead: The Performance Schema introduces some (minimal) overhead. It’s lightweight but can impact performance slightly in high-traffic environments if many monitoring queries are run frequently.
  • Use Cases: Enable it if you need detailed monitoring and diagnostics (e.g., slow query analysis, resource usage tracking, etc.).

Leave a Reply