Database Performance Monitoring with PMM

Percona Monitoring and Management (PMM) is an open-source database observability platform that provides deep performance insights for MySQL, PostgreSQL, and MongoDB through query analytics, slow query analysis, and pre-built Grafana dashboards. PMM's Query Analytics feature identifies the most impactful queries and guides optimization efforts, making it an essential tool for database performance monitoring.

Prerequisites

  • Linux server for PMM Server (minimum 2 CPU, 4 GB RAM, 20 GB disk)
  • Docker installed on the PMM Server host
  • PMM Client installed on monitored database servers
  • MySQL 5.7+, PostgreSQL 9.6+, or MongoDB 4.0+
  • Network connectivity between PMM Client and PMM Server (port 443 or 80)

Installing PMM Server

# Install PMM Server using Docker (recommended)
docker pull percona/pmm-server:2

# Create a persistent volume for PMM data
docker volume create pmm-data

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

# Verify PMM Server is running
docker ps | grep pmm-server
docker logs pmm-server

# PMM UI is available at https://your-server-ip/
# Default credentials: admin / admin (change on first login)

For production deployments, use podman or dedicated server resources:

# Production: Use custom SSL certificates and external PostgreSQL
docker run -d \
  --name pmm-server \
  -p 443:443 \
  -p 80:80 \
  --volume pmm-data:/srv \
  --volume /etc/ssl/pmm:/srv/nginx/ssl \
  -e PMM_WATCHTOWER_HOST=watchtower.example.com \
  --restart always \
  percona/pmm-server:2

# Set admin password
docker exec -it pmm-server change-admin-password your-new-password

# Check PMM Server status
docker exec -it pmm-server pmm-admin status

Installing PMM Client

# Install PMM Client on database servers (Ubuntu/Debian)
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install -y pmm2-client

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

# Connect PMM Client to PMM Server
sudo pmm-admin config \
  --server-insecure-tls \
  --server-url=https://admin:password@pmm-server-ip:443

# Verify connection
pmm-admin status

Adding MySQL to PMM

# Create a dedicated PMM user in MySQL
mysql -u root -p <<EOF
CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pmm-password' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';
GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO 'pmm'@'localhost';
FLUSH PRIVILEGES;
EOF

# Enable Performance Schema (required for query analytics)
# Add to /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf:
cat >> /etc/mysql/mysql.conf.d/mysqld.cnf <<EOF

[mysqld]
performance_schema = ON
performance_schema_instrument = '%=ON'
performance_schema_consumer_events_statements_history_long = ON
performance_schema_consumer_events_statements_history = ON
slow_query_log = ON
long_query_time = 0   # Log all queries (adjust to 1 or 2 in production)
log_slow_rate_limit = 100
log_slow_verbosity = full
EOF

sudo systemctl restart mysql

# Add MySQL instance to PMM monitoring
sudo pmm-admin add mysql \
  --username=pmm \
  --password=pmm-password \
  --query-source=perfschema \  # Use Performance Schema
  --service-name=mysql-production \
  --host=localhost \
  --port=3306

# Verify MySQL is being monitored
pmm-admin list

Adding PostgreSQL to PMM

# Create PMM user in PostgreSQL
sudo -u postgres psql <<EOF
CREATE USER pmm WITH SUPERUSER ENCRYPTED PASSWORD 'pmm-password';
-- Or with minimal permissions:
CREATE USER pmm WITH ENCRYPTED PASSWORD 'pmm-password';
GRANT pg_monitor TO pmm;
GRANT pg_read_all_stats TO pmm;
EOF

# Enable pg_stat_statements extension (required for query analytics)
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

