Years ago, I noticed that PHP connections to MySQL were significantly slower over IPV6 (host name with an IPv6 AAAA record), when no MySQL service listens on that address. The connection is refused, and PHP has to fallback to IPv4. The fallback takes a significant amount of time. Too much time if you’d asked me. Unfortunately this fallback to IPv4 is still slow today…

Slow PHP connections to MySQL was the main reason for my post on how to disable IPv6 – or prefer IPv4 over IPv6 – in Windows Server, because with having IPv4 preference over IPv6, this is not an issue. Hoping this old bug in PHP’s mysqlnd library would have been solved by now, I created two little test PHP files. With PHP 5.6.5 and MySQLi.

Imagine the following situation, where a mysql hostname has both an IPv4 and IPv6 address, without a listener on IPv6:

$ host mysql.saotn.org
mysql.saotn.org has address 77.94.248.254
mysql.saotn.org has IPv6 address 2a00:f60::254

Knowing that, normally, IPv6 takes precedence over IPv4 (which is configurable), users are left with a slow responding website and database operations, only because connecting to an IPv6 address in PHP is refused. The connection refused isn’t handled correctly, making the fallback to IPv4 slow. It takes mysql.connect_timeout seconds.

Here are my two sample PHP scripts:

<?php
error_reporting(-1);
$time = microtime(TRUE);
$mem = memory_get_usage();

$conn = mysqli_connect( "mysql.saotn.org", "db-user", "db-pass", "db-name" );
if( $result = mysqli_query( $conn, "SELECT COUNT(*) FROM `table`" ) ) {
  $row_cnt = mysqli_num_rows( $result );
  printf( "Result set has %d rows.\n", $row_cnt );
  mysqli_free_result( $result );
}

mysqli_close( $conn );
print_r( array( 'memory' => ( memory_get_usage() - $mem ) / ( 1024 * 1024 ), 'microtime' => microtime( TRUE ) - $time ) );
?>
<?php
error_reporting(-1);
$time = microtime( TRUE );
$mem = memory_get_usage();

$conn = mysqli_connect( "77.94.248.254", "db-user", db-pass", "db-name" );
if( $result = mysqli_query( $conn, "SELECT COUNT(*) FROM `table`" ) ) {
$row_cnt = mysqli_num_rows( $result );
  printf( "Result set has %d rows.\n", $row_cnt );
  mysqli_free_result( $result );
}

mysqli_close( $conn );
print_r(array( 'memory' => ( memory_get_usage() - $mem ) / ( 1024 * 1024 ), 'microtime' => microtime( TRUE ) - $time ) );
?>

For the database hostname, I used mysql.saotn.org in one PHP script, and the IPv4 address 77.94.248.254 in the other. In stead of the IPv4 address, you may also use gethostbyname("mysql.saotn.org"), since gethostbyname only works for IPv4 addresses.

And the script output:

Result set has 1 rows.
Array
(
    [memory] => 0.000518798828125
    [microtime] => 0.0075149536132812
)
Result set has 1 rows.
Array
(
    [memory] => 0.000518798828125
    [microtime] => 1.0293030738831
)

Did you notice the huge microtime difference?!

Conclusion slow MySQL connections with PHP

PHP’s fallback from IPv6 to IPv4 is very slow. PHP needs to fallback from IPv6 to IPv4 when no MySQL service listens on IPv6. The PHP developers should really work on improving php_network_connect_socket.

On a side note, this may only be an issue with mysqlnd library and not libmysqlclient. Using the libmysqlclient library requires compiling your own PHP.

This may interest you:   Secure WordPress uploads folder, disable PHP execution

See PHP’s Choosing a library for more information on the libraries. On Windows Server you can change the protocol preference, by disabling IPv6, or prefer IPv4 over IPv6.

IPv6 support in MySQL

Starting from MySQL version 5.5.3 there is support for remote IPv6 connections in MySQL. Just add to your my.cnf configuration file:

bind-address = ::

Now MySQL listens on both IPv4 and IPv6, and MySQL will accept connections from both protocols.