Limiting and queueing the database connections

All we need is an easy explanation of the problem, so here it is.

In mariaDB, is it possible to do something like this?

  • limit the number of connections (like in mysql, as explained in this SO answer)
  • set up a queue for all the other connection attempts, and avoid triggering User ** already has more than 'max_user_connections' active connections

If something like this is possible, is it even advisable to set things up in this way?

Real case scenario is that something is causing one of our servers to clog up, with mariaDB taking up as much as 80% of CPU and about the same percentage of RAM. SHOW FULL PROCESSLIST returns a ridiculous amount of connections from one user (+50), while the other users have 3-5 simultaneous connections. If the offending user is temporarily blocked, everything returns to normal.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

In addition to the old resource limits inherited from MySQL, MariaDB grant limits can include MAX_STATEMENT_TIME (in seconds), for each query.

There is also wait_timeout and interactive_timeout to ensure that idle connections terminate.

max_session_mem_used limits the memory usage of each connection.

Having a total max_connections within the scope of the server’s ability is a good move too.

There’s no separate queue option, however hopefully with the *_timeout options there is less chance of hitting the limit unexpectedly.

Method 2

max_connections sets a limit. back_log, in theory, provides a queue of pending connections.

But, in reality, you should change your clients to be less demanding. If you let hundreds of connections in, MariaDB starts stumbling over itself. Have you ever been in a small convenience store with a hundred other customers?

If it is just one offending client, see max_user_connections.

80% of RAM is normal. 80% of CPU may not be good. Find the slowest queries and work on speeding up (or eliminating) them.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply