SET PERSIST and mysqld-auto.cnf: The Config Drift Nobody Audits

The restart that should have taken six minutes took three hours, and the first two were pure confusion. We had doubled the primary’s RAM from 64GB to 128GB, edited my.cnf to raise innodb_buffer_pool_size to 96G, stopped MySQL, and brought it back up — and the buffer pool came back at 24GB, a number that appeared nowhere in any config file we managed. I checked for stray include files, for a second my.cnf in an unexpected location, for a configuration-management rollback; nothing. The answer, when I finally queried performance_schema.variables_info, was a column I had never once looked at in anger: VARIABLE_SOURCE = PERSISTED. Eight months earlier, during an OOM scare at 03:00, the on-call engineer had shrunk the buffer pool with SET PERSIST — a completely reasonable hotfix, since the expected reboot would otherwise have restored the memory-hungry value — and the change had been written to mysqld-auto.cnf, a JSON file in the datadir that the server reads after my.cnf at every startup. It had been silently winning over the option file ever since, through two config reviews and one migration. SET PERSIST is one of 8.0’s best features and one of its least audited, and this is the field guide to the drift it creates.

What does SET PERSIST actually do, and who wins at startup?

It changes the running value immediately and writes the variable to mysqld-auto.cnf in the datadir, and at startup the server reads that file after the option files — so a persisted value silently overrides whatever my.cnf says, with only command-line options ranking higher. The precedence ladder in full: compiled defaults at the bottom, then option files like /etc/my.cnf, then mysqld-auto.cnf, then command-line arguments on top. SET GLOBAL, by contrast, changes only the runtime value and evaporates at restart, which is why the hotfix-then-forget pattern so often ends in PERSIST: the on-call engineer correctly anticipates a restart and wants the safe value to survive it. The file itself is plain JSON — one object, variable names as keys — which means it is greppable, diffable, and back-up-able, none of which most teams do, because it lives in the datadir rather than in /etc where configuration management watches. Removal has its own subtlety: RESET PERSIST with a variable name drops that entry so the next restart falls back to my.cnf, while RESET PERSIST with no arguments wipes the entire file — a useful escape hatch and a nasty surprise if you run it expecting the first behavior.

-- where did this value actually come from?
SELECT variable_name, variable_source, variable_path
FROM performance_schema.variables_info
WHERE variable_name = 'INNODB_BUFFER_POOL_SIZE';
-- variable_source: COMPILED | GLOBAL | PERSISTED | COMMAND_LINE | ...

-- everything mysqld-auto.cnf currently overrides
SELECT * FROM performance_schema.persisted_variables;

-- drop one stray entry (falls back to my.cnf at next restart)
RESET PERSIST IF EXISTS innodb_buffer_pool_size;

-- and the raw truth, from the shell:
-- cat "$datadir/mysqld-auto.cnf" | python3 -m json.tool

How did a well-intentioned hotfix become invisible permanent config?

Because every layer of the usual config discipline looks at the wrong file: my.cnf is in git and reviewed, while mysqld-auto.cnf is in the datadir, unmanaged, unreviewed, and quietly supreme. Trace our failure and it is almost comically ordinary. The 03:00 hotfix was documented in the incident channel — SET PERSIST, shrink the pool, survive the reboot — and the follow-up ticket to restore the original value got deprioritized and then forgotten, as follow-up tickets do. For the next eight months the instance ran on a persisted 24GB pool while the git-managed my.cnf claimed 48GB, and nobody noticed because the server worked, monitoring showed the value that was actually running, and the only artifact that disagreed was a file nobody opened. The my.cnf edit during the RAM upgrade was reviewed and approved by two engineers — for a file that was no longer authoritative for that variable. This is the shape of all PERSIST drift: not recklessness, but a documentation system and an enforcement system that diverged, with the database faithfully executing the wrong one. The memory usage notes show why the running pool size matters to everything from OOM risk to query latency, which made this particular drift expensive as well as embarrassing.

How do you audit the drift between mysqld-auto.cnf and the option files?

Two performance_schema tables give you the whole answer from inside the server — persisted_variables lists what mysqld-auto.cnf is overriding, and variables_info reports where every variable’s current value came from — so the audit is a query, not a scavenger hunt. The weekly job we run now selects everything from persisted_variables and diffs it against the last known-good list; any new entry without a matching ticket is a finding. For forensics, variables_info is the sharper tool: its VARIABLE_SOURCE column distinguishes COMPILED defaults, GLOBAL runtime changes, PERSISTED file values, and COMMAND_LINE overrides, and VARIABLE_PATH even names the option file a value came from, which settles “which my.cnf is winning” arguments in one row. Two housekeeping rules complete the audit loop. First, back up mysqld-auto.cnf deliberately — mysqldump does not capture it since it is not data, and file-level backups do; losing it on a rebuild silently reverts every persisted tuning decision you ever made, which is drift in the other direction. Second, treat deliberate PERSIST usage as a source-of-truth decision: some teams declare mysqld-auto.cnf authoritative for a defined set of dynamic tunables and remove those keys from my.cnf entirely — a legitimate design, provided it is written down, because the unforgivable state is the ambiguous one where both files claim authority and nobody knows which wins without querying.

What house rules keep PERSIST an asset instead of a landmine?

Four rules, all born from that three-hour restart. Rule one: during an incident, prefer SET GLOBAL — the change dies at restart, which forces the follow-up conversation about making it permanent through the proper channel instead of letting the hotfix decide. Rule two: SET PERSIST goes through the same review as a my.cnf change, meaning a pull request that records the variable, the value, and the reason; the engineer applies it after merge, and the ledger in the repo is the audit baseline the weekly job diffs against. Rule three: incident hotfixes that genuinely need to survive a restart get SET PERSIST plus a ticket with a due date, and the weekly diff job pages the ticket’s owner when the entry outlives its window. Rule four: after any incident involving persisted variables, run RESET PERSIST on the strays — the upgrade field notes make the broader point that 8.0’s operational surface rewards teams who write their conventions down, and PERSIST is the sharpest example. Since adopting the four rules, mysqld-auto.cnf across the fleet holds exactly eleven entries, every one of them deliberate, reviewed, and known. The file did not get less powerful; it got legible, and legibility is the whole game with a config layer that outranks the one in git.

Where MonPG stands on MySQL

For MonPG product capabilities and setup information, see the MySQL monitoring page. Use the diagnostics in this article to identify the measurements and operational checks your deployment needs.

Related documentation

• MySQL: persisted system variables