Monitor backend_xmin

[anayrat]

Hi,

When you use hot_standby_feedback you may want to monitor xmin delta between primary and secondary to be alerted in case of long running transaction on secondary which cause bloat on primary.

Regards,

[ioguix]

Can you elaborate here?
What's the point as the same xact running on the primary would result in the same bloat?
What would be the threshold criteria?
How would you set it?

I would rather add in backends_status the label xact_age (whatever its status) to check long running xact on both primary or secondary.

[anayrat]

Can you elaborate here?
What's the point as the same xact running on the primary would result in the same bloat?

The issue is similar to have a long running xact on a primary : we can't vacuum tuple. For example if you have a long running pg_dump on a standby server, this could degrade primary performance: index containing old tuple, HOT chain not cleaned etc

I've seen write spikes on primary server when the xact is "freed", backends can prune record by themselves and when you have a big workload this could lead to production incident.

What would be the threshold criteria?
How would you set it?

Hard to say, I mostly depends on your workload. Maybe 100K transactions is enough?

I would rather add in backends_status the label xact_age (whatever its status) to check long running xact on both primary or secondary.

That's a good idea but I think we should also check backend_xmin in pg_stat_replication from primary.

[rjuju]

I agree that retained xmin horizon can be a real problem in some environment.

Counter proposal: a new check, say oldest_xmin, that would give the oldest age(xmin) and oldest xmin for usual causes:

• single query
• transaction (when xact_start != query_start)
• 2PC
• replication slot
• walsender

• the overall oldest. This way you can easily know if and why bloat is accumulating

[ioguix]

@anayrat @rjuju: Good for me. It would requires some more specs now. And who want to do it now?

[rjuju]

I'm volunteering to work on that.

[ioguix]

done