Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available

org.apache.tomcat.jdbc.pool.ConnectionPool

View source code

private PooledConnection borrowConnection(int wait, String username, String password) throws SQLException {
 
        if (isClosed()) {
            throw new SQLException("Connection pool closed.");
        } //end if
 
        //get the current time stamp
        long now = System.currentTimeMillis();
        //see if there is one available immediately
        PooledConnection con = idle.poll();
 
        while (true) {
            if (con!=null) {
                //configure the connection and return it
                PooledConnection result = borrowConnection(now, con, username, password);
                borrowedCount.incrementAndGet();
                if (result!=null) return result;
            }
 
            //if we get here, see if we need to create one
            //this is not 100% accurate since it doesn't use a shared
            //atomic variable - a connection can become idle while we are creating
            //a new connection
            if (size.get() < getPoolProperties().getMaxActive()) {
                //atomic duplicate check
                if (size.addAndGet(1) > getPoolProperties().getMaxActive()) {
                    //if we got here, two threads passed through the first if
                    size.decrementAndGet();
                } else {
                    //create a connection, we're below the limit
                    return createConnection(now, con, username, password);
                }
            } //end if
 
            //calculate wait time for this iteration
            long maxWait = wait;
            //if the passed in wait time is -1, means we should use the pool property value
            if (wait==-1) {
                maxWait = (getPoolProperties().getMaxWait()<=0)?Long.MAX_VALUE:getPoolProperties().getMaxWait();
            }
 
            long timetowait = Math.max(0, maxWait - (System.currentTimeMillis() - now));
            waitcount.incrementAndGet();
            try {
                //retrieve an existing connection
                con = idle.poll(timetowait, TimeUnit.MILLISECONDS);
            } catch (InterruptedException ex) {
                if (getPoolProperties().getPropagateInterruptState()) {
                    Thread.currentThread().interrupt();
                }
                SQLException sx = new SQLException("Pool wait interrupted.");
                sx.initCause(ex);
                throw sx;
            } finally {
                waitcount.decrementAndGet();
            }
            if (maxWait==0 && con == null) { //no wait, return one if we have one
                if (jmxPool!=null) {
                    jmxPool.notify(org.apache.tomcat.jdbc.pool.jmx.ConnectionPool.POOL_EMPTY, "Pool empty - no wait.");
                }
                throw new PoolExhaustedException("[" + Thread.currentThread().getName()+"] " +
                        "NoWait: Pool empty. Unable to fetch a connection, none available["+busy.size()+" in use].");
            }
            //we didn't get a connection, lets see if we timed out
            if (con == null) {
                if ((System.currentTimeMillis() - now) >= maxWait) {
                    if (jmxPool!=null) {
                        jmxPool.notify(org.apache.tomcat.jdbc.pool.jmx.ConnectionPool.POOL_EMPTY, "Pool empty - timeout.");
                    }
                    throw new PoolExhaustedException("[" + Thread.currentThread().getName()+"] " +
                        "Timeout: Pool empty. Unable to fetch a connection in " + (maxWait / 1000) +
                        " seconds, none available[size:"+size.get() +"; busy:"+busy.size()+"; idle:"+idle.size()+"; lastwait:"+timetowait+"].");
                } else {
                    //no timeout, lets try again
                    continue;
                }
            }
        } //while
    }

Parameter setting

