Re: Early hint bit setting

On 5/30/12 4:42 PM, Ants Aasma wrote:
> I was thinking about what is the earliest time where we could set hint
> bits. This would be just after the commit has been made visible.

Except that's only true when there are no other transactions running. That's been one of the big sticking points about trying to proactively set hint bits; in a real system you're not going to gain very much unless you wait a while before setting them.

An interesting option might be to keep the first XID that dirtied a page and loop through all pages in the background looking for pages where first_dirty_xid is < the oldest running XID. Those pages would have hint bits that could be set. While scanning the page you would want to set first_dirty_xid to the oldest XID that could not be hinted.

This is a modification of the idea to set hint bits when a page is on it's way out of the buffer; the advantage here is that it would also handle pages that are too hot to leave the buffer.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net