From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
Cc: | Andres Freund <andres(at)2ndquadrant(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: autovacuum not prioritising for-wraparound tables |
Date: | 2013-02-03 22:58:43 |
Message-ID: | CAMkU=1yHxyZC_9H7dvYVLKG_Cj_rrx6r2CVOtgHbwvkW0V_Zpw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sun, Feb 3, 2013 at 9:25 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>
> I was able to confirm two cases where this was a consequence of the
> lazy truncate logic which Jan recently fixed, but there are clearly
> other problems which I didn't have much of a grasp on prior to this
> thread. The only thing I knew for sure was that a bulk load, and
> in particular a pg_dump/restore cycle, was a time bomb without
> VACUUM FREEZE, and users are often reluctant to add time for that
> to their maintenance window. If we can't load tuples as frozen in
> the first plance, the next best thing would be to nibble away at
> freezing them in the background, so that there is no single big
> hit. While proposals here seem to deal with very real problems
> which we should fix, I'm not sure that anything here addresses this
> issue.
It seems like we already have all the machinery we need for this.
Don't run VACUUM FREEZE during the maintenance window, instead at the
end of the maintenance window set vacuum_cost_delay to nibble
(whatever integer that translates to on your system) and let 'er rip.
Since vacuum_cost_delay is session settable, it wouldn't interfere
with the setting for other things.
The only real problem I see with this is how to restart it so that it
picks up where it left off if it should get interrupted.
I guess giving vacuumdb new options to set the vacuum_cost settings it
runs under would be a handy feature for doing this.
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2013-02-04 00:49:07 | Re: PATCH: Split stats file per database WAS: autovacuum stress-testing our system |
Previous Message | Simon Riggs | 2013-02-03 22:37:35 | Re: [COMMITTERS] pgsql: Mark vacuum_defer_cleanup_age as PGC_POSTMASTER. |