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.
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 |