Re: Proposal: Another attempt at vacuum improvements

On Thu, May 26, 2011 at 9:40 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, May 25, 2011 at 11:51 PM, Pavan Deolasee
> Having said that, it doesn't excite me too much because I
>> think we should do the dead line pointer reclaim operation during page
>> pruning and we are already holding cleanup lock at that time and most
>> likely do a reshuffle anyways.
>
> I'll give that a firm maybe.  If there is no reshuffle, then you can
> do this with just an exclusive content lock.  Maybe that's worthless,
> but I'm not certain of it.  I guess we might need to see how the code
> shakes out.
>

Yeah, once we start working on it, we might have a better idea.

> Also, reshuffling might be more expensive.  I agree that if there are
> new dead tuples on the page, then you're going to be paying that price
> anyway; but if not, it might be avoidable.
>

Yeah. We can tackle this later. As you suggested, may be we can start
with something simpler and then see we need to do more.

>
>> There are some other issues that we should think about too. Like
>> recording free space  and managing visibility map. The free space is
>> recorded in the second pass pass today, but I don't see any reason why
>> that can't be moved to the first pass. Its not clear though if we
>> should also record free space after retail page vacuum or leave it as
>> it is.
>
> Not sure.  Any idea why it's like that, or why we might want to change it?
>

I think it precedes the HOT days when the dead space was reclaimed
only during the second scan. Even post-HOT, if we know we would
revisit the page anyways during the second scan, it makes sense to
delay recording free space because the dead line pointers can add to
it (if they are towards the end of the line pointer array). I remember
discussing this briefly during HOT, but can't recollect why we decided
not to update the FSM after retail vacuum. But the entire focus then
was to keep things simple and that could be one reason.

> Currently, I believe the only way a page can get marked all-visible is
> by vacuum.  But if we make this change, then it would be possible for
> a HOT cleanup to encounter a situation where all-visible could be set.
>  We probably want to make that work.
>

Yes. Thats certainly an option.

We did not discuss where to store the information about the start-LSN
of the last successful index vacuum. I am thinking about a new
pg_class attribute, just because I can't think of anything better. Any
suggestion ?

Also for the first version, I wonder if we should let the unlogged and
temp tables to be handled by the usual two pass vacuum. Once we have
proven that one pass is better, we will extend that to other tables as
discussed on this thread.

Do we need a modified syntax for vacuum, like "VACUUM mytab SKIP
INDEX" or something similar ? That way, user can just vacuum the heap
if she wishes so and can also help us with testing.

Do we need more autovacuum tuning parameters to control when to vacuum
just the heap and when to vacuum the index as well ? Again, we can
discuss and decide this later, but just wanted to mention this here.

So are there any other objections/suggestions ? Anyone else cares to
look at the brief design that we discussed above ? Otherwise, I would
go ahead and work on this in the coming days. Of course, I will keep
the list posted about any new issues that I see.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB     http://www.enterprisedb.com