Re: Re: [COMMITTERS] pgsql: Augment WAL records for btree delete with GetOldestXmin() to

Simon Riggs wrote:
> On Sun, 2010-01-31 at 22:05 +0100, Stefan Kaltenbrunner wrote:
>
>> hmm makes sense from the HS side - but I think to make a case for a GUC
>> it would help a lot to see actual numbers(as in benchmarks) showing how
>> much of a hit it is to the master.
>
> Agreed, though my approach to benchmarking was to provide the mechanism
> by which it was possible to benchmark. I didn't presume to be able to
> cover wide area with benchmarking tests just for this.

I don't think this patch helps much though. It's setting lastRemovedXid
to GetOldestXmin(), which is still a very pessimistic estimate. In fact,
if there's only one transaction running in the master, it's not any
better than just setting it to InvalidTransactionId and killing all
active queries in the standby.

What we'd want to set it to is the xmin/xmax of the oldest heap tuple
whose pointer was removed from the index page. That could be much much
older than GetOldestXmin(), allowing many more read-only queries to live
in the standby.

IIRC it was Greg Stark who suggested last time this was discussed that
we could calculate the exact value for latestRemovedXid in the standby.
When replaying the deletion record, the standby could look at all the
heap tuples whose index pointers are being removed, to see which one was
newest. That can be pretty expensive, involving random I/O, but it gives
an exact value, and doesn't stress the master at all. And we could skip
it if there's no potentially conflicting read-only queries running.

That seems like the most user-friendly approach to me. Even though
random I/O is expensive, surely it's better than killing queries.

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