Monitoring PGBouncer

This article will help you get the Pgbouncer plugin for sd-agent configured and returning metrics

Installing the pgbouncer plugin package

Install the pgbouncer plugin on Debian/Ubuntu:

sudo apt-get install sd-agent-pgbouncer

Install the pgbouncer plugin on RHEL/CentOS:

sudo yum install sd-agent-pgbouncer

Read more about agent plugins.

Configuring the agent to monitor PgBouncer

1. Connect to psql and create a monitoring user:

CREATE USER serverdensity WITH PASSWORD 'supersecretpassword';
GRANT SELECT ON pg_stat_database TO serverdensity;

2. Add serverdensity to your PgBouncer userlist.txt

echo '"serverdensity" "supersecretpassword"' >> /path/to/userlist.txt

3. Configure /etc/sd-agent/conf.d/pgbouncer.yaml

init_config:

instances:
 - host: localhost
   port: 15432
   username: serverdensity
   password: supersecretpassword

4. Restart the agent

sudo /etc/init.d/sd-agent restart

or

sudo systemctl restart sd-agent

Verifying the configuration
Execute info to verify the configuration with the following:

sudo /etc/init.d/sd-agent info 

or

/usr/share/python/sd-agent/agent.py info

If the agent has been configured correctly you'll see an output such as:

pgbouncer
-----
  - instance #0 [OK]
  - Collected * metrics

You can also view the metrics returned with the following command:

sudo -u sd-agent /usr/share/python/sd-agent/agent.py check pgbouncer

Configuring graphs

Click the name of your server from the Devices list in your Server Density account then go to the Metrics tab. Click the + Graph button on the right then choose the pgbouncer metrics to display the graphs. The metrics will also be available to select when building dashboard graphs.

Monitored metrics

Metric	Values
pgbouncer.pools.cl_active Client connections linked to server connection and able to process queries	connection / None Type: float
pgbouncer.pools.cl_waiting Client connections waiting on a server connection	connection / None Type: float
pgbouncer.pools.maxwait Age of oldest unserved client connection	second / None Type: float
pgbouncer.pools.sv_active Server connections linked to a client connection	connection / None Type: float
pgbouncer.pools.sv_idle Server connections idle and ready for a client query	connection / None Type: float
pgbouncer.pools.sv_login Server connections currently in the process of logging in	connection / None Type: float
pgbouncer.pools.sv_tested Server connections currently running either server_reset_query or server_check_query	connection / None Type: float
pgbouncer.pools.sv_used Server connections idle more than server_check_delay, needing server_check_query	connection / None Type: float
pgbouncer.stats.avg_query The average query duration	microsecond / None Type: float
pgbouncer.stats.avg_recv The client network traffic received	byte / second Type: float
pgbouncer.stats.avg_req The average number of requests per second in last stat period	request / second Type: float
pgbouncer.stats.avg_sent The client network traffic sent	byte / second Type: float
pgbouncer.stats.bytes_received_per_second The total network traffic received	byte / second Type: float
pgbouncer.stats.bytes_sent_per_second The total network traffic sent	byte / second Type: float
pgbouncer.stats.requests_per_second The request rate	request / second Type: float
pgbouncer.stats.total_query_time Time spent by pgbouncer actively querying PostgreSQL	microsecond / None Type: float