Determine the Number of Connections Per Server in Mysql

Coming from the Dynamic View world of sql server 2005 the show commands of mysql take me back to the old days. The primarily issue with the “Show Commands” are they don’t return a result set you can work with. As a DBA one of the most common requests is finding all the processes connected to the server and how many connections each host has. Using show processlist requires capturing this as text then writing some script to parse and aggregate. To avoid this I use the information_schema database. Although not as robust as many rdbs’s there are some good tables to get information from. In this case I like to query the processlist table.


mysql> select host, count(*) as cnt from processlist group by host;
+----------------+-----+
| host | cnt |
+----------------+-----+
| localhost:4819 | 1 |
| localhost:4833 | 1 |
+----------------+-----+
2 rows in set (0.00 sec)

However the challenge here is that concatenated with the host name is the thread id, so you still can’t get a proper count on how many connections each server has. To solve this I used the ultra cool substring_index function of mysql. Here you pass it the string to be searched, the string to be found, and the count of occurrences you want to find.

In this example I was able to group by this function.

mysql> select substring_index(host,':',1) as hostname, count(*) as cnt from processlist group by substring_index(host,':',1);
+-----------+-----+
| hostname | cnt |
+-----------+-----+
| localhost | 2 |
+-----------+-----+
1 row in set (0.02 sec)

Here’s a link to mysql’s documentation

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index