Re: 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6 - Mailing list pgsql-performance

On 25 May 2016, at 17:33, Vladimir Borodin <root@simply.name> wrote:

Hi all.

We have found that queries through PgBouncer 1.7.2 (with transaction pooling) to local PostgreSQL are almost two times slower in 9.5.3 than in 9.4.8 on RHEL 6 hosts (all packages are updated to last versions). Meanwhile the problem can’t be reproduced i.e. on Ubuntu 14.04 (also fully-updated).

Here is how the results look like for 9.4, 9.5 and 9.6. All are built from latest commits on yesterday in

* REL9_4_STABLE (a0cc89a28141595d888d8aba43163d58a1578bfb),

* REL9_5_STABLE (e504d915bbf352ecfc4ed335af934e799bf01053),

* master (6ee7fb8244560b7a3f224784b8ad2351107fa55d).

All of them are build on the host where testing is done (with stock gcc versions). Sysctls, pgbouncer config and everything we found are the same, postgres configs are default, PGDATA is in tmpfs. All numbers are reproducible, they are stable between runs.

Shortly:

OS PostgreSQL version TPS Avg. latency

RHEL 6 9.4 44898 1.425 ms

RHEL 6 9.5 26199 2.443 ms

RHEL 6 9.5 43027 1.487 ms

Ubuntu 14.04 9.4 67458 0.949 ms

Ubuntu 14.04 9.5 64065 0.999 ms

Ubuntu 14.04 9.6 64350 0.995 ms

You could see that the difference between major versions on Ubuntu is not significant, but on RHEL 9.5 is 70% slower than 9.4 and 9.6.

Below are more details.

RHEL 6:

postgres@pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg94'

transaction type: SELECT only

scaling factor: 100

query mode: simple

number of clients: 64

number of threads: 64

duration: 60 s

number of transactions actually processed: 2693962

latency average: 1.425 ms

tps = 44897.461518 (including connections establishing)

tps = 44898.763258 (excluding connections establishing)

postgres@pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg95'

transaction type: SELECT only

scaling factor: 100

query mode: simple

number of clients: 64

number of threads: 64

duration: 60 s

number of transactions actually processed: 1572014

latency average: 2.443 ms

tps = 26198.928627 (including connections establishing)

tps = 26199.803363 (excluding connections establishing)

postgres@pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg96'

transaction type: SELECT only

scaling factor: 100

query mode: simple

number of clients: 64

number of threads: 64

duration: 60 s

number of transactions actually processed: 2581645

latency average: 1.487 ms

tps = 43025.676995 (including connections establishing)

tps = 43027.038275 (excluding connections establishing)

postgres@pgload05g ~ $

Ubuntu 14.04 (the same hardware):

postgres@pgloadpublic02:~$ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg94'

transaction type: SELECT only

scaling factor: 100

query mode: simple

number of clients: 64

number of threads: 64

duration: 60 s

number of transactions actually processed: 4047653

latency average: 0.949 ms

tps = 67458.361515 (including connections establishing)

tps = 67459.983480 (excluding connections establishing)

postgres@pgloadpublic02:~$ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg95'

transaction type: SELECT only

scaling factor: 100

query mode: simple

number of clients: 64

number of threads: 64

duration: 60 s

number of transactions actually processed: 3844084

latency average: 0.999 ms

tps = 64065.447458 (including connections establishing)

tps = 64066.943627 (excluding connections establishing)

postgres@pgloadpublic02:~$ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg96'

transaction type: SELECT only

scaling factor: 100

query mode: simple

number of clients: 64

number of threads: 64

duration: 60 s

number of transactions actually processed: 3861088

latency average: 0.995 ms

tps = 64348.573126 (including connections establishing)

tps = 64350.195750 (excluding connections establishing)

postgres@pgloadpublic02:~$

In both tests (RHEL and Ubuntu) the bottleneck is performance of singe CPU core which is 100% consumed by PgBouncer. If pgbench connects to postgres directly I get the following (expected) numbers:

postgres@pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=5432'

transaction type: SELECT only

scaling factor: 100

query mode: simple

number of clients: 64

number of threads: 64

duration: 60 s

number of transactions actually processed: 10010710

latency average: 0.384 ms

tps = 166835.937859 (including connections establishing)

tps = 166849.730224 (excluding connections establishing)

postgres@pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=5433'

transaction type: SELECT only

scaling factor: 100

query mode: simple

number of clients: 64

number of threads: 64

duration: 60 s

number of transactions actually processed: 13373890

latency average: 0.287 ms

tps = 222888.311289 (including connections establishing)

tps = 222951.470125 (excluding connections establishing)

postgres@pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 -j 64 -c 64 -S -n 'host=localhost port=5434'

transaction type: SELECT only

scaling factor: 100

query mode: simple

number of clients: 64

number of threads: 64

duration: 60 s

number of transactions actually processed: 12989816

latency average: 0.296 ms

tps = 216487.458399 (including connections establishing)

tps = 216548.069976 (excluding connections establishing)

postgres@pgload05g ~ $

Compilation options look almost the same:

# RHEL 6

CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2

# Ubuntu

CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O2

Attached are a simple script to deploy the testing environment (PgBouncer should be installed) and pgbouncer config. I could provide any other needed information like backtraces or perf reports or anything else.

<pgbouncer.ini>

<deploy.sh>

--

May the force be with you…

https://simply.name