Opening new connections increase the cpu usage of database generating poor response time

[juanico18]

We have an application running in 2 or 6 ec2 c5x.large AWS instances.
Our configuration is:
4 worker 5 threads

Usually we use two instances but in a specific time we are sending a push notifications to users what ends on mmany user entering at same time generating this situation.

We have 8 instances with 4 workers and 5 threads but number of database connections is around 40. But in 1 minute we pass from 50-60 connections to 120 connections. Generating a peak of database cpu usage and ending on a poor response time.

So we are trying to prevent this problem. We assume that as the database cpu is busy generating the new connections our queries are in waiting status generating a problem on response time.

Once connections are created and number request is not too big the response time goes back to his normal result.

Any advise on this?

Thanks

[dentarg]

Scale up your database to match the amount of requests you take on?

Or scale down the web app so it does not take on more work than the database can handle?

[juanico18]

Hi I am testing to scale down.
I have reduce to 5 instances and today we are testing scale down to 4 instances.
4 instances with 4 workers and 5 threads -> 80 available connections

[juanico18]

@dentarg After scale down the response time improves a bit but not enough.
We have tested even just maintain just 2 instances with 4 workers and 5 threads.
Problem here is that at the moment we receive the peak of requests the 40 connections available appears as used and the database CPU goes to 100% so it should not be just a problem of create connections.

On other test we changed the configuration of workers and threads to 8 workers and 20 threads. But our application during all the day are under the number of 40 but at the peak of requests goes to 320.

So how can we try to solve the problem. Have 40 connections generates 100% of usage of database during 10min
Have 320 available also generates 100% of usage of database during 10min but we pass to use 40 connections to 320 at that time.

[dentarg]

It is too hard to answer, we know too little what's happening in your app. Why does the database go to 100% CPU? Does that make requests take longer to complete?

[juanico18]

btw. Is it normal that almost the 40 connections available appears with status idle?