MySQL processes slowing down host server


Site is overloading the server

MySQL InnoDB database with approx 2gb data. Quite a few sleeping MySQL processes that could be the cause.

Before drilling down in code I would like to make few tweaks to mysql db configuration.Reduce the MySQL wait_timeout value to 3 seconds and increasing the number of allowed connections.Apparently, persistent connections do not currently play nice with transactions with InnoDB.

Mysql configuretion file my.cnf needs following parameters to changewait_timeout = 30 #30 secondsIncrease the number of allowed connection.

Here is the script to check if there is a MySQL sleeping process from long kill them and write log


#!/usr/bin/php -q

<?

$link = mysql_connect (“localhost”, “root”, “password”);

$query = “show processlist”;

$result = mysql_query($query) or die (“failed $query”);

while ($row = mysql_fetch_array($result))
{
    if (($row['Time'] >1000 && $row['Command'] == “Sleep”) && ($row['User'] != “Root”))
    {
        $killed = 0;
        mysql_query(“kill “.$row['Id']) or ($killed = 1);
        
        if ($killed = 1)
        {
            echo
            “Killed process number “.$row['Id'].”, with a User of “.$row['User']
            .” in database “.$row['db'].”, which had a time of “.$row['Time']
            .”, and a command of “.$row['Command'].”\r\n\r\n”;
        }
    }
}

@mysql_close($link);

?>



Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s