Experiancing connection pool problems in production

1

Hi,
I have high intensive writes to the database.
In my repositories which are extending RepositoryBase I’m am accessing the database as the following: (Note, I’m not disposing anything here…)

await Db.SaveAsync(relatedEntity);

At the peak, I am getting 20 requests like that in a second.

this is how I inject my database, auth, and own repositories.

 container.Register<IDbConnectionFactory>(c =>
   new OrmLiteConnectionFactory(
        AppSettings.Get<string>("MysqlConnection"), MySqlDialect.Provider));

container.Register<IAuthRepository>(c => 
   new OrmLiteAuthRepository<AppUserAuth, AppUserAuthDetails>(container.Resolve<IDbConnectionFactory>())
     { UseDistinctRoleTables = true });

container.RegisterAutoWired<PostRepository>().ReusedWithin(ReuseScope.None);
container.RegisterAutoWiredAs<ResearchRepository, IResearchRepository>().ReusedWithin(ReuseScope.None);
container.RegisterAutoWiredAs<TimeLineRepository, ITimeLineRepository>().ReusedWithin(ReuseScope.None);
container.RegisterAutoWiredAs<RelatedRepository, IRelatedRepository>().ReusedWithin(ReuseScope.None);
container.RegisterAutoWiredAs<SearchEngineResultRepository, ISearchEngineResultRepository>().ReusedWithin(ReuseScope.None);

This is the error I’m experiancing

"Uncaute exception MySqlException { Number: 0, SqlState: null, Code: 0, ErrorCode: -2147467259, TargetSite: MySql.Data.MySqlClient.Driver GetConnection(), StackTrace: "   at MySql.Data.MySqlClient.MySqlPool.GetConnection()
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at ServiceStack.OrmLite.OrmLiteConnection.Open() in C:\BuildAgent\work\27e4cc16641be8c0\src\ServiceStack.OrmLite\OrmLiteConnection.cs:line 91
   at ServiceStack.OrmLite.OrmLiteConnectionFactory.OpenDbConnection() in C:\BuildAgent\work\27e4cc16641be8c0\src\ServiceStack.OrmLite\OrmLiteConnectionFactory.cs:line 96
   at ServiceStack.RepositoryBase.get_Db() in C:\BuildAgent\work\3481147c480f4a2f\src\ServiceStack\ILogic.cs:line 43
   at EyezServices.Client.Repository.ResearchRepository.HasMatch(Int64 emailId, String token) in /src/EyezServices.Client/Repository/ResearchRepository.cs:line 256
   at EyezServices.Client.Services.MetchService.Any(HasMatchRequest matchRequest) in /src/EyezServices.Client/Services/MatchService.cs:line 35
   at ServiceStack.Host.ServiceRunner`1.ExecuteAsync(IRequest req, Object instance, TRequest requestDto) in C:\BuildAgent\work\3481147c480f4a2f\src\ServiceStack\Host\ServiceRunner.cs:line 150", Message: "error connecting: Timeout expired.  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.", Data: [], InnerException: null, HelpLink: null, Source: "MySql.Data", HResult: -2147467259 }"

Thanks

2

It says your MySql connection pool is full. Here are connection pooling options for MySql ADO.NET provider.

Also instead of inheriting RepositoryBase copy it into your project:

public abstract class RepositoryBase : IDisposable, IRepository
{
    public virtual IDbConnectionFactory DbFactory { get; set; }

    IDbConnection db;
    public virtual IDbConnection Db => db ??= DbFactory.OpenDbConnection();

    public virtual void Dispose() => db?.Dispose();
}

and put a breakpoint (or verify with logging) to make sure Dispose() is being called in everywhere you’re using it.

3

Thanks.

  1. what is the difference between copying and inheritng from? can it make troubles?
  2. can you review my scoping in the dependency injection I have posted?
    Thanks
4

If you use a local copy, you can modify it and add any logging, set breakpoints etc.

I’d use AddTransient instead as it’s syntax compatible with .NET Core’s IOC making it easy to move dep registrations to/from ASP .NET Core’s & ServiceStack’s IOC.

5

And this scoping is ok?

container.Register<IDbConnectionFactory>(c =>
   new OrmLiteConnectionFactory(
        AppSettings.Get<string>("MysqlConnection"), MySqlDialect.Provider));

container.Register<IAuthRepository>(c => 
   new OrmLiteAuthRepository<AppUserAuth, AppUserAuthDetails>(container.Resolve<IDbConnectionFactory>())
     { UseDistinctRoleTables = true });
6

Scoping’s fine, they should both be singletons, consider using the injected c container instead:

container.Register<IAuthRepository>(c => 
   new OrmLiteAuthRepository<AppUserAuth, AppUserAuthDetails>(c.Resolve<IDbConnectionFactory>())
    { 
        UseDistinctRoleTables = true 
    });
7

Hi,
Thanks for the tips.
So now I see when I run show status like 'Conn%'; that there are 111 connections.
The Dispose object is hitting, but not all the times and it is very randomly.
I went ahead and switched all the database calls to using (IDbConnection db = dbFactory.OpenDbConnection())
and still, I have a lot of connections opened.
The requests are getting slower and slower. a simple api call is getting result after 1 minute…
What else can I do?
Thanks

8

Try to create a repro of when dispose isn’t getting called so you can identify why.

If all connections are in a using then should be getting returned to the pool. It’s not unusual to have a large number of open connections in the pool within the max pool size, that’s the what it does.

You can try running the same query from the command-line to workout whether the query is only slow in the ADO.NET provider or if all MySql queries are slow.

9

After reading more and more about it I realized that I need to increase the pull size. I have batch operation that needs more connections in paralel.
Thanks

1 Like