pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovac vs manual with analyze
4+ messages / 3 participants
[nested] [flat]
* Autovac vs manual with analyze
@ 2010-03-15 18:20 Scott Whitney <scott@journyx.com>
0 siblings, 2 replies; 4+ messages in thread
From: Scott Whitney @ 2010-03-15 18:20 UTC (permalink / raw)
To: pgsql-admin
I've got about 44GB of data in a few hundred production databases. I'm using PG 8.1.4, but upgrading today (even to the latest 8.1) is not an option. I know, I know. I wish it were, and it's slated here for q2, but I cannot even apply maintenance patches without a full testing cycle.
My auto-vac parameters are:
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 3 # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 400 # min # of tuple updates before vacuum
autovacuum_analyze_threshold = 200 # min # of tuple updates before analyze
autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for autovac
and auto-vacuum is running.
My problem is that each Saturday at midnight, I have to start a vacuumdb -f -z -a or my pg_clog dir never clears out.
The manual vacuum takes quite some time and impacts weekend customers.
So, my questions are:
a) Is the manual vacuum needed for performance reasons, or is auto-vac sufficient?
b) How do my settings look?
c) Is there a way that the clogs get cleared via autovac, would a full vac of just template1/template0 (if that last is possible) do it?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Autovac vs manual with analyze
@ 2010-03-15 19:24 Scott Whitney <scott@journyx.com>
parent: Scott Whitney <scott@journyx.com>
1 sibling, 1 reply; 4+ messages in thread
From: Scott Whitney @ 2010-03-15 19:24 UTC (permalink / raw)
To: pgsql-admin
I've got about 44GB of data in a few hundred production databases. I'm using PG 8.1.4, but upgrading today (even to the latest 8.1) is not an option. I know, I know. I wish it were, and it's slated here for q2, but I cannot even apply maintenance patches without a full testing cycle.
My auto-vac parameters are:
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 3 # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 400 # min # of tuple updates before vacuum
autovacuum_analyze_threshold = 200 # min # of tuple updates before analyze
autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for autovac
and auto-vacuum is running.
My problem is that each Saturday at midnight, I have to start a vacuumdb -f -z -a or my pg_clog dir never clears out.
The manual vacuum takes quite some time and impacts weekend customers.
So, my questions are:
a) Is the manual vacuum needed for performance reasons, or is auto-vac sufficient?
b) How do my settings look?
c) Is there a way that the clogs get cleared via autovac, would a full vac of just template1/template0 (if that last is possible) do it?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Autovac vs manual with analyze
@ 2010-03-15 22:35 Alvaro Herrera <alvherre@commandprompt.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 0 replies; 4+ messages in thread
From: Alvaro Herrera @ 2010-03-15 22:35 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
Scott Whitney wrote:
> So, my questions are:
>
> a) Is the manual vacuum needed for performance reasons, or is auto-vac sufficient?
> b) How do my settings look?
> c) Is there a way that the clogs get cleared via autovac, would a full vac of just template1/template0 (if that last is possible) do it?
>
Autovacuum should be sufficient, provided that the FSM settings are
large enough to hold 20% (plus a bit of slack) of your database total
size in dead tuples. If they are not, your database starts to bloat and
you need ugly hacks like vacuum full to recover the dead space.
In 8.1, clog is only cleared by database-wide vacuums, which IIRC
autovac doesn't do unless it does a for-Xid-wraparound run, which is not
often. Probably a weekly database-wide vacuum (not full, i.e. vacuumdb
without -f) is enough.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Autovac vs manual with analyze
@ 2010-03-18 09:02 Simon Riggs <simon@2ndQuadrant.com>
parent: Scott Whitney <scott@journyx.com>
1 sibling, 0 replies; 4+ messages in thread
From: Simon Riggs @ 2010-03-18 09:02 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
On Mon, 2010-03-15 at 13:20 -0500, Scott Whitney wrote:
> My problem is that each Saturday at midnight, I have to start a
> vacuumdb -f -z -a or my pg_clog dir never clears out.
Why is your pg_clog dir a problem?
> So, my questions are:
>
> a) Is the manual vacuum needed for performance reasons, or is auto-vac
> sufficient?
If you don't know why you are running it, then you should skip it and
measure the difference.
--
Simon Riggs www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-03-18 09:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-15 18:20 Autovac vs manual with analyze Scott Whitney <scott@journyx.com>
2010-03-15 19:24 ` Scott Whitney <scott@journyx.com>
2010-03-15 22:35 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-03-18 09:02 ` Simon Riggs <simon@2ndQuadrant.com>
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