From: Kevin Grittner <kgrittn@ymail.com>
To: Natalie Wenz <nataliewenz@ebureau.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Dumping a database that is not accepting commands?
Date: Wed, 18 Sep 2013 15:03:06 -0700 (PDT)
Message-ID: <1379541786.19671.YahooMailNeo@web162901.mail.bf1.yahoo.com> (raw)
In-Reply-To: <9EB0F71A-0C2A-4ED1-A700-D9C52CBDAD4E@ebureau.com>
References: <B150080B-9E9A-456F-8B8D-110F07621165@ebureau.com>
<1379421830.30619.YahooMailNeo@web162902.mail.bf1.yahoo.com>
<A8C537BF-969B-41B9-8CFC-652F63E95F57@ebureau.com>
<1379450777.26197.YahooMailNeo@web162906.mail.bf1.yahoo.com>
<9EB0F71A-0C2A-4ED1-A700-D9C52CBDAD4E@ebureau.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgrittn@ymail.com, nataliewenz@ebureau.com
Subject: Re: Dumping a database that is not accepting commands?
In-Reply-To: <1379541786.19671.YahooMailNeo@web162901.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox