How PHP handles MySQL dead connections

This article mainly introduces the method of clearing MySQL dead connections in PHP. The function of checking and clearing mysql dead connections is realized by regularly executing PHP scripts. Friends in need can refer to it. I hope to be helpful.

The main manifestation of the connection situation is that there are too many Sleep connections, and the Time is very long, occupying all the available connections, so that other users can no longer connect to the database. I began to consider adjusting the MySQL database parameters, but changed many parameters and still did not solve the problem. So I thought of a more ruthless way to write a php script and execute it every 2 minutes. If a dead connection is found (more than 120 seconds), kill it. In this way, some programs will no longer kill the database server. The following is Kill Small program for dead connections:

kill-mysql-sleep-proc.php:

define('MAX_SLEEP_TIME',120);
$hostname="localhost";
$username="root";
$password="password";
$connect=mysql_connect($hostname,$username,$password);
$result=mysql_query("SHOWPROCESSLIST",$connect);
while($proc=mysql_fetch_assoc($result)){
if($proc["Command"]=="Sleep"&&$proc["Time"]>MAX_SLEEP_TIME){
@mysql_query("KILL".$proc["Id"],$connect);
}
}
mysql_close($connect);
?>

Change $password in it to your actual database password. The time of dead connections can also be modified. . Then just add the scheduled task. For example, use the crontab-e command to add:

*/2****php/usr/local/sbin/kill-mysql-sleep-proc.php

You can check and clear dead connections in the database every 2 minutes

Related recommendations:

How does PHP generate a mysql data dictionary

How does PHP solve the problem of Chinese garbled characters in MySQL stored data

Detailed explanation about MySql super long automatic truncation example

The above is the detailed content of How PHP handles MySQL dead connections. For more information, please follow other related articles on the PHP Chinese website!