Understanding and Configuring Database Connection Pools

For backend applications, interacting with a database inevitably involves a connection pool, which caches database connections to avoid the overhead of repeatedly opening and closing them.

The pool works like a bridge: each connection is a bridge to the database, and different SQL statements are the traffic that crosses it. Reusing a cached connection is safe because the connection itself does not retain business‑specific state.

Pool configuration parameters are generally the same across languages and libraries. The most common settings are:

max : maximum number of connections the pool can hold.

min : minimum number of connections kept alive.

idle : how long a connection may stay idle before being considered for removal.

evict : interval at which the pool checks for idle connections to evict.

acquire : maximum time a request will wait for a free connection before timing out.

When the database itself limits concurrent connections (e.g., a MySQL cloud instance shows 2000 connections), the pool's max should not exceed that limit; otherwise, excess connections will cause errors under high load.

You can query the database’s maximum allowed connections with:

show variable like 'max_connections';

and the highest number of connections actually used with:

show status like 'Max_used_connections';

If the observed usage approaches or exceeds the configured max, you may need to increase it, provided the database can support more connections.

Setting min to zero means the pool will not keep any idle connections, which is suitable for low‑frequency workloads. For high‑frequency services, a non‑zero min ensures a ready pool of connections.

The idle and evict settings work together: after a connection has been idle for the idle period, the pool’s eviction routine (running every evict interval) will decide whether to close it.

If min is non‑zero while idle and evict are also set, the pool may repeatedly create and destroy connections to maintain the minimum count, which can be wasteful.

The acquire timeout defines how long a request will wait for a free connection when the pool is exhausted; if the wait exceeds this limit, the request fails.

In summary, choose min based on request frequency, set max according to the database’s capacity and expected concurrency, and adjust idle, evict, and acquire to balance resource usage and responsiveness.