Reference: https://mariadb.com/kb/en/server-system-variables/#max_user_connections Is the abuse of the nature where you are rejecting unwelcome connection attempts from random sources? If so, that does not relate to max_user_connections which only increments when a connection is successful and sustained to mariadb. The limit here is for simultaneous and successful connections - Maria doesn't keep count of unsuccessful accounts from specific users other than some global counter status such as those in %conn%. Preventing such random connections from the internet to mariadb is usually achieved using a firewall, rather than MariaDb - stop malicious activity as early in the transaction as you can to avoid risk and resource load. If your instance of Mariadb is open to the internet, it's not surprising the bots are hammering it. Apologies if I've misunderstood and for some reason you are allowing wanting each of these sources to connect up to 250 times and they do have accounts. (seems a lot to me and isn't what I'd call abuse - I know who's connecting and would ask them to reduce that number if they were causing problems, but okay). If so, then this is indeed the right setting - but check the link above and ensure those accounts do not have SUPER or CONNECTION ADMIN privileges as those privs intentionally disregard this value. S -----Original Message----- From: Marc via discuss <discuss@lists.mariadb.org> Sent: Friday, July 12, 2024 10:39 AM To: discuss@lists.mariadb.org Subject: [MariaDB discuss] Re: possible bug in dropping max connections Is it possible that someone of mariadb checks what happens when the max_user_connections is exhausted, because to me it looks like further requests are handled as being 'max_connections' which defeats the purpose of max_user_connections
I lowered the max_user_connections hoping it would keep other connections. Yet I am seeing the opposite. I have the impression that ' db: 'unconnected' user: 'unauthenticated' host:' is being counted, which they should not be.
I did not have any issues until now with these settings[1]. I had again abuse from Digital ocean and Microsoft. I thought these settings would solve my issues. However looking at the logs I have a lot of these entries
to db: 'unconnected' user: 'unauthenticated' host:
before these entries are logged:
Aborted connection 3077 to db: xxxx user: 'aaaaaa' host:
Can it be that the user is being blocked and when then the abuse continues, these 'unauthenticated' are triggering the global server blocking?
[1] MariaDB [(none)]> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 1000 | | max_user_connections | 10 | +-----------------------+-------+
MariaDB [(none)]> SELECT VARIABLE_NAME , GLOBAL_VALUE , GLOBAL_VALUE_ORIGIN , GLOBAL_VALUE_PATH FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max%connections'; +----------------------+--------------+---------------------+------- +----------------------+--------------+---------------------+---
--
--------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | GLOBAL_VALUE_PATH | +----------------------+--------------+---------------------+------- +----------------------+--------------+---------------------+--- -- --------------+ | MAX_USER_CONNECTIONS | 50 | CONFIG | /etc/my.cnf.d/server.cnf | | MAX_CONNECTIONS | 1000 | CONFIG | /etc/my.cnf.d/server.cnf | +----------------------+--------------+---------------------+------- +----------------------+--------------+---------------------+--- -- --------------+
I think you're looking for these variables:
MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec)
So set max_connections high, and limit the per-user scope using max_user_connections. works well for us.
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org