PHP-MySQLi 连接随机失败并显示“无法分配请求的地址”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3210270/
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
PHP-MySQLi connection randomly fails with "Cannot assign requested address"
提问by Nenko Ivanov
Since about 2 weeks I'm dealing with one of the weirdest problems in LAMP stack. Long story short randomly connection to MySQL server is failing with error message:
大约 2 周以来，我正在处理 LAMP 堆栈中最奇怪的问题之一。长话短说随机连接到 MySQL 服务器失败并显示错误消息：
Warning: mysqli::real_connect(): (HY000/2002): Cannot assign requested address in ..
The MySQL is on different "box", hosted at Rackspace Cloud Today we downgraded it's version to
MySQL 在不同的“盒子”上，托管在 Rackspace Cloud 今天我们将它的版本降级到
Ver 14.14 Distrib 5.1.42, for debian-linux-gnu (x86_64).
The DB server is pretty busy dealing with Queries per second avg: 5327.957 according to it's status variable.
根据它的状态变量，数据库服务器非常忙于处理每秒平均查询数：5327.957。
MySQL is in log-warnings=9 but no warring for connection refused are logged. Both site and gearman workers scripts fail with that error at let's say 1% probability. No server load DO NOT seems to be a factor as we monitor. (CPU load, IO load or MySQL load) The maximum DB connections (max_connections) are setted to 200 but we have never dealed with more than 100 simultaneous connections to the database
MySQL 在 log-warnings=9 中，但没有记录连接被拒绝的War。站点和齿轮工作人员脚本都以 1% 的概率失败并出现该错误。没有服务器负载似乎不是我们监控的一个因素。（CPU 负载、IO 负载或 MySQL 负载）最大 DB 连接数（max_connections）设置为 200 但我们从未处理过超过 100 个同时连接到数据库的连接
It happens with and without the firewall software.
无论有没有防火墙软件，它都会发生。
I suspect TCP Networking problem rather than PHP/MySQL configurationn problem.
我怀疑 TCP 网络问题而不是 PHP/MySQL 配置问题。
Can anyone give me clue how to find it?
谁能告诉我如何找到它？
UPDATE:
更新：
The connection code is:
连接代码是：
$this->_mysqli = mysqli_init();
$this->_mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 120);
$this->_mysqli->real_connect($dbHost,$dbUserName, $dbPassword, $dbName);
if (!is_null($this->_mysqli->connect_error)) {
$ping = $this->_mysqli->ping();
if(!$ping){
$error = 'HOST: {'.$dbHost.'};MESSAGE: '. $this->_mysqli->connect_error ."\n";
DataStoreException::raiseHostUnreachable($error);
}
}
回答by Naktibalda
MySQL: Using giant number of connections
What are dangers of frequent connects ?
It works well, with exception of some extreme cases. If you get hundreds of connects per second from the same box you may get into running out of local port numbers. The way to fix it could be - decrease "/proc/sys/net/ipv4/tcp_fin_timeout" on linux (this breaks TCP/IP standard but you might not care in your local network), increase "/proc/sys/net/ipv4/ip_local_port_range" on the client. Other OS have similar settings. You also may use more web boxes or multiple IP for your same database host to work around this problem. I've realy seen this in production.
频繁连接有什么危害？
它运作良好，除了一些极端情况。如果您从同一个盒子每秒获得数百个连接，您可能会遇到本地端口号用完的情况。修复它的方法可能是 - 在 linux 上减少“/proc/sys/net/ipv4/tcp_fin_timeout”（这违反了 TCP/IP 标准，但您可能不在乎本地网络），增加“/proc/sys/net/ ipv4/ip_local_port_range”在客户端。其他操作系统也有类似的设置。您还可以为同一数据库主机使用更多的网络框或多个 IP 来解决此问题。我真的在生产中看到了这一点。
Some background about this problem:
TCP/IP connection is identified by localip:localport remoteip:remote port. We have MySQL IP and Port as well as client IP fixed in this case so we can only vary local port which has finite range. Note even after you close connection TCP/IP stack has to keep the port reserved for some time, this is where tcp_fin_timeout comes from.
关于这个问题的一些背景：
TCP/IP 连接由 localip:localport remoteip:remote 端口标识。在这种情况下，我们有 MySQL IP 和端口以及客户端 IP，因此我们只能更改具有有限范围的本地端口。请注意，即使在您关闭连接之后，TCP/IP 堆栈也必须将端口保留一段时间，这就是 tcp_fin_timeout 的来源。
回答by yeeking
I had this problem and solved it using persistent connection mode, which can be activated in mysqli by pre-fixing the database hostname with a 'p:'
我遇到了这个问题并使用持久连接模式解决了它，可以通过在数据库主机名前面加上“p:”来在 mysqli 中激活它
$link = mysqli_connect('p:localhost', 'fake_user', 'my_password', 'my_db');
From: http://php.net/manual/en/mysqli.persistconns.php:
来自： http: //php.net/manual/en/mysqli.persistconns.php：
The idea behind persistent connections is that a connection between a client process and a database can be reused by a client process, rather than being created and destroyed multiple times. This reduces the overhead of creating fresh connections every time one is required, as unused connections are cached and ready to be reused. ...
To open a persistent connection you must prepend p: to the hostname when connecting.
持久连接背后的想法是客户端进程和数据库之间的连接可以被客户端进程重用，而不是被多次创建和销毁。这减少了每次需要时创建新连接的开销，因为未使用的连接被缓存并准备重用。...
要打开持久连接，您必须在连接时将 p: 添加到主机名。
回答by fgwaller
With Vicidial I have run into the same problem frequently, due to the kind of programming used, new MYSQL connections have to be established (very) frequently from a number of vicidial components, we have systems hammering the db server with over 10000 connections per second, most of which are serviced within a few ms and which are closed within a second or less. From experience I can tell you that in a local network, with close to no lost packages, tcp_fin_timeout can be reduced all the way down to 3 with no problems showing up.
使用 Vicidial 我经常遇到同样的问题，由于使用的编程类型，必须（非常）频繁地从许多 vicidial 组件建立新的 MYSQL 连接，我们有系统以每秒超过 10000 个连接的速度冲击数据库服务器，其中大部分在几毫秒内得到服务，并在一秒或更短的时间内关闭。根据经验，我可以告诉你，在本地网络中，几乎没有丢失的包，tcp_fin_timeout 可以一直减少到 3，而不会出现任何问题。
Typical linux commands to diagnose if connections waiting to be closed is your problem are:
用于诊断等待关闭的连接是否是您的问题的典型 linux 命令是：
netstat -anlp | grep :3306 | grep TIME_WAIT -wc
which will show you the number of connections that are waiting to be closed completely.
这将显示等待完全关闭的连接数。
netstat -nat | awk {'print '} | cut -d ":" -f1 | sort | uniq -c | sort -n
which will show the connections per connected host, allowing you to identify which other host is folding your system if there are multiple candidates.
这将显示每个连接的主机的连接，如果有多个候选主机，则允许您识别哪个其他主机正在折叠您的系统。
To test the fix you can just
要测试修复程序，您可以
cat /proc/sys/net/ipv4/tcp_fin_timeout
echo "3" > /proc/sys/net/ipv4/tcp_fin_timeout
which will temporarily set the tcp_fin_timeout to 3 sec and tell you how many seconds it was before, so you can revert to the old value for testing.
这将暂时将 tcp_fin_timeout 设置为 3 秒并告诉您它之前是多少秒，因此您可以恢复到旧值进行测试。
As a permanent fix I would suggest you add the following line to /etc/sysctl.conf
作为永久修复，我建议您将以下行添加到 /etc/sysctl.conf
net.ipv4.tcp_fin_timeout=3
Within a good local network with should not cause any trouble, if you do run into problems e.g. because of packet loss, you can try
在一个良好的本地网络内应该不会造成任何问题，如果您遇到问题例如因为丢包，您可以尝试
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=0
net.ipv4.tcp_fin_timeout=10
Wiche allows more time for the connection to close and tries to reuse same ip:port combinations for new connections to the same host:service combination.
Wiche 为连接关闭留出更多时间，并尝试为新连接重用相同的 ip:port 组合到相同的 host:service 组合。
OR
或者
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=10
Which will even more aggressively try to reuse connections, what can however create new problems with other applications for example with your webserver. So you should try the simple solution first, in most cases it will already fix your problem without any bad side effects!
哪个会更积极地尝试重用连接，但是可能会给其他应用程序（例如您的网络服务器）带来新问题。因此，您应该先尝试简单的解决方案，在大多数情况下，它已经可以解决您的问题，而不会产生任何不良副作用！
Good Luck!
祝你好运！
回答by TheSatinKnight
Vicidial servers regularly require increasing the connection limit in MySQL. Many installations (and we've seen and worked on a lot of them) have had to do this by modifying the limit
Vicidial 服务器经常需要增加 MySQL 中的连接限制。许多安装（我们已经看到并处理了很多）不得不通过修改限制来做到这一点
Additionally there have been reports of conntract_Max requiring increase in
此外，还有报告称 conntract_Max 需要增加
/sbin/sysctl -w net.netfilter.nf_conntrack_max=196608
when the problem turns out to be networking related.
当问题被证明与网络相关时。
Also note that Vicidial has some specific suggested settings and even some enterprise settings for mysql configuration. Have a look in my-bigvici.cnf in /usr/src/astguiclient/conf for some configuration ideas that may open your mysql server up a bit.
还要注意 Vicidial 有一些特定的建议设置，甚至还有一些用于 mysql 配置的企业设置。查看 /usr/src/astguiclient/conf 中的 my-bigvici.cnf 以了解一些可能会稍微打开您的 mysql 服务器的配置想法。
So far, no problems have resulted from increasing connection limits, just additional resources used. Since the purpose of the server is to make this application work, dedicating resources to this application does not seem like a problem. LOL
到目前为止，增加连接限制并没有导致任何问题，只是使用了额外的资源。由于服务器的目的是使此应用程序工作，因此将资源专用于此应用程序似乎不是问题。哈哈
回答by M_per
We had the same problem. Although "tcp_fin_timeout" and "ip_local_port_range" solutions worked, the real problem was poorly writen PHP script, which just created new connection almost every second query it made to database. Rewriting script to connect just once solved all trouble. Please be aware that lowering "tcp_fin_timeout" value may be dangerous, as some code may depend on DB connection being still there after some time after connection. It's rather a dirty duct tape and bubble gum path than real solution.
我们遇到了同样的问题。尽管“tcp_fin_timeout”和“ip_local_port_range”解决方案有效，但真正的问题是PHP脚本编写得不好，它几乎每秒钟对数据库进行一次查询就创建新连接。重写脚本连接一次就解决了所有问题。请注意，降低“tcp_fin_timeout”值可能很危险，因为某些代码可能取决于连接后一段时间后数据库连接仍然存在。与其说是真正的解决方案，不如说是肮脏的胶带和泡泡糖路径。
