Download as PDF, PPTX
SmartDB Office Hours: Connection Pool Sizing Concepts
- 1. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Pool Sizing Concepts Why SmartDB Doesn’t Require Large Connection Pool Toon Koppelaars Real-World Performance Oracle Server Technologies
- 2. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
- 3. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Topics • Web Application Architecture – Connection Reservation and Connection Queueing • The Issue of Too Large Connection Pools – From CPU Oversubscription to Database Oversubscription • Sizing Your Connection Pool – %Idle-Time in Foreground Processes • Recommendations 3
- 4. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Web Application Architecture • Architecture involves: – Browsers with html (and JavaScript) – Web server that takes care of http(s) traffic – Application server that runs application code – Database server that provides data persistency services 4
- 5. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Web: Past 15 Years 5 Database Server H T T P - S R V Application Server Dedicated Server Dedicated Server Dedicated Server JVM C1 Connection pool C2 C3 Number of pre- spawned connection objects each with a login into database Each connection object is logged into database using dedicated server aka foreground process Multi-threaded JVM (has thread pool) Pre-loaded application code (ear/jar/war files)
- 6. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Pool Configuration (UCP) 6 Console->Services->Data Sources • When you start application server, WLS will initialize connection pool in JVM
- 7. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Doing This Programmatically Yourself 7
- 8. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Web: Past 15 Years 8 Database Server H T T P - S R V Application Server Dedicated Server Dedicated Server Dedicated Server JVM C1 Connection pool C2 C3 Much fewer connections/ processes/sessions will be shared over much more (browser) clients Lots of browsers sharing only a few connections into database: how does that work? These are time-shared
- 9. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Browser Requests Cause Working Application Threads 9 Eight threads currently processing requests on application server Eight browsers currently waiting for request to complete
- 10. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Introducing Term: Connection Reservation • Time during which thread has claimed one of the connections from pool to do database work 10
- 11. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 11 JVM C1 C2 C3 JDBC S1 S2 S3 Thread 6 services request from browser client T1 T2 T3 T4 T5 T6 T7 T8 Connection pool Timeline T6 Java thread on CPU On network Foreground on CPU
- 12. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 12 JVM C1 C2 C3 JDBC S1 S2 S3 Executes business logic and needs to call database T1 T2 T3 T4 T5 T6 T7 T8 Connection pool Timeline T6 Java thread on CPU On network Foreground on CPU
- 13. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 13 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool C2 now unavailable for other threads Thread 6 requests and acquires arbitrary free connection from pool Timeline T6 Acquire connection Java thread on CPU On network Foreground on CPU
- 14. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 14 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool Thread 6 submits SQL via C2 (T6 now blocked on method call of C2) DB session S2 wakes up from idle state and starts work on SQL Timeline T6 Acquire connection Java thread on CPU On network Foreground on CPU
- 15. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 15 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool S2 executes SQL statement Timeline T6 Acquire connection Java thread on CPU On network Foreground on CPU
- 16. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 16 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool S2 sends result back to C2 S2 idle again TimelineT6 Acquire connection Java thread on CPU On network Foreground on CPU
- 17. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 17 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool T6 wakes up, gets results from C2 Timeline T6 Acquire connection Java thread on CPU On network Foreground on CPU
- 18. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 18 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool T6 releases C2 back to pool C2 available again for other browser’s threads Timeline T6 Acquire connection Release connection Java thread on CPU On network Foreground on CPU
- 19. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 19 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool Timeline T6 Acquire connection Release connection Java thread on CPU On network Foreground on CPU Sends results back to browser
- 20. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Reservation 20 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool Timeline T6 Acquire connection Release connection Java thread on CPU On network Foreground on CPU T6 ready for other browser request
- 21. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Timesharing Connections / Foregrounds 21 Timeline T6 Acquire Release S2 S2 exclusively in use by T6 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool
- 22. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Timesharing Connections / Foregrounds 22
- 23. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Are Durations Proportionally OK? 23 Time-in-database dominant? Time-on-wire dominant? Time-in-JVM dominant?
- 24. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Are Durations Proportionally OK? • Network latency between two servers inside same data center: – 0.5 ms – 1.5 ms (* 2) • Time to execute single-row database call: – 10 µs – 1ms • Time to descend/ascend persistency framework and JDBC layers: – 5 µs – 50 µs? (*2) 24
- 25. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Was Only Showing One Database Call 25 In real world, application will do multiple calls per reservation
- 26. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Pool At Full Capacity 26 JVM C1 C2 C3 JDBC S1 S2 S3 T1 T2 T3 T4 T5 T6 T7 T8 Connection pool What if thread T1 wants to do DB work?
- 27. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | What If 4th Thread Wants to Do Database Work? • Depends on how you have configured your connection pool A. Your pool is configured to dynamically grow (or, max # of connections is not yet reached) 1. New 4th connection created and handed out to thread T1 B. Your pool has reached max # of connections configured Two options: 2. Your thread will get Java exception 3. Your thread will be put to sleep, until connection becomes available 27
- 28. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Pool Configuration (UCP) 28 Console->Services->Data Sources
- 29. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Doing This Programmatically Yourself 29
- 30. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | In Real World: • Developers do not want: – Their threads to receive an exception from connection pool manager – Their thread to be put “on-hold” by connection pool manager • So we nearly always see connection pools that can grow to very large # of connections • We call these: dynamic connection pools – These can cause database to become CPU-oversubscribed 30 This potentially breaks the app This won’t It may cause “stuck threads”
- 31. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Topics • Web Application Architecture – Application Threads, Connection Pool, Connection Queueing • The Issue of Too Large Connection Pools – From CPU Oversubscription to Database Oversubscription • Sizing Your Connection Pool – %Idle-Time in Foreground Processes • Recommendations 31
- 32. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | #1 Issue in Real-World: Database Oversubscription • Caused by dynamic connection pools • Majority of customers that come to us with escalations, experience this • You might be having this problem without knowing it 32
- 33. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | CPU Oversubscription 33 DB Time User CPU Run-queue waits Contention in DB #cores in DB server Sys CPU # active sessions
- 34. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Database Oversubscription: Likely Scenario 34 # active sessions DB Time #cores User CPU Run-queue waits Contention in DB #cores in DB server Sys CPU Waits increase disproportionally under CPU starvation
- 35. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Database Oversubscription: Likely Scenario 35 # active sessions DB Time #cores CPU overhead in OS CPU overhead in DBMS Ever increasing waits in DBMS Adding sessions won't increase DB- CPUUser CPU Run-queue waits Status quo here Observation: CPU available! And lots of DB-waits DB-waits will lead you down wrong path Available CPU will invite you to further increase #threads and connection pool Contention in DB #cores in DB server Sys CPU Waits increase disproportionally under CPU starvation
- 36. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Only One Thing You Should Do 36 # active sessions #cores Useful work done Run-queue waits Contention in DB #cores in DB server Decrease and cap #connections to get more useful work done DB Time
- 37. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Example Database Oversubscription 37
- 38. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Moral of This Story • It is far better to have threads queue for pooled connection on application server Than, It is for database to be oversubscribed, or To have threads wait for DB-calls to be serviced terribly slow 38
- 39. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | The Big Question • What is the appropriate (maximum) size for my connection pool? • So that: – When load increases you won’t enter database oversubscription land – And application threads are willing to queue • Too small database has unused capacity • Too large database becomes inefficient in servicing your calls 39
- 40. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Network Network Database What Is Your Foreground Doing? Application End User Breakdown during connection reservation Time Line Query Query Update Update Update commit conn = Datasource.getConnection conn.close() Fast (simple SQL)SlowNoticeable
- 41. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Breakdown of Your Foreground Session Time 41 Breakdown during connection reservation Time Line SQL Application-server code Network 15% busy 85% idle 2% busy 98% idle We see this more in real world: very high %idle-time in foregrounds
- 42. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Implication of High %Idle Time in Foreground Session • So your SW-architecture keeps database session busy only 2% of time • What does that imply? – You would need 50 sessions to get one DB-core busy (= 50 connections in conn.pool) – If you have 32 cores in your database server: You would need 1600 sessions to get all cores busy • This is assuming that all your DB Time is DB CPU! – You likely need even more connections/sessions 42
- 43. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Pool Sizing • 0% FG idle time inside reservation 1 connection per core required • 80% FG idle time inside reservation 5 connections per core required • 90% FG idle time inside reservation 10 connections per core required • 95% FG idle time inside reservation 20 connections per core required • 98% FG idle time inside reservation 50 connections per core required • Appropriate connection pool size := * #cores X = % FG Idle Time inside reservation 43 100 100- X This drives connection pool size!
- 44. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Waiting in Mid-Tier Versus Waiting in DB-Tier • Again: You need to configure threads are willing to queue for connection otherwise you break the application • The formula is good starting point for appropriate connection pool size • Let’s plot that curve 44
- 45. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Basic Formula Upper Bound Connection Pool Size 10 Core Database Server 45 Sizing connection pool above curve just introduces more overhead Sizing connection pool under curve introduces server idle time
- 46. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Connection Pool Sizes in Real-World 46 What we often see
- 47. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Layered Software Architectures • Since these architectures use database as persistence layer (bit-bucket), all SQL is single row CRUD SQL • Which execute in tens of micro seconds • Causing high %Idle Times in foreground sessions • Requiring large connection pools 47
- 48. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Our Rule-of-Thumb: <10 Times Number of Cores 48 We assume your average % idle time inside reservation is anywhere between 0% and 90%
- 49. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Why Our Rule-of-Thumb Won't Always Work • If you have substantial %idle time inside connection reservation And you are not aware of that – Eg. A REST call is executed during connection reservation – Taking couple of milliseconds easily • Shrinking might disable full use of available CPU power on DB-server – As your %Idle Time during reservation is extremely high – Your only option then is to change the application and decrease %Idle Time during reservation 49
- 50. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | That’s All Very Interesting, But … • What the heck is the “%Idle Time Inside Reservation” for my application? 50
- 51. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | A Challenge For You • Ideally you’d want this to be instrumented by Java developers in their code • To create awareness with them too • However, can you as DBA get a clue? 51
- 52. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Finding %Idle Time Inside Reservation • Assuming you’re still in the safe zone – Use test-system, or “quiet” prod-system • Just do SQL-trace of one of connection-pool sessions for minute during representative workload and investigate trace-file • Likely you’ll be able to spot “acquire” and “release” events via some repetitive pattern. For example: – “Release” typically would be XCTEND (commit or rollback) – “Acquire” typically starts with “timestamp” during quiet period Or immediately after XCTEND Or you can spot it via some initialization statement 52
- 53. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Example Trace File 53 Acquire connection object Release Acquire Release
- 54. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Approximating %Idle Time Inside Reservation • Investigate one acquire-release block in trace-file – Determine DB-time by summing all ”e=“ values (dep=0 only) – Determine Elapsed time from difference between first and last “tim=“ values • Add e-value from first tim value, as tim values represent “time when completed” • %Idle-Time-inside-reservation is: ((Elapsed – DB-Time)/Elapsed) * 100 54
- 55. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Approximating %Idle Time Inside Reservation 55 Difference is: 2,237us Difference is: 2,237us + 17us = 2,254us Sum is: 1,053us ((2,254 – 1,053)/2,254)*100% = 53% approximate idle time inside reservation Connection pool size about 2X number of cores
- 56. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | A Word on Batch Programs • Context so far has been: web applications with many browser users • Batch programs: – Developers usually create some kind of do-it-yourself parallelism – Configurable number of threads to get work done – We see comparable behavior of these threads wrt. connection pool usage • They loop and do a transaction per iteration • For each transaction they acquire/release a connection – Same math applies 56
- 57. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | SmartDB Doesn’t Require Large Connection Pool • SmartDB uses database as processing engine: database calls are far from single row CRUD SQL call typically does much work in one go • Which execute in couple of milliseconds • Causing low %Idle Times in foreground sessions • Requiring small connection pool 57
- 58. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Recommendations Connection Pool Sizing • It all starts by knowing your average %Idle Time of foregrounds 58
- 59. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Recommendations Connection Pool Sizing • It all starts by knowing your average %Idle Time of foregrounds • Assuming CPU-bound, formula is a good starting point • Set minimum/maximum/initial # of connections, all to same value – And configure threads to wait for connection to become available 59
- 60. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Recommendations Connection Pool Sizing • It all starts by knowing your average %Idle Time of foregrounds • Assuming CPU-bound, formula is a good starting point • Set minimum/maximum/initial # of connections, all to same value – And configure threads to wait for connection to become available • As DBA you can maybe decrease network-time component? 60
- 61. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Recommendations Connection Pool Sizing • It all starts by knowing your average %Idle Time of foregrounds • Assuming CPU-bound, formula is a good starting point • Set minimum/maximum/initial # of connections, all to same value – And configure threads to wait for connection to become available • As DBA you can maybe decrease network-time component? • On your next application development effort try to be aware, or better in control, of Acquire/Release cycles, and (Java) code execution during these cycles • Your solution should minimize %Idle Time of foregrounds during reservation 61
- 62. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Questions? 62 Twitter: @ToonKoppelaars
- 63. Copyright © 2019,Oracle and/or its affiliates. All rights reserved. | 63
- 64. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | 64