Re: Autovacuum in the backend

I totally agree.  I think we know pg_autovacuum is just a short-term
solution.  What we need is for someone to take the lead in this.

The TODO list has the ideas documented.  There is no reason the
background writer could not load the FSM directly with free pages.

---------------------------------------------------------------------------

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 these results can be dramatically improved if the focus is on a
> more effective vacuum.
> 
> In January I was in Toronto with Jan, Tom and others and some ideas about
> vacuum were being discussed. The basic idea is that when we dirty pages we
> need we set a bit in a bitmap to say that the page has been dirty. A
> convenient place to do this is when we are writing dirty buffers out to
> disk. In many situations, this can happen inside the bgwriter meaning that
> there should be little contention for this bitmap. Of course, individual
> backends may be writing pages out and would have to account for the
> dirty pages at that point.
> 
> Now this bitmap can occur on a per heap segment basis (ie, per 1 GB heap
> file). You only need 2 pages for the bitmap to represent all the pages in
> the segment, which is fairly nice. When vacuum is run, instead of visiting
> every page, it would see which pages have been dirtied in the bitmap and
> visit only pages. With large tables and small numbers of modified
> tuples/pages, the effect this change would have would be pretty
> impressive.
> 
> This also means that we could effectively implement some of the ideas
> which are being floated around, such as having vacuum run only for a short
> time period.
> 
> One problem is whether or not we have to guarantee that we account for
> every dirtied page. I think that would be difficult in the presence of a
> crash. One idea Neil mentioned is that on a crash, we could set all pages
> in the bitmap to dirty and the first vacuum would effectively be a vacuum
> full. The alternative is to say that we don't guarantee that this type of
> vacuum is completely comprehensive and that it isn't a replacement for
> vacuum full.
> 
> Thoughts? Comments?
> 
> Thanks,
> 
> Gavin
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073