PostgreSQL Performance Monitoring using Percona Monitoring Management (PMM)


About MinervaDB

Enterprise-class open source consulting, 24*7 support and remote DBA services provider for MySQL, MariaDB, MyRocks, PostgreSQL and ClickHouse with  core expertise in performance, scalability, high availability and database reliability engineering. In this blog post we are explaining step-by-step setup of Percona Monitoring Management (PMM) platform for PostgreSQL performance monitoring.

What do we monitor in PostgreSQL infrastructure operations ?

  • PostgreSQL infrastructure throughput and latency ( query performance by response  time)
  • PostgreSQLTransaction Monitoring:
    • Transaction conflicts and deadlocks
    • Transaction locks
    • Transaction duration
    • Transaction performance
  • PostgreSQL configuration / settings
  • PostgreSQL connection monitoring
    • Monitoring active connections
    • PostgreSQL connection health check
  • Monitoring Postgresql disk I/O operations
  • PostgreSQL Monitoring Tools
    • Grafana – Monitoring dashboard to build charts and matrices from time-series data in Prometheus
    • Prometheus DB – Time-series database for PostgreSQL performance monitoring
    • Percona Monitoring Management (PMM) API
    • Query Analytics (QAN) API for storing and querying data collected from PMM client
    • Query Analytics (QAN) web application for monitoring query performance and throughput from a single dashboard

Percona Monitoring Management (PMM) tools for PostgreSQL

Percona Monitoring Management (PMM) tools is client-server monitoring infrastructure built on PMM Server and PMM Client:

PMM Server – PMM Server is the central repository of performance data of PostgreSQL, The data is presented on website infrastructure in the form of tables, dashboards and charts.

PMM Client –  PMM Client is installed on PostgreSQL nodes you want to monitor, PMM Client ships performance metrics of both system resource usage and response-time / latency of queries executed in the respective instances.

Installation and configuration of PMM Server

You can install and configure PMM Server following any of the options below:

  • PMM Server on Docker
  • PMM Server as Virtual Appliance
  • PMM Server from Amazon Marketplace

P.S. – We strongly recommend using PMM Server on Docker to reduce operational complexities and efficiency so in this post we have covered PMM Server installation and configuration on Docker.

Pulling PMM Server Docker image

You pull the latest version from Docker Hub:

Creating PMM Data container

You can create a persistent container for PMM Data running the command below:

Creating and launching PMM Server container

To create and launch PMM Server container use docker run command as below:

Installation and configuration of PMM Client

PMM Client is a package of agents and exporters installed on a database host that you want to monitor. We have PMM client distribution available for both Debian and RedHat based Linux distributions:

Debian based GNU/Linux distributions: 

Most of the Debian systems come with pre-installed packages of wget, gnupg2 and lsb-release but these packages may be missing from Docker base images. In this case, install them manually before running dpkg:

Fetch the repository package:

Install the downloaded repository package using dpkg:

Install PMM client package:

Installing PMM Client packages on RPM-based Linux distributions

Download repository for RPM-based PMM-Client package:

Install PMM Client on RPM-based Linux distributions:

Configuring PostgreSQL for PMM Monitoring

Enable PostgreSQL configuration parameter track_io_timing to record READ-WRITE transactions statistics data from PostgreSQL infrastructure, You can either do this on a configuration file or by executing the query below on a running production instance:

It’s strongly recommended to configure PostgreSQL with SUPERUSER privileges to record maximum meaningful performance statistics to troubleshoot efficiently, You can do this by running following command on a standalone PostgreSQL instance:

If you are monitoring PostgreSQL on RDS or Amazon Aurora for PostgreSQL:

Connecting PMM Clients to PMM Server

After successfully installing and configuring PMM Server, We can configure each PMM client to which PMM Server it should send its data to. To connect a PMM client with PMM server you can execute the following commands from PMM client as ​root​ user or by using ​sudo ​command :

Configure PMM Client in PostgreSQL Database Instance to to export performance metadata:

Conclusion

When you are operating a business critical PostgreSQL infrastructure, The monitoring systems plays a very important role in proactively troubleshooting performance issues. It’s technically very expensive to guarantee database infrastructure operations reliability without visibility to PostgreSQL latency and throughput dashboard. We strongly recommend our customers to invest in PostgreSQL performance monitoring systems.

P.S. – You can also download PDF of this blog post here