The read replica has served reports since 2023, which took the analytical load off the primary and left one problem it cannot solve: a replica has the primary’s schema, which is the point of a replica and means every reporting index has to exist on the tables that take four hundred writes a second.
The symptom
$ ./bin/index-usage --table=orders --by-reader
index app reads report reads
PRIMARY 4,128,840 412
idx_customer_placed 1,204,882 1,208
idx_status_created 888,204 904
idx_report_customer_tier 0 1,204
idx_report_placed_status 0 888
idx_report_channel_month 0 412
... 8 more with zero application readsEleven indexes read only by reports, on tables the application writes constantly. Each one is a write cost on every insert to make a query fast that runs a few times a day, which is a trade nobody makes deliberately and everybody makes incrementally.
Why it happens
A replica exists to move load and cannot move schema. Any index a report needs must exist on the primary, be replicated, and be maintained on every write — which is the cost of using the same tables for two purposes.
The fix
The cost of a reporting index, measured
dropped one index on a staging copy under a synthetic
write load matching production:
orders INSERT p50 1.9ms → 1.82ms
orders INSERT p99 8.4ms → 7.6ms
one index is almost nothing. eleven:
orders INSERT p50 1.9ms → 1.1ms
orders INSERT p99 8.4ms → 3.2ms
index storage 14.2 GB
buffer pool hit rate 99.1% → 99.6%
the buffer pool figure is the one that matters. 14 GB
of index pages were competing with the working set.A schema shaped for the questions
CREATE TABLE rpt_order_facts (
order_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
customer_id BIGINT UNSIGNED NOT NULL,
customer_name VARCHAR(255) NOT NULL, -- denormalised
customer_tier VARCHAR(32) NOT NULL, -- denormalised
channel VARCHAR(32) NOT NULL,
placed_date DATE NOT NULL,
placed_month CHAR(7) NOT NULL, -- '2026-05'
line_count SMALLINT UNSIGNED NOT NULL,
net_minor BIGINT NOT NULL,
tax_minor BIGINT NOT NULL,
KEY idx_month_tier (placed_month, customer_tier),
KEY idx_date_channel (placed_date, channel)
) ENGINE=InnoDB;A pre-computed month column and a denormalised customer tier are both things that would be indefensible in the transactional schema and are exactly right here. The table answers eleven of fourteen reports with two indexes, where the same questions against the normalised schema needed eleven.
Denormalisation that is safe
the copy holds a customer's name and tier, which
change.
the reason that is safe: the table is TRUNCATED and
rebuilt nightly. there is no path by which the copy
can disagree with the source, because the copy is
either current or absent.
which is the property that distinguishes this from a
projection maintained by events — that arrangement can
diverge and this one cannot.
the cost: reports are up to 24 hours stale, and that
was agreed rather than assumed.A full rebuild removes every class of synchronisation bug, which is the failure mode that made the 2023 projection expensive to maintain. It costs freshness, and asking the three people who read these reports produced an answer of “a day is fine” from all three — which had never been asked in 2023.
The rebuild
-- into a staging table, then swapped
CREATE TABLE rpt_order_facts_new LIKE rpt_order_facts;
INSERT INTO rpt_order_facts_new
SELECT o.id, o.customer_id, c.name, c.tier, o.channel,
DATE(o.placed_at), DATE_FORMAT(o.placed_at, '%Y-%m'),
COUNT(ol.id), SUM(ol.net_minor), SUM(ol.tax_minor)
FROM orders o
JOIN customers c ON c.id = o.customer_id
LEFT JOIN order_lines ol ON ol.order_id = o.id
GROUP BY o.id;
RENAME TABLE rpt_order_facts TO rpt_order_facts_old,
rpt_order_facts_new TO rpt_order_facts;
DROP TABLE rpt_order_facts_old;Building into a new table and swapping with a single RENAME is what makes the rebuild atomic from a reader’s perspective — a truncate-and-repopulate leaves the table partially filled for the duration, which is the failure that produced two days of plausible-looking wrong reports in April.
What happens when it fails
the April incident: the source query timed out, the
job exited non-zero, and the tables were half
populated.
reports were served from a partially populated database
for two days and looked plausible — revenue down 40%,
which finance noticed and assumed was real.
what was missing: a check on the OUTCOME rather than
on the exit code.
row count within 5% of the source
the newest row is from yesterday
a checksum on one stable month
and the swap happens only if all three pass.A half-completed rebuild is worse than a failed one because the output is plausible, and an exit code says nothing about whether the data is complete. The three assertions before the rename are the whole fix, and finance believing a forty per cent drop for two days is what made this an incident rather than a bug.
Dropping the eleven
one at a time, over three weeks, with the write
latency graphed:
each drop is an ALTER on a large table. INPLACE and
LOCK=NONE, and each took between 40 seconds and 4
minutes.
and the check before each: zero application reads in
the last 30 days, from performance_schema, on a
replica with 30 days of uptime.
one was kept — idx_report_placed_status turned out to
serve an admin screen that runs four times a day and
is not a report.Verifying it worked
$ ./bin/insert-latency --table=orders --since=30d
p50 1.1ms p99 3.2ms # was 1.9 / 8.4
$ mysql -e "SELECT ROUND(SUM(stat_value*@@innodb_page_size)
/1024/1024/1024,1) FROM mysql.innodb_index_stats
WHERE table_name='orders' AND stat_name='size'"
8.4 # was 22.6
$ ./bin/report-timings --all
14 reports, median 0.4s, max 2.1s # was 11.4s max
$ ./bin/rebuild-check --last=30
runs: 30 swaps: 30 assertion failures: 1
# one run aborted before the swap. correctly.The single aborted run is the check working — a source query that returned four per cent fewer rows than expected, caused by a long-running transaction on the replica, and the previous night’s data was served instead. That is the outcome the April incident should have had.
What this costs
A third copy of the data, after the primary and the replica, with a schema that must follow the first. Every schema change to orders is now a potential change to the rebuild query, and nothing connects the two — a column rename breaks the rebuild at 03:00 rather than at deploy.
The rebuild is also permanent infrastructure with all the ways a nightly job silently stops. The three assertions protect against a bad rebuild and not against no rebuild, and the guard for that is an alert on the age of the newest row — which exists and is the sort of thing that gets added after the second incident rather than the first.