01The discrepancy

A 62 GB host, 53.7 GB resident for a single mysqld, and an InnoDB buffer pool of roughly 33 GB. Nothing in the configuration accounted for the difference, and the gap left the host one workload spike from the OOM killer.

02MySQL says it's clean

Each of the usual suspects was tested against measurements rather than assumed. Temporary tables held kilobytes. Theoretical per-connection buffers across the whole connection limit came to well under a gigabyte. And MySQL's own instrumentation showed allocation and release in balance:

SUM_ALLOC   97.6 GB
SUM_FREE    97.6 GB
CURRENT     81 MB

MySQL was freeing the allocations. The allocator was retaining the memory instead of returning it efficiently to the OS.

03Below the engine

Conventional MySQL diagnostics could not explain the missing memory, so the investigation moved below the database engine into Linux process memory. Of the 53.7 GB resident, 46.1 GB was anonymous and around 32 GB was backed by huge pages — the buffer pool behaving correctly. More than 1,100 anonymous mappings, RSS still climbing during observation, and clean MySQL accounting pointed at allocator behaviour.

04Root cause

glibc allocator fragmentation, amplified by Transparent Huge Pages set to always. Under sustained churn MySQL frees correctly, but the allocator holds the fragmented memory rather than returning it to the kernel, and THP makes that reclaim behaviour worse.

The leak wasn't in MySQL. The missing memory was sitting between the database engine and the operating system.

05Remediation & validation

Both changes targeted the allocator layer rather than the database configuration, and both were reversible: disable Transparent Huge Pages, then move MySQL onto jemalloc with decay tuned to release dirty pages on a fixed interval. It shipped with success criteria rather than a shrug — track RSS after deployment and compare MySQL-accounted memory against OS resident memory, with the two expected to land within roughly 15% of each other.

01The pattern

The same query text can be handed a worse plan after a statistics change, a recompilation, a shift in data distribution or any other optimizer input. The plan cache only shows what is running now, so it cannot answer whether this plan is worse than the last one. Query Store keeps the plans and their runtime history, which is what turns the question into a comparison — and across the estate the pattern recurred often enough that reacting by hand did not scale.

02One regression

GOOD PLAN   avg CPU ≈ 336 ms
BAD PLAN    avg CPU ≈ 2,015 ms
REGRESSION  ≈ 6x CPU per execution

Not the largest regression in the data and not the most important query — it is the one that makes the mechanism legible. An extreme ratio does not by itself mean a query dominates estate CPU; execution frequency still decides the impact.

03The same pattern, another system

81 recommendations, one database
(a different product — not the estate above)

TOP 1          26,474 s   30% of the set
TOP 5                     76%
TOP 10                    90%
REMAINING 71              10%

21 of those 71 are worth under a CPU-second each

A separate engagement — a different product on a different database, where the same technique was applied and the whole recommendation set captured. None of these figures belong to the estate above; they are here because the shape recurs. Almost all of the available saving sits in a handful of queries, which is why a recommendation count is a poor measure of work. The confidence score is a poor measure of it too — the fourth-largest saving in this set scores 79, earned by a mild 4.8x regression running 150,866 times, while a 115x regression scoring 99 is worth five seconds. Impact is the regression multiplied by how often the query runs, and only one of those terms is in the score.

04The control loop

good plan recorded
      ↓
new plan regresses
      ↓
SQL Server compares Query Store history
      ↓
last known good plan forced
      ↓
result verified
      ↓
keep forcing or revert

Bounded self-healing, not blind permanent plan forcing.

05The engineering decision

Automation takes the well-defined, reversible failure class; humans take the queries that keep coming back, where the data shape or the parameter sensitivity is the real problem. Automatic plan correction does not remove the need to tune queries — it removes the wait between a plan going bad and something acting on it. That wait is the measurable part: on the second system above, regressions were identified after a median of twenty-one executions on the bad plan. The CPU saving is a consequence of that latency, which makes Query Store state for an operational reliability loop rather than only a troubleshooting feature.

01The mismatch

instance setting    static
instance placement  dynamic

max server memory is configured on the instance, but a Failover Cluster Instance decides at runtime which node it lives on. After a failover a node can run a different number of SQL Server instances than it did before, so one static limit is either too high in aggregate — several FCIs landing on one node, too little left for the host — or unnecessarily low once an instance becomes the only SQL workload on its node.

02The policy

usable SQL memory budget = 90% of physical node memory

per-instance target      = usable SQL memory budget
                         / running SQL Server instances on that node

The percentage is an environment policy. The reusable engineering pattern is the reconciliation loop.

03The reconciliation loop

OBSERVE → CALCULATE → COMPARE → RECONCILE

Discover the SQL Server FCI resources actually online on the current node, and resolve each one's client-facing FCI identity rather than the physical cluster-node name to connect to it. Derive the target from physical node memory and the number of running instances, read the configured max server memory, and write only when the two differ — repeated runs are then free. Where a prerequisite observation is missing, the run fails closed instead of applying a plausible-looking guess.

04When it runs

failover / cluster event ┐
boot                     ├→ settle delay → reconcile
periodic scheduled run   ┘

Event-driven runs converge quickly after a known change; the periodic run repairs missed events and later drift. A cluster transition rarely produces a single event, so a debounce window collapses the burst into one reconciliation, and the task will not start a second copy of itself.

The delay is the design. A cluster mid-transition is not a state worth measuring — resources are still coming online, and an instance count read at that moment is a number that will be wrong minutes later.