client) Pgbouncer might authorize a client based on some local info (such as auth_file, certificates, PAM or hba files), or in a remote way — with
auth_queryin a database. Thus a client connection while logging in might need and be executing a query. Let’s show that as
CL_ACTIVEqueries also might be actually executing some queries and so linked to actual database server connections by PgBouncer, or idling, doing nothing. This linking / matching of clients and server connections is the whole raison d’etre of PgBouncer. PgBouncer links those clients with server only for some time, depending on
pool_mode— either for a session, transaction or just one request.
As transaction pooling is the most common, we’ll assume it for the rest of this post
cl_activestate is actually might be or might be not linked to a server connection. To account for that we split this state in two:
active-linked/executing. So here’s a new diagram:
CL_WAITINGstate. This might happen as well while client only logging in, so there’s
CL_WAITING_LOGINfor that also:
SV_LOGINfor when authorizing,
SV_ACTIVEfor when it’s linked with (and used or not by) client’s connections, or if it’s free —
pgbouncer. There are a number of
SHOWcommands in it, one of those —
SHOW POOLS— will show number of connections in each state for each pool:
cl_active. And 5 server connections: 4 —
sv_activean one is in
sv_used. Here’s a nice write up on how to monitor these states. But basically you would want to track them in any way you do monitoring, so you’ll have historical picture.
pool_sizefor each proxied database. If not set, it defaults to
default_pool_sizesetting, which again by default has a value of
max_db_connectionsis exactly suitable for covering this problem — it limits total number of connections to any database, so badly behaving clients won’t be able to create too many Postgres backends.
reserve_pool_size— is a limit on an additional, reserve pool, which kicks in if a regular pool is exhausted, i.e. there are
pool_sizeopen server connections. As I understand it was designed to help serve a burst of clients.
max_user_connections— this limits total number of conns to any database from one user. From my point of view, it’s a very strange limit, it makes sense only in case of multiple databases with same users.
max_client_conn— limits total number of incoming clients connections. It differs from
max_user_connectionsbecause it includes connections from any user.
SHOW POOLShas also
SHOW DATABASEScommand, that shows actually applied limits and all configured and currently present pools:
pool_sizewill give you pool utilization, so you can trigger an alert if it goes somewhere close to 100%.
SHOW STATScommand, that provides stats (not a surprize, I know) on requests and traffic for every proxied database:
total_query_time— total number of microseconds spent by pgbouncer when actively connected to PostgreSQL, executing queries. Dividing this by respectful pool size (considering pool size to be the number of seconds that all the server connections might spent in total serving queries within one wall clock second) we get another measure/estimate of pool utilization, let’s call it “query time utilization”.
total_query_timeone can’t tell if there were some short periods of high utilization between two moments when we look at the stats. For example, you have some cron jobs configured to simultaneously start and make some queries to a database. If these queries are short enough, i.e. shorter than stats collection period, then measured utilization might still be low, while at these moment of crons start time they might exhaust the pool. But looking only on Utilization metric, you won’t be able to diagnose that.
SHOW POOLSoutput in a
cl_waitingstate, that we discussed. In Under normal circumstances you won’t see them, and seeing number of waiting client greater than 0 means pool saturation.
SHOW POOLS, and this leads to a possibility of missing such waitings.