ProxySQL runtime vs memory drift: the unapplied change that surfaces on restart

ProxySQL’s three-layer configuration model makes changes safe and atomic, but every edit requires explicit promotion through two transitions. A missed step creates silent drift between what is staged (MEMORY), what is active (RUNTIME), and what persists on disk (DISK). The drift produces no error, no log entry, and no metric. The first visible symptom is often a restart that reverts to a stale configuration.

Two failure patterns dominate. First: an operator edits a MEMORY table but forgets LOAD ... TO RUNTIME. The change sits staged but inactive. The admin interface accepted it without complaint, so the operator assumes it took effect. Later, a restart reloads from DISK and the unsaved MEMORY change is discarded. Second: the operator loads to RUNTIME (the change takes effect) but forgets SAVE ... TO DISK. Everything works until a restart, and the change vanishes. Because RUNTIME matched MEMORY during that window, there was no signal that DISK was behind. Neither form of drift has a native ProxySQL metric. Detection requires comparing MEMORY tables against RUNTIME tables directly.

Config layers and drift types

ProxySQL stores configuration in three layers:

• MEMORY: the main schema tables (mysql_servers, mysql_users, mysql_query_rules, global_variables, etc.). Edits via the admin interface land here. Contents are editable but not active.
• RUNTIME: the runtime_* mirror tables (runtime_mysql_servers, runtime_mysql_users, etc.). These reflect what ProxySQL is actually using to route traffic, authenticate clients, and manage connection pools. Read-only.
• DISK: the SQLite database file loaded on startup. If a change was never saved here, it does not survive a restart.

Drift falls into two categories:

1. MEMORY differs from RUNTIME: an edit was made but not loaded. The change is staged but inactive.
2. RUNTIME differs from DISK: a change was loaded and is active, but was never saved. It will be lost on restart.

Both forms are invisible under normal operation. No warning log, no error counter, no stats_mysql_global variable reports drift.

flowchart TD
    A["Edit MEMORY"] -->|LOAD TO RUNTIME| B["RUNTIME active"]
    B -->|SAVE TO DISK| C["DISK persisted"]
    C -->|survives restart| D["No drift"]
    A -->|forgot LOAD| E["DRIFT: staged\nbut inactive"]
    B -->|forgot SAVE| F["DRIFT: active\nbut not durable"]
    F -->|restart| G["config reverts\nsilently"]

Common causes

Diagnosing drift

All commands connect to the admin interface on port 6032. Commands below use -p<password> for clarity; in production, prefer --defaults-file or MYSQL_PWD to avoid exposing credentials in the process list.

Step 1: check for recent restarts

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name = 'ProxySQL_Uptime';"

A recent restart with unexpected behavior changes is the classic signature of RUNTIME-to-DISK drift that surfaced when DISK was loaded.

Step 2: compare MEMORY and RUNTIME checksums

ProxySQL supports CHECKSUM commands for MEMORY and DISK, not a runtime-table checksum command. Compare runtime tables with SQL.

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT * FROM mysql_servers EXCEPT SELECT * FROM runtime_mysql_servers;"
mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "CHECKSUM MEMORY MYSQL SERVERS; CHECKSUM DISK MYSQL SERVERS;"

Repeat for each config module:

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT * FROM mysql_users EXCEPT SELECT * FROM runtime_mysql_users;"
mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "CHECKSUM MEMORY MYSQL USERS; CHECKSUM DISK MYSQL USERS;"
mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT * FROM mysql_query_rules EXCEPT SELECT * FROM runtime_mysql_query_rules;"
mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "CHECKSUM MEMORY MYSQL QUERY RULES; CHECKSUM DISK MYSQL QUERY RULES;"

If an EXCEPT result is empty, MEMORY and RUNTIME agree for that table. A nonempty result indicates an unapplied change or a runtime-only status transition (for example, a backend SHUNNED at runtime). The CHECKSUM MEMORY/CHECKSUM DISK pair compares the staged and persisted layers. Standard modules to check: mysql_servers, mysql_users, mysql_query_rules, global_variables.

Step 3: find specific row differences

There is no built-in diff command. Use targeted queries:

# Find servers in MEMORY that are not in RUNTIME (new additions not yet loaded)
mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup_id, hostname, port FROM mysql_servers
      WHERE (hostgroup_id, hostname, port) NOT IN (
        SELECT hostgroup_id, hostname, port FROM runtime_mysql_servers
      );"

Step 4: check cluster sync state (if applicable)

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT * FROM stats_proxysql_servers_checksums;"

Checksum mismatches between cluster peers indicate config divergence. Each node may be routing traffic differently.

Step 5: check cluster auto-save behavior

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT variable_name, variable_value FROM global_variables
      WHERE variable_name LIKE 'admin-cluster%save_to_disk';"

If these are true (the default), the cluster sync mechanism automatically saves loaded config to DISK, which can mask missing explicit SAVE steps.

Step 6: trace admin interface access

ss -tnp | grep ':6032'

This shows currently active admin connections only, not historical sessions. To trace past access, check ProxySQL admin audit logs if configured.

Metrics and signals to monitor

Fixes

MEMORY has changes that were never loaded

The change is staged but inactive. Activate it:

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

Load only the modules that have drift. After confirming the change is correct in RUNTIME, persist it:

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

RUNTIME has changes that were never saved to DISK

