[SOLVED] PHP-MySQLi connection randomly fails with "Cannot assign requested address"

Issue

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);
    }
} 

Solution

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.

Answered By - Naktibalda

Answer Checked By - Timothy Miller (WPSolving Admin)