PHP OCI8 and Oracle 11g DRCP Connection Pooling in Pictures
- by christopher.jones
Here is a screen shot from a PHP OCI8 connection pooling demo that I
like to run. It graphically shows how little database host memory is
needed when using DRCP connection pooling with Oracle Database 11g.
Migrating to DRCP can be as simple as starting the pool and
changing the connection string in your PHP application.
The script that generated the data for this graph was a simple
"Parts" query application being run under various simulated user
loads. I was running the database on a small Oracle Linux server with
just 2G of memory. I used PHP OCI8 1.4. Apache is in pre-fork mode,
as needed for PHP.
Each graph has time on the horizontal access in arbitrary 'tick'
time units.
Click the image to see it full sized.
Pooled connections
Beginning with the top left graph, At tick time 65 I used Apache's
'ab' tool to start 100 concurrent 'users' running the
application. These users connected to the database using DRCP:
$c = oci_pconnect('phpdemo', 'welcome', 'myhost/orcl:pooled');
A second hundred DRCP users were added to the system at tick 80 and
a final hundred users added at tick 100. At about tick 110 I stopped
the test and restarted Apache. This closed all the connections.
The bottom left graph shows the number of statements being executed
by the database per second, with some spikes for background database
activity and some variability for this small test. Each extra batch of
users adds another 'step' of load to the system.
Looking at the top right Server Process graph shows the database
server processes doing the query work for each web user. As user load
is added, the DRCP server pool increases (in green). The pool is
initially at its default size 4 and quickly ramps up to about (I'm
guessing) 35. At tick time 100 the pool increases to my configured
maximum of 40 processes. Those 40 processes are doing the query work
for all 300 web users. When I stopped the test at tick 110, the
pooled processes remained open waiting for more users to connect. If
I had left the test quiet for the DRCP 'inactivity_timeout'
period (300 seconds by default), the pool would have shrunk back to 4
processes.
Looking at the bottom right, you can see the amount of memory being
consumed by the database. During the initial quiet period about 500M
of memory was in use. The absolute number is just an indication of my
particular DB configuration. As the number of pooled processes
increases, each process needs more memory. You can see the shape of
the memory graph echoes the Server Process graph above it. Each of
the 300 web users will also need a few kilobytes but this is almost
too small to see on the graph.
Non-pooled connections
Compare the DRCP case with using 'dedicated server'
processes.
At tick 140 I started 100 web users who did not use pooled
connections:
$c = oci_pconnect('phpdemo', 'welcome', 'myhost/orcl');
This connection string change is the only difference between the two
tests.
At ticks 155 and 165 I started two more batches of 100 simulated
users each. At about tick 195 I stopped the user load but left Apache
running. Apache then gradually returned to its quiescent state,
killing idle httpd processes and producing the downward slope at the
right of the graphs as the persistent database connection in each
Apache process was closed.
The Executions per Second graph on the bottom left shows the same
step increases as for the earlier DRCP case. The database is handling
this load.
But look at the number of Server processes on the top right graph.
There is now a one-to-one correspondence between Apache/PHP processes
and DB server processes. Each PHP processes has one DB server
processes dedicated to it. Hence the term 'dedicated server'.
The memory required on the database is proportional to all those
database server processes started. Almost all my system's memory was
consumed. I doubt it would have coped with any more user load.
Summary
Oracle Database 11g DRCP connection pooling significantly reduces
database host memory requirements allow more system memory to be
allocated for the SGA and allowing the system to scale to handled
thousands of concurrent PHP users.
Even for small systems, using DRCP allows more web users to be
active.
More information about PHP and DRCP can be found in the PHP
Scalability and High Availability chapter of The
Underground PHP and Oracle Manual.
