Imprimir este capítuloImprimir este capítulo

Tomcat JDBC Connection Pool

1. NUMBER OF CONNECTIONS OPENED BY POOL

According to AWS generic rule in order to determine maximum number of connections:

max_connections={DBInstanceClassMemory/12582880}


Here are some samples:

Instance typemax_connections
db.t2.micro~66
db.t2.small~150
db.t2.medium~340
db.m4.large~682
db.r3.2xlarge~5205


Database connection pool sizing recommendations:

  • Take into consideration the size of DB instance. Calculate or check maximum connections allowed.

Here are recommended settings:

Property

Recommended value

Comment

test-on-borrow

true

The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another one.

test-on-connect

true

Set to true if we should run the validation query when connecting to the database for the first time on a connection.

test-while-idle

true

Set to true if query validation should take place while the connection is idle.

initial-size

equal to minSize

Set the number of connections that will be established when the connection pool is started. Please be aware that this parameter affects start time (pool is fully created while application starts)

If value of this setting is smaller than “minSize” then idle connection validation job will not validate all connections in pool. This is why it’s important to initialSize to be equal to minSize

log-abandoned

true

Set to true if stack traces should be logged for application code which abandoned a Connection. This setting is helpful for troubleshooting.

log-validation-errors

true

Set to true if you wish that errors from validation should be logged as error messages. This setting is helpful for troubleshooting.

max-idle

depends on many factors (see recommendations above)

The maximum number of connections that should be kept in the idle pool.

max-active

depends on many factors (see recommendations above)

The maximum number of active connections that can be allocated from this pool at the same time.

max-wait

1000

The maximum number of milliseconds that the pool will wait (when there are no available connections and the maximum size has been reached) for a connection to be returned before throwing an exception (PoolExhaustedException). If we set this time for too high client response time may be affected.

min-idle

depends on many factors

The minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number if validation queries fail and connections get closed. The idle pool will not shrink below this value during an eviction run.

validation-query

/* Service Name Health Check */ SELECT 1

The SQL query that will be used to validate connections from this pool before returning them to the caller or pool. If specified, this query does not have to return any data, it just can’t throw a SQLException. In the comment it’s worth to put service name which does validation. It’ll be logged in MySQL in case of any problems.



Sample Configuration:

maxWait=

"1000"

initialSize=

"10"

minIdle=

"10"

maxIdle=

"10"

maxActive=

"32"

validationQuery=

"/* 4InsightsConsoleDB Health Check */ SELECT 1"

testOnBorrow=

"true"

testOnConnect=

"true"

testWhileIdle=

"true"

logAbandoned=

"true"

logValidationErrors=

"true"