Highly Scalable Connection Pooling in PHP

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.

Comments:

Is this feature being implemented in 11i applications as well where ever applicable ?

Posted by Nandita Saigal on January 04, 2007 at 02:50 AM PST #

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.

Posted by Christopher Jones on January 04, 2007 at 04:38 AM PST #

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

Posted by Anil Gurnani on April 15, 2007 at 06:12 PM PDT #

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!

Posted by cbryll on November 19, 2007 at 11:38 AM PST #

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.

Posted by Christopher Jones on November 21, 2007 at 06:26 AM PST #

For cross reference purposes, the OCI8 extension announcement is in http://blogs.oracle.com/opal/2007/10/connection_pooling_in_php_avai.html

Posted by Christopher Jones on November 21, 2007 at 06:29 AM PST #

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today