Database pool size too small? Here's what to do

1. Pool size is too small (the most common cause)

This is the one I see most often. Your app has a connection pool set to, say, 10 connections. But your app has 50 users hitting it at once. Those 50 users all try to grab a connection, 10 get one, the other 40 sit waiting. After a few seconds, they time out. You get errors like ORA-12519 or generic 'connection timeout'.

The fix: Increase the pool size. But don't just double it blindly. Look at your app's peak usage. If you normally have 100 concurrent requests and each takes 200ms, you need at least 100 connections in the pool. Add a buffer of 20%.

In Oracle, you'd check the current limit with:

SHOW PARAMETER processes;

Then set it higher:

ALTER SYSTEM SET processes=200 SCOPE=SPFILE;

For MySQL, check and change it with:

SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 200;

But here's the thing: don't go crazy. Too many connections can kill the database server. I had a client last month who set pool size to 1000 on a box with 4GB RAM. The DB crashed within 10 minutes. Start with a reasonable number, maybe 150, and monitor.

Also check the app side. In Java (HikariCP), you set pool size like this:

spring.datasource.hikari.maximum-pool-size=50

In Python (SQLAlchemy), it's:

create_engine('...', pool_size=20, max_overflow=10)

If you're using a framework like Django, it's in the database settings:

'OPTIONS': {
    'pool_size': 20,
    'max_overflow': 10
}

After you change it, restart the app. Then check if the timeouts stop.

2. Connection leak (connections not returned to pool)

Sometimes the pool size is fine, but your code isn't returning connections. Each time a request comes in, it grabs a connection but doesn't close it. Over time, the pool empties. You get timeouts even with moderate traffic.

How to spot it: Monitor the pool. If the number of active connections keeps going up and never comes down, you have a leak. On Oracle, query:

SELECT COUNT(*) FROM v$session WHERE status='ACTIVE';

On MySQL:

SHOW PROCESSLIST;

If you see a bunch of connections from your app that are idle but still 'ACTIVE', that's your leak.

The fix: Check your code. Make sure every open() has a matching close(). In Python, use a context manager:

with connection.cursor() as cursor:
    cursor.execute('SELECT * FROM users')

In Java, use try-with-resources:

try (Connection conn = dataSource.getConnection()) {
    // do work
}

Also, set a timeout on idle connections. In MySQL, you can set wait_timeout to kill connections that sit too long:

SET GLOBAL wait_timeout=300;

In Oracle, set IDLE_TIME in the profile:

ALTER PROFILE default LIMIT IDLE_TIME 30;

This won't fix the leak, but it will stop the pool from filling up with dead connections. You still need to fix the code.

I had a client last month who had a leak in a loop that opened connections but only closed them if no error occurred. One typo caused the pool to drain in 5 minutes. We found it by adding logging around each open/close. Then fixed the missing close in the exception handler.

3. Database server itself is overwhelmed

Sometimes it's not the pool. The database server is just slow. Maybe it's running on an old hard drive, or there's a monster query that's locking tables. Your pool might have connections, but they're all stuck waiting for the database to respond.

How to spot it: Check the database's own connections. If the DB shows high CPU or disk I/O, that's the problem. On Oracle, look at v$session for long-running queries:

SELECT sid, serial#, sql_id, seconds_in_wait FROM v$session WHERE status='ACTIVE';

On MySQL:

SHOW FULL PROCESSLIST;

If you see queries running for minutes, that's your issue.

The fix: First, kill any long-running queries that are blocking others:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Then, tune the slow queries. Use EXPLAIN to check if they need indexes. Add indexes:

CREATE INDEX idx_user_email ON users(email);

Also, check if the server has enough memory. I've seen databases on 2GB RAM trying to handle 10GB of data. The pool size doesn't matter when the DB can't even load the indexes into memory.

If you can't fix the queries, consider a read replica for reporting queries. Split the load.

One more thing: check if the DB's connection limit itself is the bottleneck. Even if your pool allows 200 connections, the DB might only allow 100. Check SHOW VARIABLES LIKE 'max_connections' and raise it if needed, but only if the server can handle it.

Quick-reference summary table

Start with the pool size. That's the easiest and most common. If that doesn't work, look for a leak. If still stuck, the DB is probably overwhelmed. Don't guess—monitor first, then fix.