MisterRaindrop commented on issue #1850:
URL: https://github.com/apache/cloudberry/issues/1850#issuecomment-4998426966
For the high-CPU part, I reproduced it locally — and the vacuum/autovacuum
logic is actually correct; the loop is driven by a **held-back `OldestXmin`**,
with AO tables just making it visible.
**Root cause.** AO tables keep `relfrozenxid=0` by design, so only their
small heap relations (the TOAST and `pg_aoseg`/`pg_aovisimap`/`pg_aoblkdir` aux
tables) carry XIDs. An anti-wraparound vacuum can only advance `relfrozenxid`
up to `OldestXmin`. If `OldestXmin` is pinned older than `recentXid −
autovacuum_freeze_max_age`, every vacuum sets `relfrozenxid` to that old value,
the age is still over the limit, and autovacuum re-selects it forever — manual
`VACUUM` can't move past `OldestXmin` either. With many AO tables their
toast/aux heaps get swept continuously → the CPU you saw (`datfrozenxid` is
pinned too).
**Reproduced** (3-seg cluster from `main`): empty AO tables + one idle
`BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1;` to pin `OldestXmin`, then
advanced XIDs past `autovacuum_freeze_max_age` → ~34 "aggressive vacuum to
prevent wraparound of …pg_toast_…"/sec, `cutoff … far in the past` WARNING
firing repeatedly, `VACUUM` unable to advance the toast `relfrozenxid`.
**Terminating the holder session immediately stopped the loop.**
**Fix = find what's holding `OldestXmin` back** (~200M+ XIDs: an orphaned
prepared/2PC txn, a long/idle-in-transaction backend, or a stale replication
slot). On the node reporting `oldest xmin: 360673`:
```sql
SELECT * FROM pg_prepared_xacts ORDER BY prepared;
SELECT pid, state, age(backend_xmin) AS xmin_age, xact_start, query
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY xact_start;
SELECT slot_name, age(xmin), age(catalog_xmin) FROM pg_replication_slots;
```
Clear the offender → the next vacuum advances the horizon and the sweeps
stop. Cloudberry already flags this with the `cutoff for removing and freezing
tuples is far in the past` WARNING.
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
