Connecting to a database server consists of several steps each of which takes time to complete. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.
To minimize the cost of opening connections, Denodo utilizes an optimization technique called connection pooling which is a standard practice widely used by the industry.
A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that the work to create a new connection does not have to be repeated.
For JDBC connections, Denodo uses the Apache Commons DBCP library. For distributed transactions, the XAPool library is used.
A detailed explanation on how the connection pool works for JDBC sources, including the meaning of the different parameters, can be found in the The Pool of Connections of the JDBC Data Sources section of the Virtual DataPort VQL Guide.
A connection pool is a commonly used optimization method which reduces the time taken to create new connections, as already open connections can be reused. Nevertheless, there could be situations where the connection can be broken or in a stale status. In these types of situations, the client application has no control over the network.
For this reason, Connection Pools have some strategies to overcome and defend against any issue that may happen in the network in order to ensure no broken or stale connections are used.
To specify the timeouts for a specific data source from a database:
The timeouts for all these parameters are specified in milliseconds.
CREATE OR REPLACE DATASOURCE JDBC ds_name
TESTWHILEIDLE = true
TIMEBETWEENEVICTION = 900000
NUMTESTPEREVICTION = 20
MINEVICTABLETIME = 1800000
You can get more information on the different properties in the CREATE DATA SOURCE JDBC statement in the The Pool of Connections of the JDBC Data Sources section of the Virtual DataPort VQL Guide. It also includes recommendations for the configuration in environments with a firewall.
The best strategy will depend on every specific case. The second strategy of testing the connection before using it with a ping query corresponds to the first Denodo solution, it is enabled by default in Denodo JDBC data sources and is the recommended solution in most scenarios.
Validating idle connections could be a good fit if we know when the connection is going to be dropped by a network component. In that case, we can adjust the validation of idle connections to that number so we avoid unnecessary ping queries execution. In any case, note that the ping or validation query should be an immediate query causing practically no impact in terms of performance. Nevertheless, this ping query introduces certain overhead and especially if the connection dropping cannot be predicted or it happens too often the recommended solution would be relying on a ping query timeout.
Virtual DataPort VQL Guide: The Pool of Connections of the JDBC Data Sources
Virtual DataPort VQL Guide: Recommended Settings of the Connection Pool in Environments with a Firewall