Highly Scalable Connection Pooling in PHP
January 3, 2007 | 3 minute readSenior 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:POOLEDFor 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.
Christopher Jones
Senior Principal Product Manager
Christopher is a Senior Principal Product Manager working with the Oracle Database Data Access team primarily on APIs for scripting languages including Node.js, Python, PHP and the newly launched ODPI-C library for Oracle Database driver writers. He has responsibilities for Oracle Call Interface (OCI) and related Oracle Database APIs; for Oracle Database clients such as Oracle Instant Client; and for general database adoption by developers, including championing Oracle Database XE. He remains a lead maintainer of PHP's OCI8 extension for Oracle Database. He co-released the popular book "The Underground PHP and Oracle Manual" and is the author of a number of technical articles. Christopher has presented at big and small conferences around the world including the O'Reilly Open Source Convention and Oracle OpenWorld.
Twitter: @ghrd
Email: christopher.jones@oracle.com
Show more