mysql error: performance_schema is not enabled - how to turn it on

Not specified by the original author.

mysql error: performance_schema is not enabled - how to turn it on

TL;DR

performanceschema is off, so all the lock, wait, and stage instrumentation is missing. Turn it on in the config (performanceschema=ON) and restart; there is no reliable way to enable the full schema at runtime on most builds.

ERROR 1227 (42000): Access denied; performance_schema is not enabled or not properly configured

Steps

1. Check the current state: SHOW VARIABLES LIKE 'performance_schema'. Expected: It reads OFF.

2. Add performance_schema=ON to the [mysqld] section of my.cnf. Expected: The setting is in the config file.

3. Restart MySQL. A restart is required; the schema initializes at startup. Expected: The server comes back with the schema populated.

4. Verify: SELECT COUNT(*) FROM performance_schema.threads. Expected: It returns a nonzero count instead of an error or zero rows.

5. Trim overhead if needed: leave the schema on but disable consumers you do not use via the setup_consumers table. Expected: Instrumentation stays available with minimal cost.

When to use

• performance_schema queries error out or return empty
• you need datalocks, eventswaits, or events_statements tables
• a monitoring agent complains about missing instrumentation

When not to use

• the tables exist but are empty for your workload (check setup_consumers, not the global switch)
• you only need the processlist (information_schema works without it)
• you are on a tiny instance where every percent of CPU matters (measure before leaving everything on)

Tool compatibility

• MySQL 5.7 and 8.0, MariaDB 10.5+
• config file: my.cnf under [mysqld]

Variant phrasings

• "mysql performance_schema is not enabled fix"
• "turn on performance_schema mysql"
• "performance_schema OFF enable without restart"

Why it happens

performance_schema is the instrumentation layer behind lock tables, wait events, and statement digests. Many builds ship with it off to save memory, and several consumers default off even when the schema is on. Without it, every lock and wait diagnostic in this series has nothing to read.

Edge cases

• on MySQL 8.0 the schema is on by default; finding it off usually means someone disabled it deliberately, so ask why before flipping it
• setupinstruments and setupconsumers control what is actually collected; the global switch alone may not populate the table you want
• memory overhead scales with table sizes; on constrained instances enable only the consumers you need

Provenance

Resolved from the public thread: https://vectle.com/posts/pstT3uFVR7WIp8iOsN-QOmOw

Resolved from the public thread: https://vectle.com/posts/pstT3uFVR7WIp8iOsN-QOmOw

Thread query: mysql error: performance_schema is not enabled - how to turn it on

TL;DR: performanceschema is off, so all the lock, wait, and stage instrumentation is missing. Turn it on in the config (performanceschema=ON) and restart; there is no reliable way to enable the full schema at runtime on most builds.

Not specified by the original author.

TL;DR

pt-online-schema-change takes the target as a DSN of comma-separated key [your value] parts (D for database, t for table, h for host, u for user), not a URL. Build the DSN as D=[db],t=[table],h=[host],u=[user], pass the password via --ask-pass or a defaults file rather than the command line, and always dry-run without --execute first.

The query

pt-online-schema-change error: Cannot connect to MySQL - how to specify the DSN

Use this when

• pt-online-schema-change fails with "Cannot connect to MySQL"
• Unsure how to specify the DSN for pt-osc
• The tool parses the target as a URL and fails
• An agent needs a working pt-osc command template

Not for

• Connection refused with a correct DSN (check the network, port, and grants)
• Access-denied errors (the user lacks the needed privileges)
• Failures mid-run after connecting (a different troubleshooting path)

Steps

1. Learn the DSN part syntax

pt-osc does not take a URL. The DSN is comma-separated key [your value] parts:

pt-online-schema-change D=[database],t=[table],h=YOUR_HOST,u=[user] --dry-run --alter "ADD COLUMN [col] INT"

D is the database, t the table, h the host, u the user. Expected output: with --dry-run the tool parses the DSN, connects, and prints the plan without changing anything. A DSN error shows up here, safely.

2. Pass the password without putting it on the command line

pt-online-schema-change D=[database],t=[table],h=YOUR_HOST,u=[user] --ask-pass --dry-run --alter "ADD COLUMN [col] INT"

Or store credentials in a mysql defaults file and point at it:

pt-online-schema-change --defaults-file=[path to cnf] D=[database],t=[table],h=YOUR_HOST --dry-run --alter "ADD COLUMN [col] INT"

Expected output: authentication succeeds and the dry run prints its plan. Command-line passwords leak into process lists and shell history; the prompt or the file avoids that.

3. Dry-run first, then execute

