MySQL process list – Customized connection counts

by nivas on August 27, 2014

You got a production support call related to connection usage. Max_connections is set to 400 and  connection usage has reached the maximum limit.

Below are some of the customized reports that can be used to trace the problem

Count by host :

mysql> select count(*),host from information_schema.processlist group by HOST;
+----------+-----------+
| count(*) | host |
+----------+-----------+
| 1 | localhost |
+----------+-----------+
1 row in set (0.03 sec)

Count by database:

mysql> select count(*),host from information_schema.processlist group by DB;
+----------+-----------+
| count(*) | host      |
+----------+-----------+
|        1 | localhost |
+----------+-----------+
1 row in set (0.00 sec)

Count by username:


mysql> select count(*),user from information_schema.processlist group by user;
+----------+------+
| count(*) | user |
+----------+------+
| 1 | root |
+----------+------+
1 row in set (0.00 sec)

Count by command:


mysql> select count(*),command from information_schema.processlist group by command;
+----------+---------+
| count(*) | command |
+----------+---------+
|        1 | Query   |
+----------+---------+
1 row in set (0.00 sec)

Count by select/delete/..:


mysql> select count(*) from information_schema.processlist where INFO like '%select%' ;
+----------+---------+
| count(*) | command |
+----------+---------+
|        1 | Query   |
+----------+---------+
1 row in set (0.00 sec)

Comments on this entry are closed.

Next post: