On 20 Jun 2005, at 18:46, Josh Berkus wrote:
Alex,
Hi, i'm trying to optimise our autovacuum configuration so that it
vacuums / analyzes some of our larger tables better. It has been set
to the default settings for quite some time. We never delete
anything (well not often, and not much) from the tables, so I am not
so worried about the VACUUM status, but I am wary of XID wraparound
nuking us at some point if we don't sort vacuuming out so we VACUUM
at least once every year ;)
I personally don't use autovaccuum on very large databases. For DW,
vacuuming is far better tied to ETL operations or a clock schedule of
downtime.
Downtime is something I'd rather avoid if possible. Do you think we
will need to run VACUUM FULL occasionally? I'd rather not lock tables
up unless I cant avoid it. We can probably squeeze an automated
vacuum tied to our data inserters every now and then though.
XID wraparound may be further away than you think. Try checking
pg_controldata, which will give you the current XID, and you can
calculate
how long you are away from wraparound. I just tested a 200G data
warehouse
and figured out that we are 800 months away from wraparound,
despite hourly
ETL.
Is this an 8.0 thing? I don't have a pg_controldata from what I can
see. Thats nice to hear though.
However not running ANALYZE for such huge
periods of time is probably impacting the statistics accuracy
somewhat, and I have seen some unusually slow queries at times.
Anyway, does anyone think we might benefit from a more aggressive
autovacuum configuration?
Hmmm, good point, you could use autovacuum for ANALYZE only. Just
set the
VACUUM settings preposterously high (like 10x) so it never runs.
Then it'll
run ANALYZE only. I generally threshold 200, multiple 0.1x for
analyze;
that is, re-analyze after 200+10% of rows have changed.
I will try those settings out, that sounds good to me though.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
