Setting Up Percona Monitoring and Management

Percona Monitoring and Management (PMM) is a free, open-source database monitoring platform designed specifically for MySQL, PostgreSQL, MongoDB, and ProxySQL. It provides deep query analytics, real-time dashboards, and alerting capabilities that generic monitoring tools cannot match. This guide covers deployment, agent setup, and using PMM for database performance optimization.

PMM Architecture

• PMM Server — central server running Grafana, VictoriaMetrics, ClickHouse, and the PMM API
• PMM Client — lightweight agent installed on database servers, collects metrics and query data

Installing PMM Server

Docker (Recommended)

# Create persistent volume
docker volume create pmm-data

# Run PMM Server
docker run -d \
    --name pmm-server \
    --restart always \
    -p 443:443 \
    -p 80:80 \
    -v pmm-data:/srv \
    -e PMM_AGENT_SERVER_ADDRESS=pmm-server-ip:443 \
    percona/pmm-server:2

# Access web UI at https://pmm-server-ip
# Default credentials: admin / admin (change immediately)

Using Docker Compose

version: '3.8'
services:
  pmm-server:
    image: percona/pmm-server:2
    container_name: pmm-server
    restart: always
    ports:
      - "443:443"
      - "80:80"
    volumes:
      - pmm-data:/srv
    environment:
      - PMM_AGENT_SERVER_ADDRESS=0.0.0.0:443
      - DISABLE_TELEMETRY=1
volumes:
  pmm-data:

Installing PMM Client

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

# Rocky Linux/RHEL
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum install pmm2-client

# Register with PMM Server
sudo pmm-admin config --server-insecure-tls --server-url=https://admin:password@pmm-server-ip:443

Adding MySQL to PMM

# Create PMM monitoring user on MySQL
CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'PMMPass123!' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost';
GRANT SELECT ON performance_schema.* TO 'pmm'@'localhost';

# Add MySQL service to PMM
sudo pmm-admin add mysql \
    --username=pmm \
    --password=PMMPass123! \
    --query-source=perfschema \
    --service-name=mysql-primary \
    --host=localhost \
    --port=3306

# Verify
sudo pmm-admin list

Adding PostgreSQL to PMM

# Create PMM monitoring user
CREATE USER pmm WITH PASSWORD 'PMMPass123!';
GRANT pg_monitor TO pmm;

# Install required extensions
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_monitor;  -- Percona enhanced stats (optional)

# Add PostgreSQL service
sudo pmm-admin add postgresql \
    --username=pmm \
    --password=PMMPass123! \
    --query-source=pgstatmonitor \
    --service-name=postgres-primary \
    --host=localhost \
    --port=5432

sudo pmm-admin list

Adding MongoDB to PMM

# Create PMM monitoring user
db.createUser({
  user: "pmm",
  pwd: "PMMPass123!",
  roles: [
    { role: "clusterMonitor", db: "admin" },
    { role: "read", db: "local" }
  ]
})

# Enable profiling
db.setProfilingLevel(2, { slowms: 100 })

# Add MongoDB service
sudo pmm-admin add mongodb \
    --username=pmm \
    --password=PMMPass123! \
    --service-name=mongo-rs0 \
    --host=localhost \
    --port=27017

Key PMM Dashboards

Query Analytics (QAN)

QAN is PMM's killer feature. It captures every query executed and provides:

• Query fingerprints with execution count, average time, and total time
• Detailed query metrics: rows examined, rows sent, lock time, temp tables
• Query plan analysis (EXPLAIN for each query)
• Time-range comparison to detect performance regressions
• Filtering by schema, user, client host

MySQL Dashboards

• MySQL Instance Summary — CPU, connections, queries, InnoDB metrics
• MySQL InnoDB Details — buffer pool, I/O, transactions, row operations
• MySQL Replication Summary — replica lag, relay log, GTID status
• MySQL Table Details — per-table row counts, sizes, index usage

PostgreSQL Dashboards

• PostgreSQL Instance Summary — connections, transactions, tuples, buffers
• PostgreSQL Vacuum Monitoring — autovacuum activity, dead tuples, table bloat

Setting Up Alerts

# PMM uses Grafana alerting (Alertmanager integration)
# Navigate to: Alerting → Alert Rules → Create alert rule

# Useful alert examples:
# - MySQL replication lag > 30 seconds
# - PostgreSQL connection usage > 80%
# - Query execution time p99 > 5 seconds
# - InnoDB buffer pool hit ratio < 95%
# - Disk usage > 85%
# - Long-running queries > 300 seconds

PMM Advisors

PMM includes automated checks (Advisors) that scan your database configuration and flag issues:

• Binary log configuration issues
• InnoDB settings recommendations
• Security configuration checks
• Version-specific advisories
• Replication configuration validation

Access Advisors from the PMM menu → "Advisors" → "All checks".

Best Practices

• Use pg_stat_monitor instead of pg_stat_statements for PostgreSQL — it captures more detail including histograms
• Set up QAN with perfschema for MySQL (not slow_query_log) for lower overhead
• Enable only the collectors you need to minimize agent resource usage
• Retain at least 30 days of metrics for trend analysis
• Set up alerting for replication lag, connection saturation, and disk usage as minimum alerts
• Use PMM's integrated Backup Management for scheduled database backups
• Update PMM Server and Clients together to maintain compatibility