Scaling PgBouncer Throughput by 4x Using SO_REUSEPORT and Peering

Scaling PgBouncer Throughput by 4x Using SO_REUSEPORT and Peering

ClickHouse increases PgBouncer throughput by 4x

ClickHouse has scaled PgBouncer throughput by 4x by running multiple PgBouncer processes on a single machine. This approach overcomes the single-threaded nature of PgBouncer, which typically limits its performance to a single CPU core, by distributing the connection load across multiple processes using the SO_REUSEPORT socket option.

Using SO_REUSEPORT for Load Distribution

To achieve high throughput, ClickHouse utilizes SO_REUSEPORT. This socket option allows multiple processes to bind to the same IP address and port. The Linux kernel then distributes incoming TCP connections across the processes in a round-robin or hashed fashion. This effectively turns a single PgBouncer instance into a multi-process architecture, allowing the system to scale linearly with the number of CPU cores allocated to the pool of processes.

Solving the Query Cancellation Problem with Peering

While SO_REUSEPORT distributes connections, it introduces a challenge: a query cancellation request may land on a PgBouncer process that does not own the session. Because the standard PgBouncer architecture is isolated, a process receiving a cancel request for a session it doesn't recognize would normally ignore it, resulting in the failure of the query cancellation.

To solve this, ClickHouse implemented a peering mechanism. Peering allows PgBouncer processes to communicate with one another. When a process receives a cancel request for a session it does not own, it forwards the request to the peer process that actually manages that specific session. This ensures that query cancellations remain functional across a distributed process pool.

Implementation and Configuration

Based on community discussion, the configuration for this setup requires enabling so_reuseport and defining the peer IDs and their corresponding sockets. An example configuration structure is as follows:

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
peer_id = 1
unix_socket_dir = /tmp/pgbouncer1

[peers]
1 = host=/tmp/pgbouncer1
2 = host=/tmp/pgbouncer2
3 = host=/tmp/pgbouncer3
4 = host=/tmp/pgbouncer4

Community Insights and Alternatives

Technical discussions around this implementation highlight several alternatives and considerations for scaling PostgreSQL connection pooling:

  • Alternative Poolers: Some users suggested using Odyssey or pgdog as scalable alternatives to PgBouncer.
  • Infrastructure-level Scaling: Running multiple PgBouncer instances via Kubernetes is cited as a straightforward way to achieve similar results across multiple machines, which helps mitigate risks associated with cloud provider VM maintenance.
  • Architectural Questions: Some engineers questioned the advantage of this approach over using a traditional HAProxy setup to distribute traffic across multiple PgBouncer instances.

"The cancel lands on a process that has never heard of the query, and nothing happens. Peering fixes this. The processes are aware of one another, so a cancel that lands on the wrong process is forwarded to the one that actually owns the session."

This peering mechanism is critical for maintaining the functionality of PostgreSQL's connection management in a multi-process PgBouncer environment.

Sources