Last modified on: 02 Apr 2018
Tags: JDBC data sources
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.
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
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 specially if the connection dropping cannot be predicted or it happens too often the recommended solution would be relying on a ping query timeout.