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

On 24/05/10 22:49, Alvaro Herrera wrote:
> Excerpts from Josh Berkus's message of vie may 21 17:57:35 -0400 2010:
>
>> Problem: currently, if your database has a large amount of "cold" data,
>> such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer
>> needs to touch it thanks to the visibility map. However, every
>> freeze_age transactions, very old pages need to be sucked into memory
>> and rewritten just in order to freeze those pages. This can have a huge
>> impact on system performance, and seems unjustified because the pages
>> are not actually being used.
>
> I think this is nonsense. If you have 3-years-old sales transactions,
> and your database has any interesting churn, tuples those pages have
> been frozen for a very long time *already*. The problem is vacuum
> reading them in so that it can verify there's nothing to do. If we want
> to avoid *reading* those pages, this solution is useless:
>
>> Suggested resolution: we would add a 4-byte field to the *page* header
>> which would track the XID wraparound count.
>
> because you still have to read the page.

What's missing from the suggestion is that relfrozenxid and datfrozenxid
also need to be expanded to 8-bytes. That way you effectively have
8-byte XIDs, which means that you never need to vacuum to avoid XID
wraparound.

You still need to freeze to truncate clog, though, but if you have the
disk space, you can now do that every 100 billion transactions for
example if you wish.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com