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
