Audits connections, calculates pool sizes, configures app-level pooling params, and deploys PgBouncer/ProxySQL for PostgreSQL/MySQL to prevent exhaustion and boost throughput.
From database-connection-poolernpx claudepluginhub nickloveinvesting/nick-love-plugins --plugin database-connection-poolerThis skill is limited to using the following tools:
assets/README.mdreferences/README.mdscripts/README.mdGuides Payload CMS config (payload.config.ts), collections, fields, hooks, access control, APIs. Debugs validation errors, security, relationships, queries, transactions, hook behavior.
Designs, audits, and improves analytics tracking systems using Signal Quality Index for reliable, decision-ready data in marketing, product, and growth.
Enforces A/B test setup with gates for hypothesis locking, metrics definition, sample size calculation, assumptions checks, and execution readiness before implementation.
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.
psql or mysql CLI for querying connection metricspostgresql.conf, my.cnf) for max_connections settingsAudit current connection usage by querying active connections:
SELECT count(*) AS total, state, usename FROM pg_stat_activity GROUP BY state, usename ORDER BY total DESCSHOW STATUS LIKE 'Threads_connected' and SHOW PROCESSLISTmax_connections setting to determine headroomCalculate 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.
Configure application-level connection pool parameters:
For PostgreSQL with many application instances, deploy PgBouncer in transaction pooling mode:
pool_mode = transaction to multiplex connections (one backend connection serves many clients between transactions)default_pool_size = 20 and max_client_conn = 1000server_idle_timeout = 600 to close unused backend connectionsserver_lifetime = 3600 to periodically refresh connectionsFor MySQL with many application instances, deploy ProxySQL:
mysql_servers tablemax_connections per backend serverfree_connections_pct = 10Set max_connections in the database server based on available memory. Each PostgreSQL connection uses approximately 5-10MB of memory. For a server with 8GB RAM: max_connections = (8192MB - 2048MB_for_OS - 2048MB_shared_buffers) / 10MB = ~400. For MySQL, each thread uses approximately 1-4MB.
Implement connection health checks. Configure the pool to validate connections before lending (testOnBorrow or validation-query). Use a lightweight query: SELECT 1 for MySQL or a simple query for PostgreSQL. Set validation interval to avoid excessive overhead.
Monitor connection pool metrics continuously:
Handle connection storms (sudden spike in connection requests) by configuring a connection request queue with a bounded wait time, implementing retry with exponential backoff in the application, and pre-warming the pool during application startup.
Document the connection architecture: application pool size per instance, number of application instances, PgBouncer/ProxySQL settings, database max_connections, and the maximum theoretical connections formula (instances * pool_size_per_instance).
| Error | Cause | Solution |
|---|---|---|
FATAL: too many connections for role | Application pool size exceeds max_connections or connection leak | Reduce pool size; fix connection leaks (enable leak detection); add PgBouncer for connection multiplexing |
| Connection timeout after 5 seconds | Pool exhausted, all connections in use | Increase pool size cautiously; check for long-running transactions holding connections; add connection queue with backpressure |
connection reset by peer errors | Server-side idle timeout killed the connection | Set pool maxLifetime shorter than server idle_in_transaction_session_timeout; enable connection validation |
PgBouncer no more connections allowed | max_client_conn exceeded | Increase max_client_conn; or reduce client connection demand; check for connection leaks in application |
| High connection churn (create/destroy rate) | Pool too small for workload or maxLifetime too short | Increase pool size; extend maxLifetime to 30 minutes; ensure minimumIdle is set to avoid constant pool resizing |
Right-sizing a pool for a Spring Boot microservice: 4-core server, SSD storage, 3 microservice instances. Optimal pool per instance: (4 * 2) + 1 = 9. Total connections: 9 * 3 = 27. Database max_connections = 100 with comfortable headroom. Application startup pre-warms 5 connections per instance. Connection leak detection set to 60 seconds catches a missing connection.close() in an error handler.
PgBouncer deployment for a serverless application: Lambda functions create a new database connection per invocation, overwhelming PostgreSQL with 500+ connections. PgBouncer deployed between Lambda and PostgreSQL with pool_mode = transaction, default_pool_size = 25, max_client_conn = 5000. Lambda connects to PgBouncer; PgBouncer multiplexes to 25 backend connections. Connection errors eliminated; database CPU reduced from 95% to 30%.
ProxySQL read/write splitting: A MySQL application sends 80% reads and 20% writes. ProxySQL routes writes to the primary and distributes reads across 2 replicas. Connection pooling reduces backend connections from 300 (direct) to 60 (pooled). Average query latency drops from 8ms to 3ms due to reduced connection overhead.