.net5.0 Web API
As there were few API's which were extensively being used by the clients, all over performance was being deteriorated. So we decided to convert those selected API's to Async/Await to reduce the load at IIS.
After implementing the same, we got better performances at about 100 parallel requests (via jMeter) in our local environment. But as soon as we increased the load to 200 requests, it started giving the below error:
"The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

We realized that we didn't exactly improve the performance but we shifted the bottleneck from IIS to DB.
To solve this we tried changing the connection string (My SQL Server) properties i.e Max Pool size, ConnectRetryCount, ConnectRetryInterval etc. which definitely worked and gave us better results but everything came with a trade-off. Increasing Max Pool size would utilize DB server resources.
Also, we can never predict how many parallel requests would come to our API i.e if my max pool size is 400, what if 450 parallel requests arrives, it would still break.

We identified few options like using SemaphoreSlime, to limit the number of requests reaching the DB which would not open too many connections and hence restricting the timeout errors. But here we are not able to utilize the async nature of my api to its fullest.

Is there a optimized solution to this, or are we missing something?
Also, how safe is SemaphoreSlime if we choose to use it?

CodePudding user response：

IIS and Kestrel can be configured to limit the maximum number of connections, so why to use your own home made solution?

You would can achieve same goal by increasing the connection timeout instead of using SemaphoreSlim.

If you want to increase the throughput capacity of you app you should start optimizing your queries, if this is not enough you should consider increasing the database server hardware resources.

If you have 400 max pool size and 450 concurrent requests arrive, it would not necessarily break. If there is no available connections in the pool, Connection.OpenAsync will wait until a connection is available. So if the queries are fast enough, it will not time out.

CodePudding user response：

Here are two suggestions that could improve the performance of the system:

1. Make the work shorter so that there is less parallel work:

   • Use a stored procedure that does the work locally rather that moving data between the api and the db
   • Scale the database up,
   • Optimize the queries
   • Confirm indexes
   • Use caching
   • etc.

2. Accept the requests but put them on a queue and make the consumer of the queue process them in batches rather than one by one.