Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Friday, 14 June 2019

MAX_USED_CONNECTIONS PER USER/ACCOUNT

How many connections can be opened concurrently against my MySQL database can be configured and checked with the following command:

[mysqld]
max_connections= 2500
SHOW GLOBAL VARIABLES LIKE 'max_connections';

Variable_name
Value
max_connections
2500

We can check the status of this limit as below..

SHOW STATUS LIKE 'max_used%';

Variable_name
Value
Max_used_connections
587
Max_used_connections_time
2019-06-05 16:07:28

But MySQL instances may be configured with many different applications (= database's/schema's) 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 limit the maximum concurrent connection per user with:

[mysqld]
max_user_connections= 500

SHOW GLOBAL VARIABLES LIKE 'max_user_connections';

Variable_name
Value
max_user_connections
500

OR

Further we can limit one specific user with:

GRANT USAGE ON *.* TO 'alok'@'%' WITH MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 700;

and check with: 

SELECT User, Host, max_connections, max_user_connections FROM mysql.user;

User
Host
max_connections
max_user_connections
alok
172.31.28.36
0
700
alok
%
1000
700
root
localhost
0
0

Now if you want to check Max_used_connections per user wise currently we have no chance to check the same.

This feature request was opened at MySQL bug (#77888) report https://bugs.mysql.com/bug.php?id=77888

Implementing Max_used_connections per user:-


I will show you how to implement a custom table for storing max_used_connections &max_used_tim user wise.
Create Table max_used_connections in database mysql

USE mysql;
CREATE TABLE `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,
  `CONNECTION` bigint(20) NOT NULL,
  `MAX_USED_CONNECTIONS` bigint(20) NOT NULL,
  `MAX_USED_TIME` varchar(50)DEFAULT NULL,
  UNIQUE KEY `USER` (`USER`,`HOST`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8;


Now create an event to update the table “max_used_connections” and store the required status.

CREATE DEFINER=root@localhost EVENT mysql.max_used_connections
ON SCHEDULE EVERY 5 SECOND
DO
INSERT INTO mysql.max_used_connections
SELECT user, host, current_connections,current_connections as CONNECTION ,'' MAX_USED_TIME 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),
CONNECTION = current_connections ,
MAX_USED_TIME = IF(max_used_connections > current_connections, MAX_USED_TIME,now());

NOTE: Event Scheduler must be Enable to achieve this feature.
Now you can check the current status of max_used_connections per user as below.



No comments:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...