Thread: Transaction wraparound vacuum synchronicity

Transaction wraparound vacuum synchronicity

From
Michael Graham
Date:
Hi all,

I have a database with a number of tables that are partitioned monthly,
after that the tables are mostly read only (on rare occasions we may
delete from a table but normally we just drop the partitions).  Recently
I've noticed that we have a lot of these tables are vacuumed around the
same time, after a little big of digging I've realised that postgres is
vacuuming them to stop xaction wrap around.  So for example in a few
million xactions (later today) postgres is going to want to vacuum 37
tables for just this reason.

I know I can fiddle autovacuum_freeze_max_age and vacuum_freeze_min_age
to change how regularly the tables have this occur, and I can do this on
a per table basis in pg_autovacuum (yes this means I'm running an old
version, version 8.2) but what I'm wondering is how other people are
breaking this synchronisation?

Should I add a random value to the freeze_max_age for all the old tables
when I start a new month?  Or do the same with the freeze_min_age?
Perhaps I should just force a vacuum on some of the tables the break it?

Cheers,
--
Michael Graham <mgraham@bloxx.com>



Re: Transaction wraparound vacuum synchronicity

From
Andrew Sullivan
Date:
On Wed, Mar 09, 2011 at 10:52:25AM +0000, Michael Graham wrote:
> Perhaps I should just force a vacuum on some of the tables the break it?

It seems to me that you might want to VACUUM FREEZE the table when it
becomes read-only.  (I note that FREEZE is deprecated according to the
9.0 manual.  Too bad, that, because it seems to me that this is a use
case where one might want to put a thumb on the scale, and having to
twiddle a parameter just to affect one table is kind of
user-unfriendly.)

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Re: Transaction wraparound vacuum synchronicity

From
Noah Misch
Date:
On Wed, Mar 09, 2011 at 10:52:25AM +0000, Michael Graham wrote:
> I have a database with a number of tables that are partitioned monthly,
> after that the tables are mostly read only (on rare occasions we may
> delete from a table but normally we just drop the partitions).  Recently
> I've noticed that we have a lot of these tables are vacuumed around the
> same time, after a little big of digging I've realised that postgres is
> vacuuming them to stop xaction wrap around.  So for example in a few
> million xactions (later today) postgres is going to want to vacuum 37
> tables for just this reason.
>
> I know I can fiddle autovacuum_freeze_max_age and vacuum_freeze_min_age
> to change how regularly the tables have this occur, and I can do this on
> a per table basis in pg_autovacuum (yes this means I'm running an old
> version, version 8.2) but what I'm wondering is how other people are
> breaking this synchronisation?
>
> Should I add a random value to the freeze_max_age for all the old tables
> when I start a new month?

I haven't tried such a thing, but that seems like a great plan.  You wouldn't
need to change all old tables every month or actually make it random.  Each time
you create a monthly table, decrease the last-used-value by some fixed interval
and use that for the new table.  (You'll probably need to subtract, not add,
because per-table freeze_max_age cannot usefully exceed the global
autovacuum_freeze_max_age.)

> Or do the same with the freeze_min_age?

This should work about as well, perhaps slightly better.  Reducing
freeze_max_age can force freeze_min_age down, but not vice-versa.

> Perhaps I should just force a vacuum on some of the tables the break it?

That strategy does work.  However, if you ever run a full-database manual VACUUM
(in PostgreSQL < 8.4, anyway), they'd get resynchronized.  Fiddling the
per-table freeze_max_age or freeze_min_age does not have that liability.

nm