Is the following the right way to implement database connection pool for Pear DB?

Question

I understand there is no connection pooling in PHP Connection pooling in PHP, and we are currently using Pear DB.

I have a legacy cron job code, which is using pear DB connection.

while (true) {
    ...
    foreach ($keys as $key) {
        $connection_string = get_connection_string_based_on_key($key);
        $DB = & \DB::connect($connection_string);
        ...
        // Avoid resource leakage.
        $DB->disconnect();
    }
}

We realize DB::connect does give us some performance hotspot. I plan to make a pseudo connection pool

$pool = array();
while (true) {
    ...
    foreach ($keys as $key) {
        $connection_string = get_connection_string_based_on_key($key);
        if (array_key_exists ($connection_string, $pool) {
            $DB = $pool[$connection_string];
        } else {
            $DB = & \DB::connect($connection_string);
            $pool[$connection_string] = $DB;
        }

        ...
        // No $DB->disconnect(); As we want the 
        // DB connection remains valid inside the pool.
    }
}

The cron job might run for several days, several weeks or several months. I was wondering, is there any catcha behind such pseudo connection pool? For instance,

Will DB connection remain valid, after it stays inside pool for a long period (Says a week)?
Possible run out of DB resource? If yes, what is a suitable mechanism to handle ever growing pool?

What is the purpose of your script ? Why do you need to keep your connections open ? Technically it may work but your database may kill your connections if your keep them opened for a long time. It your script keep running of a very long time and DB is not useful I suggest to close and open the DB each time your need it. If you really need them always open you can keep them in your pool but you must add the case the DB killed you, and reopen the connection if necessary. — Kevin Labécot, Jun 22 '14 at 11:41
I would have thought that this is an issue about 'frequency' of use. i.e. if you are going to access the database a lot in the next few minutes then do not close the connections but keep re-using the active connections. When it goes quiet then allow them to close. So keep a last used time with any connection and close it after some 'not busy' time interval. I assume a list of active but free connections is available. — Ryan Vincent, Jun 27 '14 at 23:42

score 1 · Answer 1 · edited May 23 '17 at 12:20

It is not the question concerning your PHP code. The connection timeout and max number of simultaneous connections have to be configured within your database system.

When using mysql:

connections: http://www.electrictoolbox.com/update-max-connections-mysql/

timeout: How can I change the default Mysql connection timeout when connecting through python?

I think connect_timeout=0 means that a mysql db will try to keep the connection open as long as possible. There is no configuration option for unlimited connections (with respect to system resources) as far as I know.

Is the following the right way to implement database connection pool for Pear DB?

1 Answers1