Re: Single pass vacuum - take 1

On Mon, Jul 18, 2011 at 2:20 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Jul 15, 2011 at 7:23 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> My additional requests would be that we can easily tell which blocks
>> have been modified like this, that we have a way to turn this off if
>> we get bugs for next few releases, that we check it all works with Hot
>> Standby just fine and that all the block inspection tools support it.
>
> To me, this seems like a bit too much of an internal change to justify
> adding a GUC.

I will be happy to remove it again when we have shown there are no
bugs.... getting this wrong is a data loss issue.

> But it probably is a good idea to ping the authors of
> the various block inspection tools -- does contrib/pageinspect care
> about this sort of thing, or just the out-of-core stuff?

I'm the author of the main sections of the pageinspect module, and
yes, I care. :-)

I want to be able to say easily and quickly "ahhh, that page was
touched by the new code". As mentioned above, this code has potential
for causing data loss and it is important to look at ways of
diagnosing bugs. Pageinspect was written to allow us to confirm at a
low level that HOT was working.

> I'd also think that it would be a good idea to consider (most likely
> as a separate patch) the idea you suggested upthread: skip phase 2 if
> the number of dead tuples is insufficient to justify the cost of
> scanning the indexes.  I've been wondering if it might make sense to
> couple that change with a decrease in vacuum_scale_factor - in effect,
> vacuum more frequently, but don't scan the indexes every time.
>
> One possibly useful metric for benchmarking these sorts of changes is
> to run a write workload for a while until the size of the tables
> stabilize and then measure how big they are.  If a given change causes
> the table size to stabilize at a lower value, that suggests that the
> change makes vacuum more effective at controlling bloat, which is
> good.  Conversely, if the change causes the table size to stabilize at
> a higher value, that suggests that we've made vacuum less effective at
> controlling bloat, which is bad.  In fact, I'd venture to say that
> anything that falls into the latter category is dead on arrival...

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services