Re: Set hint bits upon eviction from BufMgr

On Fri, Mar 25, 2011 at 2:32 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 25.03.2011 16:52, Merlin Moncure wrote:
>>
>> Without this bit, the only way to set hint bits going during bufmgr
>> eviction is to do a visibility check on every tuple, which would
>> probably be prohibitively expensive.
>
> I don't think the naive approach of scanning all tuples would be too bad,
> actually. The hint bits only need to be set once, and it'd be bgwriter
> shouldering the overhead.
>
> The problem with setting hing bits when a buffer is evicted is that it
> doesn't help with the bulk load case. The hint bits can't be set for a bulk
> load until the load is finished and the transaction commits.

Not the true bulk load case. However, if you can break up a load into
multiple transactions and sneak out 10-100mb of pages into the buffer
per transaction, you have a good chance of getting most/all the bits
out correct before bgwriter eats them up. I was thinking to also
teach bgwriter to keep xmin flagged pages in a separate lower priority
pool so that it didn't race to them before the transaction had a
chance to go in.

Long term, I'm imagining more direct transaction control in the
backend, either via autonomous transactions, or stored procedures with
explicit transaction control, so we don't have to load N gigabytes in
a single transaction.

> Maybe it would still be worthwhile to have bgwriter set hint bits, to reduce
> I/O caused by hint bit updates in an OLTP workload, but that's not what
> people usually complain about.

well, if bgwriter does it, you lose the ability to bail the clog check
via TransactionIdIsCurrentTransactionId, right? If it's done in the
bufmgr you at least have a chance to not have to go all the way out.
Either way though, you at least have to teach bgwriter to be more
cooperative.

merlin