Pool datasource implementation

You are viewing an old version of this article. View the current version here.

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 :

poolUse pool. This option is useful only if not using a DataSource object, but only connection object.
Default: false. since 2.2.0
poolNamePool name that will permit to identify thread.
default: auto-generated as MariaDb-pool-<pool-index>since 2.2.0
maxPoolSizeThe maximum number of physical connections that the pool should contain.
Default: 8. since 2.2.0
minPoolSizeWhen 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
poolValidMinDelayWhen 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
maxIdleTimeThe 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
staticGlobalIndicate 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
useResetConnectionWhen 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
registerJmxPoolRegister 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
    }
}


Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.