Monitoring PgBoucner with InfludDB / Grafana

The concept

PgBouncer is a connection pooler for PostgreSQL. It allows clients to connect to a PostgreSQL DB with limited requirements in terms of CPU / RAM, and faster sessions establishemnt time than doing a direct connection to the DB.

In order to identify misbehaving clients, or simply to do some simple capacity planning, it's possible to use the InfluxDB / Grafana setup to graph the number of clients connected by source IP address at regular intervals.

For this, we're going to run our data collection script locally on the PgBouncer server, and push the data to Grafana.

The code

#!/usr/bin/python

import psycopg2

from collections import defaultdict
from influxdb import InfluxDBClient
from tendo import singleton

# Disable potential SSL warnings
import urllib3
urllib3.disable_warnings()

me = singleton.SingleInstance()  # will sys.exit(-1) if other instance is running

json_body = []

client = InfluxDBClient(GRAFANA_HOST, GRAFANA_PORT, GRAFANA_USER, GRAFANA_PASS, GRAFANA_DB, ssl=True, verify_ssl=True)

conn = psycopg2.connect(dbname='pgbouncer', host='localhost', port=6432, user=DB_USER, password=DB_PASS)
# The "pgbouncer" DB doesn't like the "BEGIN" statement
conn.set_session(readonly=True, autocommit=True)

cur = conn.cursor()

# Connections per client
cur.execute("""
        SHOW CLIENTS;
""")
conn_per_client = defaultdict(int)
for row in cur.fetchall():
    conn_per_client[row[4]] += 1

for (k, v) in conn_per_client.iteritems():
    json_body.append({
        "measurement": "pgbouncer_client_sessions",
        "tags": {"client": k},
        "fields": {"number": v},
    })

client.write_points(json_body)