Stats
Actions
Tags
From database-connection-pooler
Audits connections, calculates pool sizes, configures app-level pooling params, and deploys PgBouncer/ProxySQL for PostgreSQL/MySQL to prevent exhaustion and boost throughput.
How this skill is triggered — by the user, by Claude, or both
Slash command
/database-connection-pooler:optimizing-database-connection-poolingThis skill is limited to the following tools:
The summary Claude sees in its skill listing — used to decide when to auto-load this skill
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.
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.
npx claudepluginhub jeremylongshore/claude-code-plugins-plus-skills --plugin database-connection-poolerGuides configuration of database connection pools including sizing formulas, PgBouncer transaction-mode pooling, and diagnostics. Useful when connections are exhausted or latency spikes from new connections.
Configures connection pools like PgBouncer, pgcat, ProxySQL for PostgreSQL/MySQL, app-level pools (Prisma, SQLAlchemy, HikariCP), serverless proxies (RDS Proxy, Neon). Optimizes sizing, detects leaks.
Pool sizing, connection lifetime, health checks, and resource exhaustion prevention.