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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461