Very simple demonstration how to limit latency impact (improve consumer experience) by switching to out-of-band/background validation connection checking for DB connections

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).

Comments are closed.