PgBouncer monitoring with Okmeter

Okmeter collects and reports full set of metrics on all aspects of PgBouncer operation. That gives you a complete picture of PostgreSQL database performance from client's point of view and enables you to easily understand root causes of any issues with PgBouncer or PostgreSQL functioning.

Okmeter helps to stay on top of everything happening with PgBouncer / PostgreSQL.

Okmeter automatically gathers operational data and statistics about:

• SQL query execution and transaction processing.
• PgBouncer server connections
• Client's connections

PgBouncer Auto-discovery

Okmeter agent will automatically detect a PgBouncer instance running on a server or in a container. For every PgBouncer process found on a server Okmeter agent will gather all needed operational metrics.

Query execution and transaction monitoring

Workload monitoring is the cornerstone of any monitoring. PgBouncer's job is to proxy queries to a DB, so query monitoring is the most crucial part of DB monitoring.

Problems with query processing usually show themselves in a manner of:

• Either query processing durations change, i.e. increase in query execution time is a sure sign of problems.
• Or as query / transaction throughput — a sudden decrease of queries per second might indicate a problem.

Okmeter allows to easily diagnose such issues through detailed metrics for all proxied SQL-queries and transactions.

Our monitoring agent periodically runs SHOW STATS; command against PgBouncer instance and reports it on a dashboard with these metrics and charts:

pgbouncer.total_requests {database: "D", instance: “Y”, source_hostname: “Z”}

Total number of SQL queries proxied (or "pooled" as they say) by pgbouncer. Also known in modern pgbouncer versions as total_query_count.
This metric allow easily spot a sudden change in usual PostgreSQL / PgBouncer load profile:

pgbouncer.total_query_time {database: "D", instance: “Y”, source_hostname: “Z”}

Total number of seconds spent by pgbouncer when actively executing queries connected to PostgreSQL. Note that it's in seconds, not microseconds as in pgbouncer's SHOW STATS output.
On a chart with a ratio of total_query_time / total_requests we will get a clear picture of average query duration:

pgbouncer.total_xact {database: "D", instance: “Y”, source_hostname: “Z”}

Total number of SQL transactions proxied (or as the doc says "pooled") by pgbouncer into a specific database.
You can not only chart it as is, but also get a chart of average number of SQL queries per transaction by dividing total_requests / total_xact. This will allow to spot anomalies in transaction profile:

pgbouncer.total_xact_time {database: "D", instance: “Y”, source_hostname: “Z”}

Total number of seconds spent by pgbouncer when connected to PostgreSQL in a transaction, either executing queries or in idle in transaction state.
Together with total_query_time this metric allows to estimate the percentage of time that PgBouncer's server connections are idling while being in transaction. One can get that as 1 - total_query_time / total_xact_time:

pgbouncer.total_wait_time {database: "D", instance: “Y”, source_hostname: “Z”}

Time in seconds spent by clients waiting for a server connection to the database D. This metric is avalable since PgBouncer 1.8.
This will show if database performance degradation from client's point of view was due to a corresponding PgBouncer pool is exhausted, so one can try to tune its size with pool_size or max_db_connections settings:

pgbouncer.total_received {database: "D", instance: “Y”, source_hostname: “Z”}

pgbouncer.total_sent {database: "D", instance: “Y”, source_hostname: “Z”}

Total volume in bytes of network traffic received and sent by pgbouncer to each database:

PgBouncer server connections monitoring

Okmeter agent periodically executes SHOW POOL against a special virtual administrative pgbouncer database.
For each PgBouncer instance Okmeter reports its operational stats on a dashboard with these metrics and charts:

pgbouncer.server_connections.count {database: "D", user: "U", state: "S", instance: “Y”, source_hostname: “Z”}

Current number of open Server connections (i.e. from PgBouncer to PostgreSQL) to different databases (tagged as database label) from different users in different states according to SHOW POOLS sv_ fields — sv_active, sv_idle, sv_used, sv_tested and sv_login.

• active — number of Server connections that are linked to a client. Depending on pool_mode within that connection either a transaction is in progress or an SQL query is executing, or, in case of pool_mode = session it might be idling without open transactions.
• idle — number of Server connections that are unused right now and are immediately usable for client queries.
• login — Server connections that are currently logging / authorizing in a database.
• used — Server connections that have been idle more than server_check_delay (which is 30 seconds by default), so they need to be checked with server_check_query before they can be used.
• tested — Server connections that are currently running either server_reset_query after being used by some client and before being avalable to a new one. Or running server_check_query after being in used/SV_USED state.

These server connection charts are avalable not only on one particular PgBouncer instance, but also combine stats from multiple pgbouncers in your environment. Or contrariwise you can drill down into a specific database connection pool:

PgBouncer client's connections monitoring

Okmeter monitoring agent repeatedly runs SHOW CLIENTS; on PgBouncer and gathers that:

pgbouncer.clients.count {database: "D", user: "U", client_address: "A", state: "S", instance: “Y”, source_hostname: “Z”}

Current number of client's connections from different users and IP addresses (tagged as client_address label) to different databases in different states.
This metric not only allows to see how many connections were opened to a particular database as a particular user, but having client_address label allow to see connection origin IP address. Which is super handy when investigating an abnormal behavior of some application instances, like here, for example: Or, if on Average Query Time chart you identify a performance degradation as here: You can not only see that there was a surge of client connections, but with Okmeter you'll get a picture of these connections states:

• active — established client connections.
• active-link — client connections linked with server connections, i.e. a client session got an actual PostgreSQL connection from a pool for this moment, and probably is executing a query within it.
• waiting — client connections that have sent queries, but have not yet got a server connection. And are waiting to be linked with either a connection in a pool released/freed by another client or for newly established by pgbouncer server connection if pool's limits allow that.

Such surge as on the chart above might degrade database performance from client's point of view. And then it's crucial to understand who's responsible for it. Okmeter helps in that by providing a chart of connections' distribution in terms of IP addresses of clients with client_address label. That helps to quicker diagnose surge origin: Or, for example, in a case of database connection pool limit exhaustion, you'll see client connections in waiting state, i.e. awaiting a free server connection to be linked with: This is definetelly might cause problems to overall performance of your system. But thanks to Okmeter's built-in auto-triggers, you'll be notified whenever this happens.
And with detailed metrics on waiting clients you'll be able to easily pinpoint which databases/clients are suffering these waits:

And there's more!

This is only a part of what Okmeter helps with. It will not only show you detailed charts of all the connections and query processing. Okmeter also notifies when something goes wrong with a DB, because of a built-in base of configured by experts auto-triggers.

Also Okmeter knows everything about PostgreSQL monitoring, and will help you navigate in PostgreSQL operations as well.

With Okmeter monitoring you'll be prepared to any trouble with PgBouncer and PostgreSQL.