Re: Faster inserts with mostly-monotonically increasing values

On 14 March 2018 at 13:33, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
>
>
> On Wed, Mar 14, 2018 at 4:53 PM, Simon Riggs <simon(dot)riggs(at)2ndquadrant(dot)com>
> wrote:
>>
>> On 14 March 2018 at 04:36, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
>> wrote:
>> > I wonder if the shortened code path actually leads to
>> > heavier contention for EXCLUSIVE lock on the rightmost page, which in
>> > turn
>> > causes the slowdown. But that's just a theory. Any ideas how to prove or
>> > disprove that theory or any other pointers?
>>
>> Certainly: dropping performance with higher sessions means increased
>> contention is responsible. Your guess is likely correct.
>>
>> Suggest making the patch attempt a ConditionalLock on the target
>> block, so if its contended we try from top of index. So basically only
>> attempt the optimization if uncontended. This makes sense because in
>> many cases if the block is locked it is filling up and the RHS block
>> is more likely to change anyway.
>
>
> Hmm. I can try that. It's quite puzzling though that slowing down things
> actually make them better.

That's not what is happening though.

The cache path is 1) try to lock cached block, 2) when got lock check
relevance, if stale 3) recheck from top

The non-cached path is just 3) recheck from top

The overall path length is longer in the cached case but provides
benefit if we can skip step 3 in high % of cases. The non-cached path
is more flexible because it locates the correct RHS block, even if it
changes dynamically between starting the recheck from top.

If there is enough delay in step 1 then any benefit is lost anyway, so
there is no point taking the cached path even when successful, so its
better to ignore in that case.

> I can also try to reduce the amount of time EX lock is held by doing some
> checks with a SHARE lock and then trade it for EX lock if we conclude that
> fast path can be taken. We can do page lsn check to confirm nothing changed
> when the lock was traded. Will check both approaches.

That will still be slow.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services