MitchDrage opened a new issue, #14671:
URL: https://github.com/apache/cloudstack/issues/14671
### problem
`StatsCollector` schedules `VmStatsCleaner` and `VolumeStatsCleaner` every
60 seconds on every management server.
A cluster with three management servers runs the same retention DELETE three
times a minute against the same database, leading to unnecessary DB load.
vm_stats has no index on timestamp, so each pass is a full table scan
(indexes were discussed in #8737). With 30-day retention our table is around 3M
rows, and each pass takes 2-3 seconds. Only the first pass each minute finds
anything to delete:
```
ms1 Removed a total of [0] vm_stats rows older than [Sun Oct 11 20:16:49
UTC 2026].
ms3 Removed a total of [0] vm_stats rows older than [Sun Oct 11 20:16:54
UTC 2026].
ms2 Removed a total of [0] vm_stats rows older than [Sun Oct 11 20:17:29
UTC 2026].
ms1 Removed a total of [0] vm_stats rows older than [Sun Oct 11 20:17:52
UTC 2026].
ms3 Removed a total of [0] vm_stats rows older than [Sun Oct 11 20:17:56
UTC 2026].
```
(We recently switched to 30 days of retention, which is why we're seeing 0
removed on each pass)
### versions
ACS 4.22, three management servers, MariaDB Galera cluster, KVM.
### The steps to reproduce the bug
1. Run two or more management servers against the same database.
2. Set vm.stats.max.retention.time > 0 (the default is 720).
3. Watch the management server logs at INFO, or the database slow log. Each
management server logs "Removed a total of [N] vm_stats rows" once a minute.
### What to do about it?
I think the cleaners should run on one management server only.
`VmDiskStatsUpdaterTask` in the same class already does this: it takes a
GlobalLock and runs only on the Up management server with the lowest id.
Wrapping both cleaners the same way would cut the work to one pass a minute.
Related to #14397. I'm happy to raise a PR.
**For discussion:**
I did some digging with some AI help and there might be other ways to run
this that might be more efficient.
**Option 1: delete by id**
The cleaner could find the first row in id order that is newer than the
cutoff, then run `DELETE ... WHERE id < <that id>`.
Both steps use the primary key so a pass only reads the expired rows plus
one and doesn't need a new index.
This approach relies on id order closely following timestamp order. On our
3M-row table about 0.5% of rows are out of order by up to 4 minutes, however
these rows were rows that were older than the cutoff, not newer, so those rows
can stay a few minutes past expiry.
Rows newer than the cutoff can't be matched and deleted, because the lookup
stops at the first one it finds. I confirmed this against our table by testing
a cutoff every 10 seconds across 28 days, and none of them matched a newer row.
**Option 2: delete by id, plus a 10-minute window**
This adds a second step to catch the out-of-order rows from Option 1. The
cleaner also finds the first row newer than the cutoff plus 10 minutes, then
runs `DELETE ... WHERE id >= <first id> AND id < <second id> AND timestamp <
<cutoff>`.
The second step only reads about 10 minutes of rows by primary key, so it
stays cheap. On our table a 5-minute window caught every leftover row, so 10
minutes leaves some headroom.
Estimated per pass at 1,000 VMs, one management server writing stats (PR
#14398), 30-day retention (43.2M rows, ~1,000 expiring each minute):
| | Current | Option 1 | Option 2 |
|---|---:|---:|---:|
| Rows read and locked | 43.2M | ~1,000 | ~11,000 |
| Read + lock | ~37 s | ~1 ms | ~9 ms |
| Delete 1,000 rows | ~25 ms | ~25 ms | ~25 ms |
| **Total** | **~37 s** | **~26 ms** | **~35 ms** |
I therefore recommend Option 2. It is significantly faster than the current
delete, which means less lock time and less load on the DB, and on our table it
deleted exactly the same rows as the current delete.
To confirm this I compared Option 2 against the current delete on our table
(3.08M rows) at 88 cutoffs: one per day for 28 days, 40 random cutoffs, and the
20 cutoffs where Option 1 left the most rows behind (30 rows each). At every
cutoff Option 2 deleted exactly the same rows as the current delete.
| Cutoff times tested | Cutoffs | Rows where Option 2 and current deletes
differ | Expired rows Option 1 left for a later pass |
|---|---:|---:|---:|
| One per day, 1-28 days ago | 28 | 0 | 0 |
| Random times | 40 | 0 | 0 |
| Times where Option 1 left the most rows | 20 | 0 | 30 each |
If anyone wants to run this on their own DB to cross-check, set `@cutoff` to
any time inside your data and run the following. It only reads, inside a
read-only transaction, and takes about a second per 3M rows.
`rows_where_option2_and_current_differ` should be 0.
```sql
START TRANSACTION READ ONLY;
SET @cutoff = UTC_TIMESTAMP() - INTERVAL 1 DAY; -- any cutoff inside your
data
SET @first_newer_id = NULL, @window_end_id = NULL;
SELECT MAX(id) + 1 INTO @max_id FROM vm_stats;
SELECT id INTO @first_newer_id FROM vm_stats WHERE timestamp >= @cutoff
ORDER BY id LIMIT 1;
SELECT id INTO @window_end_id FROM vm_stats WHERE timestamp >= @cutoff +
INTERVAL 10 MINUTE ORDER BY id LIMIT 1;
SET @window_end_id = COALESCE(@window_end_id, @max_id);
SELECT SUM(timestamp < @cutoff) AS current_deletes,
SUM(id < @first_newer_id OR (id < @window_end_id AND timestamp <
@cutoff)) AS option2_deletes,
SUM((timestamp < @cutoff) <> (id < @first_newer_id OR (id <
@window_end_id AND timestamp < @cutoff))) AS
rows_where_option2_and_current_differ,
SUM(id >= @first_newer_id AND timestamp < @cutoff) AS
expired_rows_option1_leaves_for_later
FROM vm_stats;
COMMIT;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
