MySQL 如何在Mysql中删除睡眠进程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5026204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete sleep process in Mysql
提问by Suresh Kamrushi
I found that my mysql sever have many of connection who is sleep. i want to delete them all.
我发现我的mysql服务器有很多连接谁在睡觉。我想全部删除它们。
so how i can configure my mysql server than then delete or dispose the connection who is in sleep not currently in process.
那么我如何配置我的 mysql 服务器然后删除或处理当前未处理的处于睡眠状态的连接。
are this possible to delete this thing in mysql tell me how i can do following
这是否可以在 mysql 中删除这个东西告诉我我该怎么做
a connection allow only one time datareader open and destroy the connection [process] after giving resposnse of query.
一个连接只允许一次数据读取器打开并在给出查询响应后销毁连接 [进程]。
回答by JamesHalsall
Why would you want to delete a sleeping thread? MySQL creates threads for connection requests, and when the client disconnects the thread is put back into the cache and waits for another connection.
为什么要删除休眠线程？MySQL为连接请求创建线程，当客户端断开连接时，线程被放回缓存并等待另一个连接。
This reduces a lot of overhead of creating threads 'on-demand', and it's nothing to worry about. A sleeping thread uses about 256k of memory.
这减少了“按需”创建线程的大量开销，并且无需担心。一个休眠线程使用大约 256k 的内存。
回答by Suresh Kamrushi
If you want to do it manually you can do like this:
如果你想手动完成，你可以这样做：
login to Mysql as admin:
以管理员身份登录Mysql：
mysql -uroot -ppassword;
And than run command:
然后运行命令：
mysql> show processlist;
You will get something like below :
你会得到如下内容：
+----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+--------------------+----------+---------+------+-------+------------------+
| 49 | application | 192.168.44.1:51718 | XXXXXXXX | Sleep | 183 | | NULL ||
| 55 | application | 192.168.44.1:51769 | XXXXXXXX | Sleep | 148 | | NULL |
| 56 | application | 192.168.44.1:51770 | XXXXXXXX | Sleep | 148 | | NULL |
| 57 | application | 192.168.44.1:51771 | XXXXXXXX | Sleep | 148 | | NULL |
| 58 | application | 192.168.44.1:51968 | XXXXXXXX | Sleep | 11 | | NULL |
| 59 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+-------------+--------------------+----------+---------+------+-------+------------------+
You will see complete details of different connections. Now you can kill the sleeping connection as below:
您将看到不同连接的完整详细信息。现在您可以终止睡眠连接，如下所示：
mysql> kill 52;
Query OK, 0 rows affected (0.00 sec)
回答by Guixing Bai
you can find all working process execute the sql:
你可以找到所有执行sql的工作进程：
show process;
and you will find the sleep process, if you want terminate it, please remember the processid and excute this sql:
然后你会找到sleep进程，如果你想终止它，请记住processid并执行这个sql：
kill processid
but actually you can set a timeout variable in my.cnf:
但实际上你可以在 my.cnf 中设置一个超时变量：
wait_timeout=15
connect_timeout=10
interactive_timeout=100
