MariaDB "Too many connections"

Support
Log in to reply
  • M

    Hello!
    I have an install of Theopenem at a school system with five com servers in a cluster and 4178 endpoints. Every morning from around 7:45-9, it becomes unusably slow, with weird errors and access denied pages. After this period every morning it's fine, with everything checking in and responding normally. Digging into the logs, MariaDB is having a fit, giving this "too many connections" error up to around a dozen times per second. Is there anything I can do to lighten the load on the database? What causes this?

    ce8265fb-1ba7-4639-941a-426457ac0b05-image.png
    2f696491-d060-4325-b838-25bc93dd407e-image.png
    a7058854-b41a-4e98-b8d4-68b43406c85a-image.png

    0
  • T

    I would start by increasing the memory available to Mariadb. You want it to be about 80% of your available memory to get as much of the working set into memory as possible.

    In c:\program files\mariadb\data\my.ini
    Change or add this line.
    innodb_buffer_pool_size=12GB

    make sure it's under the [mysqld] section and not the [client] section.

    Then restart mariadb service.

    M 2 Replies 1
  • M

    @theopenem_admin This solved the issue, thank you!

    0
  • M

    @theopenem_admin The issue is back, unfortunately, it just took a couple days to show up again. I set innodb_buffer_pool_size=10GB, and it is using 5.2GB of memory. Should it be using that variable's worth of memory?

    The results of show processlist; in MariaDB are here.

    0
  • M

    The errors in Event Viewer are pretty much constant, with over 40 per second. They're accompanied by failed scheduled tasks, and this in the logs:

    2024-12-19 12:41:45,199 [Worker #19] ERROR Hangfire.Server.Worker 10 state change attempt(s) failed due to an exception, moving job to the FailedState
MySqlConnector.MySqlException (0x80004005): Too many connections
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at MySqlConnector.Core.ServerSession.<ConnectAsync>d__68.MoveNext() in /_/src/MySqlConnector/Core/ServerSession.cs:line 385
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at MySqlConnector.Core.ConnectionPool.<GetSessionAsync>d__10.MoveNext() in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 111
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at MySqlConnector.Core.ConnectionPool.<GetSessionAsync>d__10.MoveNext() in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 140
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at MySqlConnector.MySqlConnection.<CreateSessionAsync>d__115.MoveNext() in /_/src/MySqlConnector/MySqlConnection.cs:line 819
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at MySqlConnector.MySqlConnection.<OpenAsync>d__27.MoveNext() in /_/src/MySqlConnector/MySqlConnection.cs:line 407
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at MySqlConnector.MySqlConnection.Open() in /_/src/MySqlConnector/MySqlConnection.cs:line 365
at Hangfire.MySql.MySqlStorage.CreateAndOpenConnection()
at Hangfire.MySql.MySqlStorage.UseConnection[T](Func`2 func)
at Hangfire.States.BackgroundJobStateChanger.GetJobData(StateChangeContext context)
at Hangfire.States.BackgroundJobStateChanger.ChangeState(StateChangeContext context)
at Hangfire.Server.Worker.TryChangeState(BackgroundProcessContext context, IStorageConnection connection, IFetchedJob fetchedJob, IState state, String[] expectedStates, CancellationToken initializeToken, CancellationToken abortToken)
    0
  • T

    Not really sure what else to try. You could try adding the following to your connection string in web.config for the clientapi to try and close connections faster.

    ConnectionIdleTimeout=5

    <add name="toems" connectionString="server=localhost;port=3306;database=theopenem;Uid=root;Pwd=password;Allow User Variables=True; ConnectionIdleTimeout=5;" providerName="MySql.Data.MySqlClient" />

    or disable pooling

    Pooling=false

    <add name="toems" connectionString="server=localhost;port=3306;database=theopenem;Uid=root;Pwd=password;Allow User Variables=True; Pooling=false;" providerName="MySql.Data.MySqlClient" />
    M 1 Reply 0
  • M

    @theopenem_admin I'll try these one at a time and report back. Thank you!

    0
  • T

    You could also just try increasing the max connections.

    In the my.ini file just add something like
    max_connections = 1024

    you can check the existing max_connections with a query:
    SHOW VARIABLES LIKE "max_connections";

    0