VM and volume stats cleanup runs on every management server

Start by reading StatsCollector, VmStatsCleaner, VolumeStatsCleaner, and the VmDiskStatsUpdaterTask locking pattern mentioned in the issue. Check the cleaner scheduling and database deletion paths, including the related issues #14397 and #14398; the issue proposes both single-server scheduling and a faster deletion strategy. Done means preventing duplicate cleanup work across management servers and validating that cleanup preserves the intended retention behavior.

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):

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.

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.

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;