Limiting and queueing the database connections
In mariaDB, is it possible to do something like this?
- limit the number of connections (like in mysql, as explained in https://dba.stackexchange.com/a/35248/133693 )
- 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 PROCESSLISTreturns 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.
In addition to the https://dba.stackexchange.com/a/35248/133693 inherited from MySQL, https://mariadb.com/kb/en/grant/#resource-limit-options limits can include
MAX_STATEMENT_TIME(in seconds), for each query.
There is also https://mariadb.com/kb/en/server-system-variables/#wait_timeout and https://mariadb.com/kb/en/server-system-variables/#interactive_timeout to ensure that idle connections terminate.
https://mariadb.com/kb/en/server-system-variables/#max_session_mem_used limits the memory usage of each connection.
Having a total https://mariadb.com/kb/en/server-system-variables/#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
*_timeoutoptions there is less chance of hitting the limit unexpectedly.