Re: Built-in connection pooling

On 18.01.2018 18:00, Claudio Freire wrote:
>
>
> On Thu, Jan 18, 2018 at 11:48 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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