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

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

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

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

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

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

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

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

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

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