Comprehensive guide to monitoring MySQL and PostgreSQL database performance. Learn how to track query performance, connection pools, slow queries, and optimize database operations with automated monitoring.

Last updated: 2026-01-11

Database Performance Monitoring - MySQL and PostgreSQL Best Practices

Database performance monitoring is critical for maintaining optimal application performance and ensuring reliable data access. This comprehensive guide covers everything you need to know about monitoring MySQL and PostgreSQL database performance, including query analysis, connection management, and automated monitoring strategies.

For related database monitoring topics, see Server Performance Monitoring. For troubleshooting database issues, see Database Connection Timeout Errors.

Why Database Performance Monitoring Matters

Database performance directly impacts application responsiveness, user experience, and system scalability. Without proper monitoring, slow queries can cause application timeouts, connection pool exhaustion can lead to service failures, and unoptimized database operations can consume excessive server resources.

Effective database monitoring enables you to:

Identify slow queries and optimize them proactively
Monitor connection pool usage and prevent exhaustion
Track database resource consumption (CPU, memory, disk I/O)
Detect database performance degradation early
Plan capacity upgrades based on actual usage patterns
Ensure database availability and reliability

Key Database Metrics to Monitor

Query Performance Metrics

Slow Query Count: Number of queries exceeding threshold time
Query Execution Time: Average and maximum query execution times
Query Throughput: Queries per second processed by database
Lock Wait Time: Time queries wait for locks

Connection Metrics

Active Connections: Current number of database connections
Connection Pool Usage: Percentage of available connections used
Max Connections: Maximum allowed connections
Connection Errors: Failed connection attempts

Resource Metrics

Database Size: Total size of databases and tables
Table Size Growth: Rate of database growth over time
Cache Hit Ratio: Percentage of queries served from cache
Disk I/O: Read/write operations per second

Method 1: Monitor MySQL Performance

Check MySQL Slow Queries

# Enable slow query log
mysql -e "SET GLOBAL slow_query_log = 'ON';"
mysql -e "SET GLOBAL long_query_time = 2;"

# View slow query log
sudo tail -f /var/log/mysql/slow-query.log

# Check slow queries from MySQL
mysql -e "SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;"

Monitor MySQL Connections

# Check current connections
mysql -e "SHOW PROCESSLIST;"

# Count active connections
mysql -e "SHOW STATUS LIKE 'Threads_connected';"

# Check max connections
mysql -e "SHOW VARIABLES LIKE 'max_connections';"

Monitor MySQL Performance Metrics

# Check MySQL status
mysql -e "SHOW STATUS;"

# Check key performance indicators
mysql -e "SHOW STATUS LIKE 'Questions';"
mysql -e "SHOW STATUS LIKE 'Slow_queries';"
mysql -e "SHOW STATUS LIKE 'Connections';"

Method 2: Monitor PostgreSQL Performance

Check PostgreSQL Slow Queries

# Enable slow query logging in postgresql.conf
# log_min_duration_statement = 1000  # Log queries > 1 second

# View slow queries from pg_stat_statements
psql -U postgres -c "SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;"

Monitor PostgreSQL Connections

# Check active connections
psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

# View connection details
psql -U postgres -c "SELECT pid, usename, application_name, state, query FROM pg_stat_activity;"

# Check max connections
psql -U postgres -c "SHOW max_connections;"

Monitor PostgreSQL Database Size

# Check database sizes
psql -U postgres -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database;"

# Check table sizes
psql -U postgres -c "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;"

Method 3: Automated Database Monitoring with Zuzia.app

While manual database checks work for troubleshooting, production databases require automated monitoring that continuously tracks performance metrics, stores historical data, and alerts you when issues are detected.

How Zuzia.app Database Monitoring Works

Zuzia.app automatically monitors database performance through scheduled command execution. The platform:

