TLDR Decrypting PgBouncer’s Diagnostic Information by Ivan Vergiliev

Why high # of active connections is not a problem, and what to do with waiting client's connections.

Monitoring PgBouncer

Use SHOW POOLS !

Some definitions first:

• pool – a set of connections to the same database for a given user.
• client connection – a connection from some client to PgBouncer.
• server connection – a connection from PgBouncer to Postgres.

Client Connections monitoring

They can be in one of 4 states: active, used, waiting or idle.

SHOW POOLS reports only two most important (from admin PoV) ones:

• CL_ACTIVE – is an established client connection. Some clients are EXECUTING queries, but others may not be.
• CL_WAITING — a client is waiting for a server connection to be available — either to be created (if pool_size and limits allow) or for other client to release a connection.

Ops/admin conclusions:

• High cl_active is not a problem.
• High cl_waiting is a problem. If it’s high for long time, there are constantly more clients trying to run queries in PG than limits allow.

Server Connections monitoring

SHOW POOLS but sv_… columns:

• SV_ACTIVE – a server connection is paired with a client connection.
• SV_IDLE – a server connection is ready to serve clients, but is not paired with a client connection.

Failure Scenarios

1. High cl_waitingand high sv_active (or low sv_idle) — PgBouncer waits for running queries to complete. It’s either

• Queries are running for too long. Possible ways to solve: optimize queries or set a strict query timeout.
• Or there are more incoming queries than Postgres can handle :-/

2. High cl_waitingand low (compared to pool_size) sv_active

• Short bursts mean PgBouncer creates new connections to Postgres not fast enough. Maybe increase min_pool_size.
• Or something is preventing creation of new PgBouncer->PG connections. E.g. sum of the pool_sizes for all pools is larger than max_db_connections . Or Postgres setting max_connections is lower than max_db_connections.

Try okmeter.io — a monitoring solution that will show you everything about Postgresql and PgBouncer operations.