Re: Locking B-tree leafs immediately in exclusive mode

On 13 July 2018 at 03:14, Imai, Yoshikazu <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com> wrote:
> On Mon, July 9, 2018 at 5:25 PM, Simon Riggs wrote:
>> Please can you check insertion with the index on 2 keys
>> 1st key has 10,000 values
>> 2nd key has monotonically increasing value from last 1st key value
>>
>> So each session picks one 1st key value
>> Then each new INSERTion is a higher value of 2nd key
>> so 1,1, then 1,2 then 1,3 etc
>>
>> Probably easier to do this with a table like this
>>
>> CREATE UNLOGGED TABLE ordered2 (id integer, logdate timestamp default
>> now(), value text not null, primary key (id, logdate));
>>
>> # script_ordered2.sql
>> \set i random(1, 10000)
>> INSERT INTO ordered2 (id, value) VALUES (:i, 'abcdefghijklmnoprsqtuvwxyz');
>>
>> Thanks
> I tried to do this, but I might be mistaken your intention, so please specify if I am wrong.
>
> While script_ordered.sql supposes that there is one contention point on the most right leaf node,
> script_ordered2.sql supposes that there are some contention points on some leaf nodes, is it right?

Yes, that is right, thank you for testing.

> I experimented with key1 having 10000 values, but there are no difference in the results compared to unordered.sql one

That is an important result, thanks

> so I experimented with key1 having 1, 2, 3, 5, 10, and 100 values.

OK, good

> From an attached graph("some_contention_points_on_leaf_nodes.png"), as contention points dispersed, we can see that TPS is increased and TPS difference between master and patched version becomes smaller.

So I think this clearly shows the drop in throughput when we have
index contention and that this patch improves on that situation.

In cases where we don't have contention, the patch doesn't cause a
negative effect.

So +1 from me!

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