php MySql 进程列表中充满了导致“连接过多”的“睡眠”条目？

MySql Proccesslist filled with "Sleep" Entries leading to "Too many Connections"?

提问by edorian

I'd like to ask your help on a longstanding issue with php/mysql connections.

我想就 php/mysql 连接的一个长期问题寻求您的帮助。

Every time I execute a "SHOW PROCESSLIST" command it shows me about 400 idle (Status: Sleep) connections to the database Server emerging from our 5 Webservers.

每次我执行“SHOW PROCESSLIST”命令时，它都会向我显示大约 400 个空闲（状态：睡眠）连接到从我们的 5 个 Web 服务器中出现的数据库服务器。

That never was much of a problem (and I didn't find a quick solution) until recently traffic numbers increased and since then MySQL reports the "to many connections" Problems repeatedly, even so 350+ of those connections are in "sleep" state. Also a server can't get a MySQL connection even if there are sleeping connection to that same server.

这从来都不是什么大问题（我没有找到快速解决方案），直到最近流量增加，从那时起 MySQL 反复报告“多连接”问题，即使这些连接中有 350 多个处于“睡眠”状态. 即使有到同一台服务器的休眠连接，服务器也无法获得 MySQL 连接。

All those connections vanish when an apache server is restated.

当重新启动 apache 服务器时，所有这些连接都会消失。

The PHP Code used to create the Database connections uses the normal "mysql" Module, the "mysqli" Module, PEAR::DB and Zend Framework Db Adapter. (Different projects). NONE of the projects uses persistent connections.

用于创建数据库连接的 PHP 代码使用普通的“mysql”模块、“mysqli”模块、PEAR::DB 和 Zend Framework Db 适配器。（不同的项目）。没有一个项目使用持久连接。

Raising the connection-limit is possible but doesn't seem like a good solution since it's 450 now and there are only 20-100 "real" connections at a time anyways.

提高连接限制是可能的，但似乎不是一个好的解决方案，因为它现在是 450，而且一次只有 20-100 个“真实”连接。

My question:

我的问题：

Why are there so many connections in sleep state and how can I prevent that?

为什么在睡眠状态下有这么多连接，我该如何防止？

-- Update:

- 更新：

The Number of Apache requests running at a time never exceeds 50 concurrent requests, so i guess there is a problem with closing the connection or apache keeps the port open without a phpscript attached or something (?)

一次运行的 Apache 请求数永远不会超过 50 个并发请求，所以我猜关闭连接有问题，或者 apache 在没有附加 phpscript 或其他东西的情况下保持端口打开（？）

my.cnf in case it's helpful:

my.cnf 以防万一：

innodb_buffer_pool_size = 1024M

max_allowed_packet = 5M
net_buffer_length = 8K

read_buffer_size = 2M
read_rnd_buffer_size = 8M

query_cache_size = 512M
myisam_sort_buffer_size = 128M

max_connections = 450
thread_cache = 50
key_buffer_size = 1280M
join_buffer_size = 16M

table_cache = 2048
sort_buffer_size = 64M
tmp_table_size = 512M
max_heap_table_size = 512M

thread_concurrency = 8

log-slow-queries = /daten/mysql-log/slow-log
long_query_time = 1
log_queries_not_using_indexes

innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table

回答by Pascal MARTIN

Basically, you get connections in the Sleep state when :

基本上，您会在以下情况下获得处于睡眠状态的连接：

• a PHP script connects to MySQL
• some queries are executed
• then, the PHP script does some stuff that takes time
  • without disconnecting from the DB
• and, finally, the PHP script ends
  • which means it disconnects from the MySQL server

• PHP 脚本连接到 MySQL
• 执行了一些查询
• 然后，PHP 脚本会做一些需要时间的事情
  • 无需断开与数据库的连接
• 最后，PHP 脚本结束
  • 这意味着它与 MySQL 服务器断开连接

So, you generally end up with many processes in a Sleep state when you have a lot of PHP processes that stay connected, without actually doing anything on the database-side.

因此，当您有许多 PHP 进程保持连接时，通常会导致许多进程处于睡眠状态，而实际上并未在数据库端执行任何操作。

A basic idea, so : make sure you don't have PHP processes that run for too long -- or force them to disconnect as soon as they don't need to access the database anymore.

一个基本的想法，所以：确保你没有运行太长时间的 PHP 进程——或者在它们不再需要访问数据库时强制它们断开连接。

Another thing, that I often see when there is some load on the server :

另一件事，当服务器上有一些负载时，我经常看到：

• There are more and more requests coming to Apache
  • which means many pages to generate
• Each PHP script, in order to generate a page, connects to the DB and does some queries
• These queries take more and more time, as the load on the DB server increases
• Which means more processes keep stacking up

• 越来越多的请求来到 Apache
  • 这意味着要生成许多页面
• 每个 PHP 脚本，为了生成一个页面，连接到数据库并执行一些查询
• 随着数据库服务器负载的增加，这些查询花费的时间越来越长
• 这意味着更多的进程不断堆积

A solution that can help is to reduce the time your queries take -- optimizing the longest ones.

一个可以提供帮助的解决方案是减少查询花费的时间——优化最长的查询。

回答by Qammar Feroz

Increasing number of max-connections will not solve the problem.

增加最大连接数并不能解决问题。

We were experiencing the same situation on our servers. This is what happens

我们在我们的服务器上遇到了同样的情况。这就是发生的事情

User open a page/view, that connect to the database, query the database, still query(queries) were not finished and user leave the page or move to some other page. So the connection that was open, will remains open, and keep increasing number of connections, if there are more users connecting with the db and doing something similar.

用户打开一个页面/视图，连接到数据库，查询数据库，仍然查询（查询）没有完成，用户离开页面或移动到其他页面。因此，如果有更多用户连接到数据库并执行类似操作，则打开的连接将保持打开状态并保持连接数量不断增加。

You can set interactive_timeout MySQL, bydefault it is 28800 (8hours) to 1 hour

你可以设置interactive_timeout MySQL，默认是28800（8小时）到1小时

SET interactive_timeout=3600

回答by S.Bao

Before increasing the max_connections variable, you have to check how many non-interactive connection you have by running show processlist command.

在增加 max_connections 变量之前，您必须通过运行 show processlist 命令来检查您有多少非交互式连接。

If you have many sleep connection, you have to decrease the value of the "wait_timeout" variable to close non-interactive connection after waiting some times.

如果您有很多睡眠连接，则必须在等待一段时间后减小“wait_timeout”变量的值以关闭非交互式连接。

• To show the wait_timeout value:

• 显示 wait_timeout 值：

SHOW SESSION VARIABLES LIKE 'wait_timeout';

显示会话变量，如“wait_timeout”；

+---------------+-------+

+--------------+-------+

| Variable_name | Value |

| 变量名 | 价值 |

+---------------+-------+

+--------------+-------+

| wait_timeout | 28800 |

| 等待超时| 28800 |

+---------------+-------+

+--------------+-------+

the value is in second, it means that non-interactive connection still up to 8 hours.

该值以秒为单位，表示非交互式连接仍长达 8 小时。

• To change the value of "wait_timeout" variable:

• 要更改“wait_timeout”变量的值：

SET session wait_timeout=600;Query OK, 0 rows affected (0.00 sec)

SET session wait_timeout=600; 查询正常，0 行受影响（0.00 秒）

After 10 minutes if the sleep connection still sleeping the mysql or MariaDB drop that connection.

10 分钟后，如果睡眠连接仍处于睡眠状态，mysql 或 MariaDB 将删除该连接。

回答by Marcelo Agimóvel

The above solutions like run a query

上述解决方案如运行查询

SET session wait_timeout=600;

Will only work until mysql is restarted. For a persistant solution, edit mysql.conf and add after [mysqld]:

只会在 mysql 重新启动之前工作。对于持久解决方案，编辑 mysql.conf 并在 [mysqld] 后添加：

wait_timeout=300
interactive_timeout = 300

Where 300 is the number of seconds you want.

其中 300 是您想要的秒数。

回答by Antony

So I was running 300 PHP processes simulatenously and was getting a rate of between 60 - 90 per second (my process involves 3x queries). I upped it to 400 and this fell to about 40-50 per second. I dropped it to 200 and am back to between 60 and 90!

所以我模拟地运行了 300 个 PHP 进程，并且获得了每秒 60 - 90 个之间的速率（我的进程涉及 3x 查询）。我将它提高到 400，然后下降到大约每秒 40-50。我把它降到了 200，然后又回到了 60 到 90 之间！

So my advice to anyone with this problem is experiment with running lessthan more and see if it improves. There will be less memory and CPU being used so the processes that do run will have greater ability and the speed may improve.

所以我对任何有这个问题的人的建议是尝试少跑多跑，看看它是否有所改善。将使用更少的内存和 CPU，因此运行的进程将具有更大的能力并且速度可能会提高。

回答by Mahboob I

Alright so after trying every solution out there to solve this exact issues on a wordpress blog, I might have done something either really stupid or genius... With no idea why there's an increase in Mysql connections, I used the php script below in my header to kill all sleeping processes..

好吧，在尝试了所有解决方案以解决 wordpress 博客上的确切问题后，我可能做了一些非常愚蠢或天才的事情......不知道为什么 Mysql 连接增加，我在我的博客中使用了下面的 php 脚本头杀死所有睡眠进程..

So every visitor to my site helps in killing the sleeping processes..

因此，我网站的每个访问者都有助于终止睡眠过程。

<?php
$result = mysql_query("SHOW processlist");
while ($myrow = mysql_fetch_assoc($result)) {
if ($myrow['Command'] == "Sleep") {
mysql_query("KILL {$myrow['Id']}");}
}
?>