Connection Pool

[systech44]

Hello,

I like to know about the term 'Connection Pool'. Along with this please let me know the followings.

1. How to establish connection pool using PHP and MySQL ?
2. How to use it efficiently in OOPS programing ?

Hope to hear you soon.

Thanks & Regards.

[Nightwalker83]

From Wikipedia (http://en.wikipedia.org/wiki/Connection_pool):


In software engineering, a connection pool is a cache of database connections maintained by the database so that the connections can be reused when the database receives future requests for data. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

[penagate]

(1) You do not need to maintain your own connection pool. In fact, because PHP is request-based and not a stateful application in its own right, maintaining a pool of connections is impossible. The MySQL driver maintains its own pool of connections which you can use from PHP by calling the mysql_pconnect function (or its equivalent in a data access library).

(2) In the majority of cases the build-up/tear-down overhead of non-persistent connections is minimal to non-existent, and this is especially true of MySQL connections. In such cases I would argue that persistent connections introduce more pitfalls than performance benefits. You can read about these pitfalls on the manual page for the mysql_pconnect function: Most boil down to the fact that many SQL functions we rely on (variables, session variables, transactions, locks) are connection-specific, and since pooling, by nature, reuses connections, it is easy to be caught out by entering the state created by the last time the connection was used.

The simple (one connection per request) approach scales well enough for most situations.