Re: Built-in connection pooler

New version of the patch (rebased + bug fixes) is attached to this mail.

On 20.03.2019 18:32, Konstantin Knizhnik wrote:
> Attached please find results of benchmarking of different connection
> poolers.
>
> Hardware configuration:
>    Intel(R) Xeon(R) CPU           X5675  @ 3.07GHz
>    24 cores (12 physical)
>    50 GB RAM
>
> Tests:
>      pgbench read-write (scale 1): performance is mostly limited by
> disk throughput
>      pgbench select-only (scale 1): performance is mostly limited by
> efficient utilization of CPU by all workers
>      pgbench with YCSB-like workload with Zipf distribution:
> performance is mostly limited by lock contention
>
> Participants:
>     1. pgbouncer (16 and 32 pool size, transaction level pooling)
>     2. Postgres Pro-EE connection poller: redirection of client
> connection to poll workers and maintaining of session contexts.
>         16 and 32 connection pool size (number of worker backend).
>     3. Built-in proxy connection pooler: implementation proposed in
> this thread.
>         16/1 and 16/2 specifies number of worker backends per proxy
> and number of proxies, total number of backends is multiplication of
> this values.
>     4. Yandex Odyssey (32/2 and 64/4 configurations specifies number
> of backends and Odyssey threads).
>     5. Vanilla Postgres (marked at diagram as "12devel-master/2fadf24
> POOL=none")
>
> In all cases except 2) master branch of Postgres is used.
> Client (pgbench), pooler and postgres are laucnhed at the same host.
> Communication is though loopback interface (host=localhost).
> We have tried to find the optimal parameters for each pooler.
> Three graphics attached to the mail illustrate three different test
> cases.
>
> Few comments about this results:
> - PgPro EE pooler shows the best results in all cases except tpc-b
> like. In this case proxy approach is more efficient because more
> flexible job schedule between workers
>   (in EE sessions are scattered between worker backends at connect
> time, while proxy chooses least loaded backend for each transaction).
> - pgbouncer is not able to scale well because of its single-threaded
> architecture. Certainly it is possible to spawn several instances of
> pgbouncer and scatter
>   workload between them. But we have not did it.
> - Vanilla Postgres demonstrates significant degradation of performance
> for large number of active connections on all workloads except read-only.
> - Despite to the fact that Odyssey is new player (or may be because of
> it), Yandex pooler doesn't demonstrate good results. It is the only
> pooler which also cause degrade of performance with increasing number
> of connections. May be it is caused by memory leaks, because it memory
> footprint is also actively increased during test.
>

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