What is Connection Pooling
Connection pooling is a technique which has been supported from J2SDK 1.4 and later relase which will facilitate the reuse of a set of reusable connection objects to database.
In the general mechanism for every request the client will open the connection to database and close the connection on response. Opening/closing connections is an expensive process and will decrease the system performance. Also the performance will increase because it brings down the average time required to wait for establishing the connection to database.In connection pooling mechanism firstly we need to configure the maximum number of connections based on the traffic, maximum idle time for each connection and so on.Every time a client request is received, the pool is searched for an available connection object and it's highly likely that it gets a free connection object. As soon as a request finishes using a connection object, the object is given back to the pool from where it's assigned to one of the queued requests.
Below is the Sample connection pooling configutaion
maxActive: It defines the maximum number of database connections in pool. The default value is -1 which means no limit.
maxIdle: It defines the maximum number of idle database connections to retain in pool. The default value is -1 which means no limit.
maxWait: Maximum time to wait for a database connection to become available in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set it to -1 which means to wait indefinite time.
user : Database user name
password: Database password
url: JDBC thin client connection along with servicename
Some times due to connection issues the the database will close the connection from its end but the tomcat will be waiting for response without releasing the connection. In these cases the pool will be exhausted and will result in th e following error.
sql.SQLException: No more data to read from socket java.sql.SQLException: Io exception: Broken pipe
(or)
java.net.SocketException MESSAGE: Broken pipe
For handling this situation we need to add below paramaters as shown in above example 1) ValidationQuery: This will check the database connectivity for each request. So if there is any error in oracle connection the request will be ignored & will proceed for enother request. Please keep in mind that the query should be simple and database specific. In this case it is written for oracle validationQuery="select 1 from dual" 2) testOnBorrow="true" This value should be set to true
Connection pooling is a technique which has been supported from J2SDK 1.4 and later relase which will facilitate the reuse of a set of reusable connection objects to database.
In the general mechanism for every request the client will open the connection to database and close the connection on response. Opening/closing connections is an expensive process and will decrease the system performance. Also the performance will increase because it brings down the average time required to wait for establishing the connection to database.In connection pooling mechanism firstly we need to configure the maximum number of connections based on the traffic, maximum idle time for each connection and so on.Every time a client request is received, the pool is searched for an available connection object and it's highly likely that it gets a free connection object. As soon as a request finishes using a connection object, the object is given back to the pool from where it's assigned to one of the queued requests.
Below is the Sample connection pooling configutaion
Some times due to connection issues the the database will close the connection from its end but the tomcat will be waiting for response without releasing the connection. In these cases the pool will be exhausted and will result in th e following error.
sql.SQLException: No more data to read from socket java.sql.SQLException: Io exception: Broken pipe
(or)
java.net.SocketException MESSAGE: Broken pipe
For handling this situation we need to add below paramaters as shown in above example 1) ValidationQuery: This will check the database connectivity for each request. So if there is any error in oracle connection the request will be ignored & will proceed for enother request. Please keep in mind that the query should be simple and database specific. In this case it is written for oracle validationQuery="select 1 from dual" 2) testOnBorrow="true" This value should be set to true
No comments:
Post a Comment