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.

Screen_Shot_2018-01-18_at_11.46.12.png

Monitored metrics

MetricValues
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
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Monday  —  Friday.

10am  —  6pm UK.

Dedicated Support.