On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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." -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
