In addition to PostgreSQL server connection status, PgBouncer's administration console can provide details regarding clients within its queue. Maintaining a healthy and active PgBouncer queue is the key to high throughput over limited resources. In this case, we artificially limited the amount of server connections available to clients, which means that there is potential for stubborn or broken clients to prevent connection turnover.
This, of course, will effectively remove the connections from the pool, creating a bottleneck that could lead to choking transaction throughput. Let's explore the PgBouncer console a bit more to learn what it knows about the database clients attempting to communicate with PostgreSQL.
In this section, we will continue our previous exploration into the PgBouncer console. Check the Listing PgBouncer client connections recipe for a refresher. Remember to use the pgbouncer database name to enter the administration console.
Follow these steps to get the status of PgBouncer clients:
- Connect to the
pgbouncerdatabase on port6432of the PostgreSQL server as thepostgresuser. - Issue the following query:
SHOW CLIENTS;
As before, we connect to the pgbouncer database name on port 6432 to use the administration console. By sending SHOW CLIENTS as a query, PgBouncer responds with a list of every client using or waiting for a PostgreSQL connection. Fields of particular interest include the following:
user: This displays the user that is currently connected to the database. If we used advanced settings, this could differ from the user that is connected to PgBouncer.database: This column indicates the database that the client is attached to. A PostgreSQL server can host many databases, so this is very helpful information. Again, advanced settings can change this from the database name used to create the connection to PgBouncer.state: This column shows whether the connection is active, used, waiting, or idle. Clients are marked as active when they are currently using a connection. If the client is queued prior to a connection becoming available, they are marked as waiting. The used and idle status assignments do not seem to actually be valid for the client state, so don't worry about them.connect_time: This provides the exact time PgBouncer created the connection to PostgreSQL. Although we specifically ask about the client status, this element is associated with the connection to PostgreSQL. Since connections are recycled, they can be hours or even days old. In determining health, we actually want slightly older connections in this list, as that suggests low connection turnover, and connection turnover can be expensive.request_time: This lists the last time the listed client transmitted query activity. On busy servers, this should always be a very recent timestamp. Otherwise, we are potentially wasting server resources by maintaining unnecessary idle connections. In this case, we need to examine the pool size settings and consider reducing them. Alternatively, there may be a problem with the marked PostgreSQL connection, or the assigned client could be frozen. This will indicate that we need to investigate the database health, poll the development, or support departments to check applications for normal operation.
Feel free to browse the PgBouncer documentation for other available fields.
If this recipe looked familiar, that's because the important fields are exactly the same as those in the Listing PgBouncer server connections recipe. Though their interpretation is slightly different, and the list itself is probably more dynamic due to active client states, it's effectively the same data.
The primary difference is the waiting state that we discussed, which doesn't exist when listing server connections. If there are too many clients waiting for too long, it can be a sign of a potential issue. Perhaps the connection pool is too small, resulting in insufficient connection assignments. Maybe a client has gone haywire and is opening hundreds of connections and never closing them, which could lock up all the available connections in the pool.
Whatever the case is, we look for regular state transitions between waiting and active. It is unfortunate that there is no field that details the connection assignment time. With this datum, we can readily discover the clients that are unfairly monopolizing database resources.
We know that we've listed these documentation links before, but we're still working with complicated configuration settings and usage. We've listed them again for convenience:
- PgBouncer Usage: http://pgbouncer.projects.pgfoundry.org/doc/usage.html
- PgBouncer General Mailing List: http://lists.pgfoundry.org/mailman/listinfo/pgbouncer-general