How to Monitor MySQL and PostgreSQL with Percona PMM 3

Percona Monitoring and Management (PMM) is an open-source monitoring platform for MySQL, PostgreSQL and MongoDB. A central PMM Server stores metrics and serves Grafana dashboards, and a lightweight PMM Client on each database host collects metrics and query statistics. In this tutorial you will run PMM Server 3 in Docker on Ubuntu 24.04, register a MySQL and a PostgreSQL server with it, and use Query Analytics to find the queries that cost the most time.

Prerequisites

To follow this guide you need:

A server for PMM Server running Ubuntu 24.04 with at least 2 vCPU, 4 GB of RAM and 50 GB of free disk, with Docker Engine installed. A CubePath VPS works well. Disk use grows with the number of monitored hosts and the retention period.
One or more database servers running Ubuntu 24.04 with MySQL 8.0 or newer, or PostgreSQL 13 or newer, and a non-root user with sudo on each.
Network access from every database server to the PMM Server on TCP port 443.

In the commands below, replace pmm_server_ip with the address of your PMM Server.

Step 1 - Running PMM Server

PMM Server is distributed as a single Docker image. Create a volume so metrics and settings survive container upgrades:

docker volume create pmm-data

Start the container. PMM Server 3 listens on port 8443 inside the container, which you publish as the standard HTTPS port 443 on the host:

docker run -d \
  --name pmm-server \
  --restart always \
  -p 443:8443 \
  -v pmm-data:/srv \
  percona/pmm-server:3

The first start takes a minute or two while the internal services initialize. Check the container and its logs:

docker ps --filter name=pmm-server --format 'table {{.Image}}\t{{.Status}}\t{{.Ports}}\t{{.Names}}'
docker logs --tail 20 pmm-server

IMAGE                  STATUS                   PORTS                                         NAMES
percona/pmm-server:3   Up 2 minutes (healthy)   0.0.0.0:443->8443/tcp, [::]:443->8443/tcp   pmm-server

The default login is admin / admin. Change it right away, from the command line or at the first login in the browser. Replace your_strong_password:

docker exec -t pmm-server change-admin-password your_strong_password

Open https://pmm_server_ip in your browser. PMM Server uses a self-signed certificate by default, so the browser shows a warning you need to accept. Log in with admin and the new password; the Home dashboard appears, still empty.

Important

Ports published by Docker bypass UFW rules, because Docker inserts its own iptables rules. If port 443 should only be reachable from your database servers and your office, restrict it with a network-level firewall in front of the server, or publish it on a private interface only (for example -p 10.0.0.5:443:8443).

Step 2 - Installing PMM Client on the database servers

Run the next commands on each database server. The client is published in Percona's repository, which is configured with the percona-release tool:

sudo apt update
sudo apt install -y curl gnupg2 lsb-release
curl -fsSLO https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo apt install -y ./percona-release_latest.generic_all.deb

Enable the PMM 3 client repository and install the client:

sudo percona-release enable pmm3-client
sudo apt update
sudo apt install -y pmm-client

Check the installed version:

pmm-admin --version

The output shows the PMM version, which should match the major version of your server (3.x).

Step 3 - Registering the client with PMM Server

sudo pmm-admin config --server-insecure-tls \
  --server-url=https://admin:your_strong_password@pmm_server_ip:443

The command registers the node with PMM Server and reloads the local pmm-agent service with the new settings.

If the password contains special characters such as @ or :, URL-encode them in the URL. Confirm the agent is connected:

sudo pmm-admin status

The output lists the server URL and Connected: true. At this point the server already shows basic operating system metrics (CPU, memory, disk, network) for the host in the Node Overview dashboard.

Step 4 - Adding a MySQL server

PMM needs a MySQL account with read access to status and performance data. Connect to MySQL as root:

sudo mysql

Create the monitoring user. Use a strong password in place of your_pmm_password; MAX_USER_CONNECTIONS keeps PMM from using many connections if the server is under stress:

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'your_pmm_password' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';
EXIT;

For Query Analytics, PMM reads statement statistics from the Performance Schema, which is enabled by default in MySQL 8. Verify it:

sudo mysql -e "SHOW VARIABLES LIKE 'performance_schema';"

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

Add the MySQL instance to PMM, connecting through the local socket so the localhost account matches:

sudo pmm-admin add mysql \
  --username=pmm \
  --password=your_pmm_password \
  --socket=/var/run/mysqld/mysqld.sock \
  --query-source=perfschema \
  --service-name=mysql-db01

Pick a --service-name that identifies the instance, since it is how the service appears in every dashboard. Confirm that the service and its agents are running:

sudo pmm-admin list

