Re: Idea for getting rid of VACUUM FREEZE on cold pages

On 28/05/10 04:00, Josh Berkus wrote:
>> Consider a table that is
>> regularly written but append-only. Every time autovacuum kicks in,
>> we'll go and remove any dead tuples and then mark the pages
>> PD_ALL_VISIBLE and set the visibility map bits, which will cause
>> subsequent vacuums to ignore the all-visible portions of the table...
>> until anti-wraparound kicks in, at which point we'll vacuum the entire
>> table and freeze everything.
>>
>> If, however, we decree that you can't write a new tuple into a
>> PD_ALL_VISIBLE page without freezing the existing tuples, then you'll
>> still have the small, incremental vacuums but those are pretty cheap,
>>
> That only works if those pages were going to be autovacuumed anyway. In
> the case outlined above (which I've seen at 3 different production sites
> this year), they wouldn't be; a table with less than 2% updates and
> deletes does not get vacuumed until max_freeze_age for any reason. For
> that matter, pages which are getting autovacuumed are not a problem,
> period; they're being read and written and freezing them is not an issue.
>
> I'm not seeing a way of fixing this common issue short of overhauling
> CLOG, or of creating a freeze_map. Darn.
>
Don't you not get a positive enough effect by adjusting the table's
autovacuum_min_freeze_age and autovacuum_max_freeze_age. If you set
those numbers small, it appears to me that you would get very quickly to
a state where the vacuum would example only the most recent part of the
table rather than the whole thing. Does that give you enough of a win
that it stops the scanning and writing of the whole table which reduces
the performance problem being experienced. It's not a complete
solution, but does it go someway?

Regards

Russell