.Net Core connection pool exhausted (postgres) under heavy load spike, when new pod instances are created

user_1448545 0
Viewed 1533

I have an application which runs stable under heavy load, but only when the load increase in graduate way. I run 3 or 4 pods at the same time, and it scales to 8 or 10 pods when necessary. The standard requests per minute is about 4000 (means 66 req-per-second per node, means 16 req-per-second per single pod).

There is a certain scenario, when we receive huge load spike (from 4k per minute to 20k per minute). New pods are correctly created, then they start to receive new load.

Problem is, that in about 10-20% of cases newly created pod struggles to handle initial load, DB requests are taking over 5000ms, piling up, finally resulting in exception that connection pool was exhausted: The connection pool has been exhausted, either raise MaxPoolSize (currently 200) or Timeout (currently 15 seconds)

Here goes screenshots from NewRelic:

single pod not being able to handle initial load - example 1

single pod not being able to handle initial load - example 2

I can see that other pods are doing well, and also that after initial struggle, all pods are handling the load without any issue.

Here goes what I did when attempting to fix it:

  1. Get rid of non-async calls. I had few lines of blocking code inside async methods. I've changed everything to async. I do not longer have non-async methods.

  2. Removed long-running transactions. We had long running transactions, like this:

    • beginTransactionAsync
    • selectDataAsync
    • saveDataAsync
    • commitTransactionAsync

which I refactored to:

- selectDataAsync
- saveDataAsync // under-the-hood EF core short lived transaction

This helped a lot, but did not solve problem completely.

  1. Ensure some connections are always open and ready. We added Minimum Pool Size=20 to connection string, to always keep at least 20 connections open. This also helped, but still sometimes pods struggle.

Our pods starts properly after Readiness probe returns success. Readiness probe checks connection to the DB using standard .NET core healthcheck.

Our connection string have MaxPoolSize=100;Timeout=15; setting.

I am of course expecting that initially new pod instance will need some spin-up time when it operates at lower parameters, but I do not expect that so often pod will suffocate and throw 90% of errors.

Important note here: I have 2 different DbContexts sharing same connection string (thus same connection pool). Each of this DbContext accesses different schema in the DB. It was done to have a modular architecture. DbContexts never communicate with each other, and are never used together in same request.

My current guess, is that when pod is freshly created, and receives a huge load immediately, the pod tries to open all 100 connections (it is visible in DB open sessions chart) which makes it too much at the beginning. What can be other reason? How to make sure that pod does operate at it's optimal performance from very beginning?

Final notes:

  • DB processor is not at its max (about 30%-40% usage under heaviest load).
  • most of the SQL queries and commands are relatively simple SELECT and INSERT
  • after initial part, queries are taking no more than 10-20ms each
  • I don't want to patch the problem with increasing number of connections in pool to more than 100, because after initial struggle pods operates properly with around 50 connections in use
  • I rather not have connection leak because in such case it will throw exceptions after normal load as well, after some time
  • I use scoped DbContext, which is disposed (thus connection is released to the pool) at the end of each request

EDIT 25-Nov-2020

My current guess is that new created pod is not receiving enough of either BANDWITH resource or CPU resource. This reasoning is supported by fact that even those requests which DOES NOT include querying DB were struggling.

Question: is it possible that new created pod is granted insufficient resources (CPU or network bandwidth) at the beginning?

EDIT 2 26-Nov-2020 (answering Arca Artem)

App runs on k8s cluster on AWS. App connects to DB using standard ADO.NET connection pool (max 100 connections per pod).

I'm monitoring DB connections and DB CPU (all within reasonable limits). Hosts CPU utilization is also around 20-25%.

I thought that when pod start, and /health endpoint responds successfully (it checks DB connections, with simple SELECT probe) and also pod's max capacity is e.g. 200rps - then this pod is able to handle this traffic since very first moment after /health probe succeeded. However, from my logs I see that after '/health' probe succeed 4 times in a row under 20ms, then traffic starts coming in, few first seconds of pod handling traffic is taking more than 5s per request (sometimes even 40seconds per req).

I'm NOT monitoring hosts network.

ef-core-3.1 kubernetes-pod .net-core postgresql
2 Answers

At this point it's just a speculation on my part without knowing more about the code and architecture, but it's worth mentioning one thing that jumps out to me. The health check might not be using the normal code path that your other endpoints use, potentially leading to a false positive. If you have the option, use of a profiler could help you pin-point exactly when and how this happens. If not, we can take educated guesses where the problem might be. There could be a number of things at play here, and you may already be familiar with these, but I'm covering them for completeness sake:

First of all, it's worth bearing in mind that connections in Postgres are very expensive (to put it simply, it's because it's a fork on the database process) and your pods are consequently creating them in bulk when you scale your app all at once. A relatively considerable time is needed to set each one up and if you're doing them in bulk, it'll add up (how long is dependent on configuration, available resources..etc).

Assuming you're using ASP.NET Core (because you mentioned DbContext), the initial request(s) will take the penalty of initialising the whole stack (create min required connections in the pool, initialise ASP.NET stack, dependencies...etc). Again, this will all depend on how you structure your code and what your app is actually doing during initialisation. If your health endpoint is connecting to the DB directly (without utilising the connection pool), it would mean skipping the costly pool initialisation resulting in your initial requests to take the burden.

You're not observing the same behaviour when your load increases gradually possibly because usually these things are an interplay between different components and it's generally a non-linear function of available resources, code behaviour...etc. Specifically if it's just one new pod that spun up, it'll require much less number of connections than, say, 5 new pods spinning up, and Postgres would be able to satisfy it much quicker. Postgres is the shared resource here - creating 1 new connection would be significantly faster than creating 100 new connections (5 pods x 20 min connections in a pool) for all pods waiting on a new connection.

There are a few things you can do to speed up this process with config changes, using an external connection pooler like PgBouncer...etc but they won't be effective unless your health endpoint represents the actual state of your pods.

Again it's all based on assumptions but if you're not doing that already, try using the DbContext in your health endpoint to ensure the pool is initialised and ready to take connections. As someone mentioned in the comments, it's worth looking at other types of probes that might be better suited to implementing this pattern.

user_14173110

I found ultimate reason for the above issue. It was insufficient CPU resources assigned to pod.

Problem was hard to isolate because NewRelic APM CPU usage charts are calculated in different way than expected (please refer to NewRelic docs). The real pod CPU usage vs. CPU limit can be seen only in NewRelic kubernetes cluster viewer (probably it uses different algorithm to chart CPU usage there).

What is more, when pod starts up, it need a little more CPU at the beginning. On top of it, the pods were starting because of high traffic - and simply, there was no enough of CPU to handle these requests.

user_14485450
Related