+1 --Srinath On Fri, Jan 21, 2011 at 12:40 PM, Anjana Fernando <[email protected]> wrote: > Hi, > > I noticed that most of the time, we are using the validation query in > an inefficient way. The current logic that is in Registry/UM is the > "testOnBorrow" approach. Here, each time a connection is acquired from > the connection pool, the validation query is run to validate it. So > most of the places, we just execute a single query at a time, so > effectively for each query, the validation query will be executed as > well, so this will result in 2x query execution with validation query > on. > > So, most of the time, we will not need this, the main reason we use > this is, to recover from stale connections. We've two alternatives for > this, > > 1. Use the mode "testWhileIdle" with the validation query, also > setting "timeBetweenEvictionRunsMillis" to specify the interval where > an 'evictor' thread it run. Here when the evictor thread is run, it > executes the validation query for all the connections and check if > they are active, if not, they are dropped from the pool. > > 2. No validation query is used, "minEvictableIdleTimeMillis" is > specified with "timeBetweenEvictionRunsMillis". This effectively > signals DBCP, for all the connections that are idle for > "minEvictableIdleTimeMillis" time period, the evictor thread will > identify them and drop them. Note that, this will not use a validation > query. > > So the perfect option would be to use the second option. Where it will > not at least use the validation query, where you can simply put a > "minEvictableIdleTimeMillis" value which is a bit less than the > connection time-out for a specific DBMS. For example, if MySQL > connection time-out is 8hrs, we can safely put 4 or 6 hrs as the > "minEvictableIdleTimeMillis" value. > > The only upside when using "testOnBorrow" is, for a situation like, if > the database is resetted for some reason or some unexpected > communication problem occurs, then all the connections in the > connection pool becomes invalid, and then when a connection is > acquired from the pool, it will immediately run the validation query > and renew the connections. > > This functionality is already there in the data sources component to > provide the configuration, and this also will be there in the next DSS > release. It will be better if this is also added to Registry/UM. For > more info, refer to [1]. > > [1] http://commons.apache.org/dbcp/configuration.html > > Cheers, > Anjana. > > -- > Anjana Fernando > Software Engineer > WSO2, Inc.; http://wso2.com > lean.enterprise.middleware > _______________________________________________ > Carbon-dev mailing list > [email protected] > https://wso2.org/cgi-bin/mailman/listinfo/carbon-dev >
-- ============================ Srinath Perera, Ph.D. Senior Software Architect, WSO2 Inc. Visiting Lecturer, University of Moratuwa Member, Apache Software Foundation Research Scientist, Lanka Software Foundation Blog: http://srinathsview.blogspot.com/ _______________________________________________ Carbon-dev mailing list [email protected] https://wso2.org/cgi-bin/mailman/listinfo/carbon-dev
