Re: Autovacuum in the backend

On Thu, 16 Jun 2005 12:54 pm, Alvaro Herrera wrote:
> On Thu, Jun 16, 2005 at 11:07:31AM +1000, Gavin Sherry wrote:
> > On Wed, 15 Jun 2005, Bruce Momjian wrote:
> > 
> > > I am going to start working on it.  I am concerned it is a big job.
> > >
> > > I will post questions as I find them, and the one below is a good one.
> > 
> > I'm wondering if effort is being misdirected here. I remember when Mark
> > Wong at OSDL was running pg_autovacuum during a dbt run, he was seeing
> > significant performance loss -- I think on the order of 30% to 40% (I will
> > try and dig up a link to the results).
> 
> I think those are orthogonal issues.  One is fixing whatever performance
> issues there are because of VACUUM.  Note that the fact that Mark was
> having such a drop in performance with autovacuum does only mean that
> at the enormous load under which the OSDL tests are run, autovacuum is
> not the best solution.  Not everybody runs with that sort of load
> anyway.  (In fact lots of people don't.)

I agree here.  There have been a couple of patches for improvements to VACUUM rejected in the past.
EG, partial vacuum.  If we have autovacuum in the backend, it doesn't matter about people 
vacuuming the wrong part of a file.  The system will manage it.

I'd also say there are a much greater number of people who will be able to fiddle with
an implemented autovac to improve its performance and load.  However I think there
are less people who can complete what Alvaro is doing.

> So, one issue is that at high loads, there are improvements to be made
> to VACUUM.  The other issue is to get VACUUM to run in the first place,
> which is what autovacuum addresses.

There are plenty of ideas to shoot around here.  Like 
- only run one iteration of a vacuum so you only clean indexes once, then stop the vacuum till the next cycle.
- Create the dead space man stuff with the bgwriter
- Make sure you have individual table analyze and vacuum stats so vacuum can be flexible to different tables.

Some of the autovac issues we have seen recently like O(n^2) with tables will go away by being in the backend.
So not everything will perform the same after the integration.

> 
> I can easily predict that we will make adjustments and improvements to
> VACUUM in the future, but I'm not so sure if it will happen before 8.1
> feature-freezes.  I have more confidence that we can integrate
> autovacuum for 8.1, which will be a leap forward.
> 
The big leap will be to get it in the backend, which will as Chris KL suggested stop people shooting themselves in the
foot.
I'm not sure what strict rules are imposed by Feature freeze, but there may be time for others to make some
improvementsbefore 8.1.
 
We have also looked at this for at least 2 releases now.  If it doesn't get in now, it will just get in for 8.2 and no
improvementstill 8.2.
 

Regards

Russell Smith