On Apr 6, 2006, at 8:57 AM, Jebus wrote:
I am totally new to postgres and I wondering what settings should I be
using for autovacuum ?
For now I just uncommented all the defaults, is this good enough ? The
database is not large but it is a web database so there is a lot of
inserts and updates.
There's no reason to uncomment the defaults. They're defaults. :)
Unless you change settings they'll be used.
autovacuum = on # enable autovacuum
subprocess?
autovacuum_naptime = 60 # time between autovacuum
runs, in secs
autovacuum_vacuum_threshold = 1000 # min # of tuple updates
before
# vacuum
autovacuum_analyze_threshold = 500 # min # of tuple updates
before
# analyze
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
# analyze
I recommend cutting the above 4 parameters in half.
autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay
for
# autovac, -1 means use
# vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit
for
# autovac, -1 means use
# vacuum_cost_limit
I also uncommented this too I am not what they are though ?
vacuum_cost_delay = 0 # 0-1000 milliseconds
If you set vacuum_cost_delay > 0 then every time vacuum accumulates
enough 'credits' it will sleep for that long. So with the default
settings below, vacuum would sleep after dirtying 10 pages. Or
reading 20 pages that weren't in PostgreSQL's buffer (that cost is
probably too high, depending on how much memory you have on the server).
vacuum_cost_page_hit = 1 # 0-10000 credits
vacuum_cost_page_miss = 10 # 0-10000 credits
vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 200 # 0-10000 credits
---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that
your
message can get through to the mailing list cleanly
--
Jim C. Nasby, Database Architect decibel@xxxxxxxxxxx
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
