Saltar al contenido principal

Percona Monitoring and Management (PMM) in FILESS

Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL, MariaDB, PostgreSQL, and MongoDB performance. On Filess.io Dedicated Runtime, PMM is pre-configured and ready to use on every database instance.

🎯 What is PMM?​

PMM provides enterprise-grade database monitoring capabilities:

  • Query Analytics: Identify slow queries and optimization opportunities
  • Performance Metrics: Real-time monitoring of connections, queries, replication, and resource usage
  • Database Advisors: Automated recommendations for query optimization and index creation
  • Historical Data: Long-term performance trends for capacity planning
  • Security Monitoring: Track authentication failures and suspicious activity

🚀 Accessing PMM in FILESS​

PMM is automatically deployed with every Dedicated Runtime database. To access it:

  1. Navigate to your database instance in the Filess.io console
  2. Go to the Monitoring section
  3. Click on Percona Monitoring and Management or PMM Dashboard

The PMM dashboard opens in a new tab, showing real-time metrics and performance data.

📊 Key PMM Features​

Query Analytics​

Query Analytics helps you identify performance bottlenecks:

Access Query Analytics:

  1. Open PMM dashboard
  2. Navigate to Query Analytics in the left menu
  3. View top queries by execution time, frequency, or load

What to Look For:

  • Queries with high execution time
  • Queries executed frequently
  • Queries with high load (total time spent)
  • Queries that could benefit from indexes

Example Analysis:

Top Slow Queries:
1. SELECT * FROM customers WHERE email = ? 
   - Avg time: 2.3s
   - Executions: 1,234/hour
   - Recommendation: Add index on email column

Performance Metrics​

Monitor critical database metrics in real-time:

Key Metrics to Monitor:

  • Connections: Active connections, max connections, connection errors
  • Query Performance: Queries per second, slow queries, query response times
  • Resource Usage: CPU, memory, disk I/O, network I/O
  • InnoDB Metrics: Buffer pool usage, read/write operations, lock waits
  • Replication: Replication lag, replication status, binlog position

Access Performance Metrics:

  1. Open PMM dashboard
  2. Navigate to MySQL Overview or MariaDB Overview
  3. View real-time graphs and metrics

Database Advisors​

Get automated recommendations for database optimization:

Types of Recommendations:

  • Query Optimization: Queries that could benefit from indexes
  • Index Analysis: Missing or unused indexes
  • Configuration Tuning: Suggestions for my.cnf optimizations
  • Table Optimization: Recommendations for table maintenance

Access Advisors:

  1. Open PMM dashboard
  2. Navigate to Advisors in the left menu
  3. Review recommendations and apply suggested changes

Example Recommendation:

Recommendation: Add Index
Table: customers
Column: email
Impact: High
Estimated improvement: 80% faster queries
SQL: CREATE INDEX idx_email ON customers(email);

🔍 Using Query Analytics​

Finding Slow Queries​

  1. Open Query Analytics in PMM
  2. Sort by Time (descending) to see slowest queries
  3. Click on a query to see:
    • Execution plan
    • Sample queries
    • Performance trends over time
    • Recommendations

Analyzing Query Patterns​

Query Analytics groups similar queries together:

-- These queries are grouped together:
SELECT * FROM customers WHERE id = 1;
SELECT * FROM customers WHERE id = 2;
SELECT * FROM customers WHERE id = 3;

-- Shown as:
SELECT * FROM customers WHERE id = ?

This helps identify queries that are executed frequently with different parameters.

Optimizing Based on Query Analytics​

Example Workflow:

  1. Identify Problem Query:

    Query: SELECT * FROM orders WHERE customer_id = ? AND status = 'pending'
    Avg Time: 1.5s
    Executions: 500/hour

  2. Review Execution Plan:

    • Check if indexes are being used
    • Identify full table scans
    • Look for missing indexes

  3. Apply Optimization:

    -- PMM suggests:
    CREATE INDEX idx_customer_status ON orders(customer_id, status);

  4. Verify Improvement:

    • Monitor query performance after index creation
    • Check Query Analytics for updated metrics

📈 Monitoring Performance Metrics​

Connection Metrics​

Monitor database connections:

Key Metrics:

  • Threads_connected: Current active connections
  • Threads_running: Currently executing queries
  • Max_used_connections: Peak connections used
  • Connection_errors: Failed connection attempts

Alert Thresholds:

  • Alert when connections > 80% of max_connections
  • Alert on connection errors > 10/minute

Query Performance Metrics​

Track query execution:

Key Metrics:

  • Questions: Total queries executed
  • Slow_queries: Queries exceeding long_query_time
  • Queries: Total queries (includes stored procedures)
  • Com_select, Com_insert, Com_update, Com_delete: Query type counts

Alert Thresholds:

  • Alert when slow query rate > 10/minute
  • Alert when query rate drops significantly (possible issue)

Resource Usage​

Monitor server resources:

