Monitor Database Connection Pool Usage (MySQL, PostgreSQL)

Monitor Database Connection Pool Usage (MySQL, PostgreSQL)

Monitor database connection pool usage to track connection count, detect pool exhaustion, and optimize connection management. This guide shows you how to monitor connection pools and set up automated monitoring.

For comprehensive database monitoring, see Database Performance Monitoring. For troubleshooting connection issues, see Database Connection Timeout Errors.

Why Monitoring Connection Pools Matters

Connection pool exhaustion can cause application failures and poor performance. Monitoring connection pool usage helps prevent pool exhaustion and ensures optimal database performance.

Method 1: Monitor MySQL Connection Pool

Check Connection Count

# Count active connections
mysql -u root -p -e "SELECT count(*) FROM information_schema.processlist;"

# Check current vs max connections
mysql -u root -p -e "SELECT 
  (SELECT count(*) FROM information_schema.processlist) as current_connections,
  (SELECT @@max_connections) as max_connections,
  ROUND((SELECT count(*) FROM information_schema.processlist) / (SELECT @@max_connections) * 100, 2) as usage_percent;"

# View connection details
mysql -u root -p -e "SELECT id, user, host, db, command, time, state FROM information_schema.processlist;"

Method 2: Monitor PostgreSQL Connection Pool

Check Connection Count

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

# Check current vs max connections
psql -U postgres -c "SELECT 
  count(*) as current_connections,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') as max_connections,
  ROUND(count(*)::float / (SELECT setting::int FROM pg_settings WHERE name = 'max_connections')::float * 100, 2) as usage_percent
FROM pg_stat_activity;"

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

Method 3: Automated Connection Pool 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 Connection Pool Monitoring

1. Monitor Pool Usage Continuously

• Track connection count regularly
• Alert when pool usage exceeds thresholds
• Monitor connection trends over time
• Plan capacity upgrades based on data

2. Monitor Connection States

• Track active vs idle connections
• Identify connection leaks
• Monitor long-running connections
• Optimize connection usage

3. Set Appropriate Pool Limits

• Configure max_connections based on server resources
• Monitor connection usage vs limits
• Adjust limits based on actual usage
• Plan upgrades before limits are reached

4. Optimize Connection Usage

• Implement connection pooling
• Close connections properly
• Monitor connection leaks
• Optimize application connection usage

Troubleshooting Connection Pool Issues

Step 1: Identify Pool Problems

When connection pool is exhausted:

# Check current connection count
mysql -u root -p -e "SELECT count(*) FROM information_schema.processlist;"
psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"

# Check max connections
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"
psql -U postgres -c "SHOW max_connections;"

Step 2: Resolve Pool Issues

Based on investigation:

1. Kill Idle Connections:

   # MySQL: Kill idle connections
   mysql -u root -p -e "KILL <process_id>;"
   
   # PostgreSQL: Kill idle connections
   psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle';"
   

2. Increase Pool Limits:

   # MySQL: Increase max connections
   mysql -u root -p -e "SET GLOBAL max_connections = 500;"
   
   # PostgreSQL: Edit postgresql.conf
   # max_connections = 500
   

3. Fix Connection Leaks:

   • Review application code
   • Fix unclosed connections
   • Implement connection pooling

FAQ: Common Questions About Connection Pool Monitoring

How often should I check connection pool usage?

For production databases, continuous automated monitoring is essential. Zuzia.app can check connection pool usage every few minutes, alerting you when pool usage exceeds thresholds.

What is considered high connection pool usage?

High connection pool usage depends on your max_connections setting. Generally, usage above 70-80% of max_connections indicates potential issues and should be investigated.

How do I prevent connection pool exhaustion?

Prevent connection pool exhaustion by monitoring pool usage continuously, implementing connection pooling, fixing connection leaks, optimizing connection usage, and scaling database infrastructure if needed.

Can connection pool monitoring impact database performance?

Connection pool monitoring commands have minimal impact on database performance when done correctly. Use appropriate monitoring frequency and avoid monitoring during peak database usage periods.

Related guides, recipes, and problems

• Related guides

  • guides--monitoring-database-performance-mysql-postgresql.md
  • guides--server-performance-monitoring-best-practices.md

• Related recipes

  • recipes--check-postgresql-active-connections.md
  • recipes--monitor-mysql-slow-queries-performance.md

• Related problems

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