Skip site navigation (1) Skip section navigation (2)

Re: Database-wide VACUUM ANALYZE

From: "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com>
To: "Jim Nasby" <decibel(at)decibel(dot)org>
Cc: "Bill Moran" <wmoran(at)collaborativefusion(dot)com>, "Francisco Reyes" <lists(at)stringsutils(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Database-wide VACUUM ANALYZE
Date: 2007-06-26 17:25:44
Message-ID: 357fa7590706261025l5b9cf016p7c61505643aabb9c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 6/25/07, Jim Nasby <decibel(at)decibel(dot)org> wrote:
>
> If you set that to 2B, that means you're 2^31-"2 billion"-1000000
> transactions away from a shutdown when autovac finally gets around to
> trying to run a wraparound vacuum on a table. If you have any number
> of large tables, that could be a big problem, as autovac could get
> tied up on a large table for a long enough period that the table
> needing to be frozen doesn't get frozen in time.
>
> I suspect 1B is a much better setting. I probably wouldn't go past 1.5B.


From my understanding of the docs, for tables that are not otherwise
vacuumed, autovac will be invoked on it once every autovacuum_freeze_max_age
minus vacuum_freeze_min_age transactions.  In our case that's 2 billion -
100 million = 1.9 billion transactions.  So when an autovac finally kicks
off on an otherwise non-vacuumed table, we are (2^31 - 1.9 billion) - 1
million =~ 250 million transactions away from shutdown.  (I guess that's
close to what you were saying.)

Most of our large (partitioned) tables are insert-only (truncated
eventually) so will not be touched by autovacuum until wraparound prevention
kicks in.  However the tables are partitioned by timestamp so tables will
cross the 1.9 billion marker at different times (some not at all, as the
data will have been truncated).

Do you still think the 250 million transactions away from shutdown is
cutting it too close?  Recall that the unintentional db-wide vacuum analyze
that was going on last week on our system took less than two days to
complete.

Steve

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2007-06-26 18:14:24
Subject: Re: PostgreSQL Configuration Tool for Dummies
Previous:From: Josh BerkusDate: 2007-06-26 15:26:28
Subject: Re: PostgreSQL Configuration Tool for Dummies

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group