User Comments

Here the sample script to kill automatic the mysql user. I used this and my mysql server keep alive and not hang again.

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
$process_id=$row["Id"];
if ($row["Time"] > 200 ) {
$sql="KILL $process_id";
mysql_query($sql);
}
}

Note that if you have to kill an ALTER TABLE command, while the documentation says that it sets a KILL flag, I think this seems to only works when copying over to a new table (and not building the index).

I just did this on a 8G table... it was in REPAIR BY SORTING at the time and it took another 2 or 3 minutes to kill.

The original table was then restored.

The status of the task said KILLED though. Its possible that it checks the status less often during the repair.

FYI.

This should make it easy for windows based mysql installations but can be used on either system (its a php file).
It will remove all user's threads (who aren't root). Note: its just an adaption from a previous script, just complete for copying.
This is a VERY handy class to re-use in future scripts.

<?php
 class dbMysql
  {
    var $host, $user, $pass, $conn;
    var $errNum, $errMsg;
    var $rowCount, $qryType;

    function dbMysql($host, $user, $pass)
     {
      $this->host = $host;
      $this->user = $user;
      $this->pass = $pass;
      $this->rowCount = 0;
      $this->errNum = 0;
      $this->errMsg = '';
      $this->qryType = 0;
     }

    function open()
     {
      if (!$this->conn = @mysql_connect($this->host,$this->user,$this->pass))
       {
        $this->errNum = 2;
        $this->errMsg = 'Cannot Connect to DataBase Server';
        return false;
       }
      $this->errNum = 0;
      $this->errMsg = 'OK';
      return true;
     }

    function select($dbName)
     {
      if (!(@mysql_select_db($dbName,$this->conn)))
       {
        $this->errNum = 2;
        $this->errMsg = 'Cannot Select Requested DataBase';
        return false;
       }
      $this->errNum = 0;
      $this->errMsg = 'OK';
      return true;
     }

    function getAutoId()
     {
      return @mysql_insert_id($this->conn);
     }

    function getResultCount($result)
     {
      return ($this->qryType) ? @mysql_num_rows($result) : @mysql_affected_rows($this->conn);
     }

    function query($qryStr)
     {
       eregi('^SELECT',$qryStr) ? $this->qryType = 0 : $this->qryType = 1;
       return @mysql_query($qryStr,$this->conn);
     }

    function fetch($result)
     { 
      return @mysql_fetch_object($result);
     }

    function close()
     {
      @mysql_close($this->conn);
     }
  }

 /* START HERE - ADD HOST USER AND PASSSWORD */

 $db = new dbMysql("servername", "username", "password");

 if (!$db->open())
  { print("ERROR: " . $db->errNum . " -- " . $db->errMsg); }

 $result = $db->query("SHOW FULL PROCESSLIST");
 while ($row=mysql_fetch_array($result)) {
    $process_id=$row["Id"];
    if ($row["User"] != "root" ) {
        print("KILLED:  " . $process_id);
        $sql="KILL $process_id";
        mysql_query($sql);
    }
 }
 if (!$result)
  {
   $db->close();
   print("ERROR: " . $db->errNum . " -- " . $db->errMsg);
  }



 
 $db->close();
?>

I think you should resolve the problem of your low performance instead of killing everything all time.

You can starting saving all killed querys in a table, so you can see how optimize them later. MySQL has a list of optimizations you can do (like removing unnecesary '()'s, removing 'ORDER BY' clause when not needed, etc).

Add your own comment.