MisterRaindrop commented on issue #1850:
URL: https://github.com/apache/cloudberry/issues/1850#issuecomment-5018486583
Thanks for checking — and no worries, not finding anything on the
coordinator is actually expected here, so I don't think you missed anything. 🙂
The likely reason: in Greenplum/Cloudberry each segment is its own
PostgreSQL instance with its own transaction horizon, and a segment's vacuum
`OldestXmin` also folds in the *distributed* snapshot horizon. The
coordinator's `pg_stat_activity` / `pg_prepared_xacts` / `pg_replication_slots`
can't really see segment-local sessions or the distributed layer — and the
wraparound sweeping is happening on the segments (that's where the CPU was in
your earlier iotop). So whatever is holding the horizon back is probably *on a
segment or in the distributed layer*, which would explain why the coordinator
came up empty.
Could you try the same checks fanned out across the segments?
```sql
SELECT * FROM gp_dist_random('pg_stat_activity')
WHERE backend_xmin IS NOT NULL ORDER BY xact_start;
SELECT * FROM gp_dist_random('pg_prepared_xacts');
SELECT * FROM gp_dist_random('pg_replication_slots');
-- which segment / relation is actually the oldest:
SELECT c.gp_segment_id, c.relname, c.relkind, age(c.relfrozenxid) AS age
FROM gp_dist_random('pg_class') c WHERE c.relfrozenxid <> 0
ORDER BY age DESC LIMIT 20;
```
Once you see which segment is oldest, it can help to connect straight to it
and look locally: `PGOPTIONS='-c gp_role=utility' psql -h <seghost> -p
<segport> -d <db>`, then the same `pg_stat_activity` / `pg_prepared_xacts`
there.
A couple of things might be worth ruling out, since they could hold a
segment's horizon without showing up as a running query:
- A **mirror that's down or not in sync** — each primary keeps a replication
slot for its mirror, and if the mirror is lagging that slot's `xmin` can hold
the primary back. A quick `gpstate -e` (and a look at
`gp_segment_configuration` for anything not up / in-sync) alongside the
per-segment `pg_replication_slots` above might be telling.
- An **orphaned / in-doubt distributed transaction** (e.g. left over from a
crash or recovery) — `gp_distributed_xacts` might show something even when
there's no live session.
And if all of that still comes up empty, restarting the affected segment (or
the cluster) should reset the in-memory horizon — if the sweeping stops and
doesn't return, it was likely a stale/orphaned horizon; if it comes back
quickly, something is actively re-creating it and the per-segment views above
should catch it in the act.
Hope that helps narrow things down — happy to keep digging once you have the
per-segment output.
--
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]
