Posted inDatabase

Percona Monitoring and Management (PMM) Setup Guide: Advanced MySQL and PostgreSQL Monitoring

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Quick Start: Up and Running in 5 Minutes

Need to monitor your database status immediately? Here is the fastest way to set up PMM Server using Docker and connect your first database to the system.

1. Run PMM Server

# Create volume for persistent data storage
docker volume create pmm-data

# Launch PMM Server container
docker run -d \
  -p 80:80 -p 443:443 \
  --name pmm-server \
  --restart always \
  -v pmm-data:/srv \
  percona/pmm-server:2

2. Install PMM Client on the Database Node

# For Ubuntu/Debian
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
dpkg -i percona-release_latest.generic_all.deb
apt update
apt install pmm2-client

3. Connect Client to Server

# Replace <PMM_SERVER_IP> with your server's IP
pmm-admin config --server-insecure-tls --server-url=https://admin:admin@<PMM_SERVER_IP>

Once completed, access http://<PMM_SERVER_IP>. The default username/password is admin/admin. Your system is ready!

Why I Chose PMM Over Building Prometheus + Grafana from Scratch

In the past, whenever the database lagged at 2 AM, I used to blindly type SHOW PROCESSLIST. Manually building a combo of Prometheus, Grafana, and various individual exporters takes a lot of time just for dashboard configuration, often leading to information blindness.

Percona’s PMM is like a premium “instant noodle” package. It integrates everything from CPU and RAM charts to deep Query Analytics (QAN). Instead of spending two days designing dashboards, it only takes 10 minutes to have a comprehensive observability system.

How PMM Architecture Works

PMM operates on a very lean Client-Server model:

  • PMM Server: The data processing hub using VictoriaMetrics, displaying charts via Grafana, and managing alerts.
  • PMM Client: Runs directly on the Database server. It collects system metrics and parses slow logs through lightweight agents.

Detailed Configuration for MySQL and PostgreSQL

After connecting the Client, whether you are using PostgreSQL or MySQL, you need to register specific databases for PMM to start collecting deep-dive data.

For MySQL:

Create a dedicated user with minimal required permissions for security:

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'very_hard_password';
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost';

Add the node to PMM:

pmm-admin add mysql --username=pmm --password=very_hard_password --query-source=perfschema

For PostgreSQL:

PostgreSQL requires the pg_stat_statements extension so PMM can “read” the queries. You need to modify the postgresql.conf file:

shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on
track_functions = all

Restart Postgres and run the add command:

pmm-admin add postgresql --username=postgres --password=your_pass --query-source=pgstatstatements

Query Analytics (QAN): The Ultimate “Weapon”

This is PMM’s most valuable feature. After 6 months of use, I realized that 80% of performance issues don’t come from hardware. They usually stem from queries lacking indexes or excessive joins.

In the Query Analytics section, you will see a list of the most resource-intensive queries, providing insights similar to optimizing PostgreSQL queries with EXPLAIN ANALYZE. Pay attention to these metrics:

  • Latency: Average response time of the statement.
  • Rows Examined vs Sent: If you’re scanning 1 million rows just to retrieve 1, you definitely need an index.
  • Query Count: The frequency of the statement within a specific timeframe.

Setting Up Automated Alerting

Don’t let your customers be the first to report a site crash. PMM has a powerful Integrated Alerting system.

  1. Contact Points: Configure notifications to Slack, Telegram, or Email.
  2. Alert Rules: Use built-in templates like “MySQL down” or “High CPU usage”.
  3. Alert Thresholds: For example, if CPU usage exceeds 85% for 5 consecutive minutes, PMM will automatically trigger an alert message.

Real-World Experience After 6 Months in Production

To ensure the monitoring system doesn’t become a burden, keep these 3 key points in mind:

1. Client Resource Optimization

The PMM Client typically consumes less than 1% of CPU. However, if the database has extremely high traffic (over 10,000 requests/sec), detailed log collection can increase disk I/O. Prioritize using performance_schema over slow_query_log to reduce disk load.

2. Data Retention Policy

Monitoring data accumulates very quickly and can take up dozens of GBs after a month. You should configure it to keep detailed data for 15-30 days. This prevents the monitoring server’s disk from filling up unexpectedly, which is critical to avoid PostgreSQL disk bloating issues.

3. Dashboard Security

The PMM Dashboard contains sensitive information about configurations and query content. Never expose ports 80/443 to the public internet. You should place the PMM Server within an internal network (VPN) or use strict IP Whitelisting.

In summary, PMM is a free tool that provides immense value for database administrators. It helps you move from a reactive to a proactive state, resolving incidents based on actual data rather than guesswork.

Share:
Tags: