Application connections storm slow downs the DB response Merged

I've been assisting with an ecommerce site. During typical days, let's say non-peak/low volume of orders, the DB response is always good.

However, the DB response time slows down for a few seconds, notably during peak season/high amount of orders.

BOOM !! it will ultimately happen whenever an application connection storm occurs !!

Let me explain a little about how DB/App is configured for this :

1. DB server is configured with high resources in terms of memory/CPU etc ..

NUM_POOLAGENTS and NUM_INITAGENTS with a value of 1000

2.The database had 168 established connections (14 JVMs x 12 connections).

Now with the JVMs' WebContainer pool completely saturated, the number of connections surged all the way to 600( 14 JVMs * 42 WebContainer threads = 600)

A connection storm was created in a matter of a second, and DB was unable to assign the delta new connections 360 on the fly. This resulted spikes in DB connection and reduced DB response time.

As we know, creating a connection has a cost. The database needs to handle the tcp connection, allocate memory and create the agent. If a large number of connection requests is received in a very short period of time, this can bog down the performance of the database to a point that it can appear to be unresponsive.

If we can play around with DB parameters like NUM_POOLAGENTS and NUM_INITAGENTS by keeping the values of 1000 , automatic etc ..it doesn't help during the connection storm . The more connections to the database server, the slower the DB response is, which in turns drives even more new connections to the database.

During connection storm , I've noticed a lot of outbound connections with NULL values which means that most threads waiting on the outbound Web Service call

Example for connection storm :

Solution :

Nothing can be done at the database layer; just increase the number of JVM connections to the database during peak season, when the highest value is targeted.

Let's say in this scenario , just increase the number of established connections from 168 to 600 (14 JVMs x 12 connections) (14 JVMs x 42 connections). And maintain the largest number of connections in the database to manage the application storm.

Once peak season is over, re-configure the JVM connections to normal.