mysql sleep 进行过多优化方法
解决办法是:打开include/pub_db_mysql.php大概第73行,把下面的一段代码:
//连接数据库
if($pconnect == -100){ $this->linkID = @mysql_connect($this->dbHost,$this
->dbUser,$this->dbPwd); }
else{ $this->linkID = @mysql_pconnect($this->dbHost,$this->dbUser,$this-
>dbPwd); }
替换成如下代码:
//连接数据库
// if($pconnect == -100){ $this->linkID = @mysql_connect($this-
>dbHost,$this->dbUser,$this->dbPwd); }
// else{ $this->linkID = @mysql_pconnect($this->dbHost,$this->dbUser,$this-
>dbPwd); }
//直接使用connect
$this->linkID = @mysql_connect($this->dbHost,$this->dbUser,$this->dbPwd);
保存,再看mysql的进程。是不是OK了?
官方放弃connect()而采用pconnect()的链接方式未必是最好的选择。
mysql 假死状态,sleep进程过多等等现像。导致几十个用户,就把系统搞定了。
其实这是 mysql 配置上的问题, 默认的 linux 中, mysql 的配置是 my-large.cnf
配置,该配置适合大型服务器。有高内存,比如2G,4G内存的,适合使用,而一般512M
内存 的就不行了。它会占用512M内存来保存系统 mysql 的进程,这些进程长期堆质,
并不释放,导致系统缓慢。所以,把配置改变成为 my-small.cnf ,小型配置就可以了
#!/bin/sh
while :
do
n=`/usr/bin/mysqladmin processlist | grep -i sleep | wc -l`
date=`date +%Y%m%d[%H:%M:%S]`
echo $n
if [ "$n" -gt 10 ]
then
for i in `/usr/bin/mysqladmin processlist | grep -i sleep | awk '{print
$2}'`
do
/usr/bin/mysqladmin kill $i
done
echo "sleep is too many i killed it" >> /tmp/sleep.log
echo "$date : $n" >> /tmp/sleep.log
fi
sleep 5
done
方法二
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);
?>
将它当中的$password改成你实际的数据库密码,死连接的时间也可以修改。然后
加入计划任务就可以了。比如用crontab-e命令加入:
*/2****php/usr/local/sbin/kill-mysql-sleep-proc.php
就可以每隔2分钟检查并清除一次数据库中的死连接了。
PHP高手学的killsleep的程序:
/**
* This is a batch process program to kill the long time mysql's query.
* Please add this program to crontab, auto run it per 5 seconds
* It will output a log file. make sure that file can write.
*
* mysqlprocesslogs.txt
* php dbprocess.php
* @author Yorgo Sun (sunshaoxuan@cdeledu.com)
*/
try
{
while (true)
{
$process = array();
$dbh = new PDO("mysql:host=localhost;dbname=blog", "root", "chinaacc{}!#%blog");
$fp = fopen("mysqlprocesslogs.txt", "a");
$result = $dbh->query("show processlist");
$i=0;
foreach ($result as $row)
{
if ($row["Time"] > 5 && $row["User"] != 'repl' && $row["User"] != 'root' && $row["User"] != 'backend
' && $row["User"] != 'system user')
{
$process[$i]["id"] = $row["Id"];
$process[$i]["user"] = $row["User"];
$process[$i]["host"] = $row["Host"];
$process[$i]["db"] = $row["db"];
$process[$i]["command"] = $row["Command"];
$process[$i]["time"] = $row["Time"];
$process[$i]["status"] = $row["State"];
$process[$i]["info"] = $row["Info"];
//var_dump($process[$i]);
$i++;
}
}
foreach($process as $row)
{
$dbh->query("kill ".$row["id"]);
$output = date("Y-m-d H:i:s", time())."|".$row["id"]."|".$row["user"]."|".$row["host"]."|".$row["db"
]."|".$row["command"]."|".$row["status"]."|".$row["info"]."\n";
fwrite($fp, $output);
}
$process = array();
fclose($fp);
$dbh = null;
sleep(5);
}
}
catch (Exception $e)
{
echo $e->getMessage();
}