Enhancing PostgreSQL Insights with Percona Monitoring and Management: A Closer Look at Recent Updates

Database monitoring tools play a crucial role in ensuring the smooth operation and performance optimization of database systems, and Percona Monitoring and Management (PMM) has been a go-to choice for many organizations seeking robust monitoring solutions for their open source databases. Recently, two significant enhancements have been made to PMM and the postgres_exporter to make it an even more powerful tool for PostgreSQL, contributing to its efficacy and usability. Let’s discuss these updates and explore their implications for PostgreSQL administrators and users.

Necessity for connections

In PostgreSQL, monitoring typically involves querying various system views and tables to gather metrics related to database performance, resource utilization, query execution, and other vital aspects. Each monitoring tool or agent requires a database connection to retrieve this information effectively.

A connection to each monitored database is necessary to collect real-time data accurately. This connection allows the monitoring tool to execute queries against system catalogs and views, retrieving essential metrics and statistics.

Limits and considerations

PostgreSQL has the capability of running many databases on the same server simultaneously.  While most database servers establish one connection per server instance for monitoring purposes, it’s essential to consider potential limitations and factors that may impact this approach specific to PostgreSQL:

Resource utilization: Each database to be monitored requires its own connection, and each connection consumes system resources, including memory and CPU. When monitoring numerous databases, the cumulative resource usage of these connections can become significant, potentially affecting the performance of the PostgreSQL server itself, resulting in the exhaustion of resources.

Connection limits: PostgreSQL imposes limits on the maximum number of connections that can be established concurrently. Administrators need to ensure that the number of monitoring connections, along with regular client connections, does not exceed these limits to prevent connection failures and performance degradation. There’s nothing worse than blocking your application from accessing the data it needs because your monitoring solution consumed all the available connections!

Problem statement: This doesn’t scale!

Enhancements to the postgresql_exporter and PMM

To address the scaling and the number of connections issue by postgresql_exporter, our approach involves implementing a flexible strategy to manage connections efficiently. This entails a configurable connection limit that limits the number of parallel database connections to fetch the required metrics. By adhering to connection limits, we ensure optimal resource utilization and prevent potential bottlenecks. Additionally, we prioritize closing connections promptly once they are no longer needed, thereby preventing the accumulation of hanging or idle connections and exhausting connection pool limits. This proactive management of connections ensures that system resources are utilized judiciously, contributing to smoother operation and enhanced performance of our monitoring infrastructure.

Connection amount limits can be set to postgres_exporter when the service is added to PMM. We added a new flag to the pmm-admin command max-exporter-connections (default value is 5). For example, here is how you can add the Postgresql service with a connection limit equal to ten:

Here we are using auto-discovery-limit=200 to allow auto-discovery of up to 200 databases, it’s needed to let PMM collect per-database metrics. The value should be greater than the actual number of databases in the service. Then we specify max-exporter-connections=10 parameter, which limits how many connections postgres_exporter can open at a time.

On top, we enhanced PMM’s performance on PostgreSQL servers by shifting two heavy queries (pg_stat_user_tables & pg_statio_user_tables) from medium to low resolution. This adjustment reduces strain on server resources while maintaining valuable monitoring insights.

These enhancements were shipped with PMM version 2.41.2. But we have more for PMM version 2.41.3. There we are planning to add a limit for opened connections for pmm-agent. pmm-agent can utilize connections for query explains, advisors, and other features.  To add limitations on that side, we introduce a new flag to pmm-agent runner-max-connections-per-service (default value is 2). This parameter defines how many connections pmm-agent can open per service. 

The last thing that uses connections is QAN, but it always uses a single connection per service. So knowing that we can count the total amount of connections needed by PMM:

max-exporter-connections + runner-max-connections-per-service + 1

Or if all parameters use default values:

5 + 2 + 1 = 8 connections

The development ticket can be found in Jira: https://perconadev.atlassian.net/browse/PMM-12893

Conclusion

The recent enhancements to PMM and the postgres_exporter represent a significant step forward in PostgreSQL monitoring. By improving metrics collection accuracy, enhancing platform stability, and refining user experience, these updates empower administrators and operators to monitor, analyze, and optimize their PostgreSQL databases effectively.

Tools like PMM play a pivotal role in ensuring the reliability, performance, and scalability of mission-critical database infrastructures. With a commitment to innovation and continuous improvement, PMM can help organizations seeking to unlock the full potential of their PostgreSQL deployments.  If you aren’t using a tool to monitor your database environment, give PMM a try; you can monitor it with just a few commands!

Percona Monitoring and Management is a best-of-breed open source database monitoring solution tool for use with MySQL, PostgreSQL, MongoDB, and the servers on which they run. Monitor, manage, and improve the performance of your databases no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today