Skip to main content
Version: Self Hosted Lite

Monitor PgBouncer on Instance

Prerequisites

a. Install sfagent.

Click here to install sfagent to the application environment.

b. Configure PgBouncer admin and stats user.

Configure the pgbouncer user as admin and stats user by adding the below lines in the pgbouncer.ini config file.

The default location of the Pgbouncer configuration file is /etc/pgbouncer/pgbouncer.ini.

Execute the following command in the Pgbouncer configuration file.

admin_users = <username>

stats_users = <username>
c. Add ignore startup parameters.

By default, PgBouncer allows only the parameters it can keep track of in the startup packets: client_encoding, datestyle, timezone, and standard_conforming_strings. All other parameters will raise an error. To allow other parameters, ignore startup parameters command is used.

Example: ignore_startup_parameters = extra_float_digits

Configuration 

To initiate the monitoring of PgBouncer running on an instance, add the following configuration to the config.yaml file.

key: <profile key>
tags:
Name: <unique instance name or will be generated from IP>
appName: <add application name>
projectName: <add project name>
metrics:
plugins:
- name: pgbouncer
enabled: true
interval: 30
config:
port: <port>
host: <host/IP>
user: <username>
password: <password>
logging:
  plugins:
- name: pgbouncer
enabled: true
config:
log_path: "/var/log/postgresql/pgbouncer.log"
   

View Metrics and Logs

Follow the below steps to view metrics and logs collected from PgBouncer running on an instance.

  1. Go to the Application tab in SnappyFlow and navigate to your Project > Application > Dashboard

  2. You can view the PgBouncer metrics in the Metrics section and logs in Log Management section.

note

Once the PgBouncer configuration settings are done, the PgBouncer plugin will be automatically detected within the Metrics section. However, if the plugin is not detected, you can import template = PgBouncer to view the corresponding metrics and logs.

  1. To access the unprocessed data gathered from the plugin, navigate to the Browse data section and choose the Index: Metric or Logger, Instance: Endpoint, Plugin, and Document Type.
Template Details
TemplatePluginDocument Type
PgBouncerPgBouncerpgbouncerDetails, databaseStats, databasePools, pgbouncerLogs

Metrics list

PgbouncerDetails
MetricDescription
versionCurrent Version of the PgBouncer
numServerActiveTotal server connections that are linked to a client.
numServerUsedTotal server connections that have been idle for more than server_check_delay, so they need server_check_query to run on them before they can be used again.
numServerIdleServer connections that are unused and immediately usable for client queries.
numServerLoginServer connections currently in the process of logging in.
numServerTestedServer connections that are currently running either server_reset_query or server_check_query
numClientsActiveServer connections that are linked to a client.
numClientsWaitClient connections that have sent queries but have not yet got a server connection.
maximumClientConnMaximum number of clients that can connect to PgBouncer
totalTransactionCountTotal number of SQL transactions pooled by pgbouncer.
totalQueryCountTotal number of SQL queries pooled by pgbouncer.
totalRecievedBytesTotal volume in bytes of network traffic received by pgbouncer.
totalSentBytesTotal volume in bytes of network traffic sent by pgbouncer.
averageTransactionCountPerSecondAverage transactions per second in last stat period.
averageQueryCountPerSecondAverage queries per second in last stat period.
averageSentBytesPerSecondAverage sent (to clients) bytes per second.
averageRecievedBytesPerSecondAverage received (from clients) bytes per second.
Database Stats
MetricDescription
databaseNameStatistics are presented per database.
totalTransactionTimeMicrosecondsTotal number of microseconds spent by pgbouncer when connected to PostgreSQL in a transaction, either idle in transaction or executing queries.
averageTransactionTimeMicrosecondsAverage transactions per second in last stat period.
totalRecievedBytesTotal volume in bytes of network traffic received by pgbouncer.
averageTransactionCountPerSecondAverage transactions per second in last stat period.
averageRecievedBytesPerSecondAverage received (from clients) bytes per second.
totalTransactionCountTotal number of SQL transactions pooled by pgbouncer.
averageWaitTimeMicrosecondsAverage time spent by clients waiting for a server that were assigned a backend connection within the current stats_period, in microseconds (averaged per second within that period).
totalWaitTimeMicrosecondsTime spent by clients waiting for a server, in microseconds. Updated when a client connection is assigned a backend connection.
averageQueryTimeMicrosecondsAverage query duration, in microseconds.
totalQueryCountTotal number of SQL queries pooled by pgbouncer.
averageQueryCountPerSecondAverage queries per second in last stat period.
averageSentBytesPerSecondAverage sent (to clients) bytes per second.
totalSentBytesTotal volume in bytes of network traffic sent by pgbouncer.
totalQueryTimeMicrosecondsTotal number of microseconds spent by pgbouncer when actively connected to PostgreSQL, executing queries.
Database Pools
MetricDescription
userUser name
databaseNameDatabase name
numServerActiveServer connections that are linked to a client.
numServerUsedServer connections that have been idle for more than server_check_delay, so they need server_check_query to run on them before they can be used again.
numServerIdleServer connections that are unused and immediately usable for client queries.
numServerLoginServer connections currently in the process of logging in.
numServerTestedServer connections that are currently running either server_reset_query or server_check_query
numClientsActiveServer connections that are linked to a client.
numClientsWaitClient connections that have sent queries but have not yet got a server connection.
maximumWaitTimeinSecondsMaximum wait time of the first (oldest) client in the queue has waited, in seconds
maximumWaitMicrosecondsMicrosecond part of the maximum waiting time.
poolModeThe pooling mode in use.