Edit report at https://bugs.php.net/bug.php?id=64549&edit=1
ID: 64549
Comment by: u...@php.net
Reported by: rgagnon24 at gmail dot com
Summary: mysqlnd persistent connection handling out of
control
Status: Open
Type: Bug
Package: MySQL related
Operating System: CentOS 5.9
PHP Version: 5.3.23
Block user comment: N
Private report: N
New Comment:
PDO - that's the thing that does its own pooling, isn't it? Wondering if this
is MySQL specific at all...
Previous Comments:
------------------------------------------------------------------------
[2013-04-02 16:48:20] rgagnon24 at gmail dot com
Question: Why is there a gtk window git pull request recorded on this bug?
------------------------------------------------------------------------
[2013-04-02 16:47:07] rgagnon24 at gmail dot com
Another thing that is probably related to this...
PDO constructor can emit a warning that it really should not be able to. It
just doesn't make any sense:
PHP Warning: PDO::__construct(): MySQL server has gone away in <filepath> on
line <line_number>
This can happen when PDO::ATTR_PERSISTENT => true is passed during PDO
construction, as in:
$dbh = new PDO($dsn_str, $user_name, $password, array(PDO::ATTR_PERSISTENT =>
true));
A warning like "mysql server has gone away" doesn't make any sense here. What
seems to be happening is that the underlying mysqlnd code is finding a
connection in its pool that has died, and it performs a reconnect for you, but
the warning is still emitted on the connection.
You would think if you can't connect, that a PDOException would be raised. No
exception is raised because the connection is in fact returned in a working
condition, but the warning is still emitted to the error system.
The only workaround I could find was to prefix the statement with "@" as in
$dbh = @new PDO($dsn_str, $user_name, $password, array(PDO::ATTR_PERSISTENT =>
true));
Any REAL connection exception is still raised, but at least the fake warning is
suppressed.
------------------------------------------------------------------------
[2013-03-29 16:55:27] rgagnon24 at gmail dot com
Description:
------------
When PHP 5.3 is compiled with
--enable-mysqlnd=shared
--with-mysql=shared,mysqlnd
--with-mysqli=shared,mysqlnd
--with-pdo-mysql=shared,mysqlnd
In order to use the native driver, persistent connections using PDO don't
appear to use any kind of managable or determinate connection pooling.
Running the test script below via apache web server, refreshing the page every
few seconds (10 or 12 times), will produce at least 10 connections to the
database as shown by the mysql "SHOW PROCESSLIST" command... yet the phpinfo()
section will indicate a number that is not the same as the actual number of
connections.
In my test prior to posting, I had 10 actual connections (of which 9 were
sleeping, and the 10th one was just used to run the test query) and phpinfo()
showed 5 active_persistent_connections, and pconnect_success was 8 (under the
mysqlnd stats section).
This leads me to believe there may be a memory leak in the area of code where
the module is managing the connection pool. If no memory leak, the management
of the connections is off somehow as idle connections to a production webserver
are ridiculously high.
Test script:
---------------
<?php
$options = array(
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
);
$host_name = 'database_host';
$database_name = 'some_database';
$port = 3306;
$username = 'db_user';
$password = 'db_pass';
$dsn = sprintf("mysql:host=%s;dbname=%s;port=%d",
$host_name, $database_name, $port);
$dbh = new PDO($dsn, $username, $password, $options);
$sql = 'SELECT * FROM test WHERE id=1 LIMIT 1';
print "<pre>";
$stmt = $dbh->query($sql, PDO::FETCH_ASSOC);
while ($row = $stmt->fetch()) {
var_dump($row);
}
$stmt->closeCursor();
print '</pre>';
Expected result:
----------------
active_persistent_connections and pconnect_success should be accurate to match
what you are really doing. Also the command line 'netstat -anp|grep :3306|grep
httpd|grep ESTABLISHED" should show a limit at some point on the number of
connections that are persistent, or they should get re-used.
Actual result:
--------------
There are a lot of unaccounted for idle ESTABLISHED in the netstat command, for
connections from httpd to mysql when mysqlnd indicates there are not that many.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=64549&edit=1
