This is very simple demonstration on how to limit latency impact (improve consumer experience) by switching to out-of-band/background validation connection checking for DB connections:
Test setup: laptop <-- 0ms latency --> VM { Wildfly JBoss} <-- 6ms latency --> VM { Oracle DB }, latency was simulated using linux tc netem (6ms but only one way).
Benchmark generator settings: ApacheBenchmark 2.4 launched with concurrency = 1, timeout = 5, 1000 requests to simple JSP page, test was repeated multiple times to heat caches/etc.
Middle-tier: JBoss/WildFly Core 2.0.10.Final, 100 connection pool, validation connection checker explicitly set to “SELECT 1 FROM DUAL”
Application: simple JSP page performing 1 DB CALL (1x INSERT)
with 6ms RTT with JDBC Connection Pool with default foreground connection check (actually 4 packets; 4*6 =~ 24ms):
Requests per second: 55.97 [#/sec] (mean) Time per request: 17.867 [ms] (mean) Time per request: 17.867 [ms] (mean, across all concurrent requests) Transfer rate: 16.07 [Kbytes/sec] received Connection Times (ms) min mean[+/-sd] median max Connect: 0 0 0.1 0 1 Processing: 13 18 9.8 16 218 Waiting: 13 17 9.8 16 218 Total: 13 18 9.8 17 218 Percentage of the requests served within a certain time (ms) 50% 17 66% 17 75% 18 80% 18 90% 19 95% 21 98% 38 99% 71 100% 218 (longest request)
with 6ms RTT with JDBC Connection Pool with background connection check (just 2 packets to DB; 2*6 =~ 12ms):
Requests per second: 100.74 [#/sec] (mean) Time per request: 9.927 [ms] (mean) Time per request: 9.927 [ms] (mean, across all concurrent requests) Transfer rate: 28.92 [Kbytes/sec] received Connection Times (ms) min mean[+/-sd] median max Connect: 0 0 0.1 0 1 Processing: 7 10 2.8 9 41 Waiting: 7 9 2.8 9 41 Total: 7 10 2.9 9 42 Percentage of the requests served within a certain time (ms) 50% 9 66% 10 75% 10 80% 10 90% 11 95% 13 98% 18 99% 26 100% 42 (longest request)
This demonstrated that the average consumer experience can be improved from ~18ms to ~10ms just by setting two options that move the checking “out-of-band” for business transaction processing. Of course the more times the application calls .getConnection() even transparently like with the case of JSP, the more impact of those settings.
p.s. I’ve used
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
in my experiment however you should be really using
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
as it calls native OJDBC .isValid() or ping methods that perform the check even without SQL layer (smaller packets).