Zuzia.app logo
Get started
Features
How It Works
Pricing
About
Faq
Sign up Log In
← All guides

MySQL & PostgreSQL Performance Monitoring: Key Queries

Updated:

MySQL & PostgreSQL Performance Monitoring: Key Queries

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

Manual checks work while you are looking. To keep running them without you, Zuzia uses its agent and scheduled tasks.

How it works in Zuzia

  • Install the Zuzia agent on the server with one command. CPU, RAM, disk, load and network metrics start reporting on their own, with alerts on a threshold, on drift from the server's normal, or on a disk fill-up forecast.
  • Save the commands from this guide as a scheduled task and choose how often it runs, from every minute to once a year. The agent runs it on every host you pick.
  • If a command fails, you get an alert right away by email, Slack, Discord, push (through your Pushover account) or SMS (through your Vonage account).
  • The output of every run is kept, so you can look back at what changed. On paid plans, AI can read the output and alert you only when it spots a problem.

Setting it up

  1. Add the server in the Zuzia dashboard and install the agent.
  2. Create a scheduled task with the check commands below, then choose the hosts and how often it runs.
  3. Choose your notification channels. Turn on maintenance mode during planned work so it doesn't trigger alerts.

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:

  1. Check Slow Queries:

    • Review slow query logs
    • Identify queries with longest execution times
    • Analyze query execution plans

  2. Monitor Connection Usage:

    • Check active connection count
    • Review connection pool utilization
    • Identify connection leaks

  3. 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:

  1. Optimize Slow Queries:

    • Add indexes to frequently queried columns
    • Rewrite inefficient queries
    • Use query caching where appropriate

  2. Optimize Connection Management:

    • Adjust connection pool size
    • Fix connection leaks in applications
    • Implement connection pooling best practices

  3. 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.

Related guides, recipes, and problems

  • Related guides

    • guides--server-performance-monitoring-best-practices.md
    • guides--monitoring-application-performance-linux.md
    • guides--server-metrics-monitoring-automated.md

  • Related recipes

    • recipes--monitor-mysql-slow-queries-performance.md
    • recipes--check-postgresql-active-connections.md
    • recipes--monitor-database-connection-pool-usage.md
    • recipes--monitor-mysql-database-size-growth.md
    • recipes--monitor-postgresql-database-size.md

  • Related problems

    • problems--database-connection-timeout-errors.md
    • problems--server-slow-performance-issues.md

We use cookies to ensure the proper functioning of our website.