Re: Adjust autovacuum naptime automatically

On Thu, Aug 17, 2006 at 03:00:00PM +0900, ITAGAKI Takahiro wrote:
>
> "Matthew T. O'Connor" <matthew(at)zeut(dot)net> wrote:
>
> Sorry, I should have explained more.
>
> > What is this based on? That is, based on what information is it
> > deciding to reduce the naptime?
>
> If there are some vacuum or analyze jobs, the naptime is shortened
> (i.e, autovacuum is accelerated). And if there are no jobs, the naptime
> is lengthened (autovacuum is decelerated).

IMO, the only reason at all for naptime is because there is a
non-trivial cost associated with checking a database to see if any
vacuuming is needed.

One problem that I've run across is that in a cluster with a lot of
databases it can take a very long time to cycle through all of them.

Perhaps a better idea would be to check a number of databases on each
pass. That way you won't bog the server down while checking, but it
won't take as long to get to all the databases.

Also, autovac should immediately continue checking databases after it
finishes vacuuming one. The reason for this is that while vacuuming,
the vacuum_cost_delay settings will almost certainly be in effect, which
will prevent autovac from hammering the system. Since the system won't
be hammered during the vacuum, it's ok to check more databases
immediately after finishing vacuuming on one.

Does anyone have any info on how much load there actually is when
checking databases to see if they need vacuuming?
--
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