Mysql Store Procedure Handling Too Many Connections

In a recent post I talked about handling too many connections in mysql. In that post I talked about scripting out monitoring or detection scripts. I gave some fundamental commands that would be needed but didn’t have anything coded. I’ve decided to take it one step further in writing my own stored procedure that does just that.

This proc takes two parameter values, PercentOfMax which is the percentage threshold, meaning if your current connections percentage of the max_Connections is higher then this number take action. This action is to increase the number or ceiling for max connections. The amount raised is also a percentage and is supplied in the second parameter which is PercentChange. After the SP makes the change it spits out some results that can be consumed by the calling component.

ActionTaken – Were connections increased
ActualConnections – How many threads are currently connected to the server
PreConnections – Prior to taking action what was the max_connection value
PostConnections – After taking action what was the max_connection value




/*Check For Existance*/
Drop procedure if exists RepairConnections;
delimiter //
create procedure RepairConnections(PercentOfMax int, PercentChnge int)
begin

/* Declare Variables */
Declare ActionTaken bool;
Declare PreConnections int;
Declare PostConnections int;
Declare ActualConnections int;
Declare ConnPercent int;
Declare SetConnection int;
Declare PercentageChange Decimal(2,2);

Set PercentageChange = PercentChnge * .01;



/*Set Value of Current Max_Connections*/
select VARIABLE_VALUE into PreConnections
from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME ='MAX_CONNECTIONS';

/*Set Value of Current Connections to Server*/
Select Count(*) into ActualConnections from information_schema.PROCESSLIST;

/*Set Value of ConnPercent which is how many connections are being used*/
select ((ActualConnections/PreConnections) * 100) into ConnPercent;

/*If Connection Percent is higher then input value up by percent of second input*/
if ConnPercent > PercentOfMax then Set ActionTaken = 1;
Set SetConnection = Round((PreConnections * PercentageChange) + PreConnections);
Set global Max_Connections = SetConnection;

else Set ActionTaken = 0;
end if;

/*Set Value of New Max_Connections*/
select VARIABLE_VALUE into PostConnections
from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME ='MAX_CONNECTIONS';

/*Return Values back to Caller to Act upon*/
Select ActionTaken, ActualConnections, PreConnections, PostConnections;

end;
//