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).