0

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.

2
  • Additional information request, please. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND very helpful OS information, includes - htop OR top for most active apps, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, for server workload tuning analysis to provide suggestions. May 11 at 18:55
  • Our suggestions will assist with reduced time to complete query requests. WAIT time will be less. May 11 at 21:31

2 Answers 2

2

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.