Re: Autovacuum on by default?

On Thu, Aug 17, 2006 at 01:29:57PM -0400, Matthew T. O'Connor wrote:
> Josh Berkus wrote:
> >>Is it time to turn on autovacuum by default in 8.2? I know we wanted
> >>to be on the side of caution with 8.1, but perhaps we should evaluate
> >>the experiences now. Comments?
> >
> >I'm in favor of this, but do we want to turn on vacuum_delay by default
> >as well?
>
> I thought about this, might be a good idea as it will mitigate the
> impact of vacuums, however it will also slow down vacuums, I'm a bit
> concerned that it won't be able to keep up on really large database, or
> that it'll fall really far behind after vacuuming a big table.

Probably a better idea is to turn on autovacuum_delay instead of
vacuum_delay.

> Also, if we do enable it, what is a good default?

I did some limited testing on one clients system and 10ms seemed a good
value for their fairly decent drive array. How that would translate on a
slower machine (which is what I'm more concerned with) I don't know.
Maybe 20ms would be better?

I also discovered on that system that upping vacuum_cost_limit and
vacuum_cost_page_dirty to 300 and 30 was a better setting. I suspect
that might partly be due to vacuum_cost_page_miss being pretty high.
That number makes sense for a page that actually comes off the disk, but
with so many folks still using 10% of memory for shared_buffers I think
it should be lower (5?), since a lot of page misses will come out of the
kernel cache anyway.

Of course if we had some way to determine if a page came out of the OS
cache...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461