Re: Transaction ID Wraparound Monitoring

On 8/4/15 2:47 AM, Jan Keirse wrote:
>> CAST (age(relfrozenxid) AS real) / CAST(trunc(((2^32)/2)-1-1000000) AS real)
>> >AS perc_until_wraparound_server_freeze
>> >
>> >
>> >(Note that we do this at the table level rather than the database level like
>> >you did, though, so that we have the information we need to tune the
>> >settings for individual tables.)
> Thanks for the correction regarding the 1.000.000 safety margin! I
> chose to monitor only the total value value to limit the amount of
> extra metrics in the monitoring database. In case the value increased
> we'll execute the queries to find out what table(s) is/are causing the
> problem interactively.

Something to be aware of; with certain workloads you can actually run
out of MXIDs faster than XIDs, and I don't think there's any easy way to
monitor MXID consumption.

BTW, you might find my talk from pgCon or the related video useful:

http://www.pgcon.org/2015/schedule/events/829.en.html
https://www.youtube.com/watch?v=b1fcvkl0ffQ
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com