MySQL 命令中的 SHOW PROCESSLIST：sleep
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12194241/
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
SHOW PROCESSLIST in MySQL command: sleep
提问by gthm
When I run SHOW PROCESSLIST in MySQL database, I get this output:
当我在 MySQL 数据库中运行 SHOW PROCESSLIST 时，我得到以下输出：
mysql> show full processlist;
+--------+------+-----------+--------+---------+-------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------+-------+---------+-------+-------+-----------------------+
| 411665 | root | localhost | somedb | Sleep | 11388 | | NULL |
| 412109 | root | localhost | somedb | Query | 0 | NULL | show full processlist |
+--------+------+-----------+-------+---------+-------+-------+------------------------+
I would like to know the process "Sleep" that is under Command. What does it mean? Why it is running since a long time and showing NULL? It is making the database slow and when I kill the process, then it works normally. Please help me.
我想知道命令下的进程“睡眠”。这是什么意思？为什么它运行了很长时间并显示NULL？它使数据库变慢，当我终止进程时，它可以正常工作。请帮我。
采纳答案by Rufinus
It's not a query waiting for connection; it's a connection pointer waiting for the timeout to terminate.
这不是等待连接的查询；它是一个等待超时终止的连接指针。
It doesn't have an impact on performance. The only thing it's using is a few bytes as every connection does.
它对性能没有影响。它唯一使用的是每个连接都使用的几个字节。
The really worst case: It's using one connection of your pool; If you would connect multiple times via console client and just close the client without closing the connection, you could use up all your connections and have to wait for the timeout to be able to connect again... but this is highly unlikely :-)
最糟糕的情况：它使用了池的一个连接；如果您将通过控制台客户端多次连接并且只是在不关闭连接的情况下关闭客户端，则您可能会用完所有连接，并且必须等待超时才能再次连接......但这极不可能:-)
See MySql Proccesslist filled with "Sleep" Entries leading to "Too many Connections"?and https://dba.stackexchange.com/questions/1558/how-long-is-too-long-for-mysql-connections-to-sleepfor more information.
看到MySql 进程列表充满了导致“连接过多”的“睡眠”条目吗？和https://dba.stackexchange.com/questions/1558/how-long-is-too-long-for-mysql-connections-to-sleep了解更多信息。
回答by Drew
"Sleep" state connections are most often created by code that maintains persistent connections to the database.
“睡眠”状态连接通常由维护与数据库的持久连接的代码创建。
This could include either connection pools created by application frameworks, or client-side database administration tools.
这可能包括由应用程序框架创建的连接池或客户端数据库管理工具。
As mentioned above in the comments, there is really no reason to worry about these connections... unless of course you have no idea where the connection is coming from.
正如上面评论中提到的，真的没有理由担心这些连接......当然除非你不知道连接来自哪里。
(CAVEAT: If you had a long list of these kinds of connections, there might be a danger of running out of simultaneous connections.)
（注意：如果您有一长串此类连接，则可能存在同时连接用完的危险。）
回答by Antony
I found this answer here: https://dba.stackexchange.com/questions/1558. In short using the following (or within my.cnf) will remove the timeout issue.
我在这里找到了这个答案：https: //dba.stackexchange.com/questions/1558。简而言之，使用以下（或在 my.cnf 中）将消除超时问题。
SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;
SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;
This allows the connections to end if they remain in a sleep State for 3 minutes (or whatever you define).
这允许连接在睡眠状态保持 3 分钟（或您定义的任何内容）时结束。
回答by ding
Sleep meaning that thread is do nothing. Time is too large beacuse anthor thread query,but not disconnect server, default wait_timeout=28800;so you can set values smaller,eg 10. also you can kill the thread.
睡眠意味着线程什么都不做。时间过长，因为Anthor线程查询，但不断开服务器，默认wait_timeout=28800；所以你可以设置较小的值，例如10。你也可以杀死线程。
