Re: Transaction ID Wraparound Monitoring

On Tue, Aug 4, 2015 at 6:24 AM, William Dunn <dunnwjr(at)gmail(dot)com> wrote:
> Hello Jan,
>
> I think your calculation is slightly off because per the docs when
> PostgreSQL comes within 1 million of the age at which an actual wraparound
> occurs it will go into the safety shutdown mode. Thus the calculation should
> be ((2^32)-1)/2-1000000 rather than just ((2^32)-1)/2 as I think you are
> using.
>
> When I first started building out my group's PostgreSQL monitoring solution
> I too found the wording of transaction freeze to be a bit difficult to
> understand. For my team's internal documentation I have summarized it as
> follows, I hope it might be more clear:
>
> ...normal XIDs are compared using modulo-2^32 arithmetic, which means that
> ~(2^32-1)/2- transactions appear in the future and ~(2^32-1)/2 transactions
> appear in the past.
>
> This [Transaction ID freeze] behavior of autovacuum is primarily dependent
> on the settings autovacuum_freeze_table_age and autovacuum_freeze_max_age,
> which are set as database defaults but can also be specified on a per table
> basis (as storage parameters in CREATE TABLE or ALTER TABLE)
>
> When a table's oldest transaction reaches autovacuum_freeze_table_age, the
> next autovacuum that is performed on that table will be a vacuum freeze
>
> PostgreSQL implicitly caps autovacuum_freeze_table_age at
> 0.95*autovacuum_freeze_max_age.
>
> When a table reaches autovacuum_freeze_max_age PostgreSQL will force an
> autovacuum freeze on that table, even if the table would not otherwise be
> autovacuumed or autovacuum is disabled.
>
> PostgreSQL implicitly caps autovacuum_freeze_max_age at 2 billion
> (2000000000)
>
> The actual age that a wraparound occurs is ((2^32)/2)-1. When a PostgreSQL
> database comes within 1 million of this age (2^32/2-1-1000000) the database
> will go into the safety shutdown mode" and no longer accept commands,
> including the vacuum commands, and your only recovery option is to stop the
> server and use a single-user backend (where shutdown mode is not enforced)
> to execute VACUUM. This should, obviously, be avoided at all costs.
>
> References:
>
> http://www.PostgreSQL.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
> http://www.PostgreSQL.org/docs/current/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE
> http://www.PostgreSQL.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE
>
>
> Based on the above explanation we consider the following to be the most
> correct check for how close you are to an actual wraparound freeze:
>
> 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.

> However it is better to set autovacuum max freeze age well below that value
> and monitor that instead. Autovacuum should always do a vacuum freeze for a
> table that has exceeded max freeze age, and if you are monitoring for that
> you should avoid a wrap around freeze:
>
> CAST (age(relfrozenxid) AS real) / CAST ((least(autovacuum_freeze_max_age,
> 2000000000)) AS real) AS perc_until_freeze_max_age
>
>
> And ensure that value does not exceed 100%. Though it is important to note
> that max freeze age can be set on a per table basis, so to get the true
> autovacuum_freeze_max_age of a table (or the real max of the database) you
> would need to check the reloptions field of pg_class for that table and only
> if there is no value specified for '%autovacuum_freeze_table_age%' use
> current_setting('autovacuum_freeze_max_age')

I'll see to add this one to the monitoring too. Thanks for your clarifications!

--

**** 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."