Monitoring PgBouncer in Azure PostgreSQL Flexible Server

[Guest varun-dhawan]

PgBouncer is widely used with Postgres to provide connection pooling. It is an easy-to-use and easy-to-install piece of software. The general idea is to specify n number of connections allowed to PgBouncer and m connections allowed to Postgres itself where m is much less than n. A typical configuration is to set PgBouncer to transaction mode. This allows PgBouncer to multiplex the “real” connections to Postgres as transactions are committed or rolled back.

 

 

Azure Database for PostgreSQL – Flexible Server offers PgBouncer as a built-in option, wherein customers can easily enable PgBouncer from Azure Postgres server parameters and update the app connection string to talk to PgBouncer on port 6432, rather than directly to the database server. PgBouncer implements the Postgres wire protocol so that your app will still “think” it is connecting to the Postgres database server.

 

 

However, monitoring PGBouncer can be challenging due to its use of SHOW commands, that are only available via a special database connection as opposed to statistics made available over standard monitoring channels. Today, we are releasing 6 new metrics to monitor PgBouncer connection pooling performance. Each metric is emitted at a 30-minute frequency and has up to 93 days of retention. Customers can configure alerts on the metrics and can also access the new metrics dimensions, to split and filter the metrics data on database name. In this blog post, we will discuss how to use the new metrics to monitor and optimize the PgBouncer settings in Azure database for postgres – flexible server. For a complete list monitoring metrics, please refer Monitor metrics on Azure Database for PostgreSQL - Flexible Server.

 

 

PgBouncer metrics list

 

1. Active client connections
    
   
   • This metric specifies the number of client connections that are currently linked to a server connection and can process queries from all connection pools. This could be an application code, an ETL job or a client query.
     

[*]Waiting client connections

 

• This metric specifies the number of client connections that are waiting on a server connection from all connection pools. Connections from clients which are associated with a PostgreSQL connection.
  

[*]Active server connections

 

• This metric specifies the number of connections from PgBouncer to Postgres. ‘Active’ here doesn’t necessarily mean that it’s actively executing a query instead it means connection is paired with a client a client connection.
  

[*]Idle server connections

 

• This metric specifies the number of server connections that are idling and ready for a client query from all connection pools.
  

[*]Total pooled connections

 

• This metric list the current number of pooled connections.
  

[*]Number of connection pools

 

• This metric list the total number of connection pools per database.
  

 

 

 

It is also important to monitor the PgBouncer metrics in conjunction with PostgreSQL server metrics. This will help you to find any issues with your PostgreSQL server that might be impacting the performance of your connection pool. Some of the key metrics to monitor include the number of connections, the number of active queries, and the amount of available memory.

 

 

PgBouncer admin console

 

 

 

 

Besides the metrics, customers can also leverage PgBouncer admin console for monitoring. Admin console an integrated management interface that allows to manage connection pooling and browse various statistics. To use this interface, you need to connect to PgBouncer internal database (called PgBouncer) using a psql utility. Once connected to the database you can execute SHOW commands that provide information on the current state of PgBouncer. For more details on SHOW commands, refer pgbouncer admin console documentation.

 

 

 

Steps to connect to PgBouncer database in Azure Postgres Flexible Server:

 

1. Set pgBouncer.stats_users parameter to the name of an existing user (ex. "myUser"), and apply the changes

2. Connect to PgBouncer database with the user chosen in a point above via port 6432

Ex. psql "host=myPgServer.postgres.database.azure.com port=6432 dbname=pgbouncer user=myUser password=myPassword sslmode=require"

 

 

 

 

 

 

Why monitor PgBouncer?

 

 

 

 

Monitoring PgBouncer is important for several reasons. PgBouncer is used with Postgres to provide connection pooling, which allows for better management of database connections and improved application performance. However, monitoring PgBouncer can be challenging as it uses SHOW commands that are only available via a special database connection.

 

 

With the new PgBouncer monitoring metrics such as active client connections, waiting client connections, active server connections, idle server connections, total pooled connections, and number of connection pools, customers can gain insights into the performance of their connection pool and identify any issues before they become critical. In addition, monitoring PgBouncer metrics in conjunction with PostgreSQL server metrics can help customers identify any issues with their PostgreSQL server that might be impacting the performance of their connection pool.

 

 

 

Conclusion

 

 

 

 

PgBouncer metrics provides the details of the connection pooling in your database, as well as count of active/idle connections. You can use this information to tune your database connections for better overall performance. In addition, you can also setup alerts to get proactive notifications, prevent any issues and ensure that your Flexible Server instance is able to handle the high traffic of your application.

 

I hope that this post provides you with a better understanding of PgBouncer connection pooling in Azure database for PostgreSQL and helps make your life as a database owner easier and more productive. We are always eager to get your feedback, please reach out via email to us at Ask Azure DB for PostgreSQL.

 

Continue reading...