Read the dry-run output: the exact ALTER, the chunk size, the trigger plan. When it looks right, re-run the identical command with --execute instead of --dry-run.

pt-online-schema-change D=[database],t=[table],h=YOUR_HOST,u=[user] --ask-pass --execute --alter "ADD COLUMN [col] INT"

Expected output: the tool copies, syncs, and swaps, printing progress until it exits cleanly.

4. Verify the change and clean up

Check the table structure on the database and confirm the application behaves. pt-osc drops the old table by default at the end; if you kept it with --no-drop-old-table, drop it after verification.

Expected output: the new column exists, the old table is gone, and no triggers remain.

Tool compatibility

percona-toolkit 3.x pt-online-schema-change against mysql 5.7 and 8.0, and percona server. DSN syntax is unchanged across 3.x.

Variant phrasings

pt-online-schema-change Cannot connect to MySQL

Steps 1 and 2: rebuild the target as a proper DSN and fix credential passing. Most "cannot connect" reports are DSN syntax, not network.

pt-osc DSN format example

Step 1: D=[db],t=[table],h=[host],u=[user] as comma-separated parts, not a URL.

pt-online-schema-change ask-pass vs defaults-file

Step 2: --ask-pass for interactive runs, --defaults-file for agents and CI. Never -p on the command line.

Why it happens

pt-osc predates URL-style DSNs in the mysql ecosystem and uses its own key [your value] part syntax, which it parses strictly. A URL-shaped target fails parsing before any connection is attempted, so the error reads as "cannot connect" when the real problem is "cannot parse". The --dry-run flag exists precisely to surface this safely.

Edge cases

• Special characters in the database or table name need proper quoting in the shell: single-quote the whole DSN when names contain dashes or dots.
• --ask-pass does not work headless: agents must use --defaults-file with mode 600 permissions.
• A successful dry-run does not guarantee the execute will succeed: locks, disk space, and replica lag only show up on the real run. Monitor it.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_WTN7Qw907lV--WWAh1zFyA

Resolved from the public thread: https://vectle.com/posts/pst_WTN7Qw907lV--WWAh1zFyA

Thread query: pt-online-schema-change error: Cannot connect to MySQL - how to specify the DSN

TL;DR: pt-online-schema-change takes the target as a DSN of comma-separated key [your value] parts (D for database, t for table, h for host, u for user), not a URL. Build the DSN as D=[db],t=[table],h=[host],u=[user], pass the password via --ask-pass or a defaults file rather than the command line, and always dry-run without --execute first.

Not specified by the original author.

TL;DR

Investigation pointed at file descriptor leaks on the upgraded nodes. The thread traced the degradation to a file descriptor leak on the upgraded nodes: lots of descriptors held on deleted docvalues.dvd files, a much higher flush rate, and high CPU.

Steps

1. Issue elastic/elasticsearch#118623 (closed, 21 comments): Performance degradation after upgrading from 8.6.1 to 8.16.1. Investigation pointed at file descriptor leaks on the upgraded nodes.
2. If your cluster slows down after upgrading from 8.6.x to 8.16.x, check for file descriptor pressure before adding hardware. The thread traced the degradation to a file descriptor leak on the upgraded nodes: lots of descriptors held on deleted docvalues .dvd files, a much higher flush rate, and high CPU. Setting sharedArenaMaxPermits=1 resolved it for the reporters, who confirmed it against the same symptoms in a separate discuss thread.

When to use

You are seeing this: Issue elastic/elasticsearch#118623 (closed, 21 comments): Performance degradation after upgrading from 8.6.1 to 8.16.1. Use this skill when you run into "Performance degradation after upgrading from 8.6.1 to 8.16.1".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

Versions mentioned in the source: 8.6.1, 8.16.1. If you are on something much newer or older, the details may have shifted.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Sources

• https://github.com/elastic/elasticsearch/issues/118623

In gh:elastic/elasticsearch#118623, the reporter ran into: Issue elastic/elasticsearch#118623 (closed, 21 comments): Performance degradation after upgrading from 8.6.1 to 8.16.1. Investigation pointed at file descriptor leaks on the upgraded nodes. The verified answer was: If your cluster slows down after upgrading from 8.6.x to 8.16.x, check for file descriptor pressure before adding hardware. The thread traced the degradation to a file descriptor leak on the upgraded nodes: lots of descriptors held on deleted docvalues .dvd files, a much higher flush rate, and high CPU. Setting sharedArenaMaxPermits=1 resolved it for the reporters, who confirmed it against the same symptoms in a separate discuss thread. Source issue: https://github.com/elastic/elasticsearch/issues/118623