|
Home > Archive > PostgreSQL Discussion > April 2006 > autovacuum settings
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
autovacuum settings
|
|
|
| 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.
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 60 # time between autovacuum runs, in secs
autovacuum_vacuum_th
reshold = 1000 # min # of tuple updates before
# vacuum
autovacuum_analyze_t
hreshold = 500 # min # of tuple updates before
# analyze
autovacuum_vacuum_sc
ale_factor = 0.4 # fraction of rel size before
# vacuum
autovacuum_analyze_s
cale_factor = 0.2 # fraction of rel size before
# analyze
autovacuum_vacuum_co
st_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
# vacuum_cost_delay
autovacuum_vacuum_co
st_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
vacuum_cost_page_hit
= 1 # 0-10000 credits
vacuum_cost_page_mis
s = 10 # 0-10000 credits
vacuum_cost_page_dir
ty = 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@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Jim Nasby 2006-04-07, 3:36 am |
| 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_th
reshold = 1000 # min # of tuple updates
> before
> # vacuum
> autovacuum_analyze_t
hreshold = 500 # min # of tuple updates
> before
> # analyze
> autovacuum_vacuum_sc
ale_factor = 0.4 # fraction of rel size before
> # vacuum
> autovacuum_analyze_s
cale_factor = 0.2 # fraction of rel size before
> # analyze
I recommend cutting the above 4 parameters in half.
> autovacuum_vacuum_co
st_delay = -1 # default vacuum cost delay
> for
> # autovac, -1 means use
> # vacuum_cost_delay
> autovacuum_vacuum_co
st_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_mis
s = 10 # 0-10000 credits
> vacuum_cost_page_dir
ty = 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@postgresql
.org so that
> your
> message can get through to the mailing list cleanly
>
--
Jim C. Nasby, Database Architect decibel@decibel.org
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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
|
|
|
|