This is post about quick & dirty experiment to better understand how network round-trip time (RTT) affects single thread performance of Oracle JDBC operations.
- Non-XA transaction description: single INSERT to simple table.
- XA transaction description: two XA connections to the same DB, each doing simple INSERT to simple table, no latency on transaction/2PC object store has been simulated (in reality fsync() costs! – no real Transaction Manager has been used)
- Transaction initiator: custom written Java7 OraXAJDBC program for purposes of this test running on small VM.
- DB: Oracle DB 188.8.131.52.2, RAC, servie running on single node
- Latency has been introduced via netem qdisc on Linux (both ingress and egress).
XA JDBC results:
Increasing number of connections – not a solution
Common sense workaround seems to be increase threads/number of connections in connection pool, however this approach in longer term seems to be not scalable. Each connection in Oracle 11.2 is served by separate process on the DB server, that takes some resources (mainly memory – PGA). The more connections coming to the DB to server the more often CPUs need to switch between processes – process known as context-switch. Context-switch usually destroys L1-L3 CPU cache efficiency and thus degrade performance (introduces additional latency) when many processes compete for CPU core.
As evidenced here OLTP Performance – Concurrent Mid-Tier Connections (must see for any serious DBA/Java/J2EE developer/architect) 2000 connections to the DB has avg trx respone time ~100ms but when Connection Pool is reduced to 96 connections (and thus queuing is introduced in the Middleware itself), system as a whole achieves much greater throughput with response time of ~3ms, because of reducing concurrency/connection-switches. Some potential option (which has NOT been tested) is new feature in Oracle 12.1 named “JDBC Support for Database Resident Connection Pool” (DRCP) which apparently seems to be implemented as Process Pool on the DB server side. As any new feature it might introduce strict requirements (incomparability) and bugs. This feature requires Oracle Universal Connection Pool. Please note that other problems/issues with no# of connections in CP were not investigated in this
document (failover/HA time, etc).