Thursday Mar 21, 2013

Python cx_Oracle and Oracle 11g DRCP Connection Pooling

The topic of Oracle 11g DRCP connection pooling in Python cx_Oracle came up twice this week for me. DRCP is a database tier connection pooling solution which is great for applications run in multiple processes. There is a whitepaper on DRCP that covers a lot of background and talks about configuration. This whitepaper is ostensibly about PHP but is good reading for all DRCP users.

The first DRCP and cx_Oracle scenario I dealt with was a question about mod_python.

To cut a long story short, I created a handler and requested it 1000 times via Apache's 'ab' tool. In my first script, and despite having increased the default pool parameters, there were a high number of NUM_WAITS. Also NUM_AUTHENTICATIONS was high. Performance wasn't the best. Querying V$CPOOL_CC_STATS showed:

CCLASS_NAME  NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS NUM_AUTHENTICATIONS
------------ ------------ ---------- ---------- ---------- -------------------
HR.CJDEMO1           1000        992          8        478                1000

At least the session information in each DRCP server was reused (shown via a high NUM_HITS).

Results were better after fixing the script mptest.py to look like:

from mod_python import apache
import cx_Oracle
import datetime

# mptest.py
# Example: Oracle 11g DRCP with cx_Oracle and mod_python

# These pool params are suitable for Apache Pre-fork MPM
mypool = cx_Oracle.SessionPool(user='hr', password='welcome',
         dsn='localhost/orcl:pooled', min=1, max=2, increment=1)

def handler(req):
    global mypool

    req.content_type = 'text/html'
    n = datetime.datetime.now()
    req.write (str(n) + "<br>");

    db = cx_Oracle.connect(user='hr', password='welcome',
            dsn='localhost/orcl:pooled', pool=mypool, cclass="CJDEMO1",
            purity=cx_Oracle.ATTR_PURITY_SELF)

    cur = db.cursor()
    cur.execute('select * from locations')
    resultset = cur.fetchall()
    for result in resultset:
        for item in result:
            req.write (str(item) + " ")
        req.write ("<br>")
    cur.close()
    mypool.release(db)

    return apache.OK

The 'ab' benchmark on this script ran much faster and the stats from V$CPOOL_CC_STATS looked much better. The number of authentications was right down about to about 1 per Apache (ie. mod_python) process:

CCLASS_NAME  NUM_REQUESTS   NUM_HITS NUM_MISSES  NUM_WAITS NUM_AUTHENTICATIONS
------------ ------------ ---------- ---------- ---------- -------------------
HR.CJDEMO1           1000        977         23         13                  26

The NUM_HITS was high again, because the DRCP purity was ATTR_PURITY_SELF. If I hadn't wanted session information to be reused each time the handler was executed, I could have set the purity to ATTR_PURITY_NEW. If I'd done this then NUM_HITS would have been low and NUM_MISSES would have been high.

If you're testing this yourself, before restarting the DRCP pool don't forget to shutdown Apache to close all DB connections. Otherwise restarting the pool will block. Also, if you're interpreting your own V$CPOOL_CC_STATS stats don't forget to account for the DRCP "dedicated optimization" that retains an association between clients (mod_python processes) and the DB. The whitepaper previously mentioned discusses this.

The second place where DRCP and python came up this week was on the cx_Oracle mail list. David Stanek posed a question. He was seeing application processes blocking while waiting for a DRCP pooled server to execute a query. My variant of David's script is drcp2.py:

import os
import time
import cx_Oracle

# drcp2.py
# Example: Sub-optimal connection pooling with Oracle 11g DRCP and cx_Oracle

def do_connection():
    print 'Starting do_connection ' + str(os.getpid())
    con = cx_Oracle.connect(user=user, password=pw, dsn=dsn, cclass="CJDEMO2",
           purity=cx_Oracle.ATTR_PURITY_SELF)
    cur = con.cursor()
    print 'Querying ' + str(os.getpid())
    cur.execute("select to_char(systimestamp) from dual")
    print cur.fetchall()
    cur.close()
    con.close()
    print 'Sleeping ' + str(os.getpid())
    time.sleep(30)
    print 'Finishing do_connection ' + str(os.getpid())
 
user = 'hr'
pw = 'welcome'
dsn = 'localhost/orcl:pooled'
for x in range(100):
    pid = os.fork()
    if not pid:
        do_connection()
        os._exit(0)

