The question:
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.
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
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