Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes

From: "Saul, Jean Paolo" <paolo(dot)saul(at)verizonconnect(dot)com>
To: Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes
Date: 2019-02-04 02:26:59
Message-ID: CA+73ANcqd2mMLJBwJrJKwSUiiVqn5Kbde2a=e+WhuCPSCP+p3A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi Peter,

Thanks! I see.
It looks like I cannot replicate it in one transaction, but I have to use
pgbench instead.
Another simple test output is below.

What do you think?

Cheers,

Paolo

p.s.
synchronous_commit = off
pg9.5 on port 9500
pg11 on port 11000

-------
demo_server.pg $ echo 'INSERT INTO lowc_test (int_data) SELECT 42; --
arbitrary ' > /home/postgres/simple_insert_low.sql
demo_server.pg $

----- POSTGRESQL 9.5 -----
demo_server.pg $
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c 'CREATE TABLE
lowc_test (id bigserial, int_data int, PRIMARY KEY(id))'
CREATE TABLE
demo_server.pg $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/simple_insert_low.sql -n -c 60 -j 60 -T10 -p 9500 postgres
transaction type: /home/postgres/simple_insert_low.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 10 s
number of transactions actually processed: 1083976
latency average = 0.554 ms
tps = 108379.219155 (including connections establishing)
tps = 108472.988431 (excluding connections establishing)
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c 'CREATE INDEX
ON lowc_test USING BTREE (int_data);'
CREATE INDEX
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c 'TRUNCATE
lowc_test;'
TRUNCATE TABLE
demo_server.pg $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/simple_insert_low.sql -n -c 60 -j 60 -T10 -p 9500 postgres
transaction type: /home/postgres/simple_insert_low.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 10 s
number of transactions actually processed: 478797
latency average = 1.254 ms
*tps = 47865.701374 (including connections establishing)*
*tps = 47909.167492 (excluding connections establishing)*

----- POSTGRESQL 11 -----
demo_server.pg $
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c 'CREATE TABLE
lowc_test (id bigserial, int_data int, PRIMARY KEY(id))'
CREATE TABLE
demo_server.pg $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/simple_insert_low.sql -n -c 60 -j 60 -T10 -p 11000 postgres
transaction type: /home/postgres/simple_insert_low.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 10 s
number of transactions actually processed: 1215185
latency average = 0.494 ms
tps = 121488.366924 (including connections establishing)
tps = 121610.790950 (excluding connections establishing)
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c 'CREATE INDEX
ON lowc_test USING BTREE (int_data);'
CREATE INDEX
demo_server.pg $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c 'TRUNCATE
lowc_test;'
TRUNCATE TABLE
demo_server.pg $ /usr/pgsql-11/bin/pgbench -Upostgres -f
/home/postgres/simple_insert_low.sql -n -c 60 -j 60 -T10 -p 11000 postgres
transaction type: /home/postgres/simple_insert_low.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 10 s
number of transactions actually processed: 341168
latency average = 1.759 ms
*tps = 34100.743631 (including connections establishing)*
*tps = 34137.949909 (excluding connections establishing)*

On Mon, 4 Feb 2019 at 12:10, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Sun, Feb 3, 2019 at 2:45 PM Saul, Jean Paolo
> <paolo(dot)saul(at)verizonconnect(dot)com> wrote:
> > The last post was too long. I think I have a much more simpler example
> that is easier to replicate.
>
> This new example is very similar to examples that I have personally
> come up with. I have no difficulty explaining why the case with lots
> of duplicates is slower, so it doesn't really help.
>
> I cannot account for why you can observe a difference across Postgres
> versions, though -- that's what I'm having difficulty with. Are you
> sure about that effect? There haven't been any directly relevant
> changes in this area in many years.
>
> --
> Peter Geoghegan
>

--------------------------------------------------------------------------------------------------------------------------------------------
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Robins Tharakan 2019-02-04 08:18:41 Re: DROP TABLE CASCADE doesn't drop dependencies
Previous Message Michael Paquier 2019-02-04 01:03:42 Re: DROP TABLE CASCADE doesn't drop dependencies