From: | "Imai, Yoshikazu" <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com> |
---|---|
To: | 'Simon Riggs' <simon(at)2ndquadrant(dot)com> |
Cc: | Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RE: Locking B-tree leafs immediately in exclusive mode |
Date: | 2018-07-13 02:14:58 |
Message-ID: | 0F97FA9ABBDBE54F91744A9B37151A5118C7C3@g01jpexmbkw24 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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?
I experimented with key1 having 10000 values, but there are no difference in the results compared to unordered.sql one, so I experimented with key1 having 1, 2, 3, 5, 10, and 100 values.
Also, If I created primary key, "ERROR: duplicate key value violates unique constraint "ordered2_pkey" happened, so I created non-unique key.
#DDL
CREATE UNLOGGED TABLE ordered2 (id integer, logdate timestamp default now(), value text not null);
CREATE INDEX ordered2_key ON ordered2 (id, logdate);
# script_ordered2.sql
\set i random(1, 100) #second value is 1, 2, 3, 5, 10, or 100
INSERT INTO ordered2 (id, value) VALUES (:i, 'abcdefghijklmnoprsqtuvwxyz');
# ordered2 results, key1 having 1, 2, 3, 5, 10, and 100 values
master, key1 with 1 values: 236428
master, key1 with 2 values: 292248
master, key1 with 3 values: 340980
master, key1 with 5 values: 362808
master, key1 with 10 values: 379525
master, key1 with 100 values: 405265
patched, key1 with 1 values: 295862
patched, key1 with 2 values: 339538
patched, key1 with 3 values: 355793
patched, key1 with 5 values: 371332
patched, key1 with 10 values: 387731
patched, key1 with 100 values: 405115
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.
Yoshikazu Imai
Attachment | Content-Type | Size |
---|---|---|
image/png | 24.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2018-07-13 02:22:11 | Re: pg_create_logical_replication_slot returns text instead of name |
Previous Message | Masahiko Sawada | 2018-07-13 02:14:01 | Re: pg_create_logical_replication_slot returns text instead of name |