Calculating The Percentage of Max Connections in with MySQL 5.1.9 and higher

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’;

 

Leave a Reply