Skip to main content

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
note

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

  1. Log in to the Aiven Console, and go to a desired organization, project, and service.
  2. Click Connection pools, and find a desired pool.
  3. 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

  1. Enable the expanded display by running:

    pgbouncer=# \x
  2. 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)
tip

Run SHOW HELP to see all pgbouncer commands.