CPU Metrics:

  • CPU usage percentage
  • CPU wait time
  • CPU steal time (in virtualized environments)

Memory Metrics:

  • Buffer pool usage
  • Key buffer usage
  • Query cache usage
  • Temporary table usage

Disk I/O Metrics:

  • Read/write operations per second
  • Disk latency
  • Disk queue depth

Alert Thresholds:

  • Alert when CPU > 80% for 5 minutes
  • Alert when buffer pool hit rate < 95%
  • Alert when disk I/O wait > 50ms

Replication Metrics​

Monitor replication status:

Key Metrics:

  • Seconds_Behind_Master: Replication lag in seconds
  • Slave_IO_Running: IO thread status
  • Slave_SQL_Running: SQL thread status
  • Relay_Log_Pos: Current position in relay log

Alert Thresholds:

  • Alert when replication lag > 30 seconds
  • Alert when replication threads stop

🔔 Setting Up Alerts​

PMM includes built-in alerting capabilities:

Pre-configured Alerts​

PMM comes with sensible defaults:

  • High connection count
  • Slow query rate
  • Replication lag
  • Disk space usage
  • Memory pressure

Custom Alerts​

Create custom alerts in PMM:

  1. Navigate to Alerting in PMM
  2. Click New Alert Rule
  3. Define:
    • Alert name
    • Condition (PromQL query)
    • Threshold
    • Duration
    • Notification channels

Example Custom Alert:

Alert: High Query Rate
Condition: rate(mysql_global_status_questions[5m]) > 1000
Threshold: 1000 queries/second
Duration: 5 minutes
Action: Send notification to Slack

Notification Channels​

Configure notifications:

  • Email: Send alerts to email addresses
  • Slack: Integrate with Slack channels
  • PagerDuty: Integrate with PagerDuty for on-call
  • Webhook: Send to custom webhook endpoints

📊 Using Pre-built Dashboards​

PMM includes pre-configured dashboards:

MySQL/MariaDB Overview​

Main dashboard showing:

  • Connection metrics
  • Query performance
  • Resource usage
  • Replication status

InnoDB Metrics​

Detailed InnoDB metrics:

  • Buffer pool usage
  • Read/write operations
  • Lock waits
  • Transaction metrics

System Metrics​

Server-level metrics:

  • CPU usage
  • Memory usage
  • Disk I/O
  • Network I/O

Custom Dashboards​

Create custom dashboards:

  1. Navigate to Dashboards in PMM
  2. Click New Dashboard
  3. Add panels with custom queries
  4. Save and share with your team

🎓 Best Practices​

Regular Monitoring​

  • Daily: Check Query Analytics for new slow queries
  • Weekly: Review Database Advisors for optimization opportunities
  • Monthly: Analyze performance trends and plan capacity

Alert Configuration​

  • Avoid Alert Fatigue: Set meaningful thresholds
  • Use Escalation: Different severity levels for different issues
  • Test Alerts: Verify alerts work correctly
  • Document Alerts: Keep documentation of what each alert means

Query Optimization​

  • Act on Recommendations: Review and apply PMM advisor suggestions
  • Monitor Impact: Verify improvements after applying optimizations
  • Track Trends: Watch for queries that gradually slow down

Capacity Planning​

  • Historical Data: Use PMM's historical data for capacity planning
  • Growth Trends: Identify metrics that are growing over time
  • Resource Limits: Monitor when you're approaching resource limits

🔧 Troubleshooting​

PMM Not Showing Data​

Check:

  1. PMM client agent is running on database node
  2. Database is accessible from PMM server
  3. PMM user has correct permissions

Verify PMM Agent:

# Check if PMM agent is running
systemctl status pmm-agent

# Check PMM agent logs
journalctl -u pmm-agent -f

Missing Metrics​

Common Issues:

  • Performance Schema not enabled
  • Insufficient permissions for PMM user
  • Network connectivity issues

Enable Performance Schema:

-- Check if enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Enable if needed (usually enabled by default)
SET GLOBAL performance_schema = ON;

High PMM Resource Usage​

PMM is designed to be lightweight, but if you notice high resource usage:

  • Reduce Retention: Lower metric retention period
  • Disable Unused Collectors: Disable collectors you don't need
  • Scale PMM Server: Increase PMM server resources if needed

✅ Checklist​

  • PMM dashboard accessible from Filess.io console
  • Query Analytics showing data
  • Performance metrics visible
  • Database Advisors providing recommendations
  • Alerts configured and tested
  • Team trained on using PMM
  • Regular review schedule established

🔮 Next Steps​

With PMM in place, you can:

  1. Optimize Queries: Use Query Analytics to find and fix slow queries
  2. Plan Capacity: Use historical data to plan for growth
  3. Automate Monitoring: Set up alerts for proactive issue detection
  4. Share Insights: Use dashboards to communicate database health

PMM is your window into database performance. Use it to maintain optimal performance and catch issues before they impact users.