Hello,
is there any reason why I am getting worse results using pgsql11.2 in writing comparing it with pgsql 10.6?
I have two Instances, both just restored, so no bloats.
Running read queries I have pretty much same results, a little bit better on pg11- Running writes the difference is in favour of 10.
I am expecting pg11 to be better.
Running pgbench :
PG11
[root@STAGING-CMD1 ~]# /usr/local/pgsql11.2/bin/pgbench -t 1000 -c 20 -C -f stress_service_order.sql cmdstaging -U admin
transaction type: stress_service_order.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 20000/20000
latency average = 45.322 ms
tps = 441.283336 (including connections establishing)
tps = 463.731537 (excluding connections establishing)
PG10
[root@STAGING-CMD1 ~]# pgbench -t 1000 -c 20 -C -f stress_service_order.sql cmdstaging -U admin
transaction type: stress_service_order.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 20000/20000
latency average = 44.686 ms
tps = 447.565403 (including connections establishing)
tps = 470.285561 (excluding connections establishing)
This is making a really big difference with longer queries.
Here I am updating a field in a random record.
With more transactions the difference is bigger
WITH POSTGRES 10
[root@STAGING-CMD1 ~]# pgbench -t 100000 -c 20 -C -f stress_service_order_read.sql cmdstaging -U postgres
transaction type: stress_service_order_read.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100000
number of transactions actually processed: 2000000/2000000
latency average = 55.291 ms
tps = 442.1490778 (including connections establishing)
tps = 454.846844 (excluding connections establishing)
WITH POSTGRES 11
[root@STAGING-CMD1 ~]# pgbench -t 100000 -c 20 -C -f stress_service_order_read.sql cmdstaging -U postgres
transaction type: stress_service_order_read.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100000
number of transactions actually processed: 2000000/2000000
latency average = 53.291 ms
tps = 375.297748 (including connections establishing)
tps = 392.316057 (excluding connections establishing)
The postgres.conf file are the same.
max_connections = 220
shared_buffers = 10GB
effective_cache_size = 120GB
work_mem = 600MB
maintenance_work_mem = 2GB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
#default_statistics_target = 100
Using data_sync_retry=on doesn't make any difference.
Is there anything else changed in the default values?
Any trick?
I don't want to go live and loose performances.
Thanks a lot,
Nicola
