Re: Built-in connection pooling

On Thu, Jan 18, 2018 at 11:48 AM, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:

Attached please find new version of the patch with few fixes.
And more results at NUMA system with 144 cores and 3Tb of RAM.

Read-only pgbench (-S):

#Connections\kTPS	Vanilla Postgres	Session pool size 256
1k	1300	1505
10k	633	1519
100k	-	1425

Read-write contention test: access to small number of records with 1% of updates.

#Clients\TPS	Vanilla Postgres	Session pool size 256
100	557232	573319
200	520395	551670
300	511423	533773
400	468562	523091
500	442268	514056
600	401860	526704
700	363912	530317
800	325148	512238
900	301310	512844
1000	278829	554516

So, as you can see, there is no degrade of performance with increased number of connections in case of using session pooling.

TBH, the tests you should be running are comparisons with a similar pool size managed by pgbouncer, not just vanilla unlimited postgres.

Of course a limited pool size will beat thousands of concurrent queries by a large margin. The real question is whether a pthread-based approach beats the pgbouncer approach.

Below are are results with pgbouncer:

#Connections\kTPS	Vanilla Postgres	Builti-in session pool size 256	Postgres + pgbouncer with transaction pooling mode and pool size  256	Postgres + 10 pgbouncers with pool size 20
1k	1300	1505	105	751
10k	633	1519	94	664
100k	-	1425	-	-

(-) here means that I failed to start such number of connections (because of "resource temporary unavailable" and similar errors).

So single pgbouncer is 10 times slower than direct connection to the postgres.
No surprise here: pgbouncer is snigle threaded and CPU usage for pgbouncer is almost 100%.
So we have to launch several instances of pgbouncer and somehow distribute load between them.
In Linux it is possible to use REUSEPORT(https://lwn.net/Articles/542629/) to perform load balancing between several pgbouncer instances.
But you have to edit pgbouncer code: it doesn't support such mode. So I have started several instances of pgbouncer at different ports and explicitly distribute several pgbench instances  between them.

But even in this case performance is twice slower than direct connection and built-in session pooling.
It is because of lacked of prepared statements which I can not use with pgbouncer in statement/transaction pooling mode.

Also please notice that with session pooling performance is better than with vanilla Postgres.
It is because with session pooling we can open more connections with out launching more backends.
It is especially noticeable at my local desktop with 4 cores: for normal Postgres optimal number of connections is about 10. But with session pooling 100 connections shows about 30% better result.

So, summarizing all above:

1. pgbouncer doesn't allows to use prepared statements and it cause up to two times performance penalty.
2. pgbouncer is single threaded and can not efficiently handle more than 1k connections.
3. pgbouncer never can provide better performance than application connected directly to Postgres with optimal number of connections. In contrast session pooling can provide better performance than vanilla Postgres with optimal number of connections.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company