X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

  • php
    January 3, 2007

Highly Scalable Connection Pooling in PHP

Christopher Jones
Senior Principal Product Manager

My guest blogger today is Luxi Chidambaran talking about Oracle Database Resident Connection Pooling - a connection pooling feature in the next major release of the Oracle DB that will allow very high traffic web sites to scale better.  We announced this feature at Oracle OpenWorld in 2006. One cool thing about the feature is that it is not just limited to PHP - any database client could use it. Now over to Luxi.

Oracle Database Resident Connection Pooling

Pooling Software Objects In General


Pooling objects is an optimization that is widely adopted in many scenarios. Pooling an object makes sense if the object:

  •   is required for a relatively short duration
  •   is expensive to create every time it is required
  •   it is expensive to tear down when not required
  •   is expensive to maintain all the time (even when not required) because it consumes resources

Pooling such objects enables the user that needs the object to quickly access it from the pool and release it back when the object is not required, thereby enabling other users to reuse the pooled object. This typically improves system performance dramatically.

Pooling Database Connections


Database connections generally satisfy all of the above mentioned criteria. Database connection establishment involves creation of the network connection, spawning an associated operating system process/thread, performing the requisite authentication, creation of the session involving session private contexts for SQL statements etc. Tearing down database connections is also expensive. And needless to state that unnecessarily maintaining them when not required just eats up precious resources on the database server. That explains why database connection pooling greatly helps high traffic web applications where connections are needed for short durations. Most multithreaded systems that talk to a database offer some form of database connection pooling.

Currently PHP Apache processes, being single threaded, are unable to pool opened database connections; each process uses its own connection. PHP users are left with two options: either to repeatedly create and tear down the connection or to keep the connection around. The former option is typically called non persistent connections and the latter is called persistent connections. Neither of these options really achieves pooling.

Oracle Database Resident Connection Pooling

The next major release of Oracle will have support for Database Resident Connection Pooling (DRCP) which performs pooling on the database side. This allows process-oriented architectures such as PHP to take advantage of Database connection pooling in the absence of a middle tier connection pooling process. Since DRCP is server side functionality, it can be leveraged across middle tier processes and middle tier boxes by all clients that want pooling of database connections.

DBA View of DRCP


The DRCP is optionally enabled by the DBA on the database side. The DRCP is configured by specifying the minimum and maximum number of connections allowed. The DBA can also configure inactivity timeout settings and specify the number of cursors to be cached per connection. All of this is achieved by calls to a new PL/SQL connection pool package. Starting the pool brings up the minimum number of pooled connections and DRCP dynamically sizes the number of connections in response to load.

There are some DBA views available for monitoring DRCP activity and performance as well.

Client Side View of DRCP


On the client side, the client needs to specify ":POOLED" in the Easy Connect string that directs the client to the pool instead of getting a dedicated server connection:

  hostname:port/service:POOLED

For users who use TNS style connect strings, there is an equivalent option:

  (SERVER=POOLED)

All clients connecting as a certain user, e.g. "SCOTT" can then share other "SCOTT" connections from the pool.

At the Oracle 'C' interface level, there is an additional parameter called "connection_class" that allows for further sub-pools within a user's pool. This allows for separation of distinct applications that may connect as the same user to the database. The connection_class can be specified programmatically in the OCI connect calls.

PHP User View


Oracle is proposing enhancements to the open source PHP oci8 extension so that PHP can leverage the pooling functionality. The oci8 extension would be modified to use the DRCP by getting connections from the pool and releasing the connection at the end of the script or when the connection is not in use by the application. E.g. the oci_pconnect() call will get the connection from the DRCP and an explicit or implicit indication that the connection is not in use will release the connection back to the pool.

The proposal includes an additional php.ini setting  that allows for identification of all Apache servers that run the same application:

  oci8.connection_class="NAME"

This allows all of the Apache instances to share connections from the same sub-pool on the database side, thereby achieving pooling across middle tier instances.

More Information


For more information on the proposed oci8 changes, or if anyone is interested in contributing to the oci8 enhancements or specification, please contact us.

Join the discussion

Comments ( 6 )
  • Nandita Saigal Thursday, January 4, 2007
    Is this feature being implemented in 11i applications as well where ever applicable ?
  • Christopher Jones Thursday, January 4, 2007
    Good question. I know that product architecture groups are aware of the feature. Sorry I can't give any details of their evaluations but I will pass your comment along.
  • Anil Gurnani Monday, April 16, 2007
    I would be interested in contributing to make the oci8 extension use connection pooling. I have a lot of experience with Oracle including building an extension for Kiva Enterprise Server (1998) which later became Netscape Application Server, which later became iPlanet Applicatin Server, which later became Java Enterprise Server and which is now Sun Java Application Server (I think :-) Anil
  • cbryll Monday, November 19, 2007
    How does tnsname (server = pooled) differ from tnsname (server = shared) - MTS in oracle 10g? We have a web application that has the front end connecting to the oracle db using shared servers. Though this works mostly, MTS has 'issues/bugs' in Oracle. Looking for a better way to connection pool front end PHP to backend Oracle databases. Thanks!
  • Christopher Jones Wednesday, November 21, 2007
    There is a comparison with Shared Server (aka MTS) in the DRCP whitepaper http://www.oracle.com/technology/tech/oci/pdf/oracledrcp11g.pdf Another difference is that each client-to-server request in a Shared Server deployment goes through the broker. With DRCP, only the initial connection request goes through the broker, but subsequent communication between client and server is similar to a traditional dedicated connection model.
  • Christopher Jones Wednesday, November 21, 2007
    For cross reference purposes, the OCI8 extension announcement is in http://blogs.oracle.com/opal/2007/10/connection_pooling_in_php_avai.html
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.