Executes database monitoring commands every few minutes automatically
Stores all database metrics historically in the database
Sends alerts when performance metrics exceed thresholds
Tracks database performance trends over time
Provides AI-powered analysis (full package) to detect unusual patterns
Monitors databases across multiple servers simultaneously

Setting Up Database Monitoring in Zuzia.app

Add Database Monitoring Commands
- Create scheduled tasks for slow query checks
- Add commands to monitor connection counts
- Set up database size monitoring
- Configure query performance tracking
Configure Alert Thresholds
- Set warning threshold for slow queries (e.g., > 10 slow queries/hour)
- Set critical threshold for connection usage (e.g., > 80% of max connections)
- Configure alerts for database size growth
- Set up alerts for query performance degradation
Choose Notification Channels
- Select email notifications for critical alerts
- Configure webhook notifications for integration
- Set up Slack or Discord notifications
Automatic Monitoring Begins
- System automatically executes monitoring commands
- Historical data collection begins immediately
- You'll receive alerts when thresholds are exceeded

Best Practices for Database Performance Monitoring

1. Monitor Query Performance Continuously

Track slow queries daily
Analyze query execution times weekly
Optimize queries that consistently perform poorly
Use query analysis tools to identify bottlenecks

2. Monitor Connection Pool Usage

Track active connections vs max connections
Alert when connection usage exceeds 70-80%
Review connection patterns to optimize pool size
Monitor connection errors and failures

3. Track Database Growth

Monitor database and table sizes regularly
Identify tables with rapid growth
Plan capacity upgrades based on growth trends
Implement data archiving strategies if needed

4. Monitor Database Resource Usage

Track database CPU and memory usage
Monitor disk I/O for database operations
Correlate database performance with server resources
Optimize database configuration based on resource usage

5. Set Up Automated Alerts

Configure alerts for slow query thresholds
Set up alerts for connection pool exhaustion
Monitor database availability and response times
Alert on database errors and failures

Troubleshooting Database Performance Issues

Step 1: Identify Performance Bottlenecks

When database performance degrades:

Check Slow Queries:
- Review slow query logs
- Identify queries with longest execution times
- Analyze query execution plans
Monitor Connection Usage:
- Check active connection count
- Review connection pool utilization
- Identify connection leaks
Review Resource Usage:
- Check database CPU and memory usage
- Monitor disk I/O for database operations
- Review server resource availability

Step 2: Optimize Database Performance

Based on investigation:

Optimize Slow Queries:
- Add indexes to frequently queried columns
- Rewrite inefficient queries
- Use query caching where appropriate
Optimize Connection Management:
- Adjust connection pool size
- Fix connection leaks in applications
- Implement connection pooling best practices
Optimize Database Configuration:
- Tune database parameters based on workload
- Optimize buffer pool sizes
- Configure appropriate cache settings

FAQ: Common Questions About Database Performance Monitoring

How often should I check database performance?

For production databases, continuous automated monitoring is essential. Zuzia.app can execute database monitoring commands every few minutes, storing historical data and alerting you when performance issues are detected.

What is considered a slow query?

Slow queries depend on your application requirements. Generally, queries taking longer than 1-2 seconds are considered slow and should be investigated. Set thresholds based on your application's performance requirements.

How do I identify which queries are slow?

Use database slow query logs or performance monitoring tools. MySQL provides slow query log, while PostgreSQL offers pg_stat_statements extension. Zuzia.app can execute queries to retrieve slow query information automatically.

What should I do when connection pool is exhausted?

When connection pool is exhausted, investigate connection leaks in applications, optimize connection usage, increase max connections if needed, or scale database infrastructure. Monitor connection patterns to identify root cause.

How can I monitor database performance across multiple servers?

Zuzia.app allows you to monitor database performance across multiple servers from one centralized dashboard. Each server executes database monitoring commands independently, and all results are stored for centralized analysis.

Database Performance Monitoring - MySQL and PostgreSQL Best Practices