Property name describe Default
driverClassName user name -
url password -
username The url to establish the connection -
password Full class name of database driver -
initialSize Number of initial connections created when the connector starts 10
maxActive The maximum number of connections, usually the maximum number of database concurrent for regular access, is recommended to be gradually tuned according to jmx monitoring in the later stage 100
maxIdle The maximum number of idle connections is a parameter that is difficult to grasp. Many connection pools have also removed this attribute (such as Druid). Systems with centralized peak access, such as attendance, can be set a little smaller to save connection resources in most time periods. If it is too small, frequent connection creation and shutdown may also affect performance. It is recommended that the general system not be less than 50% of maxActive 100
minIdle The minimum number of connections, generally the same as initialSize 10
maxWait When the connection in the connection pool runs out, the waiting time of the new request, timeout returns an exception, in milliseconds Default 30000
testWhileIdle Whether the connection will be verified by the free object eviction process when it enters the idle state. The recommended verification method depends on validationQuery false
validationQuery The SQL statement used to verify whether the connection is valid before the connection is returned to the caller. It must be a SELECT statement with at least one result line -
validationQueryTimeout Timeout time of connection verification, in seconds. Note: the pool itself does not allow the query to time out. It depends on JDBC Driver to force the query to time out -
validationInterval Tomcat JDBC specific property, check the time interval of connection availability to prevent too frequent check when testOnBorrow and testOnReturn are true, in milliseconds 30000
timeBetweenEvictionRunsMillis Free object eviction check interval, in milliseconds 5000
minEvictableIdleTimeMillis The minimum time, in milliseconds, for a connection to remain idle in the pool before being evicted by the free object eviction process 60000
defaultAutoCommit The default auto commit state of the connection created by the connection pool (JDBC default means that the setAutoCommit method will not be called by default) JDBC default
jmxEnabled Whether to register connection pool with JMX true
jdbcInterceptors Tomcat JDBC unique properties, QueryTimeoutInterceptor (query timeout interceptor, queryTimeout, unit second, default 1 second), SlowQueryReport (slow query record, attribute threshold timeout record threshold unit millisecond, default 1000), multiple interceptors with; separate, example: QueryTimeoutInterceptor(queryTimeout=5);SlowQueryReport(threshold=3000) Note: called automatically when a new statement is created Statement.setQueryTimeout(seconds). The pool itself does not allow the query to time out. It relies entirely on the jdbc driver to force the query to time out. For more details, please see the official document -
testOnBorrow Whether to verify the connection when it is called depends on validationQuery, which has a certain impact on performance. It is not recommended to use false
testOnReturn Whether to verify when the connection is returned to the pool depends on validationQuery, which has a certain impact on performance. It is not recommended to use false
removeAbandoned Whether to clear the connection that has exceeded the removeAbandonedTimeout setting can be used to troubleshoot some uncommitted transactions (the formal environment is used cautiously, which has a certain impact on performance). It is not recommended to use it. Instead, QueryTimeOut interceptor can be used false
removeAbandonedTimeout Time to clear invalid connections in seconds in combination with removeAbandoned 60
defaultReadOnly Whether the connection created by the connection pool is read-only? It should be noted that setting true only tells the database that the connection is read-only, which is convenient for the database to do some optimization (such as not arranging the database lock). It is not that the update operation cannot be performed, but the data consistency protection is not strong (similar to the read-only transaction in spring) JDBC default

Solution (Reference)

Basically, most projects need to interact with the database, so what's the appropriate size of the database connection pool?

Connection calculation formula

Number of connections = ((number of cores * 2) + number of valid disks)

The number of cores should not include hyper threads, even if hyper threads are turned on. If all the hot data is cached, the number of effective disks is actually 0. With the decrease of cache hit rate, the number of effective disks is gradually approaching the actual number of disks. In addition, it should be noted that the effect of this formula on SSD is not clear.

Well, according to this formula, if your server CPU is 4-core i7, the connection pool size should be ((4 * 2) + 1) = 9.

Take the whole, let's set it to 10. Can you do this? 10 is too small!

If you don't think it's OK, you can run a performance test. We can guarantee that it can easily support the scenario where 3000 users can execute simple queries concurrently at 6000 TPS. You can also increase the connection pool size by more than 10, and then you will see the response time begin to increase and the TPS begin to decrease.

For example, if your system mixes long transactions and short transactions at the same time, it is difficult to calculate according to the above formula. The right approach is to create two connection pools, one for long transactions and one for "real-time" queries, that is, short transactions.

There is another situation, for example, when a system executes a task queue, the business requires that only a certain number of tasks can be executed at the same time. At this time, we should allow the number of concurrent tasks to adapt to the number of connections in the connection pool, rather than the number of connections to adapt to the number of concurrent tasks.

When you configure the tomcat database connection pool, you are always confused about the specific value of the configuration. Here are specific suggestions.

First, the formula:
Number of database connection pool connections = ((number of cores * 2) + number of valid disks)
How to get the core number?

linux view the number of physical CPUs

 cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l

View the number of cores in each physical CPU (that is, the number of cores)

 cat /proc/cpuinfo| grep "cpu cores"| uniq

Multiply the two numbers to get the core number.

The number of valid disks is usually one. Is the number of database connection pools obtained in this way much smaller than what I guess?
Let's understand.

The single core CPU can only execute one thread at a time, and then the operating system switches the context. The CPU core quickly schedules and executes the code of another thread, repeatedly, which makes all processes run at the same time. In fact, the performance loss of context switching is very large at this time.

In fact, on A single core CPU machine, sequential execution of A and B is always faster than "simultaneously" executing A and B through time slicing switch, because once the number of threads exceeds the number of CPU cores, increasing the number of threads will only make the system slower, not faster, because the context switching here will consume additional performance.

Tags: JDBC Database Tomcat Apache

Posted on Fri, 26 Jun 2020 03:29:04 -0400 by callmecheez