Benchmarking connection poolers

Below are 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.

TPC-P like workload (pgbench):

Select-only workload:

Zipf distribution: