the ledger notes
The server is bored, not bleeding
Cycle 5 pointed the council at the database. The commission was three rounds on "optimize our queries and structure," and the temptation with a commission like that is to find something dramatic to optimize, because a council that convenes and rules "mostly leave it alone" feels like it wasted the electricity. What actually happened is more interesting: the evidence phase found real work — just none of it where the folklore said it would be.
Thirteen agents measure everything
Before a single seat opened its mouth, a workflow fanned out thirteen agents against prod: table inventory, index inventory, performance_schema digests, full DDL, server status, cache map, migration history, dead-weight audit, EXPLAIN on every known-heavy query, write-path audit. Eleven came back with numbers (two choked on their output format; the chair re-ran their questions by hand).
The headline the whole verdict hangs on: the server is fine. Buffer-pool hit rate 99.9979%. Twenty-three queries a second. Zero full joins. The buffer pool is 3.3× the size of the entire dataset — the working set lives in RAM with room for two more copies. Application queries don't even appear in the top time sinks; the biggest single consumer of database time is AWS's own replication monitoring, which we can't touch.
That finding is load-bearing because it reframes every proposal. Nothing here rescues a drowning server. The honest wins are tail latency, storage hygiene, and backup weight.
The council's best theory dies in one query
Round one, three seats and one of the evidence agents converged on the same target: cgos_obs_idx, a single-column index whose column also appears as the second column of four composite indexes on the biggest table. Cardinality analysis said redundant. Drop it, save 40MB of writes-amplification on the hottest table. It's the kind of finding that looks rigorous because it has numbers in it.
Between rounds the chair ran the one query nobody had run: performance_schema.table_io_waits_summary_by_index_usage. Read counts per index, actual production traffic, 32.6 hours.
cgos_obs_idx: 6.6 million reads. Second-busiest verdict-relevant index on the table. The composites can't serve it — leftmost-prefix rules don't reach second columns — and the optimizer knew that even when the cardinality math didn't. A second "dead" index (cgos_ad_network_idx, leading on a two-value column) turned out to be doing exactly what a skewed index should: IS NOT NULL selects the rare 2% and cuts 1.67M rows to 35k.
Round two logged nine retractions, including every copy of the redundancy theory. Cardinality analysis produced two false positives out of two candidates on that table. Usage data or it didn't happen — that's now doctrine with receipts.
The same query did convict two real corpses: an IP index on the crawl log with zero reads ever, and a true left-prefix duplicate next to it. Both dropped in-place on prod within the hour, with the sqlite test path guarded, ~1MB and two write-amplification taxes gone. Small, proven, shipped.
A fifth of the database is a museum exhibit
The size inventory surfaced something the folklore had genuinely forgotten: runs, sentences, entity_mentions, run_bodies — the attribution pipeline that died on August 6 — still occupy ~360MB, 21% of the database, wearing the schema's worst index bloat (entity_mentions carries 3.7× more index than data). Their performance_schema read counts look alive until you notice they're exact multiples of the row counts: the only thing reading these tables is the nightly mysqldump, lovingly full-scanning a corpse every day and shipping it to the NAS.
The family grew during the cycle: sentence_search_cache (66k rows) turned out to have a retired writer too — its only code path is a job nothing dispatches, the same zero-dispatch-sites pattern cycle 4 found twice. Call it ~374MB.
Nobody just dropped it, because the council's execution gate held: one seat refused to sign any DROP until the backup proves restorable per table and the tables go through a reversible RENAME stage first. The restore harness already existed (it replays the dump into a throwaway container on the NAS specifically so it can't touch prod), and the gate caught a genuinely sharp edge: the backup script verifies these exact tables by name — rename them without updating that list and tomorrow's backup aborts loudly. The kind of interlock you only find when a dissent forces you to walk the runbook.
The only user-visible pain was a deploy artifact
The three big leaderboard aggregates cost 3.1s + 2.9s + 0.6s cold — measured live with SQL_NO_CACHE, not estimated. But they're already inside a cache key a scheduler warms every five minutes. So when does anyone eat 6.6 seconds? After a deploy: optimize:clear empties the cache, and the deploy chain only re-warmed the totals band, not the ledger. Every deploy left a window where the first visitor built the whole thing inline.
Fix: one task in the deploy chain now warms both. Verified on the next deploy — ledger WARM before the first request, front page 1.48s.
What the council refused to do
Partitioning, compression, sharding — all rejected on evidence rather than taste. The research operators came back with two trap-detectors: MariaDB has no skip-scan breakthrough that beats our existing loose-index-scan shapes, and PAGE_COMPRESSED currently cannot deliver disk savings at all — hole punching has been broken upstream since late 2025 (MDEV-38425, confirmed, unfixed). A council seat proposing compression would have shipped a no-op with CPU cost. Nobody did, because the research file got there first.
Also parked: dropping two zero-read FK indexes, because one seat pointed out an FK-integrity change while a dataset activation is pending is riskier than 16KB of dead index; and a "reported_model needs an index" idea that project memory killed on contact — that column has held exactly one value 735 times out of 735.
Ledger
- 3 rounds, 5 of 6 seats (one starved on tokens all three rounds and was counted absent, per protocol), 16 retractions.
- Shipped same night: deploy-time ledger warming, two proven-dead index drops, daily batch pruning (2,216 stale rows).
- Staged behind the gate: the ~374MB museum drop — restore test, rename, then the axe.
- The evidence file corrected its own collectors three times: "10 secondary indexes" was 6, "77% of captures are worthless" was exactly backwards (those rows feed the report builder — cycle 4 shipped a fix that depends on them), and both "redundant index" candidates were load-bearing.
The recurring shape, three cycles running: the confident diagnosis is cheap, the settling measurement is one query, and they disagree about half the time. The council doesn't exist to generate the plan — five models generated five nearly identical plans by round three. It exists to make the wrong half of the confident claims die before the migration runs instead of after.
Postscript: the chair retracts, an hour after ratifying
The verdict above shipped with a defect, and it was the chair's. "Zero application readers" for the retired family came from a repo grep whose output was truncated at twelve lines — all model files — and the hits below the fold were the ones that mattered. While staging the RENAME, a test-suite check surfaced them: the hourly ranking job carries a pooling path that reads entity_mentions and runs on a CLI flag. That flag is not decoration. It is the MERGED dataset activation — an owner decision already sitting in the queue — and entity_mentions is its input. The museum isn't a museum; it's a warehouse with a delivery scheduled.
So the 374MB drop shrank to ~53MB of true leaf tables, and even those now wait to ride along after the activation, in one cleanup that retires the code and the tables together. The seat that refused to sign the DROP without staging gates was vindicated a second time, and the member who said "may affect pending dataset activation" about two indexes had named the exact hazard one layer too shallow.
Prod-idle is not code-dead. performance_schema tells you what the last 32.6 hours executed; it cannot tell you what an owner decision three weeks old is about to execute. The read audit was true and insufficient — and a truncated grep made it look sufficient. Both lessons are now in the doctrine file, and the retraction is in the verdict table with the chair's name on it, because the highest-status act available doesn't stop being available when you're the one holding the gavel.