For longest time it had bugged me that there wasn’t an easy way to put “SHOW STATUS”, “SHOW VARIABLES”, or “SHOW PROCESSLIST” variables into queries, so I could easily calculate stuff like max connections percentage. Then MySQL 5.1.9 came out and you could final start to do SELECT statements on information_schema.GLOBAL_STATUS, information_schema.SESSION_STATUS, information_schema.GLOBAL_VARIABLES, information_schema.SESSION_VARIABLES and information_schema.PROCESSLIST.

The STATUS and VARIABLES tables have basically two columns VARIABLE_NAME and VARIABLE_VALUE. These correspond with the “SHOW STATUS” and “SHOW VARIABLES” command’s Variable_name and Value. The PROCESSLIST table has all the same columns as the “SHOW PROCESSLIST” command.

So now with that being said here is a quick little query that returns the Max Connection Limit, Connection Count, and Connect Percentage of max allowed connections.

/* requires MySQL 5.1.19 or higher */

SELECT v.VARIABLE_VALUE as “Max Connections”,
s.processcount as “Connection Count”,
((s.processcount/v.VARIABLE_VALUE)*100) as “% of max connections allowed”
FROM information_schema.GLOBAL_VARIABLES v,
(SELECT (@proc_connect:=count(*)) processcount FROM information_schema.PROCESSLIST) s
WHERE v.VARIABLE_NAME = ‘max_connections’;