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
