Database Connection Pooler

Overview

Configure and optimize database connection pooling using external poolers (PgBouncer, ProxySQL, Odyssey) and application-level pool settings to prevent connection exhaustion, reduce connection overhead, and improve database throughput.

Prerequisites

• psql or mysql CLI for querying connection metrics
• Access to database configuration files (postgresql.conf, my.cnf) for max_connections settings
• PgBouncer, ProxySQL, or Odyssey installed if using external pooling
• Application connection pool settings accessible (database URL, pool size parameters)
• Server CPU core count and available memory for pool sizing calculations

Instructions

1. Audit current connection usage by querying active connections:

• PostgreSQL: SELECT count(*) AS total, state, usename FROM pg_stat_activity GROUP BY state, usename ORDER BY total DESC
• MySQL: SHOW STATUS LIKE 'Threads_connected' and SHOW PROCESSLIST
• Compare against max_connections setting to determine headroom

1. Calculate the optimal pool size using the formula: pool_size = (core_count * 2) + effective_spindle_count. For SSD-backed databases, use core_count * 2 + 1. A 4-core server with SSD storage should have a pool size of approximately 9. This formula applies per application instance.

1. Configure application-level connection pool parameters:

• minimumIdle: Set to 2-5 for low-traffic periods (avoids cold-start latency)
• maximumPoolSize: Set using the formula from step 2
• connectionTimeout: 5-10 seconds (fail fast rather than queue indefinitely)
• idleTimeout: 10-30 minutes (release idle connections back to pool)
• maxLifetime: 30 minutes (prevent stale connections from accumulating)
• leakDetectionThreshold: 60 seconds (log warning for connections held too long)

1. For PostgreSQL with many application instances, deploy PgBouncer in transaction pooling mode:

• Set pool_mode = transaction to multiplex connections (one backend connection serves many clients between transactions)
• Set default_pool_size = 20 and max_client_conn = 1000
• Configure server_idle_timeout = 600 to close unused backend connections
• Set server_lifetime = 3600 to periodically refresh connections

1. For MySQL with many application instances, deploy ProxySQL:

• Configure connection multiplexing in mysql_servers table
• Set max_connections per backend server