Access PgBouncer statistics
PgBouncer is used at Aiven as a connection pooler to lower the performance impact of opening new connections to Aiven for PostgreSQL®.
After connecting to PgBouncer, you can display statistics available from PgBouncer, such as:
total_xact_count
total_query_count
total_received
total_sent
total_xact_time
total_query_time
total_wait_time
avg_xact_count
avg_query_count
avg_recv
avg_sent
avg_xact_time
avg_query_time
avg_wait_time
You have the read-only access to PgBouncer statistics since PgBouncer pools are automatically managed by Aiven.
Get PgBouncer URI
To get the PgBouncer URI, you can use either the Aiven Console or the Aiven CLI client.
PgBouncer URI in the console
- Log in to the Aiven Console, and go to a desired organization, project, and service.
- Click Connection pools, and find a desired pool.
- Click Actions > Info > Primary Connection URI.
PgBouncer URI with the Aiven CLI
Use jq to parse the JSON response. Execute the following
command replacing SERVICE_NAME
and PROJECT_NAME
as needed:
avn service get SERVICE_NAME --project PROJECT_NAME --json | jq -r '.connection_info.pgbouncer'
Expect to receive an output similar to the following:
postgres://avnadmin:xxxxxxxxxxx@demo-pg-dev-advocates.aivencloud.com:13040/pgbouncer?sslmode=require
Connect to PgBouncer
To connect to PgBouncer, use the extracted URI:
psql 'EXTRACTED_PGBOUNCER_URI'
Access statistics
-
Enable the expanded display by running:
pgbouncer=# \x
-
Show the statistics by running:
pgbouncer=# SHOW STATS;
Depending on the load of your database, expect an output similar to the following:
database | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
pgbouncer | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(1 row)
Run SHOW HELP
to see all pgbouncer
commands.