Re: Transaction ID Wraparound Monitoring

On 07/30/2015 02:55 AM, Jan Keirse wrote:
> Hello,
>
> we have some very write heavy databases and I have our monitoring
> system watch the transaction age of my databases to be alerted before
> we get into problems in case autovacuum can't keep up to avoid
> transaction ID wraparound.
>
> The query I am executing is this:
> SELECT max(age(pg_database.datfrozenxid)) / 2147483648.0 * 100.0 AS
> "Percentage of transaction ID's used" FROM pg_database;
>
> My believe was that if this reaches 100 the database will stop
> accepting writes and one must vacuum. I have set alerts on 50 and 90,
> the result is around 9 so my believe was autovacuum is working fine
> for my workload.
> I often see autovacuum kicking in to prevent XID Wraparround, I
> thought that was just to be on the safe side and vacuum well before
> it's too late.
>
> However today I saw this post:
> http://blog.getsentry.com/2015/07/23/transaction-id-wraparound-in-postgres.html
>
> The following line has me worried:
> ... that database is going to reach a situation where the XID counter
> has reached its maximum value. The absolute peak is something around 2
> billion, but it can be far lower than that in some situations...
>
> Could someone shed some light on this? Is my query insufficient? Can
> the transaction wrapparound freeze problem indeed occur earlier? And
> if so, could someone suggest a better query to monitor?

I would look at:

http://www.postgresql.org/docs/9.4/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

Which includes some query examples.

>
> Kind Regards,
>
> Jan Keirse
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com