Thread: autovacuum settings

autovacuum settings

From
Jebus
Date:
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_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
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
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

Re: autovacuum settings

From
Jim Nasby
Date:
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