Implement a datasource with pool

Actual implementation of datasource as javadoc indicate correspond to "Basic implementation"

Goal is to provide "Connection pooling implementation".
There is already good framework solution that does the job.

Why integrate pooling inside driver ?

• provide functionality to permit easier migration from other database
• When connection is created, Driver ask server for some configuration information. Those query would be done only on first connection, permitting to reduce by 25% connection creation time.
• Driver knows some state that would ensure connection state, and can reset connection state more efficiently.
• Automatically handle max connection time according to @@wait_timeout, connection eviction automatically if wait_timeout is reached
• no isValid() repetitive call, only call connection.isValid() before any new Connection request

Implementation requirement :

New DataSource implementation "MariaDbPoolDataSource" will be the pooling implementation, "MariaDbDataSource" won't change, as basic implementation.

When using MariaDbPoolDataSources, additional pool configuration properties will be available :

• "poolName" : for better thread name identification.
• "maxPoolSize" : This property indicate the maximum simultaneous connection that pool can keep. Default to 8.
• "minPoolSize" : the number of connection to keep even after maxIdleTime is reached. Cannot be > maxPoolSize. default to maxPoolSize.
• "maxIdleTime" : this indicate to remove connection not used after this amount of time to avoid having too many unused connection to server (only applicable if minPoolSize < maxPoolSize).
  default 10 minutes

Connection handling :

When a new connection is asked to pool, if there is existing connection not used, Connection validation is done then borrowed directly.
If no connection is available, connection query will be put in queue until connection timeout.
When a new connection is available ( new creation or released to pool), those will be used.

A dedicated thread will handle new connection creation (one by one) to avoid connection burst. This thread will create connection until maxPoolSize if needed. This only thread will permit to handle "Burst of connection" :
99.99% of the time, connection is created, 1-3 queries are executed, then connection is released.

Creating connection one after another permit to handle sudden peak of connection, to avoid creating lot of connections immediately and drop them after idle timeout:

Example

configuration minPoolSize=2 and maxPoolSize=20 with no activity (only 2 connection in pool) with average query executing in 0.1 milliseconds, a connection average taking 5 milliseconds
If a sudden peak of 50 connections are needed to execute one query, that mean that there will be 2 connection borrowed and released, 25 times each, so 2.5 milliseconds after, all queries are handled, and 2.5 milliseconds after, there will be 3 connections in pool.
This is the reason connections are created one after another. If peak continue, then connections in pool will quickly increase.

Connection close:

On connection closing, borrowed connection state will be reset, then give back to pool.
State reset correspond to resetting following value (so connection correspond to a newly "fresh" created connection) :

• initial database
• read-only
• socket timeout
• autocommit
• Transaction Isolation

Idle timeout Thread

if option minPoolSize is set and different than maxPoolSize, an additional thread will periodically close idle connections not used after "maxIdleTime", keeping a minumum number of connection to satisfy "minPoolSize".

The idle connections in pool will be validated (COM_PING) if last use time approach @@wait_timeout value.

Connection performance boost.

When creating a connection, java driver need to execute 1 or 2 additional query after socket initialisation / ssl initialisation.
Using pool, most of the time (depending on options), no additional query will be needed on connection.

futur possible evolution :

• add JMX statistic information to permit easily support (that report idle Connection count, active Connections, total Connections and the number of threads waiting for a connection)