It usually happens on a server to have high load for no apparent reason. My experience have shown that most of the times this high load is due to badly written SQL queries which cause mysql to use all available resources for lots of time. This also causes an an excessive number of connections as the queries never end so each new site user creates a new SQL connection.

For this reason I have written the attached perl script. I have used code portions from several other scripts that i used to have on my servers. The script creates a perl  daemon which runs every one minute and checks MySQL processlist. It then kills any SELECT queries that are in the execute state for more than 60 seconds. Finally it counts the total number of connections per user and if they exceed 5 it kills them all.

I would be very satisfied with your comments, suggestions and contributions.

@Marcelo: You can safely kill the pid. Nothing bad happens, no worries :-) As for the resources, less that 20MB most of the times (depending on how large the mysql processlist is).

@Geraldo Ramos:
db_string -> How to connect to the DB. Do not change it.
db_user -> The mysql to connect and have the priviledge to kill sessions (probably root)
db_pass -> The appropriate mysql password for the above user.
report_to -> Who get's notifications
sendmail_bin -> Where is the sendmail binary
counter -> Internal counter hash list. DO NOT change it.
key, abusers, abuser_user ->Internal variables for the script to work.

what should be put on this variables?

my $db_string = "dbi:mysql:mysql";
my $db_user = "";
my $db_pass = "";

my $report_to = "root";
my $sendmail_bin = "/usr/sbin/sendmail";
my %counter = ('aUser' => 0);
my $key = "";
my $abusers = "";
my $abuser_user = "";

Hi there, it seems to be a cool tool. I see the code and this script can autoinstall itself to run every 60 seconds. Before starting it: how much resources it consumes? and how to stop it in case I don't finally need it? can I simply and easily kill the pid and exclude its entry from the crontab?