mysqli opens multiple new processes with p: connec

2019-03-01 02:58发布

问题:

This is my first time asking a question on here. I've scoured Google, stackoverflow, etc. looking for help with the issue I'm having. We're currently using PHP 5.3.10 & MySQL 5.0.95 with Apache 2.2.21 (CentOS).

We're in the process of starting to cut over from the old mysql library to mysqli in new code, and I'm leading the charge. I've tried

  • making sure I explicitly close the connection to the database when I'm done with it
  • freeing result sets when I'm done with them
  • upping the connection limit to 250 from 150

There are included files (having to do with session checking, etc.) that use the old style mysql_pconnect() to validate certain things. These are included in nearly all of our code.

something like the code:

$mysqli =  new mysqli('p:'.DBHOST, DBUSER, DBPASS, $_SESSION['dbname']);
if ($mysqli->connect_error) {
    throw new Exception($mysqli->connect_error,  $mysqli->connect_errno);
    exit;
}
// do my stuff here, a bunch of SQL queries like:
$sql = 'SELECT * FROM MyTable';
$result = $mysqli->query($sql);
if (!$result) {
    throw new SQLException($sql, $mysqli);
    exit;  
    // SQLException is an extension to mysqli_sql_exception that adds the 
    // query into the messaging internally
}
while ($result && $row = $result->fetch_assoc()) {
// do stuff here, like show it on screen, etc., all works normally
}
$result->free(); // free up the result
$mysqli->close(); // close the connection to the database

freeing the results and closing the connection were things I did after getting a "Too many connections" error. Before doing that, I would get 3-4 new database connections each time I ran my program. (viewed in the back end with SHOW PROCESSLIST)

The problem is lessened somewhat (it adds 0 to 3 new connections, rather than 3 new connections each time).

Some of my reading suggests that this could have something to do with Apache threading + the new persistent connections added if there are no existing idle ones in the current thread. Is it this? Are persistent connections not supported well with mysqli? (should I give up on persistence?)

Thanks for any suggestions you might have.

回答1:

I have no experience with mysqli persistent connections but some of your questions and expectations looks strange to me.

mysqli opens multiple new processes with p: connect option

Yes, that's what permanent connections are for

making sure I explicitly close the connection to the database when I'm done with it

You cannot make sure you closed it explicitly as you just can't do that. Again because the only point of permanent connection is to lasts open

I would get 3-4 new database connections each time I ran my program.

So, you have to make sure you're opening only one.

  • You have at least two connects from your script - one old style and some from mysqli?
  • How many mysqli objects being instantiated?
  • How many php scripts being run to serve one HTTP request? Are you sure?

After all, if it bothers you so much, why you're using persistent connections? Are you any real (not imaginary) benefits from it? After all, if your version is 5.3, why bother with rewriting from mysql at all?



标签: php mysqli