This script forks off a bunch of processes - more than the number of pooled DRCP servers (see MAXSIZE in the DBA_CPOOL_INFO view). The first few processes grab a DRCP server from the pool and do their query. But they don't release the DRCP server back to the DRCP pool until after the sleep() when the process ends. The other forked processes are blocked waiting for those DRCP servers to become available. This isn't optimal pool sharing.

My suggestion was to use an explicit cx_Oracle session pool like drcp3.py:

import os
import time
import cx_Oracle

# drcp3.py
# Example: Connection pooling with Oracle 11g DRCP and cx_Oracle
 
def do_connection():
    print 'Starting do_connection ' + str(os.getpid())
    mypool = cx_Oracle.SessionPool(user=user,password=pw,dsn=dsn,min=1,max=2,increment=1)
    con = cx_Oracle.connect(user=user, password=pw,
          dsn=dsn, pool = mypool, cclass="CJDEMO3", purity=cx_Oracle.ATTR_PURITY_SELF)
    cur = con.cursor()
    print 'Querying ' + str(os.getpid())
    cur.execute("select to_char(systimestamp) from dual")
    print cur.fetchall()
    cur.close()
    mypool.release(con)
    print 'Sleeping ' + str(os.getpid())
    time.sleep(30)
    print 'Finishing do_connection ' + str(os.getpid())

user = 'hr'
pw = 'welcome'
dsn = 'localhost/orcl:pooled'
for x in range(100):
    pid = os.fork()
    if not pid:
        do_connection()
        os._exit(0)

The mypool.release(con) call releases the DRCP server back to the DRCP pool prior to the sleep. When this second script is run, there is a smoothness to the output. The queries happen sequentially without noticeably being blocked.

Like with any shared resource, it is recommended to release DRCP pooled servers back to the pool when they are no longer needed by the application.

Update August 2014: Oracle submitted a cx_Oracle patch which Anthony has merged but not yet released. This patch now allows drcp2.py to work efficiently. For the fork scenario the non-explicit session pool solution in drcp2.py is likely to be better than drcp3.py, since it does fewer DRCP requests. For the mod_python example, continuing to use a session pool as shown in mptest.py can minimize the number of authentications, which helps scalability.

Friday Mar 15, 2013

Using PHP 5.5's New "OPcache" Opcode Cache

Zend have contributed their Zend Optimizer+ opcode cache to PHP - thanks Zend!!! (Update 19 March 2013: the renaming to "Zend OPcache" is complete)

"The Zend OPcache provides faster PHP execution through opcode caching and optimization."

The new OPcache extension can be seen as substitute for the venerable APC cache, the maintenance of which had become an issue. Note: although OPcache is now readily available, there is currently nothing preventing you from using any available (working!) opcode cache in PHP 5.5.

A few minutes ago Dmitry Stogov did the physical merge to the PHP 5.5 source's ext/opcache directory. The current PHP 5.5 snapshot has the code. Future Alpha or Beta (and Production) releases will include it too.

Please test OPcache. It is not a panacea for all performance problems. There are a lot of settings which may need adjusting. Understanding how it works and identifying issues during the stabilization phase of PHP 5.5 release process will greatly help.

To build Zend OPcache for PHP 5.5:

When you configure PHP, add the option --enable-opcache like:

./configure ... --enable-opcache

Then make and make install, as normal. This will build OPcache shared extension. It's not possible to build it statically.

Find the shared library in your installation directory with a command like find /home/cjones/php55 -name opcache.so

Edit php.ini and add the extension with its full path:

zend_extension=/home/cjones/php55/lib/php/extension/debug-non-zts-20121212/opcache.so

Update (25 March 2013): Dmitry merged a PHP 5.5 change so that the full path is not required for zend_extension libraries in the extension_dir directory. You can now simply do zend_extension=opcache.so.

You'll want to enable OPcache too:

opcache.enable=On

The ext/opcache/README is the current source of documentation, and lists all the other php.ini parameters.

Problems can be reported in the Github issue tracker

Update (18 March 2013): In a commit over the weekend, the build option --enable-opcache is On by default. You will still need to update php.ini.

To build Zend OPcache for older versions of PHP:

You should be able to build OPcache with PHP 5.2 onwards

Install it by getting the source from Github. There is also a PECL repository; this is slightly out of date so I recommend using Github. Follow the README instructions to install it.

User-data cache:

The new opcode cache does not include a user-data cache. Joe Watkins recently started the APCu project to extract the user-data cache code from APC. Test this too!

Update 19 March 2013: Xinchen Hui is working on a lockless user-data cache, see https://github.com/laruence/yac and his blog about it here (in Chinese).

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
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book

Follow:
Blaine Carter
Dan McGhan

Search

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