Re: Transaction ID Wraparound Monitoring

On 07/30/2015 08:41 AM, Jan Keirse wrote:
> 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?
>

Well if you click on the parameters in the above page you will go to
their definitions:

So for autovacuum_multixact_freeze_max_age:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MULTIXACT-FREEZE-MAX-AGE

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