Re: Transaction ID Wraparound Monitoring

On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver
<adrian(dot)klaver(at)aklaver(dot)com> wrote:
> 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.

Yes, I have seen that documentation and it is because of it that I
believed that my queries were ok, but now I think I may be
misinterpreting or misunderstanding the documentation and have to look
at more information, like autovacuum_multixact_freeze_max_age?

--

**** DISCLAIMER ****

http://www.tvh.com/glob/en/email-disclaimer

"This message is delivered to all addressees subject to the conditions
set forth in the attached disclaimer, which is an integral part of this
message."