I'm a huge fan of SQL. But, I've never been a database administrator. At least, nothing like Brad Brewer - our DBA and general data architect. Lately, however, I've been trying to take a more active role in the monitoring of our database infrastructure; which means, learning more about MySQL features like the PROCESSLIST and SLOW_LOG tables. For example, today, we saw an odd increase in the number of connections coming into the master database. So, in an attempt to figure out which client machine was causing the unexpected increase, I had to figure out how to group the PROCESSLIST table by host IP address.

Now, I've known about the SHOW FULL PROCESSLIST command for a while. But, I had no idea that you could actually run queries against the PROCESSLIST table in MySQL. In the past, I've certainly tried to do this with no luck. Thankfully I came across a really helpful StackOverflow post today that taught me how to execute such a query by denoting the PROCESSLIST as a child of the INFORMATION_SCHEMA.

Once I was able to actually run queries against the PROCESSLIST table, all I had to do was strip the port off the host column and do a little grouping on the naked IP address:

SELECT
	tmp.ipAddress,
	-- Calculate how many connections are being held by this IP address.
	COUNT( * ) AS ipAddressCount,
	-- For each connection, the TIME column represent how many SECONDS it has been in
	-- its current state. Running some aggregates will give us a fuzzy picture of what
	-- the connections from this IP address is doing.
	FLOOR( AVG( tmp.time ) ) AS timeAVG,
	MAX( tmp.time ) AS timeMAX
	-- Let's create an intermediary table that includes an additional column representing
	-- the client IP address without the port.
		SELECT
			-- We don't actually need all of these columns for the demo. But, I'm
			-- including them here to demonstrate what fields COULD be used in the
			-- processlist system.
			pl.id,
			pl.user,
			pl.host,
			pl.db,
			pl.command,
			pl.time,
			pl.state,
			pl.info,
			-- The host column is in the format of "IP:PORT". We want to strip off
			-- the port number so that we can group the results by the IP alone.
			LEFT( pl.host, ( LOCATE( ':', pl.host ) - 1 ) ) AS ipAddress
			INFORMATION_SCHEMA.PROCESSLIST pl
	) AS tmp
GROUP BY
	tmp.ipAddress
ORDER BY
	ipAddressCount DESC
This returns a list of client IP addresses along with the number of connections that the given client is holding.
Honestly, I can't tell you how long I've longed to be able to query against the PROCESSLIST table. Being able to do that now is a simple yet major breakthrough for me in my journey towards SQL mastery. It makes things like grouping the PROCESSLIST by IP address finally possible.
Want to use code from this post?
Check out the license.
Enjoyed This Post?
Share the Love With Your Friends!
Tweet This
Great article by @BenNadel - Grouping The MySQL PROCESSLIST By IP Address To View Connection Counts https://www.bennadel.com/go/3054
Inspecting Primary And Secondary Index Key Utilization For MySQL 5.7.32 In Lucee CFML 5.3.7.47
Killing Slow MySQL Queries In An Emergency In Lucee CFML 5.3.7.47
Looking For Database Performance Bottlenecks And Optimizations Using The Sys Schema In MySQL 5.7
Trying To Debug "Deadlock found when trying to get lock; try restarting transaction" Errors In Lucee CFML 5.2.9.40
Keeping Prepared Statements Consistent Even With Dynamic Parameterized Queries In MySQL And ColdFusion
MySQL's Slow Query Log "start_time" Column Is Actually The "end" Time Of The Query
ColdFusion Does Not Appear To Use The Connection Pool When Issuing KILL QUERY Commands
CFQuery Timeout Uses KILL QUERY Command With MySQL 5 Driver In ColdFusion
Sequelize-Comment: A Sequelize Plug-in That Injects Comments Before Your SQL Statements
Putting DEBUG Comments In Your SQL Statements Makes Debugging Performance Problems Easier
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel