pgbouncer
Configure and enforce max connections per pool at runtime
Description
This PR introduces the ability to tune the maximum number of connections for a specific connection pool associated to a user and a database. Applying these now pool level configurations will take effect immediately; existing connections that surpass the new max pool_size limit will be evicted. PgBouncer will prioritize killing any idle and unused server connections before killing the oldest active connections within the targeted pool.
Motivation
We have seen the need for PgBouncer to throttle/reduce connections. When our upstream services (PG users) acquire too many connections while executing expensive and/or repetitive queries, this often puts strain on our Postgres primary server and starves CPU/Disk IO resources at the server. This also creates back-pressure by blocking other queued user clients from acquiring server side connections and executing queries, increasing overall query latencies.
Many of our tenants have different workloads that span across different databases (and by extension different pools). Under high load scenarios, although throttling max user connections owned by a tenant across all their pools will reduce load, being able to throttle a tenant's specific pool effectively reduces load in a targeted manner. It also minimizing service disruptions to other well behaved pools owned by the tenant.
Ini Configuration
A new [pools] section has been added in the .ini configuration file, where an example of a pool's configuration is as follows:
[pools]
maxedout.p7a = pool_size=3
Each pool name is uniquely denoted by <user>.<database>. Similar to the [users] section, upon PgBouncer RELOAD;, the values under this section will be loaded into memory and applied to the relevant PgPool * references at runtime.
Admin Command
We also introduce a new command to enforce per pool max connections at runtime. Operators can now execute SET POOL <user>.<database> = 'pool_size=<new limit>' to update the maximum number of server side connections that a specific pool can use. This syntax matches the proposed SET USER ... convention in the previous PR: https://github.com/pgbouncer/pgbouncer/pull/707
In addition, this PR also updates the SHOW POOLS; command to display the maximum configured connections per pool to increase visibility when throttling users via the new pool_size column.
Note
This PR follows from https://github.com/pgbouncer/pgbouncer/pull/707 and has some overlapping commits. The previous PR should merge before this one.
cc @viggy28
