Monitoring_PGBouncer.md

PgBouncer - Benchmarking

What is PgBouncer?

PgBouncer is an open-source, lightweight, single-binary connection pooler for PostgreSQL. It can pool connections to one or more databases (on possibly different servers) and serve clients over TCP and Unix domain sockets. The primary benefit of PgBouncer is to improve idle connections and short-lived connections at the database server.

When enabled, PgBouncer runs on port 6432 on your database server. You can change your application’s database connection configuration to use the same host name, but change the port to 6432 to start using PgBouncer.

PgBouncer in Azure Database for PostgreSQL Flexiable Server

Azure Database for PostgreSQL – Flexible Server offers PgBouncer as a built-in connection pooling solution. This is an optional service that can be enabled on a per-database server basis. PgBouncer runs in the same virtual machine as the Postgres database server.

Monitoring

PgBouncer also provides an internal database that you can connect to called pgbouncer. Once connected to the database you can execute SHOW commands that provide information on the current state of pgbouncer.

• SHOW HELP - list all the available show commands
• SHOW POOLS — show number of connections in each state for each pool
• SHOW DATABASES - show current applied connection limits for each database
• SHOW STATS - show stats on requests and traffic for every database

Benchmarking (via Pgbench)

Checking PgBouncer performance impact using a simple script

$ cat test.sql
SELECT 1;

Scenario 1 : Without the connection pooling - Port 5432

$ pgbench -c 20 -t 100 -S foo -C -f test.sql -p 5432

pgbench (14.2, server 14.4)
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
latency average = 4333.507 ms
average connection time = 230.568 ms
tps = 3.615200 (including reconnection times)

Scenario 2 : With the connection pooling - Port 6432

$ pgbench -c 20 -t 100 -S foo -C -f test.sql -p 6432

pgbench (14.2, server 14.4)
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
latency average = 3590.498 ms
average connection time = 149.425 ms
tps = 6.570258 (including reconnection times)

As you can see the transaction throughput tps have increased by 50%, a nice gain without tweaking anything in the app or spending on any extra resources. In addition, the average connection time have reduced by 81 milliseconds, a clear reduction in connection overhead.

References

• PgBouncer synopsis - https://www.pgbouncer.org/usage.html#synopsis
• PgBouncer in Azure Database for PostgreSQL - Flexible Server - https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-pgbouncer
• Azure Database for PostgreSQL – Flexible Server with managed PgBouncer in preview - https://azure.microsoft.com/en-us/updates/azure-database-for-postgresql-flexible-server-with-managed-pgbouncer-in-preview/
• Repo - https://github.com/pgbouncer/pgbouncer