Upgrade to Pro — share decks privately, control downloads, hide ads and more …

JUGNsk Meetup #12. Александр Колесников: "JDBC Pools Battle".

0605d4091b5f5ab4da0cb1e921aa3f57?s=47 jugnsk
November 19, 2019

JUGNsk Meetup #12. Александр Колесников: "JDBC Pools Battle".

Александр работает бекенд-техлидом/SRE в компании Wrike. Большая часть бекенд-сервисов в компании написана на Java, основным хранилищем является реляционная БД (PostgreSQL).

Отказоустойчивость и перфоманс базы критичны, т.к. Wrike предоставляет пользователям сервис с аптаймом 99.99%.

В докладе Александр ответит на следующие вопросы:

1) Для чего нужны JDBC пулы

2) Почему так много реализаций

3) В чем их отличия

4) Какой пул выбрать для своего приложения

5) На какие настройки пула стоит обратить внимание

0605d4091b5f5ab4da0cb1e921aa3f57?s=128

jugnsk

November 19, 2019
Tweet

Transcript

  1. JDBC Pools Tournament

  2. Nice to Meet You Tech Lead / SRE in Wrike

    Java-based Back-End PostgreSQL as main storage 99.99% uptime 2
  3. Which pool to use in 2019?

  4. Contents • some theory • trends & candidates • proxies

    • statements caching • connection recovery • troubleshooting • performance • summary • project health • collections & thread-safety 4
  5. Some Theory

  6. JDBC Pools - Theory • reuse heavy resources ◦ TCP

    + SSL ◦ DB connection ◦ JDBC Statements ◦ CPU system time • connection maintenance ◦ validation ◦ leakage tracking ◦ recovery • load limitation ◦ many apps - one database 6
  7. Pooled vs Unpooled 7

  8. Candidates

  9. JDBC Pools Trends * * https://trends.google.com/trends/explore?date=today%205-y&q=hikaricp,tomcat%20jdbc,c3p0%20jdbc,dbcp,bonecp 9

  10. JDBC Pools Shortlist • HikariCP • Tomcat JDBC • c3p0

    • DBCP • BoneCP • Vibur DBCP 10
  11. Abandoned BoneCP https://github.com/wwadge/bonecp 11

  12. Abandoned Vibur DBCP https://github.com/vibur/vibur-dbcp 12

  13. JDBC Pools Shortlist (Up-to-Date) HikariCP Tomcat JDBC c3p0 DBCP BoneCP

    Vibur DBCP 13
  14. HikariCP Tomcat JDBC c3p0 DBCP proxies collections statements cache recovery

    troubleshooting library extensibility performance 14
  15. Project Health

  16. Short Repository Summary 1 Pool Started at Commits Contributors HikariCP

    2013 ~2.7k 93 Tomcat JDBC 2008 ~600 313 c3p0 20032 ~350 5 DBCP 2005 ~2k 33 1. https://github.com, master branch 2. https://sourceforge.net/projects/c3p0/files/historical 3. whole Tomcat project repository 16
  17. Short Code Summary * Pool Sources lines Tests lines JAR

    size HikariCP ~5k ~7.5k 149 KB Tomcat JDBC ~7k ~5.5k 146 KB c3p0 ~16k ~2k 488 KB DBCP ~15k ~17k 203 KB * https://github.com/AlDanial/cloc 17
  18. HikariCP Tomcat JDBC c3p0 DBCP proxies collections statements cache recovery

    troubleshooting library extensibility performance 18
  19. Proxies

  20. Proxies: DBCP 20

  21. Proxies: c3p0 21

  22. Proxies: c3p0 22

  23. Proxies: Tomcat JDBC 23

  24. Proxies: Tomcat JDBC 24

  25. Proxies: Tomcat JDBC 25

  26. Proxies: HikariCP 26

  27. Proxies: HikariCP 27

  28. Proxies: HikariCP 28

  29. Proxies: HikariCP 29

  30. HikariCP Tomcat JDBC c3p0 DBCP proxies collections statements cache recovery

    troubleshooting library extensibility performance 30
  31. Statements Caching

  32. Statements Caching: DBCP 32

  33. Statements Caching: c3p0 “If your driver does preprocess PreparedStatements …

    you will probably see a significant performance gain by turning Statement pooling on.” * * https://www.mchange.com/projects/c3p0/#known_shortcomings Turned off by default, but there are 2 options: • maxStatements • maxStatementsPerConnection 33
  34. Statements Caching: Tomcat JDBC Can be turned on using “interceptor”:

    org.apache.tomcat.jdbc.pool.interceptor.StatementCache 34
  35. Statements Caching: HikariCP “Using a statement cache at the pooling

    layer is an anti-pattern, and will negatively impact your application performance compared to driver-provided caches.” * * https://github.com/brettwooldridge/HikariCP#statement-cache 35
  36. HikariCP Tomcat JDBC c3p0 DBCP proxies collections statements cache recovery

    troubleshooting library extensibility performance 36
  37. Connection Recovery

  38. Connection Maintenance maxLifetime connectionTestQuery validationTimeout leakDetectionThreshold testOnBorrow testOnConnect testOnReturn testWhileIdle

    validationQuery validationQueryTimeout logAbandoned validationInterval suspectTimeout automaticTestTable connectionTesterClassName idleConnectionTestPeriod preferredTestQuery testConnectionOnCheckin testConnectionOnCheckout validationQuery validationQueryTimeout testOnCreate testOnBorrow testOnReturn testWhileIdle timeBetweenEvictionRunsMillis numTestsPerEvictionRun minEvictableIdleTimeMillis softMinEvictableIdleTimeMillis maxConnLifetimeMillis logExpiredConnections connectionInitSqls 38
  39. Dead Connection Issue 39

  40. Connection Recovery: DBCP 40

  41. Connection Recovery: c3p0 41

  42. Connection Recovery: Tomcat JDBC 42

  43. Connection Recovery: HikariCP 43

  44. HikariCP Tomcat JDBC c3p0 DBCP proxies collections statements cache recovery

    troubleshooting library extensibility performance 44
  45. Troubleshooting

  46. Expected Metrics • pool utilization • queries latency/count • connection

    wait/lease duration • slow log • result set size 46
  47. JMX: DBCP 47

  48. JMX: c3p0 48

  49. Extensibility: c3p0 https://www.mchange.com/projects/c3p0/#connection_customizers 49

  50. JMX: Tomcat JDBC 50

  51. Metrics: Tomcat JDBC org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport 51

  52. Extensibility: Tomcat JDBC 52

  53. Extensibility: Tomcat JDBC 53

  54. Metrics: HikariCP 54

  55. Metrics: HikariCP 55

  56. Extensibility: HikariCP “The HikariCP design aesthetic is Minimalism. In keeping

    with the simple is better or less is more design philosophy, some configuration axis are intentionally left out.” * * https://github.com/brettwooldridge/HikariCP#missing-knobs 56
  57. HikariCP Tomcat JDBC c3p0 DBCP proxies collections statements cache recovery

    troubleshooting library extensibility performance 57
  58. HikariCP Tomcat JDBC c3p0 DBCP proxies collections statements cache recovery

    troubleshooting library extensibility performance 58
  59. Collections & Thread-Safety

  60. Collections: DBCP Uses Apache Commons Pool * library * https://commons.apache.org/proper/commons-pool

    60
  61. Collections: c3p0 61

  62. Collections: Tomcat JDBC “A simple implementation of a blocking queue

    with fairness waiting. Invocations to method poll(...) will get handed out in the order they were received.” 62
  63. Collections: HikariCP com.zaxxer.hikari.util.ConcurrentBag “This is a specialized concurrent bag that

    achieves superior performance to LinkedBlockingQueue and LinkedTransferQueue for the purposes of a connection pool.” 63
  64. HikariCP Tomcat JDBC c3p0 DBCP proxies collections statements cache recovery

    troubleshooting library extensibility performance 64
  65. Performance

  66. Benchmark JMH 1.22 1 20 threads, 5 iterations * 10

    seconds 2 1. https://openjdk.java.net/projects/code-tools/jmh 2. https://gist.github.com/alekkol/e7c1d6c0a08da683ca982a5c9e25d44f 24 CPU, JDK 11.0.3, OpenJDK 64-Bit Server VM, 11.0.3+7 66
  67. Stub Statement Benchmark 67

  68. What’s Going On: DBCP 72.8% WAITING 17.2% TIMED_WAITING 10.0% RUNNABLE

    25.7% jdk.internal.misc.Unsafe.park j.u.c.locks.LockSupport.park j.u.c.locks.AbstractQueuedSynchronizer$ConditionObject.await org.apache.commons.pool2.impl.LinkedBlockingDeque.takeFirst org.apache.commons.pool2.impl.GenericObjectPool.borrowObject 22.5% jdk.internal.misc.Unsafe.park j.u.c.locks.LockSupport.park ... j.u.c.locks.AbstractQueuedSynchronizer$ConditionObject.await org.apache.commons.pool2.impl.LinkedBlockingDeque.takeFirst 68
  69. What’s Going On: HikariCP 80.5% RUNNABLE 18.9% TIMED_WAITING 0.6% WAITING

    62.9% java.lang.Thread.yield com.zaxxer.hikari.util.ConcurrentBag.requite com.zaxxer.hikari.pool.HikariPool.recycle com.zaxxer.hikari.pool.PoolEntry.recycle com.zaxxer.hikari.pool.ProxyConnection.close com.wrike.db.DataSourceBenchmark.hikari 12.3% com.wrike.db.DataSourceBenchmark.hikari com.wrike.db.generated.*.hikari_thrpt_jmhStub com.wrike.db.generated.*.hikari_Throughput 69
  70. Network Latency Does Matter L1 cache reference 0.5 ns Branch

    mispredict 5 ns L2 cache reference 7 ns Mutex lock/unlock 25 ns Main memory reference 100 ns Compress 1K bytes with Zippy 3,000 ns 3 us Send 1K bytes over 1 Gbps network 10,000 ns 10 us Read 4K randomly from SSD* 150,000 ns 150 us Read 1 MB sequentially from memory 250,000 ns 250 us Round trip within same datacenter 500,000 ns 500 us Read 1 MB sequentially from SSD* 1,000,000 ns 1,000 us Disk seek 10,000,000 ns 10,000 us 10 ms Read 1 MB sequentially from disk 20,000,000 ns 20,000 us 20 ms Send packet CA->Netherlands->CA 150,000,000 ns 150,000 us 150 ms https://gist.github.com/jboner/2841832 70
  71. 71

  72. More Believable Option 72

  73. Statement Benchmark: 1 ms delay 73

  74. HikariCP 95.1% TIMED_WAITING 4.4% RUNNABLE 0.5% WAITING 37.5% jdk.internal.misc.Unsafe.park j.u.c.locks.LockSupport.parkNanos

    j.u.c.SynchronousQueue$TransferQueue.awaitFulfill j.u.c.SynchronousQueue$TransferQueue.transfer j.u.c.SynchronousQueue.poll com.zaxxer.hikari.util.ConcurrentBag.borrow com.zaxxer.hikari.pool.HikariPool.getConnection 37.2% java.lang.Thread.sleep com.wrike.db.pseudodriver.PseudoPreparedStatement.execute com.zaxxer.hikari.pool.ProxyPreparedStatement.execute 74
  75. Tomcat JDBC 92.3% TIMED_WAITING 6.0% RUNNABLE 1.7% WAITING 38.2% jdk.internal.misc.Unsafe.park

    j.u.c.locks.LockSupport.parkNanos ... j.u.c.CountDownLatch.await org.apache.tomcat.jdbc.pool.FairBlockingQueue.poll 34.9% java.lang.Thread.sleep com.wrike.db.pseudodriver.PseudoPreparedStatement.execute ... java.lang.reflect.Method.invoke org.apache.tomcat.*.*.StatementFacade$StatementProxy.invoke 75
  76. Statement Benchmark: 5 ms delay 76 866 ops/sec

  77. What’s Going On: HikariCP # Warmup Iteration 1: 545.312 ops/s

    Iteration 1: 579.384 ops/s Iteration 2: 500.878 ops/s Iteration 3: 585.681 ops/s Iteration 4: 535.962 ops/s Iteration 5: 2129.979 ops/s 77 avg = 866.377 ops/s p0.99 0.005 s/op p0.999 29.542 s/op p0.9999 97.399 s/op p1.00 162.403 s/op
  78. HikariCP Tomcat JDBC c3p0 DBCP proxies collections statements cache recovery

    troubleshooting performance library extensibility 78
  79. Summary

  80. Performance vs Flexibility Flexibility DBCP Performance Tomcat JDBC HikariCP c3p0

    80
  81. Summary • All mentioned pools are good • Real-world performance

    is not much different • Some of them required thoughtful configuration • Choose between configuration simplicity / flexibility 81
  82. Recommendations Flexibility Simplicity HikariCP Tomcat JDBC 82

  83. Q & A Alexander Kolesnikov / 19.11.2019 github.com/alekkol/jdbc-pool-tournament alexander.kolesnikov@team.wrike.com alekkol@ya.ru

    t.me/alekkol