Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes - Mailing list pgsql-bugs
| From | Saul, Jean Paolo |
|---|---|
| Subject | Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes |
| Date | |
| Msg-id | CA+73ANcqd2mMLJBwJrJKwSUiiVqn5Kbde2a=e+WhuCPSCP+p3A@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes (Peter Geoghegan <pg@bowt.ie>) |
| Responses |
Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes
|
| List | 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
----- POSTGRESQL 9.5 -----
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 $ /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@bowt.ie> wrote:
On Sun, Feb 3, 2019 at 2:45 PM Saul, Jean Paolo
<paolo.saul@verizonconnect.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.
pgsql-bugs by date: