How to Size a MySQL Connection Pool

Q: My pool size is 10 but under load, queries are still slow. Should I raise it?

Not necessarily. Check hikaricp_connections_pending — if it is 0, all threads are getting connections immediately and the slowness is in the queries themselves. Only raise pool size if threads are waiting for connections (pending > 0) and Threads_running on MySQL is below 2× core count.

Q: How do I size the pool for a background worker with unpredictable batch sizes?

Use pool_size = 2–4 for steady-state work and max_overflow = 8–10 for burst. Keep pool_timeout short (3–5 s) and implement backpressure in the worker: if it cannot get a connection within the timeout, pause and retry.

Q: Is it safe to share one pool across multiple databases?

No — each pool is bound to one (host, port, database, user) tuple. Use separate pools for separate databases. Some pool implementations allow routing different queries to different pools, but that is application-level logic, not a pool feature.

Q: After a MySQL restart, my application gets connection errors. How do I recover?

Set pool_pre_ping=True / keepalive-time so the pool detects dead connections. Set max-lifetime shorter than MySQL's wait_timeout so connections are recycled before they go stale. Most pools have automatic reconnection logic — verify your pool version supports it.

A MySQL connection pool maintains a set of pre-opened database connections that application threads reuse rather than open and close for every query. Pool sizing is one of the most impactful and least understood configuration parameters in MySQL-backed applications — both too small and too large cause serious performance problems.

Impact

An undersized pool starves application threads of connections, causing request queuing and latency spikes under load. An oversized pool sends so many concurrent queries to MySQL that the server's thread scheduler becomes a bottleneck, locking and context switching overhead rise, and throughput actually decreases — the database slows down despite (or because of) more connections. Getting this right is worth more than most query optimizations on write-heavy systems.

The Right Pool Size Is Smaller Than You Think

The correct pool size for a single application instance is bounded by the number of CPU cores on the MySQL server, not by the number of application threads. MySQL executes one query per connection per CPU core efficiently. When active threads exceed cores × 2, context switching and mutex contention dominate.

The formula from HikariCP's documentation:

pool_size = (core_count * 2) + effective_spindle_count

For a 4-core SSD MySQL server: (4 × 2) + 1 = 9 connections per application instance. This seems small — and it should be. If you have 20 application instances, that's 180 connections total, which is fine for a MySQL server with 500 max_connections.

Common Causes of Pool Misconfiguration

Setting pool size to match application thread count rather than database concurrency capacity
Ignoring the number of application instances — pool size × instances determines total MySQL connections
Not accounting for non-pool connections (monitoring tools, migration scripts, cron jobs, replication)
Setting minimumIdle / minIdle equal to maximumPoolSize, pre-opening all connections regardless of load
No connection validation — stale connections fail on first use after the server times them out
Pool timeout too high — threads wait too long before failing fast under pool exhaustion
maxLifetime too high — connections accumulate MySQL-side metadata and may degrade over time
Not recycling connections after schema changes that affect session state

Configuration

HikariCP (Java / Spring Boot)

# application.properties
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.idle-timeout=600000        # ms — remove idle connections after 10 min
spring.datasource.hikari.connection-timeout=3000    # ms — fail fast if pool is full
spring.datasource.hikari.max-lifetime=1800000       # ms — recycle all connections every 30 min
spring.datasource.hikari.keepalive-time=60000       # ms — ping idle connections to prevent timeout
spring.datasource.hikari.leak-detection-threshold=5000  # ms — warn if connection held > 5 s
spring.datasource.hikari.connection-test-query=SELECT 1   # fallback for non-JDBC4 drivers

Key settings explained:

maximum-pool-size: total connections this instance will ever open. The primary sizing knob.
minimum-idle: connections to keep open when idle. Setting this lower than maximum-pool-size saves server connections during off-peak, at the cost of connection creation latency on traffic ramp-up.
connection-timeout: how long a thread waits for a connection from the pool before failing. 3 seconds is a reasonable upper bound for OLTP — if a connection is not available after 3 s, fail fast so the caller can retry or circuit-break.
max-lifetime: forcibly recycles connections, preventing very long-lived connections from accumulating problematic server-side state. Always set this shorter than MySQL's wait_timeout.
keepalive-time: sends SELECT 1 on idle connections to prevent server-side timeout. Set this shorter than wait_timeout.

SQLAlchemy (Python)

from sqlalchemy import create_engine

engine = create_engine(
    DATABASE_URL,
    pool_size=10,                # permanent connections kept in pool
    max_overflow=5,              # extra connections allowed above pool_size under burst
    pool_timeout=3,              # seconds to wait for a connection before raising
    pool_recycle=1800,           # recycle connections older than 30 min
    pool_pre_ping=True,          # validate connection before checkout (handles wait_timeout)
)

pool_size: equivalent to maximum-pool-size. Total steady-state connections.
max_overflow: connections above pool_size allowed under burst. These are opened on demand and closed when returned to the pool, rather than kept warm. Total max = pool_size + max_overflow.
pool_pre_ping: issues SELECT 1 before handing out each connection. Adds one RTT per checkout but eliminates "server has gone away" errors from stale connections. Prefer this over pool_recycle alone.

Node.js (mysql2 / knex)

const pool = mysql.createPool({
  host: 'localhost',
  user: 'app',
  password: 'secret',
  database: 'mydb',
  connectionLimit: 10,    // maximum simultaneous connections
  queueLimit: 50,         // max queued requests before returning error (0 = unlimited)
  waitForConnections: true,
  connectTimeout: 3000,   // ms
  idleTimeout: 60000,     // ms
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000,
});

Calculating Total Connection Budget

Inventory all connection sources against your MySQL server:

Total connections = Σ (instances × pool_max_size)
                  + replication threads (1–2 per replica)
                  + monitoring agents (mysqld_exporter, pt-heartbeat, etc.)
                  + migration tools (Flyway, Liquibase connections)
                  + DBA / on-call admin connections (2–5)
                  + buffer (10–15%)

Verify against max_connections:
```
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```
Keep total connections well below max_connections. The gap is your safety margin for traffic spikes and deployment rollouts (which briefly double the instance count).

Detecting Pool Problems

HikariCP metrics (exposed via Micrometer / Prometheus):

hikaricp_connections_pending    # threads waiting for a connection (should be 0 normally)
hikaricp_connections_active     # connections currently checked out
hikaricp_connections_idle       # connections available in pool
hikaricp_connections_timeout_total  # count of connection-timeout exceptions
hikaricp_pool_wait_seconds_max  # max wait time observed

Alert when hikaricp_connections_pending > 0 for more than a few seconds — that means threads are queuing for connections.

Detect connection leaks with leak-detection-threshold:
```
spring.datasource.hikari.leak-detection-threshold=5000
```
HikariCP logs a stack trace for any connection held longer than 5 seconds, showing exactly which code path is failing to return the connection. Indispensable for finding ORM or code-level leaks.
Check pool saturation from the MySQL side:
```
SELECT user, command, COUNT(*) AS cnt
FROM information_schema.PROCESSLIST
GROUP BY user, command
ORDER BY cnt DESC;
```
A large number of Sleep connections from one user indicates the pool has more idle connections than it needs (minimum-idle too high). A large number of active connections indicates either pool size is too small or queries are running too long.

Using ProxySQL for Connection Multiplexing

When the number of application instances makes it impossible to keep pool sizes small enough:

# /etc/proxysql.cnf (simplified)
mysql_variables=
{
    threads=4
    max_connections=2048       # ProxySQL accepts up to 2048 frontend connections
    default_query_timeout=5000
}

mysql_servers=
(
    {
        address="mysql-primary"
        port=3306
        max_connections=50     # ProxySQL opens at most 50 backend connections
    }
)

ProxySQL multiplexes thousands of application-side connections onto tens of backend connections. The application pool size can be generous (even 100+ per instance) because ProxySQL only opens as many backend connections as there are concurrent active queries.

Additional Information

Connection creation overhead (TCP handshake + MySQL authentication) is 1–5 ms. For 100 req/s with 0% connection reuse, that's up to 500 ms of overhead per second on connection creation alone — which is why pools exist.
minimum-idle = 0 in HikariCP means all connections are closed during idle periods. Applications that are invoked infrequently (Lambda-style workers, batch jobs) should use 0 to avoid keeping unused connections open. Always-on web services should use a small positive value (2–5) to eliminate connection creation latency on the first requests after a quiet period.
AWS RDS Proxy and PlanetScale provide managed connection pooling with additional features (IAM authentication, failover awareness) suitable for serverless and high-scale deployments.

Frequently Asked Questions

Q: My pool size is 10 but under load, queries are still slow. Should I raise it? A: Not necessarily. Check hikaricp_connections_pending — if it is 0, all threads are getting connections immediately and the slowness is in the queries themselves. Only raise pool size if threads are waiting for connections (pending > 0) and Threads_running on MySQL is below 2× core count.

Q: How do I size the pool for a background worker with unpredictable batch sizes? A: Use pool_size = 2–4 for steady-state work and max_overflow = 8–10 for burst. Keep pool_timeout short (3–5 s) and implement backpressure in the worker: if it cannot get a connection within the timeout, pause and retry.

Q: Should minimum-idle equal maximum-pool-size for lowest latency? A: Only on high-traffic services where pool utilization rarely drops below 80%. For most services, minimum-idle = pool_size / 4 (floored at 2) balances latency and server-side connection count. Keeping 100 idle connections open when traffic is zero wastes MySQL's max_connections budget.

Q: Is it safe to share one pool across multiple databases? A: No — each pool is bound to one (host, port, database, user) tuple. Use separate pools for separate databases. Some pool implementations allow routing different queries to different pools, but that is application-level logic, not a pool feature.

Q: After a MySQL restart, my application gets connection errors. How do I recover? A: Set pool_pre_ping=True / keepalive-time so the pool detects dead connections. Set max-lifetime shorter than MySQL's wait_timeout so connections are recycled before they go stale. Most pools have automatic reconnection logic — verify your pool version supports it.