Case study: fewer database writes for public-data products
How a MySQL server shared by public-data products stopped writing terabytes a day: measurement per statement, aggregated writes, a hot table and periodic jobs that read only what changed.
The challenge
Several public-data products use the same MySQL server: company data, a radar of new internet domains and a catalog of tools for AI agents.
In September 2026, the server wrote 1.1 to 2.2 TB a day. Most of it came from small statements that ran many times a day.
Approach
- Measure before you change: the performance_schema summaries by file and by statement show what writes the most.
- Count writes in 16 KiB pages, not in rows. With the doublewrite buffer, each dirty page is written twice.
- Do not upsert for each event into a large table. Sum in memory for each hour and write each key once, only if it changed.
- Put new keys in a small hot table and merge them once a day, in key order.
- Make periodic jobs read only what changed, by update time, and skip the run when nothing changed.
Measured results
- Temporary tables on disk: from 426 to 3 per hour (−99%), in the first hour after the memory change (26 September 2026).
- Domain radar: one write per domain per hour removed 58% of the rows, but only 12% of the bytes (from 6.20 to 5.43 GB per hour). Pages, not rows, set the cost.
- Domain radar with the hot table: outside the daily merge, no statement writes to the 126-million-row table (3 October 2026).
- Agent tools catalog: writing only the difference removed 60% of the writes to the table file.
- A monitoring metric that read 551 MiB every 2 minutes now reads nothing (3 October 2026).
- The whole server: from 1.1–2.2 TB a day to 650–740 GiB a day (measured on 3 October 2026).
Sources
The measurements come from the MySQL performance_schema (summaries by file and by statement) and from the disk log of the server, on 26 September and 3 October 2026.
Limits
- These numbers come from one server and one workload. Another schema needs its own measurement.
- The server total also includes changes in other systems on the same server. The items above isolate each fix.
- More database memory helps only after the unnecessary reads stop. It does not replace the fix.
Hire me for this
- Find the statements that cost the most writes and reads, with the numbers before and after.
- Rewrite periodic jobs to read only what changed, and add indexes without stopping the database.
Let's assess your project
I reply with scope and a proposal.