There will be case when you will require to tune your existing connection pool configuration to sustain more user load with your old product or you want to identify very first time that how many concurrent user load your new product can handle.
Being architect or application designer, high probability will be that you will target tuning of connection pool configurations first at your end.
Below are some crucial configurations with tomcat jdbc and apache dbcp one can use to get high concurrency with less to no database connection issues.
//I used this in my application to hit database server with 20 running threads to update 14 tables(average 10 fields per table).
//It was 238 insertion of 0.5MB of data(encrypted XML string) in a second.
//Encryption time is also considered in this otherwise database operation number can be higher than this.
poolProperties.setRemoveAbandonedTimeout(30); poolProperties.setMinEvictableIdleTimeMillis(5000); poolProperties.setTimeBetweenEvictionRunsMillis(1500);
RemoveAbandonedTimeout – This is a timeout value. This should be the longest running query of your application however if your single connection object is used to fire multiple queries, value of timeout should be sum all those query execution. Keep this wide open to avoid ‘connection is already closed’ issue.
Above will not apply if you use ResetAbandonedTimer JDBC interceptor. In case of this, use timeout number as longest running single query.
MinEvictableIdleTimeMillis – Minimum time a connection can stay idle before it gets evacuated by evacuation thread to free up the resources.
TimeBetweenEvictionRunsMillis – Evacuation thread will kick in every x milliseconds to evacuate idle or abandoned connection objects.
Please note: Performance is subject of tuning of multiple elements. It also depends on hardware where database files will get stored, network adapters and database server configuration itself. To get maximum throughput of any application it is necessary that all these elements are tuned/configured properly.