2

I am using aspnetboilerplate core and entityframework core.

I have 1 request entity framework core which is big:

var user = _userRepository.GetAll()
            .Include(u => u.FavoriteMeals).ThenInclude(c => c.Category)
            .Include(u => u.FavoriteRestaurants).ThenInclude(c => c.CategoryMaster)
            .Include(u => u.FavoriteSpecialityMeals).ThenInclude(c => c.Speciality)
            .Include(u => u.FavoriteSuperBookings).ThenInclude(c => c.Boooking)
            .Include(u => u.FavouritePlaces).ThenInclude(c => c.Place)
            .Include(u => u.Followers).ThenInclude(u => u.User1)
            .Include(u => u.Followings).ThenInclude(u => u.User2)
            .FirstOrDefault(r => r.Id == id);

I use AWS RDS MySQL. And I can see that the number of connections to the database is raising to 58 when I call the API through swagger.

Then I would like to know:

Is it normal to have arround 60 connections to the database? Then I plan to use this api connected to a mobile app.

The limit of max connections on aws is per mobile or global. I mean if two mobiles are connected to my api and calls the same api function are they gonna be blocked because the number of max connections was reached?

How can I optimize this?

Thanks,

///// EDIT

I have discovered that I have a lot of requests which are kept in slepp state.

How can I fix this? My code is a s below:

builder.UseMySql(connectionString);
        //todo
        builder.EnableSensitiveDataLogging(true);
        builder.EnableDetailedErrors(true);

Please find below my requests result to find the sleep state

enter image description here

lauxjpn
  • 4,749
  • 1
  • 20
  • 40
dalton5
  • 915
  • 2
  • 14
  • 28
  • one user causes 58 connections? redesign your app – nbk Aug 28 '20 at 20:50
  • Ok I found some more clues. MySql keeps 1 connection and a lot of requests are in sleeping state and stays in threads.I see this here: select id, user, host, db, command, time, state, info from information_schema.processlist; They are not killed. What am I missing? – dalton5 Aug 28 '20 at 23:01
  • check if you have some locks – nbk Aug 28 '20 at 23:09
  • How can I see this? – dalton5 Aug 28 '20 at 23:11
  • see https://stackoverflow.com/questions/11034504/show-all-current-locks-from-get-lock – nbk Aug 28 '20 at 23:17
  • I tried the request show open tables where In_Use > 0 ; and nothing appears. – dalton5 Aug 28 '20 at 23:32
  • see https://dev.mysql.com/doc/refman/8.0/en/thread-information.html and check if they have still open and for how long – nbk Aug 28 '20 at 23:37

1 Answers1

2

The reason many connections are open with a sleep status is, that by default, MySqlConnector which is used by Pomelo, has connection pooling enabled (Pooling=true), with a max. of 100 connections per connection string (MaxPoolSize=100). See MySQL .NET Connection String Options for all default settings.

So having around 60 connections opened can easily happen, when either 60 people use the app API in parallel, or e.g. when 3 different connection strings are used, each with 20 users in parallel.

Once those connections have been opened, they will effectively stay open for a long time by default.

With ConnectionLifeTime=0 as the default setting, they will never be explicitly closed by MySqlConnector's connection pooling management. However, they will always be closed after a number of seconds specified by the MySQL system variable wait_timeout. But since this variable is set to 28800 by default, it will take 8 hours before once created pooled connections are being closed by MySQL (independent of how long they were in a sleep state).

So, to lower the number of parallel connections, either disable Pooling (radical method with some performance implications if the server is not hosted locally) or manage their lifetime through the MaxPoolSize and ConnectionLifeTime connection string parameters.

In the screenshot you provided, you can see that the host varies in its address and port, but these are the address and outgoing port of the connecting client.

However, what I have written above does also apply here. So if you are connecting from multiple web servers (clients from the viewpoint of MySQL) to your database server, by default, each web server will manage its own connection pool and keep up to 100 connections open.

