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

Yes its 128MB, the default.

You raise an interesting point, Peter.
Just to clarify we were using shared_buffers=30GB for our initial pgbench
runs that would take a few hours to complete. In an attempt to make the
tests repeatable before submitting I have simplified the steps.

I have updated the testing rigs to use 40GB of shared buffers with the same
results. :(

--------------

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 -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: simple
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 4493742
latency average = 1.602 ms
tps = 37447.000308 (including connections establishing)
tps = 37449.413044 (excluding connections establishing)
table size = 581 MB

* DB ON PORT 11000 *
40GB

transaction type: insert.sql
scaling factor: 1
query mode: simple
number of clients: 60
number of threads: 60
duration: 120 s
number of transactions actually processed: 3606004
latency average = 1.997 ms
tps = 30049.353360 (including connections establishing)
tps = 30052.041139 (excluding connections establishing)
table size = 467 MB

On Tue, 12 Feb 2019 at 13:49, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Mon, Feb 11, 2019 at 4:47 PM Saul, Jean Paolo
> <paolo(dot)saul(at)verizonconnect(dot)com> wrote:
> > Aside from the ports:
> >
> > listen_addresses = '*'
> > max_connections = 300
> > max_wal_senders = 0 -- (on PG11)
> > synchronous_commit = off
>
> You mean shared_buffers is set to the default?
>
> I don't think that that's an interesting test case. If shared_buffers
> is ridiculously small, then there is little that we can take away from
> your benchmark, or any benchmark like it.
>
> --
> 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.