Pool datasource implementation
Pool datasource implementation
MariaDB has 2 different Datasource implementation :
- MariaDbDataSource : The existing basic implementation. A new connection each time method getConnection() is called.
- MariaDbPoolDataSource : Connection pooling implementation. MariaDB Driver will keep a pool of connection and borrow Connections when asked for it.
When using MariaDbPoolDataSource, different options permit to indicate pool behaviour :
pool | Use pool. This option is useful only if not using a DataSource object, but only connection object. Default: false. since 2.2.0 |
---|---|
poolName | Pool name that will permit to identify thread. default: auto-generated as MariaDb-pool-<pool-index>since 2.2.0 |
maxPoolSize | The maximum number of physical connections that the pool should contain. Default: 8. since 2.2.0 |
minPoolSize | When connection are removed since not used since more than "maxIdleTime", connections are closed and removed from pool. "minPoolSize" indicate the number of physical connections the pool should keep available at all times. Should be less or equal to maxPoolSize. Default: maxPoolSize value. Since 2.2.0 |
poolValidMinDelay | When asking a connection to pool, Pool will validate connection state. "poolValidMinDelay" permit to disable this validation if connection has been borrowed recently avoiding useless verification in case of frequent reuse of connection. 0 meaning validation is done each time connection is asked. Default: 1000 (in milliseconds). Since 2.2.0 |
maxIdleTime | The maximum amount of time in seconds that a connection can stay in pool when not used. This value must always be below @wait_timeout value - 45s Default: 600 in seconds (=10 minutes), minimum value is 60 seconds. Since 2.2.0 |
staticGlobal | Indicate the following global variable (@@max_allowed_packet,@@wait_timeout,@@autocommit,@@auto_increment_increment,@@time_zone,@@system_time_zone,@@tx_isolation) values won't changed, permitting to pool to create new connection faster. Default: false. Since 2.2.0 |
useResetConnection | When a connection is closed() (give back to pool), pool reset connection state. Setting this option, session variables change will be reset, and user variables will be destroyed when server permit it (MariaDB >= 10.2.4, MySQL >= 5.7.3), permitting to save memory on server if application make extensive use of variables Default: false. Since 2.2.0 |
registerJmxPool | Register JMX monitoring pools. Default: true. Since 2.2.0 |
Example of use :
MariaDbPoolDataSource pool = new MariaDbPoolDataSource("jdbc:mariadb://server/db?user=myUser&maxPoolSize=10"); try (Connection connection = pool.getConnection()) { try (Statement stmt = connection.createStatement()) { ResultSet rs = stmt.executeQuery("SELECT CONNECTION_ID()"); rs.next(); System.out.println(rs.getLong(1)); //4489 } } try (Connection connection = pool.getConnection()) { try (Statement stmt = connection.createStatement()) { ResultSet rs = stmt.executeQuery("SELECT CONNECTION_ID()"); rs.next(); System.out.println(rs.getLong(1)); //4489 (reused same connection) } } pool.close();
Pooling can be configured at connection level using the "pool" option: (The main difference is that there is no accessible object to close pool if needed.)
//option "pool" must be set to indicate that pool has to be used String connectionString = "jdbc:mariadb://server/db?user=myUser&maxPoolSize=10&pool"; try (Connection connection = DriverManager.getConnection(connectionString)) { try (Statement stmt = connection.createStatement()) { ResultSet rs = stmt.executeQuery("SELECT CONNECTION_ID()"); rs.next(); System.out.println(rs.getLong(1)); //4506 } } try (Connection connection = DriverManager.getConnection(connectionString)) { try (Statement stmt = connection.createStatement()) { ResultSet rs = stmt.executeQuery("SELECT CONNECTION_ID()"); rs.next(); System.out.println(rs.getLong(1)); //4506 (reused same connection) } }
Pool connection handling :
Each time a connection is asked, if pool contain a connection not used, pool will validate the connection exchanging an empty MySQL packet with the server to ensure connection state then give connection. Pool reuse connection intensively, so this validation is done only if Connection has not been use since some time (option "poolValidMinDelay" with the default value of 1000ms).
If no connection is available, the request for a connection will be put in a queue until connection timeout. When a connection is available (new creation or released to the pool), it will be use to satisfy queued request in FIFO order.
A dedicated thread will handle new connection creation (one by one) to avoid connection burst. This thread will create connections until "maxPoolSize" if needed with a minimum connection of "minPoolSize".
99.99% of the time, connection is created, a few 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:
Connection close:
On connection.close(), Connection is not really closed, but given back to pool. Pool will then reset connection state. This reset goal is that next Connection get from pool has the same state as a newly "fresh" created connection.
Reset operations :
- rollback remaining active transaction
- reuse the initial configured database if changed
- default connection read-only state to false (master in a masters/slaves configuration) if changed
- re-initialize socket timeout if changed
- autocommit reset to default
- Transaction Isolation if changed
If server version is >= 10.2.4 (5.7.3 for MySQL server), then option "useResetConnection" can be used. This option will delete all user variables, and reset session variables to their initial state.
Idle timeout Thread
An additional thread will periodically close idle connections not used for a time corresponding to option "maxIdleTime". Pool will ensure to recreate connection to satisfy the option "minPoolSize" value.
This avoids keeping unused connection in pool, overloading server uselessly. If option "staticGlobal" is set, driver will ensure that option "maxIdleTime" is less than server @@wait_timeout.
Connection performance boost.
When creating a connection, driver need to execute between 2 to 4 additional queries after socket initialization / ssl initialization depending on options.
If your application never change the following global variables don't change (rarely changed) :
- @@max_allowed_packet
- @@wait_timeout
- @@autocommit
- @@auto_increment_increment
- @@time_zone
- @@system_time_zone
- @@tx_isolation
Then you can use the option "staticGlobal". Those value will be kept in memory, avoiding any additional queries when establishing a new connection (connection creation can be 30% faster, depending on network)
Additional enhancement then : Statement.cancel, Connection.abort() methods using pool are super fast, because of reusing a connection from pool.
If any change occur, JMX method resetStaticGlobal permit to reset values from memory.
JMX
if not disabled by option "registerJmxPool", JMX give some information on pool state. MBeans name are like "org.mariadb.jdbc.pool:type=*".
Some statistics of current pool :
- long getActiveConnections(); -> indicate current used connection
- long getTotalConnections(); -> indicate current number of connections in pool
- long getIdleConnections(); -> indicate the number of connection currently not used
- long getConnectionRequests(); -> indicate threads number that wait for a connection.
Example accessing JMX through java :
try (MariaDbPoolDataSource pool = new MariaDbPoolDataSource(connUri + "jdbc:mariadb://localhost/testj?user=root&maxPoolSize=5&minPoolSize=3&poolName=PoolTestJmx")) { try (Connection connection = pool.getConnection()) { MBeanServer server = ManagementFactory.getPlatformMBeanServer(); ObjectName filter = new ObjectName("org.mariadb.jdbc.pool:type=PoolTest*"); Set<ObjectName> objectNames = server.queryNames(filter, null); ObjectName name = objectNames.iterator().next(); System.out.println(server.getAttribute(name, "ActiveConnections")); //1 System.out.println(server.getAttribute(name, "TotalConnections")); //3 System.out.println(server.getAttribute(name, "IdleConnections")); //2 System.out.println(server.getAttribute(name, "ConnectionRequests")); //0 } }