Re: Autovacuum fails to keep visibility map up-to-date in mostly-insert-only-tables

On 10/21/14, 4:36 PM, Jeff Janes wrote:
> On Mon, Oct 20, 2014 at 5:46 PM, Andres Freund <andres(at)2ndquadrant(dot)com <mailto:andres(at)2ndquadrant(dot)com>> wrote:
>
> On 2014-10-20 17:43:26 -0700, Josh Berkus wrote:
> > On 10/20/2014 05:39 PM, Jim Nasby wrote:
> > > Or maybe vacuum isn't the right way to handle some of these scenarios.
> > > It's become the catch-all for all of this stuff, but maybe that doesn't
> > > make sense anymore. Certainly when it comes to dealing with inserts
> > > there's no reason we *have* to do anything other than set hint bits and
> > > possibly freeze xmin.
> >
> > +1
>
> A page read is a page read. What's the point of heaving another process
> do it?
>
>
> It is only a page read if you have to read the page. It would seem optimal to have bgwriter adventitiously set hint bits and vm bits, because that is the last point at which the page can be changed without risking that it be written out twice. At that point, it has been given the maximum amount of time it can be given for the interested transactions to have committed and to have aged past the xmin horizon. I seem to recall that the main problem with that, though, is that you must be attached to a database in order to determine visibility, and bgwriter is not attached to a database.

It's also a bit more complex than a simple question of "is the page still in shared buffers". Our *real* last chance is when the page is about to be evicted from the filesystem cache; after that reading it back it will be extremely expensive (relatively speaking).

I think it's worth considering this, because if you have any moderate length transactions on a busy database bgwriter won't be able to help much; you'll be burning through shared buffers too quickly.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com