By Juan Pablo Bosnjak & Javier Neyra
Introduction
Almost all applications use a database, furthermore, almost all applications with a database find that their main performance bottleneck is in their own database. Databases are too slow compared to code processing, but we need them.
We spend a lot of time improving queries, avoiding full scans, but we forget that to open, establish and close a connection to the database often takes more time than the query itself.
I am new in the PHP world (I come from J2EE), so imagine my surprise when I found out that most PHP productive applications do not use connection pooling.
After some research, we have found that PHP does not provide native connection pool support. Fortunately, I came across a new feature in Oracle 11g: Database Resident Connection Pooling (DRCP).
Basically, DRCP is a feature of Oracle Database 11g: it has been designed to address scalability requirements in environments that require a large number of connections with minimal database resource usage. DRCP pools a set of dedicated database server processes (pooled servers), which can be shared across multiple applications running on the same or several hosts.
For additional information, check:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc002.htm#ADMIN12483
Let’s give it a try.
Test Case – Simple PHP web Page
PHP test page:
Now, let’s create the pool in our Oracle 11g+ database:
Note: pool parameters should be defined according to your application needs.
Let’s create 2 simple scenarios with JMeter.
10 clients made concurrent HTTP request connections for 30 seconds with think time at 0 milliseconds.
Environment
-
3 desktop machines, one for Oracle, one for Apache, one for Jmeter.
-
Standard LAN megabit layout.
-
Apache configuration:
We used Apache 2.4 with the Event module as MPM with the following configuration parameters:
-
StartServers: 1
-
MinSpareThreads: 5
-
MaxSpareThreads: 10
-
ThreadLimit : 10
-
ThreadsPerChild: 10
-
ServerLimit: 1
-
MaxRequestWorkers: 10
For PHP we used php-fpm with PHP5.6 with the following settings:
-
pm: dynamic
-
pm.max_children: 50
-
pm.start_servers : 8
-
pm.min_spare_servers: 4
-
pm.max_spare_servers: 16
-
pm.max_requests: 500
-
Results
With Pool Connections:
Without Pool Connections:
Result Summary
Test |
# Request |
#Average response time |
Throughput |
Pooled |
18255 |
6 ms |
605,0 request / sec |
Not Pooled |
1631 |
89 ms |
53,8 request / sec |
Conclusions
Results show a huge difference using pooled connections. Your application will be able to handle ten times more throughput with pooled connections.
Also you should consider that DBAs will thank you a lot:
-
Database Memory usage will decrease a lot (at least 4 times less memory is used).
-
DBAs know how many connections will be established from your application.
-
High concurrent peaks of requests won’t crash the database, plus other applications using the same database should not be affected.
The best part is, we only need to make a minor setting change in the connection string; the API, and the code workflow remain exactly the same. You will get a huge performance boost with no effort.
http://www.oracle.com/technetwork/topics/php/php-scalability-ha-twp-128842.pdf
https://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc002.htm#ADMIN12483