Re: large table vacuum issues

On Jan 4, 2008 7:29 PM, Ed L. <pgsql(at)bluepolka(dot)net> wrote:
> On Friday 04 January 2008 6:21 pm, Scott Marlowe wrote:
> >
> > Have you tried adjusting the
> >
> > #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
> >
> > settings to something so as to make vacuum less intrusive?
> > might be the easiest fix.
>
> Any particular suggested changes for these parameters?

Well, it really depends on your I/O subsystem, but a good start would
be to read this section of the manual:

http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

I'd start setting the delay to 10 or 20 and seeing if vacuuming has a
low enough impact to allow it to run in the background, even during
peak hours.

Keep an eye on vmstat / iostat output while vacuum is running to see
if you're flooding your I/O or not.

note that there's a whole other set of vars for autovacuum (at least
in 8.2, don't know about 8.1) that you can set so that regular vacuums
can happen with greater or less priority than autovacuuming.