On 20 Jun 2005, at 15:59, Jacques Caron wrote:
Hi,
At 16:44 20/06/2005, Alex Stapleton wrote:
We never delete
anything (well not often, and not much) from the tables, so I am not
so worried about the VACUUM status
DELETEs are not the only reason you might need to VACUUM. UPDATEs
are important as well, if not more. Tables that are constantly
updated (statistics, session data, queues...) really need to be
VACUUMed a lot.
We UPDATE it even less often.
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 ;)
That would give you a maximum average of 31 transactions/sec...
Don't know if that's high or low for you.
It's high as far as inserts go for us. It does them all at the end of
each minute.
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?
ANALYZE is not a very expensive operation, however VACUUM can
definitely be a big strain and take a looooong time on big tables,
depending on your setup. I've found that partitioning tables (at
the application level) can be quite helpful if you manage to keep
each partition to a reasonable size (under or close to available
memory), especially if the partitioning scheme is somehow time-
related. YMMV.
Jacques.
That's not currently an option as it would require a pretty large
amount of work to implement. I think we will have to keep that in
mind though.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
