Connection pooling in PHP

Question

Is it possible to cache database connections when using PHP like you would in a J2EE container? If so, how?

Geier

Have you looked at persistent connects using mysql_pconnect()? – Masjid

How about this? github.com/junamai2000/mod_namy_pool – Kenweigh

Actually there's something about connection pooling in PHP, check out the manual: php.net/manual/en/mysqlnd-ms.pooling.php and php.net/manual/en/mysqlnd-mux.connection_pool.php – Leake

Answer 1

There is no connection pooling in php.
mysql_pconnect and connection pooling are two different things. There are many problems connected with mysql_pconnect and first you should read the manual and carefully use it, but this is not connection pooling.

Connection pooling is a technique where the application server manages the connections. When the application needs a connection it asks the application server for it and the application server returns one of the pooled connections if there is one free.

We can do connection scaling in php for that please go through following link: http://www.oracle.com/technetwork/articles/dsl/white-php-part1-355135.html

So no connection pooling in php.

As Julio said apache releases all resources when the request ends for the current reques. You can use mysql_pconnect but you are limited with that function and you must be very careful. Other choice is to use singleton pattern, but none of this is pooling.

This is a good article: https://blogs.oracle.com/opal/highly-scalable-connection-pooling-in-php

Also read this one http://www.apache2.es/2.2.2/mod/mod_dbd.html

Roundel

No connection pooling? Wow. – Olli

"The connection to the SQL server will not be closed when the execution of the script ends." - php.net/manual/en/function.mysql-pconnect.php – Blakey

Working link for the oracle blog: blogs.oracle.com/opal/entry/highly_scalable_connection_poo – Malkamalkah

@Olli see swoole/openswoole - its a widely popular extension to php that basically makes php async and adds tons of enterprise features. You can easily do connection pools in php wihth it: see this for example github.com/open-smf/connection-pool, see hyperf.wiki/2.2/#/en/pool. Im using it with pdo/mysql and phpredis - and its amazing! – Electropositive

If that is the case, does this make J2EE or Java, in general, better than PHP in this context? Or to put it in other words, how does PHP solve the problem of needing to reconnect to the DB every time a request is made to the server? – Decumbent

Answer 2

Persistent connections are nothing like connection pooling. A persistent connection in php will only be reused if you make multiple db connects within the same request/script execution context. In most typical web dev scenarios you'll max out your connections way faster if you use mysql_pconnect because your script will have no way to get a reference to any open connections on your next request. The best way to use db connections in php is to make a singleton instance of a db object so that the connection is reused within the context of your script execution. This still incurs at least 1 db connect per request, but it's better than making multiple db connects per reqeust.

There is no real db connection pooling in php due to the nature of php. Php is not an application server that can sit there in between requests and manage references to a pool of open connections, at least not without some kind of major hack. I think in theory you could write an app server in php and run it as a commandline script that would just sit there in the background and keep a bunch of db connections open and pass references to them to your other scripts, but I don't know if that would be possible in practice, how you'd pass the references from your commandline script to other scripts, and I sort of doubt it would perform well even if you could pull it off. Anyway that's mostly speculation. I did just notice the link someone else posted to an apache module to allow connection pooling for prefork servers such as php. Looks interesting: https://github.com/junamai2000/mod_namy_pool#readme

Nourishing

"The connection to the SQL server will not be closed when the execution of the script ends." - php.net/manual/en/function.mysql-pconnect.php – Blakey

"The best way to use db connections in php is to make a singleton instance of a db object so that the connection is reused within the context of your script execution." - This is the golden goose I am looking for. Great way to explain exactly where and why the singleton would be useful! – Inoculum

Answer 3

I suppose you're using mod_php, right?

When a PHP file finishes executing all it's state is killed so there's no way (in PHP code) to do connection pooling. Instead you have to rely on extensions.

You can mysql_pconnect so that your connections won't get closed after the page finishes, that way they get reused in the next request.

This might be all that you need but this isn't the same as connection pooling as there's no way to specify the number of connections to maintain opened.

Bini

Can the connection be reused by different users?If so,why the manual says that we should configure MySQL to avoid too many connections? – Rimmer

Answer 4

You can use MySQLi.

For more info, scroll down to Connection pooling section @ http://www.php.net/manual/en/mysqli.quickstart.connections.php#example-1622

Note that Connection pooling is also dependent on your server (i.e. Apache httpd) and its configuration.

Bowrah

This answer should be up modded, as all the others are very old. Connection pooling and the problems it can cause are very real! – Amphigory

Answer 5

If an unused persistent connection for a given combination of "host, username, password, socket, port and default database can not be found" in the open connection pool, then only mysqli opens a new connection otherwise it would reuse already open available persistent connections, which is in a way similar to the concept of connection pooling. The use of persistent connections can be enabled and disabled using the PHP directive mysqli.allow_persistent. The total number of connections opened by a script can be limited with mysqli.max_links (this may be interesting to you to address max_user_connections issue hitting hosting server's limit). The maximum number of persistent connections per PHP process can be restricted with mysqli.max_persistent.

In wider programming context, it's a task of web/app server however in this context, it's being handled by mysqli directive of PHP itself in a way supporting connection re-usability. You may also implement a singleton class to get a static instance of connection to reuse just like in Java. Just want to remind that java also doesn't support connection pooling as part of its standard JDBC, they're being different module/layers on top of JDBC drivers.

Coming to PHP, the good thing is that for the common databases in the PHP echosystem it does support Persistent Database Connections which persists the connection for 500 requests (config of max_requests in php.ini) and this avoids creating a new connection in each request. So check it out in docs in detail, it solves most of your challenges. Please note that PHP is not so much sophisticated in terms of extensive multi-threading mechanism and concurrent processing together with powerful asynchronous event handling, when compared to strictly object oriented Java. So in a way it is very less effective for PHP to have such in-built mechanism like pooling.

Dutch

Answer 6

I would like to suggest PDO::ATTR_PERSISTENT

Persistent connections are links that do not close when the execution of your script ends. When a persistent connection is requested, PHP checks if there's already an identical persistent connection (that remained open from earlier) - and if it exists, it uses it. If it does not exist, it creates the link.

Cavallaro

Answer 7

You cannot instantiate connection pools manually.

But you can use the "built in" connection pooling with the mysql_pconnect function.

Harv

Answer 8

Connection pooling works at MySQL server side like this.

1. If persistence connection is enabled into MySQL server config then MySQL keep a connection open and in sleep state after requested client (php script) finises its work and die.
2. When a 2nd request comes with same credential data (Same User Name, Same Password, Same Connection Parameter, Same Database name, Maybe from same IP, I am not sure about the IP) Then MySQL pool the previous connection from sleep state to active state and let the client use the connection. This helps MySQL to save time for initial resource for connection and reduce the total number of connection.

So the connection pooling option is actually available at MySQL server side. At PHP code end there is no option. mysql_pconnect() is just a wrapper that inform PHP to not send connection close request signal at the end of script run.

Emeraldemerge

Answer 9

For features such as connection pooling - you need to install swoole extension first: https://openswoole.com/

It adds async features to php.

After that its trivial to add mysql and redis connection pooling:
https://github.com/open-smf/connection-pool

Some PHP frameworks come with pooling built-in: https://hyperf.wiki/2.2/#/en/pool

Electropositive