RE: Locking B-tree leafs immediately in exclusive mode

Hi.
On Wed, July 25, 2018 at 0:28 AM, Alexander Korotkov wrote:
> Hi!
>
> On Tue, Jul 10, 2018 at 4:39 PM 今井 良一 <yoshikazu_i443(at)live(dot)jp> wrote:
> > On 2018/07/10 20:36, Alexander Korotkov wrote:
> > > Thank you for the experiments! It seems that there is real regression
> > > here... BTW, which script were you using in this benchmark:
> > > script_unordered.sql or script_duplicated.sql?
> >
> > Sorry, I forgot to write that. I used script_unordered.sql.
>
> I've reread the thread. And I found that in my initial letter [1] I
> forget to include index definition.
> CREATE INDEX unordered_i_idx ON unordered (i);
>
> So, when you did experiments [2], did you define any index on
> unordered table? If not, then it's curious why there is any
> performance difference at all.
>
> 1. https://www.postgresql.org/message-id/CAPpHfduAMDFMNYTCN7VMBsFg_hsf0GqiqXnt%2BbSeaJworwFoig%40mail.gmail.com
> 2. https://www.postgresql.org/message-id/0F97FA9ABBDBE54F91744A9B37151A51189451%40g01jpexmbkw24

My experiment[1][2] was totally wrong that I didn't create index in unordered and duplicated case.

On Mon, July 9, 2018 at 3:19 AM, I wrote:
> # script_duplicated.sql
> INSERT INTO unordered VALUES (1, 'abcdefghijklmnoprsqtuvwxyz');
I also made mistake in duplicated.sql that I used unordered table in duplicated experiment...

I re-run the experiments.
This time, I created indexes on unordered and duplicated table, and confirmed
indexes are created in both master and patched by using \d commands in psql.
I used AWS c5.18xlarge, and executed pgbench with 36 clients.

# results with indexes created
master, ordered, Ave 252796 TPS (253122,252469)
patched, ordered, Ave 314541 TPS (314011,315070)
master, unordered, Ave 409591 TPS (413901,412010,404912,404607,416494,405619)
patched, unordered, Ave 412765 TPS (409626,408578,416900,415955)
master, duplicated, Ave 44811 TPS (45139,44482)
patched, duplicated, Ave 202325 TPS (201519,203130)

The TPS of "master, ordered" and "patched, ordered" is similar to the TPS of "master, key1 with 1 values"
in previous experiment[3] (contention is concentrated), the TPS of "master, unordered" and "patched, unordered"
is similar to the TPS of "master, key1 with 100 values" in previous experiment[3] (contention is dispersed).
Therefore this experiment and previous experiment[3] asked for from Simon is correct I think.

The TPS of "patched, duplicated" was almost 4.5 times bigger than "master, duplicated" one.

About a small performance regression in previous experiments[1], I think it is because of machine's performance.
I don't have rational reason nor evidence, so I only describe what I thought and did (TL;DR:).

In this experiments, in order to increase machine's performance, I did something like "machine warm" as below batch.

## DDL
CREATE UNLOGGED TABLE improve_performance (i integer not null, value text not null);
CREATE INDEX improve_i_idx on improve_performance (i);

## improve_performance.sql
\set i random(1, 1000000)
INSERT INTO improve_performance VALUES (:i, 'abcdefghijklmnopqrstuvwxyz');

## test batch
port=$1
client=$2
j=$3
tablenames_all=(ordered unordered duplicated ordered2)
tablenames=(unordered)

function cleaning() {
for tablename_ in ${tablenames_all[(at)]}; do
psql -p ${port} -c "truncate table ${tablename_};"
done

psql -p ${port} -c "vacuum full analyze;"
}

pgbench -p ${port} -T 60 -P 1 -M prepared -f improve_performance.sql -c 72 -j 36 postgres # do for warm
cleaning

for tablename in ${tablenames[(at)]}; do
for i in `seq 0 1`; do
pgbench -p ${port} -T 60 -P 1 -M prepared -f script_${tablename}.sql -c ${client} -j ${j} postgres`
cleaning
done
done

When I created index on improve_performance table and executed test batch against unordered table
in patched version three times, results are below.
1. 379644,381409
2. 391089,391741
3. 383141,367851

These results are little decreased compared to "patched, key1 with 100 values: 405115" (unordered)
in my previous experiments[3].

I thought if I execute pgbench for warm against table with index created, warm hasn't be completely done.
Because in experiments[3], I executed pgbench for warm against table without index created.

So I dropped index from improve_performance table and executed test batch against patched two times, results are below.
1. 409626,408578,
2. 416900,415955,

Even in the same test, performance differs largely, so a small regression can be ignored I think.
Of course, I wonder there were regressions in all cases at the experiments[2].

[1] https://www.postgresql.org/message-id/0F97FA9ABBDBE54F91744A9B37151A51186E9D%40g01jpexmbkw24
[2] https://www.postgresql.org/message-id/0F97FA9ABBDBE54F91744A9B37151A51189451@g01jpexmbkw24
[3] https://www.postgresql.org/message-id/0F97FA9ABBDBE54F91744A9B37151A5118C7C3%40g01jpexmbkw24

Yoshikazu Imai