Re: autovacuum settings

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@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