This is only discoverable after a restart has already reverted the config. The fix is to re-apply the change through the full cycle: edit MEMORY, LOAD TO RUNTIME, then SAVE TO DISK. There is no way to recover the lost RUNTIME state from before the restart.

The MEMORY change was wrong and should be discarded

Reset MEMORY to match the last-saved DISK state:

LOAD MYSQL SERVERS FROM DISK;
LOAD MYSQL USERS FROM DISK;

This loads the persisted DISK state back into MEMORY, overwriting the staged edit. Then LOAD ... TO RUNTIME to make RUNTIME match.

Warning: LOAD ... FROM DISK overwrites unsaved changes in MEMORY. Verify that the MEMORY changes you are discarding are truly unwanted before running this.

Cluster auto-save interference

If admin-cluster_mysql_users_save_to_disk or admin-cluster_mysql_servers_save_to_disk is true, cluster sync automatically saves loaded config to DISK. This masks the SAVE step and creates a false sense that changes persist without explicit SAVE. Disabling these variables does not stop cluster configuration sync; it stops automatic persistence of synced runtime configuration. If you disable them, keep an explicit SAVE runbook and verify DISK after every sync.

SET admin-cluster_mysql_users_save_to_disk = 'false';
SET admin-cluster_mysql_servers_save_to_disk = 'false';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

Prevention

Build an automated drift check. The core check is a SQL comparison between MEMORY and RUNTIME tables for each config module, plus MEMORY-vs-DISK CHECKSUM commands. Run it on a schedule and alert on any difference:

#!/bin/bash
# Drift detector: compare MEMORY vs RUNTIME checksums for each config module
# Run via cron or a scheduled job; alert if output is non-empty
ADMIN="mysql -u admin -p<password> -h 127.0.0.1 -P 6032 -N -B"

for mod in mysql_servers mysql_users mysql_query_rules; do
  diff=$($ADMIN -e "SELECT * FROM $mod EXCEPT SELECT * FROM runtime_$mod;")
  [ -n "$diff" ] && echo "DRIFT: $mod MEMORY-vs-RUNTIME: $diff"
done

for mod in "MYSQL SERVERS" "MYSQL USERS" "MYSQL QUERY RULES"; do
  mem=$($ADMIN -e "CHECKSUM MEMORY $mod;")
  disk=$($ADMIN -e "CHECKSUM DISK $mod;")
  [ "$mem" != "$disk" ] && echo "DRIFT: $mod MEMORY=$mem DISK=$disk"
done

The first loop compares table contents and therefore detects unapplied changes. The second loop compares ProxySQL’s own MEMORY and DISK checksum commands and detects unsaved staged state. Inspect any nonempty result before changing configuration.

Enforce the full workflow. Every config change through the admin interface must follow: edit MEMORY, LOAD TO RUNTIME, verify behavior, SAVE TO DISK. Treat SAVE as non-optional.

Audit config changes. The admin interface accepts any valid SQL without warning. Restrict admin access to localhost only via admin-mysql_ifaces and log who connects.

Watch for restart-triggered regressions. Correlate any ProxySQL restart with immediate changes in error rates, backend status distribution, or authentication patterns. A restart that loads stale DISK config will change behavior even though no operator made a recent change. The signature: runtime_mysql_servers matched mysql_servers before the restart, but both differed from DISK.

How Netdata helps

Netdata does not directly detect MEMORY-vs-RUNTIME table drift because no ProxySQL metric exposes it. The most effective approach is to run the MEMORY-versus-RUNTIME SQL comparison and MEMORY-versus-DISK checksums as a scheduled custom check alongside Netdata’s ProxySQL collector, and use Netdata’s per-second metrics to validate that post-restart behavior matches expectations.

Where Netdata adds value:

• ProxySQL_Uptime tracking makes restart events immediately visible. A drop to near-zero followed by behavior changes points to RUNTIME-DISK drift surfacing.
• Per-second metric collection captures the exact moment behavior shifts after a restart. Correlating error rate spikes, backend status changes, and auth failure patterns with the uptime reset helps confirm that drift was the cause.
• Backend status and connection pool metrics reveal routing differences from stale config loaded on restart. If a backend that was ONLINE is missing after restart, the DISK config may not include it.
• Cluster checksum monitoring (where available) surfaces config divergence between ProxySQL peers before it causes split-brain routing.

Related guides

• ProxySQL error 1045 Access denied for user: credential rotation not propagated
• ProxySQL backend connection pool exhausted: queries queuing for a free connection
• ProxySQL backend flapping between ONLINE and SHUNNED: monitor-induced oscillation
• ProxySQL OFFLINE_SOFT vs OFFLINE_HARD vs SHUNNED: what each backend status means
• ProxySQL backend SHUNNED: why a healthy backend gets pulled out of rotation
• ProxySQL Client_Connections_aborted rising: clients rejected or crashing on connect
• ProxySQL client connections at mysql-max_connections: frontend saturation and rejected clients
• ProxySQL config changes not applied: the LOAD TO RUNTIME / SAVE TO DISK trap
• ProxySQL config lost after restart: runtime never saved to disk
• ProxySQL connection storm after restart: an empty pool meeting a mass reconnect
• ProxySQL ConnERR climbing: backend connection errors and how to localise them
• ProxySQL ConnPool_get_conn_failure rising: the most direct pool-starvation signal