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

Using prepared and extended. No luck.

-----------------------
demo_server.postgres $ for p in 9500 11000; do echo "* DB ON PORT $p *";
/usr/pgsql-11/bin/psql -Upostgres -qp $p -f create_table.sql;
/usr/pgsql-11/bin/psql -Upostgres -qtp $p -c"show shared_buffers";
/usr/pgsql-11/bin/pgbench -Upostgres -M *prepared *-f insert.sql -n -c 60
-j 60 -T120 postgres -p $p;/usr/pgsql-11/bin/psql -Upostgres -qtp $p
-c"select 'table size = ' ||
pg_size_pretty(pg_total_relation_size('test_indexes'))"; done
* DB ON PORT 9500 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: prepared
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 4555946
latency average = 1.580 ms
tps = 37965.164927 (including connections establishing)
tps = 37967.496937 (excluding connections establishing)
table size = 589 MB

* DB ON PORT 11000 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: prepared
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 3550999
latency average = 2.028 ms
tps = 29590.591808 (including connections establishing)
tps = 29593.242681 (excluding connections establishing)
table size = 461 MB

demo_server.postgres $ for p in 9500 11000; do echo "* DB ON PORT $p *";
/usr/pgsql-11/bin/psql -Upostgres -qp $p -f create_table.sql;
/usr/pgsql-11/bin/psql -Upostgres -qtp $p -c"show shared_buffers";
/usr/pgsql-11/bin/pgbench -Upostgres -M *extended *-f insert.sql -n -c 60
-j 60 -T120 postgres -p $p;/usr/pgsql-11/bin/psql -Upostgres -qtp $p
-c"select 'table size = ' ||
pg_size_pretty(pg_total_relation_size('test_indexes'))"; done
* DB ON PORT 9500 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: extended
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 4511910
latency average = 1.596 ms
tps = 37598.593591 (including connections establishing)
tps = 37601.100548 (excluding connections establishing)
table size = 584 MB

* DB ON PORT 11000 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: extended
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 3553357
latency average = 2.026 ms
tps = 29610.805470 (including connections establishing)
tps = 29613.253046 (excluding connections establishing)
table size = 459 MB

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