Re: Locking B-tree leafs immediately in exclusive mode

Hi!

On Thu, Jul 26, 2018 at 1:31 PM Imai, Yoshikazu
<imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com> wrote:
> On Wed, July 25, 2018 at 0:28 AM, Alexander Korotkov wrote:> > On Tue, Jul 10, 2018 at 4:39 PM 今井 良一 <yoshikazu_i443(at)live(dot)jp> wrote:
> > 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

Great, thank you! So, I think the regression is demystified. We can
now conclude that on our benchmarks this patch doesn't cause
performance regression larger than measurement error. But in some
cases it shows huge performance benefit.

So, I'm going to commit this, if no objections.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company