For example, if you have a load balancer setup, and multiple web servers behind it that may each execute database queries, or you run your API with some server-less technology like AWS Lambda, where your API might be hosted on many different web servers, then you might end up with multiple connection pools (one for each connection string on each web server).

My issue is that is only for one user connected. I am using Task.WhenAll() in my app which launches several api requests in parallel and I also use asynchronous methods in my api which creates some new threads.

If your client sends multiple web request (e.g. REST) to your API hosted on multiple web servers/AWS Lambda, then MySQL will at least need that many connections opened at the same time.

The MySqlConnector connection pooling will keep those connections open by default, after they were used (up until 100 connections per web server).

You will end up with the following number of sleeping database connections (assuming the connection string always stays the same on each web server):

number-of-parallel-requests-per-client * number-of-clients * number-of-webservers

However, the max. number of connections kept open will (by default) not be higher than:

number-of-webservers * 100

So if your app executes 20 requests in parallel, with each of them establishing a database connection from the API (web server) to the database server, then depending on your API hosting scenario, the following will happen:

  • 3 Web Servers with Load Balancer: If you run your app often enough, the load balancer will distribute your requests to all 3 web servers over time. So each of the 3 web servers will keep around 20 connections open to your database server. You end up with 60 connections, as long as only one client executes these requests at the same time. If a max. of 4 clients run 20 requests in parallel, you will end up with 80 connections being kept open per web server over time, so a total of 3 * 80 = 240 total sleeping database connections.

  • Serverless technology like AWS Lambda: The same as in the previous example applies to AWS Lambda, but the number of web servers is infinite (in theory). So your might end up exceeding MySQL's max_connections setting pretty fast, if AWS decides to distribute the API calls to many different web servers.

Advice how to configure connection pooling

If you run your API on a fixed number of web servers, you might want to keep Pooling=true and set MaxPoolSize to a values, that number-of-parallel-requests-per-client * number-of-webservers will always be lower than max_connections, but if possible higher than typical-number-of-parallel-clients * number-of-parallel-requests-per-client. If that is not plausible or possible, consider setting Pooling=false or set MaxPoolSize to a number not higher than max_connections / number-of-webservers.

If you run your API on a serverless technology like AWS Lambda, either set Pooling=false or set MaxPoolSize to some low value and ConnectionLifeTime to some low value greater than zero. I would just set Pooling=false, because it is way easier to disable connection pooling than to effectively fine tune connection pooling for a serverless environment.

lauxjpn
  • 4,749
  • 1
  • 20
  • 40
  • Thanks for the détails. My issue is that is only for one user connected. I am using task.whenall in my app which launches several api requests in parallel and I also use asynchronous methods in my api which creates some new threads. What I'd not understand is why the connection changes with different port and host for each threads? My connectionstring does not change and only one user is connected. – dalton5 Aug 29 '20 at 08:21
  • So host and port are actually the client address and port, and not the one of your MySQL server, so they are not a general issue here. Though of course, if you run connections from different clients each client will manage its own connection pool. I updated my answer accordingly. – lauxjpn Aug 29 '20 at 09:53
  • I also extended the answer with two typical scenarios and gave some concrete configuration advice. – lauxjpn Aug 29 '20 at 10:34
  • Thank you very much for your time and details. I use AWS lambda. Then I will disable pooling. And try. I hope I will not have performance issues. – dalton5 Aug 29 '20 at 15:19
  • I found this which expalins the issue and provide an aws solution https://aws.amazon.com/blogs/compute/using-amazon-rds-proxy-with-aws-lambda/ – dalton5 Aug 29 '20 at 17:16
  • Using a database proxy for AWS Lambda should be a good choice. However, you will probably need to disable connection pooling in the connection string here as well, if the proxy manages its own pool of connections. – lauxjpn Aug 29 '20 at 20:27