在 MySQL 命令中显示 PROCESSLIST: sleep

当我在 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 |
+--------+------+-----------+-------+---------+-------+-------+------------------------+

我想知道在命令下的“睡眠”过程。这是什么意思？为什么它运行了很长时间并显示为 NULL？它使数据库变慢，当我终止进程时，它就正常工作了。请帮帮我。

162374 次浏览
小开

"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.)

小开
最佳答案

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-sleep for more information.

小开

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.

小开

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.

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).


提交答案