PgBouncer Monitoring

PgBouncer is an open-source connection pooling tool for PostgreSQL. By default, PostgreSQL has a fairly resource-heavy way of handling incoming database connections, creating a new process for each connection. For some use cases this is fine, but in situations where connections are being rapidly created and dropped, it is inefficient.

PgBouncer works by maintaining a pool of connections for each unique user and database pair. When new incoming client requests come in, these pre-made connections are used to handle the request. Once the client disconnects, the database connection is returned back to the pool where it can later be used by a new incoming connection. This saves server resources on the expensive process of creating and initializing TCP connections because each of these connections are created once and then reused by multiple users.

Why use a Telegraf plugin for PgBouncer?

The Telegraf plugin for PgBouncer allows you to easily collect and store valuable metrics from your PgBouncer instance for long-term storage and analysis. Telegraf has over 40 different output plugins but works particularly well with InfluxDB. By outputting your PgBouncer metrics to InfluxDB cloud, you can also set alerts and notifications based on user-defined thresholds.

How to monitor PgBouncer using the Telegraf plugin

Setting up the PgBouncer Telegraf Input Plugin is easy and requires only a few lines of configuration. The only required configuration value is the address of the database as either a PostgreSQL connection string or URL matching like the following:

PostgreSQL connection string

host=/run/postgresql port=6432 user=telegraf database=pgbouncer

URL Matching

postgres://[pqgotest[:password]]@host:port[/dbname]?sslmode=[disable|verify-ca|verify-full]

Here is a full configuration file example setup:

[[inputs.pgbouncer]]
  ## specify address via a url matching:
  ##   postgres://[pqgotest[:password]]@host:port[/dbname]\
  ##       ?sslmode=[disable|verify-ca|verify-full]
  ## or a simple string:
  ##   host=localhost port=5432 user=pqgotest password=... sslmode=... dbname=app_production
  ##
  ## All connection parameters are optional.
  ##
  address = "host=localhost user=pgbouncer sslmode=disable"

Key PgBouncer metrics to use for monitoring

The PgBouncer Telegraf Input Plugin provides access to a number of different metrics related to the PgBouncer instance itself, metrics for each connection pool, and aggregate metrics. With Telegraf, you have the option to filter and select which metrics you want to be sent to your output storage. Here is the full list of available metrics:

• pgbouncer
  • tags:
    • db
    • server
  • fields:
    • avg_query_count
    • avg_query_time
    • avg_wait_time
    • avg_xact_count
    • avg_xact_time
    • total_query_count
    • total_query_time
    • total_received
    • total_sent
    • total_wait_time
    • total_xact_count
    • total_xact_time
• pgbouncer_pools
  • tags:
    • db
    • pool_mode
    • server
    • user
  • fields:
    • cl_active
    • cl_waiting
    • maxwait
    • maxwait_us
    • sv_active
    • sv_idle
    • sv_login
    • sv_tested
    • sv_used

Project URL   Documentation