The output has two tables: the services (your mysql-db01 instance) and the agents. Every agent should show Running, or Connected for pmm_agent.

mysqld_exporter collects metrics and mysql_perfschema_agent feeds Query Analytics.

Step 5 - Adding a PostgreSQL server

For PostgreSQL, Query Analytics uses the pg_stat_statements extension, which ships with the PostgreSQL packages on Ubuntu but has to be preloaded. Ubuntu's postgresql.conf includes every file in the conf.d directory, so add the settings in a separate file (adjust 16 to your PostgreSQL version):

sudo nano /etc/postgresql/16/main/conf.d/pmm.conf

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
track_io_timing = on

Warning

If shared_preload_libraries is already set elsewhere (for example for timescaledb or pg_cron), do not add a second line. Edit the existing one and append pg_stat_statements to its comma-separated list, because only the last value set wins.

A new preloaded library requires a restart:

sudo systemctl restart postgresql

Create the monitoring role and the extension. The built-in pg_monitor role grants read access to all statistics views without making PMM a superuser:

sudo -u postgres psql

CREATE ROLE pmm LOGIN PASSWORD 'your_pmm_password';
GRANT pg_monitor TO pmm;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
\q

Confirm the extension is collecting data:

sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_statements;"

A non-zero count means statements are being tracked. Register the instance with PMM:

sudo pmm-admin add postgresql \
  --username=pmm \
  --password=your_pmm_password \
  --host=127.0.0.1 \
  --port=5432 \
  --query-source=pgstatements \
  --service-name=postgresql-db02

Run sudo pmm-admin list again: you should see postgres_exporter and postgresql_pgstatements_agent with status Running.

Step 6 - Finding slow queries with Query Analytics

Give PMM a few minutes to collect data, then open Query Analytics (QAN) from the main menu. QAN groups similar queries into fingerprints (the query with its literal values replaced by placeholders) and ranks them by Load, the average number of concurrent executions of that query over the selected period. The top of the list is where optimization pays off most.

A practical workflow:

Set the time range to a period with a known slowdown, and filter by service name on the left.
Sort by Load and open the top query.
In the details panel, compare Query Time and Rows examined against Rows sent. A query that examines millions of rows to return a few usually lacks an index.
Use the Examples and Explain tabs to see a real execution and its plan, then test an index on a staging copy.

For a quick check without the UI, the same data is available directly in PostgreSQL:

sudo -u postgres psql -c "SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 60) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

Step 7 - Using the dashboards and alerts

Besides QAN, PMM includes dashboards for each technology. The ones worth checking first:

Dashboard	What to look at
Node Overview	CPU saturation, memory, disk I/O latency of the host
MySQL Instance Summary	Connections, queries per second, InnoDB buffer pool hit ratio
MySQL InnoDB Details	Row operations, redo log and checkpoint activity
PostgreSQL Instance Summary	Connections, transactions per second, cache hit ratio

Alerting in PMM 3 is built on Grafana alerting. Under Alerting you can create rules from Percona's ready-made templates (for example high connection usage or a node that stops reporting) and connect a contact point such as email, Slack or a webhook. Start with a handful of templates for connection saturation, disk space and replication lag rather than alerting on every metric.

Upgrading PMM Server

Because all data lives in the pmm-data volume, upgrading means replacing the container with a newer image:

docker pull percona/pmm-server:3
docker stop pmm-server
docker rm pmm-server
docker run -d --name pmm-server --restart always -p 443:8443 -v pmm-data:/srv percona/pmm-server:3

Upgrade the server first and the clients after it, with sudo apt update && sudo apt install --only-upgrade pmm-client on each database host.

Troubleshooting

pmm-admin config fails with a connection error. From the database server, run curl -k https://pmm_server_ip/ and check that it returns the login page. If it times out, a firewall between the hosts is blocking port 443.

The service is listed but dashboards show no data. Check the agent logs on the database server with sudo journalctl -u pmm-agent -n 50. Wrong credentials for the pmm database user show up there as authentication errors.

Query Analytics is empty for PostgreSQL. SELECT * FROM pg_stat_statements LIMIT 1; must work as the pmm user. If it fails with pg_stat_statements must be loaded via shared_preload_libraries, the restart after Step 5 did not happen or the setting is overridden in another file.

PMM Server runs out of disk. Lower the data retention period under the PMM settings (Advanced settings) and check the size of the volume with docker system df -v.

Conclusion

You deployed PMM Server 3 in Docker, connected MySQL and PostgreSQL hosts with pmm-client, and used Query Analytics to find the queries with the highest load. Next, replace the self-signed certificate with a trusted one, add the rest of your database servers with the same pmm-admin add commands, and set up a few alert rules so you hear about saturation before users do.

On this page