# Add to postgresql.conf:
cat >> /etc/postgresql/*/main/postgresql.conf <<EOF

# PMM monitoring settings
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 2048
pg_stat_statements.track = all
pg_stat_statements.max = 10000
log_min_duration_statement = 1000  # Log queries > 1 second
EOF

sudo systemctl restart postgresql

# Add PostgreSQL to PMM
sudo pmm-admin add postgresql \
  --username=pmm \
  --password=pmm-password \
  --query-source=pgstatmonitor \
  --service-name=postgresql-production \
  --host=localhost \
  --port=5432 \
  --database=postgres

# For PostgreSQL 10+ with pg_stat_monitor (better than pg_stat_statements)
sudo apt-get install -y percona-pg-stat-monitor14  # Adjust version
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;"

sudo pmm-admin add postgresql \
  --username=pmm \
  --password=pmm-password \
  --query-source=pgstatmonitor \
  --service-name=postgresql-production

Query Analytics Setup

PMM's Query Analytics (QAN) identifies slow and resource-intensive queries:

# Access QAN at: https://pmm-server/graph/d/pmm-qan/
# Filter queries by:
# - Database
# - Time range
# - Query fingerprint
# - Wait type

# For MySQL: Configure slow query log for better query capture
mysql -u root -p <<EOF
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- Log queries > 1 second
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL log_slow_admin_statements = ON;

-- Use Performance Schema for detailed metrics
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN (
  'events_statements_history_long',
  'events_statements_history'
);
EOF

# For PostgreSQL: Reset statistics to get fresh data
sudo -u postgres psql -c "SELECT pg_stat_statements_reset();"
sudo -u postgres psql -c "SELECT pg_stat_reset();"

# View top queries by total time in PostgreSQL directly
sudo -u postgres psql -c "
SELECT
  query,
  calls,
  total_exec_time::numeric(10,2) AS total_ms,
  mean_exec_time::numeric(10,2) AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;" postgres

Dashboards and Visualization

PMM includes pre-built Grafana dashboards. Key dashboards to monitor:

# PMM Home Dashboard: Overview of all monitored services

# MySQL dashboards:
# - MySQL Overview: QPS, connections, buffer pool, InnoDB metrics
# - MySQL InnoDB Details: Row operations, deadlocks, redo log
# - MySQL Query Response Time: P99, P95 latencies
# - MySQL Replication: Lag, position, slave status

# PostgreSQL dashboards:
# - PostgreSQL Overview: TPS, connections, cache hit ratio
# - PostgreSQL Vacuum: Autovacuum activity, table bloat
# - PostgreSQL Replication: WAL lag, slot status

# Custom dashboard for application-specific metrics
# Via PMM API - add custom Grafana dashboards:
curl -s -X POST \
  -H "Content-Type: application/json" \
  -u admin:password \
  https://pmm-server/api/dashboards/import \
  -d @custom-dashboard.json

# Export metrics for external tools
# PMM exposes Prometheus metrics at:
# https://pmm-server/prometheus/

Alerting Configuration

# PMM uses Alertmanager for alerting
# Access Alerting at: https://pmm-server/graph/alerting/

# Create alert rules via PMM UI or API
# Common alert templates:

# MySQL: High connections usage
# Condition: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8

# PostgreSQL: Low cache hit ratio
# Condition: pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read) < 0.95

# Configure notification channels
curl -s -X POST \
  -H "Content-Type: application/json" \
  -u admin:password \
  https://pmm-server/api/alertmanager/v2/alerts \
  -d '{
    "name": "slack-notifications",
    "type": "slack",
    "settings": {
      "url": "https://hooks.slack.com/services/YOUR/SLACK/WEBHOOK",
      "recipient": "#database-alerts"
    }
  }'

# Create an alert rule for MySQL slow queries
curl -X POST https://pmm-server/v1/alerting/rules \
  -H "Authorization: Basic $(echo -n admin:password | base64)" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "MySQL High Slow Query Rate",
    "expr": "rate(mysql_global_status_slow_queries[5m]) > 10",
    "duration": "5m",
    "severity": "warning",
    "labels": {
      "team": "dba"
    },
    "annotations": {
      "summary": "MySQL slow query rate is high",
      "description": "More than 10 slow queries per second on {{ $labels.service_name }}"
    }
  }'

Troubleshooting

PMM Client can't connect to server:

# Check network connectivity
curl -k https://pmm-server-ip:443/ping

# Verify PMM Client configuration
cat /usr/local/percona/pmm2/config/pmm-agent.yaml

# Re-configure PMM Client
sudo pmm-admin config \
  --server-insecure-tls \
  --server-url=https://admin:password@pmm-server-ip

# Check PMM Agent logs
sudo journalctl -u pmm-agent -n 50

No data in Query Analytics:

# For MySQL: Verify Performance Schema is enabled
mysql -u root -p -e "SHOW VARIABLES LIKE 'performance_schema';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log';"

# For PostgreSQL: Check pg_stat_statements is loaded
sudo -u postgres psql -c "SELECT * FROM pg_stat_statements LIMIT 1;"

# Verify PMM is collecting metrics
pmm-admin list
pmm-admin summary

PMM Server running out of disk:

# Check data retention settings (default 30 days)
docker exec -it pmm-server pmm-admin list

# Change retention in PMM settings
# Go to: PMM Settings > Advanced Settings > Data Retention

# Clean up old data manually
docker exec -it pmm-server curl -X POST http://localhost:7772/-/clean_tombstones

Conclusion

PMM provides comprehensive database performance monitoring that goes far beyond simple uptime checks, giving you query-level visibility into what's actually slowing your databases down. By combining Query Analytics, pre-built dashboards, and alerting, you can identify and resolve performance bottlenecks before they impact users. Deploy PMM Server once and add any number of MySQL, PostgreSQL, or MongoDB instances as your infrastructure grows.