Performance comparison between Pgsql 10.5 and Pgsql 11.2

From: Nicola Contu <nicola(dot)contu(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Cc: Alessandro Aste <Alessandro(dot)aste(at)gtt(dot)net>
Subject: Performance comparison between Pgsql 10.5 and Pgsql 11.2
Date: 2019-03-01 15:01:03
Message-ID: CAMTZZh1znqSm3ja4uNZrXvoNgT7uHhycXaOd8GJq8ho3gbL=OQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)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(at)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(at)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(at)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message s400t 2019-03-01 15:15:37 Re: Mind of its own?
Previous Message Merlin Moncure 2019-03-01 14:35:59 Re: Where **not** to use PostgreSQL?