Re: Dumping a database that is not accepting commands?

Natalie Wenz <nataliewenz@ebureau.com> wrote:

>>> autovacuum_freeze_max_age       | 800000000

> We talked a little bit about lowering the
> autovacuum_max_freeze_age, at least some, but there was concern
> that it would end up doing a lot more lengthy full-table scans.
> Is that a legitimate concern?

It will cause full-table scans to occur more often, but possibly
not as much as you fear if your baseline is based on how it behaved
before recent bug fixes.  It will tend to flush "hot" data from the
caches, at least to some degree.  The up side of doing it more
often is that it will have fewer writes to do each time it is run,
which might avoid write gluts that hurt performance more than the
reads.

> Would it be prudent to change any of the other values back to
> their defaults at the same time? For example, we have the
> autovacuum_vacuum_cost_delay set to 0, with the idea that we
> don't mind if we take a hit on performance while the autovacuum
> is running; our priority is that it be able to finish as quickly
> as possible. If we start the vacuum earlier, though, maybe that
> should be bumped up too?

Maybe, but you do have a lot of machine there.  You might not
notice the hit very much.


> Does the autovacuum do different work when it is vacuuming to
> prevent wraparound (and that's triggered when a table passes the
> autovacuum_max_freeze_age, right?) and a vacuum triggered by the
> table changing in size by a certain amount, or a manually-invoked
> vacuum?

A "normal" vacuum just visits pages which need work based on the
visibility map, so those really are almost a fixed amount of work
per week regardless of the frequency of runs.  It's just a matter
of whether you wait until a lot of work needs to be done and do it
all at once, or do smaller runs that nibble away at it.  The latter
usually has less noticeable impact.

> (Are there any books, or articles, that cover "Vacuuming and
> Autovacuuming: the gory details"?)

The best book I know of for this is Greg Smith's "PostgreSQL 9.0
High Performance":

http://www.postgresql.org/docs/books/

(Full disclosure, I was one of the technical editors, but don't get
money from sales.)


> Rereading the Routine Vacuuming page in the docs, this sentence
> caught my eye:
>
> "However, for static tables (including tables that receive
> inserts, but no updates or deletes), there is no need to vacuum
> for space reclamation, so it can be useful to try to maximize the
> interval between forced autovacuums on very large static tables.
> Obviously one can do this either by increasing
> autovacuum_freeze_max_age or decreasing vacuum_freeze_min_age."
>
> We generally never delete from this database at all. This case
> was unusual; I was migrating the data from one table to another
> because we added some columns, and changed the datatype of many
> of the columns from text to more appropriate types (timestamp,
> int, uuid, inet, etc). Ideally, even then we wouldn't have
> preferred to delete anything until the whole table was migrated,
> but disk space became an issue. Bleh.

That might be a reason to tweak the settings, but since recent
versions of vacuum skip heap pages that won't benefit from vacuum,
I probably wouldn't.

> With that in mind, would you still recommend putting the
> autovacuum_max_freeze_age back to 200 million?

Where possible, I'm a big fan of incremental change.  I might nudge
it in that direction a little at a time and watch the behavior.  I
do think that periodic VACUUM ANALYZE statements (weekly?) of the
database might be a good supplement to the autovacuum jobs,
especially if you have a time when load tends to be lower to
schedule that in.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company