You are here
Max_used_connections per user/account
How many connections can be opened concurrently against my MySQL or MariaDB database can be configured and checked with the following command:
SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 505 | +-----------------+-------+
If this limit was ever reached in the past can be checked with:
SHOW GLOBAL STATUS LIKE 'max_use%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 23 | +----------------------+-------+
But on MySQL instances with many different applications (= databases/schemas) and thus many different users it is a bit more complicated to find out which of these users have connected how many times concurrently. We can configure how many connections one specific user can have at maximum at the same time with:
SHOW GLOBAL VARIABLES LIKE 'max_user_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | max_user_connections | 500 | +----------------------+-------+
Further we can limit one specific user with:
GRANT USAGE ON *.* TO 'repl'@'%' WITH MAX_CONNECTIONS_PER_HOUR 100 MAX_USER_CONNECTIONS 10;
and check with:
SELECT User, Host, max_connections, max_user_connections FROM mysql.user; +------+---------------+-----------------+----------------------+ | User | Host | max_connections | max_user_connections | +------+---------------+-----------------+----------------------+ | root | localhost | 0 | 0 | | repl | % | 100 | 10 | | repl | 192.168.1.139 | 0 | 0 | +------+---------------+-----------------+----------------------+
But we have currently no chance to check if this limit was reached or nearly reached in the past...
A feature request for this was opened at MySQL wit bug #77888
Solution
If you cannot wait for the implementation here we have a little workaround:
DROP TABLE IF EXISTS mysql.`max_used_connections`; CREATE TABLE mysql.`max_used_connections` ( `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `MAX_USED_CONNECTIONS` bigint(20) NOT NULL, PRIMARY KEY (`USER`, `HOST`) USING HASH ) ENGINE=MEMORY DEFAULT CHARSET=utf8 ; DROP EVENT IF EXISTS mysql.gather_max_used_connections; -- event_scheduler = on CREATE DEFINER=root@localhost EVENT mysql.gather_max_used_connections ON SCHEDULE EVERY 10 SECOND DO INSERT INTO mysql.max_used_connections SELECT user, host, current_connections FROM performance_schema.accounts WHERE user IS NOT NULL AND host IS NOT NULL ON DUPLICATE KEY UPDATE max_used_connections = IF(current_connections > max_used_connections, current_connections, max_used_connections) ; SELECT * FROM mysql.max_used_connections; +--------+-----------+----------------------+ | USER | HOST | MAX_USED_CONNECTIONS | +--------+-----------+----------------------+ | root | localhost | 4 | | zabbix | localhost | 21 | +--------+-----------+----------------------+
Caution: Because we used a MEMORY
table those values are reset at every MySQL restart (as it happens with the PERFORMANCE_SCHEMA
or the INFORMATION_SCHEMA
).
- Shinguz's blog
- Log in or register to post comments