Real-World Performance - 14 - Large Dynamic Connection Pools - Part 2

In this video, listen and watch Andrew Holdsworth, Vice President of Oracle Database Real-World Performance at Oracle Corporation, as he explains how large dynamic connection pools affect performance.

Copyright © 2014 Oracle and/or its affiliates. Oracle® is a registered trademark of Oracle and/or its affiliates. All rights reserved. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the "Materials"). The Materials are provided "as is" without any warranty of any kind, either express or implied, including without limitation warranties of merchantability, fitness for a particular purpose, and non-infringement.

[philh]

On oracle 19c, what is the maximum thread:cpu core ratio that is recommended? I'm trying to convince my enterprise to ditch the dynamic pool settings and reduce the connection pool size. Currently, we have min set to 10 and max to 2500 across many jvms. Some JVMs are doing different workloads and have fewer available connections in the pool. All in all, our jvms have the potential of opening over 16, 000 connections to our database. In reality, our database has 16 cpus running smt8 for a total of 128 cpu threads. That is a ratio of 125:1. To say we have a contention and waits is the biggest understatement of the century. CPU:Quantum is usually pretty high up (top 3 wait events) the list of wait events amongst latches and other things. The irony is that our database swap locks at 3500 connections and we never even get close to Killing the connections has no effect because the system becomes unresponsive and we must do an HACMP failover to recover. We have created monitors that check the total connections against the database and splash/disable our web app when connections exceed 500 as that is when we start seeing impacts to response times. Our app has about a 70% availability because of this. I have been trying to convince our teams that we must make the connections static and greatly reduce the pool in order to eliminate CPU waits. At most they will reduce is going from 16000 to 8000, but they clearly don't understand the fact we kill our app at 500 connections and would never reach 8000. As we have 128 available CPU threads, I would like to cap our JVMs at a static total connection pool size that is at most~ 4:1 ratio. My colleagues are convinced this will completely kill our performance. Furthermore, because of the pools are dynamic between 10...2500, whenever we come out of application splash/enable our app, the connection storm that results nukes the database into oblivion. I really need some oracle documentation that states optimal jdbc pool thread count : db cpu threads ratio. Further, I need documentation that states that dynamic pools are terrible and min/max should match, and also specifying how timeouts should be configured. My word is not being taken seriously.

[vsevolodafanassiev]

Thanks for posting, this is very interesting. So the idle connections aren't harmless after all.
The same result would be achieved if JDBC driver re-used the same connections over and over again.
Let's say a connection pool has 50 connections, we number them #1, #2, ..., #50. A request comes to allocate a connection.
If connection #1 is available it should be allocated, if #1 has already been allocated to another request then the pool should allocate connection #2, and so on. Oracle should program JDBC driver to work this way.