PHP-MySQLi连接随机失败，“无法分配请求的地址” [英] PHP-MySQLi connection randomly fails with "Cannot assign requested address"
问题描述
与MySQL服务器连接的故事很长时间失败，出现错误消息：
警告：mysqli :: real_connect（） ：（HY000 / 2002）：不能分配请求的地址..
MySQL在不同的 ，托管在Rackspace Cloud
今天我们将其版本降级为
Ver 14.14 Distrib 5.1.42，for debian- linux-gnu（x86_64）。
根据它的状态变量，DB服务器处理每秒查询平均值：5327.957很忙/ p>
MySQL在log-warnings = 9中，但不记录拒绝连接的交战。
站点和齿轮工人的脚本都失败了，这个错误让我们说1％的概率。
没有服务器负载似乎不是我们监控的一个因素。 （CPU负载，IO负载或MySQL负载）
最大数据库连接（max_connections）设置为200，但是我们从未处理过与数据库的100个同时连接
/ p>
我怀疑TCP网络问题，而不是PHP / MySQL配置问题。
任何人都可以告诉我如何找到它？
更新：
连接代码是：
$ this-> _mysqli = mysqli_init（）;
$ this-> _mysqli->选项（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。\\\
;
DataStoreException :: raiseHostUnreachable（$ error）;
}
}
a href =http://peter-zaitsev.livejournal.com/14954.html =noreferrer> MySQL：使用巨大的连接数
什么是频繁连接的危险？
除了一些极端情况外，它的运行良好。如果您从同一个盒子中获得每秒数百个连接，则可能会使本地端口号码耗尽。修复它的方法可能是 - 在Linux上减少/ proc / sys / net / ipv4 / tcp_fin_timeout（这打破了TCP / IP标准，但您可能不在乎本地网络），增加/ proc / sys / net / ipv4 / ip_local_port_range在客户端上。其他操作系统具有相似的设置。您也可以为同一数据库主机使用更多的Web框或多个IP来解决此问题。
有关此问题的一些背景：
TCP / IP连接由localip标识：localport remoteip：remote港口。在这种情况下，我们有MySQL IP和端口以及客户端IP固定，所以我们只能改变具有有限范围的本地端口。注意即使关闭连接TCP / IP协议栈必须保留端口一段时间，这是tcp_fin_timeout来自哪里。
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:
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
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.
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
It happens with and without the firewall software.
I suspect TCP Networking problem rather than PHP/MySQL configurationn problem.
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);
}
}
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.
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.
这篇关于PHP-MySQLi连接随机失败，“无法分配请求的地址”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
