I deleted 220,000 of 320,000 monitoring rows and not a single number on the status page changed
Health check records piled up every minute with no code to delete them, so the status page query scanned the whole table every time. With a daily summary table and deleting only success rows after 30 days, the query went from 0.68s to 0.012s.
#MySQL #Monitoring #Performance #Node.js #Operations
In my 100-day review of the monitoring server I listed five things to fix. One of them was that check records pile up forever. Back then I wrote that the size itself was still fine and moved on, but when I actually looked at it again, the query side was a bigger problem than the size. In this post I'll go over how I cut down the health check records, and how I made sure that not a single number on the status page changed while doing it. How much had piled up The health check sends a request to ten services every minute and writes each result as a row in the health_history table. There was no code anywhere that deleted them. It started in early June, so that's almost four months without deleting a single row. mysql SELECT COUNT(*), MIN(checked_at), MAX(checked_at) FROM health_history; +----------+---------------------+---------------------+ | COUNT(*) | MIN(checked_at) | MAX(checked_at) | +----------+---------------------+---------------------+ | 317971 | 2026-06-06 18:12:45 | 2026-09-29 22:29:04 | +----------+---------------------+---------------------+ data_length 14,417,920 index_length 11,173,888 That's 318,000 rows, 25.6MB with data and indexes combined. It grows by about 3,200 rows a day. On disk that's nothing, so I never paid attention to it. The query was the problem, not the size The thing that reads these records the most is the public status page. For each service it shows the last 90 days as bars, one per day, and each bar is that day's check count and success count. It was counting those straight from the raw rows every time. SELECT endpoint_id, DATE_FORMAT(checked_at, '%Y-%m-%d') AS day, COUNT(*) AS total, SUM(is_healthy = 1) AS ok FROM health_history WHERE checked_at = DATE_SUB(CURDATE(), INTERVAL 89 DAY) GROUP BY endpoint_id, day The execution plan showed it wasn't using an index. type key rows Extra ALL NULL 317,580 Using where; Using temporary There is an index on checked_at , but 90 days is most of the table, so MySQL decided reading it start to finish was better than going through the index. That decision itself is right. I timed it five times and it took about 0.68 seconds every time. The query that pulls only failed rows for incident details also read the whole table for the same reason, at 0.09 seconds. The status page has a 30 second cache, so visitors don't wait 0.68 seconds every time. Instead, someone takes that query every 30 seconds. And this number keeps growing as the records pile up. By this time next year it'll be four times what it is now. Do 90 days of bars really need all the raw rows? All one bar needs is two numbers: that day's check count and success count. The numbers for days that are already over will never change again. Yet to show them, I was recounting 3,200 rows a day for 90 days every time. So I decided to fold finished days into one row per day ahead of time. One row per day for ten services is only 3,650 rows even after a full year. CREATE TABLE health_daily ( endpoint_id INT NOT NULL, day DATE NOT NULL, total INT NOT NULL, ok INT NOT NULL, avg_response_time INT NULL, max_response_time INT NULL, PRIMARY KEY (endpoint_id, day) ); I put in average and max response time too. The status page doesn't use them right now, but once the raw rows are gone there's no way to get those numbers back, so I kept them while folding. So when do the raw rows get deleted? I thought about this one for a bit. Just deleting every row older than 30 days would be simple, but the status page has a tooltip that shows, when you hover over a bar, at what time that day something failed and why. That uses the failed rows' timestamps and error messages as is. Delete those, and the 90-day bars stay but you can't tell why one is orange. But there were only 35 failed rows in almost four months. That's 0.01% of 310,000 rows. So I decided to delete only success rows after 30 days and keep failed rows forever . The size goes down almost as much, and not a single incident record is lost. That was also the direction I'd noted in the 100-day review. I added one more condition on the deleting side. It only deletes rows for days that are already in the daily summary. If the summary fails on some day for whatever reason and the raw rows get deleted too, that day can never be recovered. // util/healthRetention.js async function purgeOldSuccess() { let total = 0; for (;;) { const [rows] = await pool.query( `SELECT h.id FROM health_history h JOIN health_daily d ON d.endpoint_id = h.endpoint_id AND d.day = DATE(h.checked_at) WHERE h.is_healthy = 1 AND h.checked_at DATE_SUB(CURDATE(), INTERVAL 30 DAY) LIMIT 5000` ); if (!rows.length) break; const [r] = await pool.query('DELETE FROM health_history WHERE id IN (?)', [rows.map((x) = x.id)]); total += r.affectedRows; if (rows.length 5000) break; } return total; } I delete in batches of 5,000 because the health check keeps writing to the same table every minute. Deleting 220,000 rows in one go could lock the table for a long time. Summarize only finished days, recount only the last 3 days The summary runs once when the server starts and every hour after that. Today isn't over yet, so it only folds up to yesterday. And instead of recounting everything each time, it only recounts the last 3 days. There's a reason. If you recount a day whose success rows have been deleted, only the failed rows are left, so that day's check count drops sharply. A day that was 99.9% becomes 0%. So the recount window must always be shorter than the 30 days used for deleting. This is something I could mess up later when changing numbers, so I left a warning in both the code and the docs. The status page query changed like this. Up to the day before yesterday it reads from the folded table, and only yesterday and today are counted from the raw rows. SELECT endpoint_id, DATE_FORMAT(day, '%Y-%m-%d') AS day, total, ok FROM health_daily WHERE day = DATE_SUB(CURDATE(), INTERVAL 89 DAY) AND day DATE_SUB(CURDATE(), INTERVAL 1 DAY) UNION ALL SELECT endpoint_id, DATE_FORMAT(checked_at, '%Y-%m-%d') AS day, COUNT(*) AS total, SUM(is_healthy = 1) AS ok FROM health_history WHERE checked_at = DATE_SUB(CURDATE(), INTERVAL 1 DAY) GROUP BY endpoint_id, day Counting yesterday from the raw rows might look a bit odd. At first I only counted today from the raw rows, but then after midnight and before the next summary run, yesterday's bar looks empty for up to an hour. One day of raw rows is about 3,200, which the index reads quickly, so counting yesterday from the raw rows is simpler and safer. Not a single number can change This was what I cared about most in this work. The uptime on the public status page is a number other people see, and if cleaning up records changed past uptime, that's not cleanup, that's tampering. So I set the order like this. First I dumped the whole table with mysqldump (2.1MB gzipped). Next I saved the /api/status response from before the change to a file. I filled the summary table first and then checked that the totals matched the raw row count. mysql SELECT COUNT(*), SUM(total) FROM health_daily; +----------+------------+ | COUNT(*) | SUM(total) | +----------+------------+ | 959 | 314915 | +----------+------------+ mysql SELECT COUNT(*) FROM health_history WHERE checked_at CURDATE(); +----------+ | COUNT(*) | +----------+ | 314915 | +----------+ Exactly the same. Then I restarted the server to run the cleanup. [Retention] health_history deleted 223777 success rows (older than 30 days) Then I took the /api/status response after the change and compared it to the one I'd saved, service by service and day by day. Check counts, uptime, incident details, all of it. Portfolio 99.996 - 99.996 days differing 0 Monitoring 99.988 - 99.988 days differing 0 Blog 99.977 - 99.977 days differing 0 Couple App API 99.984 - 99.984 days differing 0 Travel API 99.993 - 99.993 days differing 0 Coin Tracker 99.992 - 99.992 days differing 0 Game 99.984 - 99.984 days differing 0 taro 99.984 - 99.984 days differing 0 Ieum 100 - 100 days differing 0 Dapjeongneo(fishing) 100 - 100 days differing 0 All ten services, all 89 days, identical. 220,000 rows disappeared and not one number on the status page changed. Results Before After Rows 317,971 94,194 Data 14.4MB 5.5MB Index 11.2MB 9.0MB Daily summary query 0.68s 0.012s Failure list query 0.09s 0.0005s Failed rows 35 35 The query got more than 50 times faster. The execution plan also went from a full scan of 310,000 rows to reading 959 rows from the summary table plus 6,301 raw rows from the last two days. For the failure list I added a new (is_healthy, checked_at) index, so it goes straight to just the 35 failed rows. There's one thing I should be honest about. The index size didn't shrink as much as I expected. Rows dropped by 70%, but the index went from 11.2MB to 9.0MB. That's because I added two new indexes for the queries. One is for the failure list, and the other is (endpoint_id, checked_at) , used when the admin screen shows each service's recent records. Right now the index is bigger than the data. Still, the row count won't grow past 30 days' worth anymore, so the size is pretty much fixed from here. Leftover thoughts When I first made the table I thought "I'll add deletion later" and moved on, and that turned into four months. While records pile up nothing looks wrong, so it keeps getting pushed back. But deletion code is much easier to add while there are few records. This time, because the first run had to delete 220,000 rows, I had to go through the whole routine of taking a backup, deleting in batches, and comparing before and after. And when you cut down records, I'd recommend asking "what is this used to show?" first. Only after asking that did I realize one row per day is enough for success rows, and not a single failed row can be thrown away.