Monday, January 21, 2008

Oracle HTTP Server, Apache 2.0 and connection pooling

Three years ago, at the Deutsche Oracle-Anwendergruppe in Mannheim, a customer from a large German manufacturer asked me about the availability of Oracle HTTP Server based upon Apache 2.0. They had already standardized on WebSphere based upon Apache 2.0, and they did not want to support both Apache 2.0 and Apache 1.3 configurations. Certainly, a reasonable request.

Within a year, Oracle released a version of the Oracle internet Application Server based upon Apache 2.0. Most Apache modules delivered with iAS were usable in an Apache 2.0 environment. But the most glaring omission was mod_plsql, which was not ported to Apache 2.0 and still required Apache 1.3.9. To this day, I'm still baffled by the decision to not port mod_plsql at that time.

Fast forward to Fall 2007, and with the Oracle Database Release 11g distribution is something called Oracle HTTP Server (Apache 2.0) (10.1.3.3.0) for Linux x86. This is the version of Oracle HTTP Server that is based upon Apache 2.0 and has a supported version of mod_plsql. At last!

One of the benefits of Apache 2.0 is that it supports various Multi-Processing Modules. You can choose to either use the legacy process-based (prefork) architecture or the multi-threaded (worker) architecture. One of the benefits of using the multi-threaded implementation with mod_plsql is that it supports a true database connection pool. In the previous implementations of mod_plsql on Oracle HTTP Server on Linux, there is one database connection per HTTP Server process. There really isn't any "pool". But in the multi-threaded implementation, there is a database connection pool that is shared among all threads of a child process by mod_plsql.

The size of the database connection pool cannot be adjusted within mod_plsql. If N concurrent requests require database connections, then N connections will be created. If a connection is idle for more than X minutes (governed by PlsqlIdleSessionCleanupInterval), it is cleaned up. Indirectly, the Apache configuration for maximum processes and maximum threads defines the worst case peak value for possible database connections.

So I downloaded this thing called "Oracle HTTP Server (Apache 2.0) (10.1.3.3.0) for Linux x86", installed it, configured it, and thought I was good to go. But I examined the number of processes running on Linux, and I also examined the number of database connections, and I didn't notice anything different. I believe the default was set to the multi-process module, simply for backward compatibility reasons.

MetaLink Note 299125.1 gives instructions how to configure the multithreaded MPM in Oracle HTTP Server/Apache 2.0 (without recompiling). The relevant portion of this note:

SWITCHING FROM PREFORK TO WORKER
--------------------------------

- Edit opmn.xml and find the section which defines the HTTP Server:

<ias-component id="HTTP_Server">
<process-type id="HTTP_Server" module-id="OHS2">
<module-data>
<category id="start-parameters">
<data id="start-mode" value="ssl-disabled"/>
</category>
</module-data>
<process-set id="HTTP_Server" numprocs="1"/>
</process-type>
</ias-component>

- Add start parameter "mpm":

<ias-component id="HTTP_Server">
<process-type id="HTTP_Server" module-id="OHS2">
<module-data>
<category id="start-parameters">
<data id="start-mode" value="ssl-disabled"/>
<data id="mpm" value="worker"/>
</category>
</module-data>
<process-set id="HTTP_Server" numprocs="1"/>
</process-type>
</ias-component>

and save this change to the opmn.xml file

- Execute "opmnctl reload"

- Stop Oracle HTTP Server and start it up again (just "restart" will not suffice)
To verify that Oracle HTTP Server is really using the "worker" or multi-threaded MPM, examine the output of the process listing "ps -ef" on the server operating system. If you see one or more processes named "httpd", then you are still running the "prefork" or mulit-process MPM. If you see one or more processes named "httpd.worker", then you are indeed running the multi-threaded MPM of Apache 2.0.


* Oracle HTTP Server on Windows has always been multi-threaded.

Stand back, OraNA. Here I come.

14 comments:

Patrick Wolf said...

Joel, very interesting article! Especially for people running Apache/APEX on Unix.

BTW, congratulation to your "outing"! :-)

Patrick

Joel R. Kallman said...

Thanks Patrick. Maybe I'll continue to find the time. BTW, I do blame you for Flavio being able to locate me.

daust_de said...

Hi Joel,

this is very good news !!! We have faced some real problems spawning a new database session for each new httpd process ;).

Is it ready for prime time yet? Or are there any stability / performace issues? This module is brand new using the new architecture, right? Or didn't Oracle had to change the code, just switch the "process type"?

Also, have there been new features added, like using clobs instead of varchar2 (32K issue)?

We can use it with a plain 9.2.0.3, no need for 11g, right?

Greetinx,
~Dietmar.

John Scott said...

Dietmar,

I've been doing a bit of performance/stress testing with it and I haven't faced any stability issues yet, although it's still pretty early to say (compared to how long I've been using the 1.3 Apache I mean).

I was *very* happy (and surprised!) too to see it listed on the page when I was checking out 11g, it's been a long time coming!

John.

Anonymous said...

Joel,

Regarding the Oracle HTTP Server that ships with Oracle Database 11g; what kind of license does one need to run it in production?

I gather that mod_plsql is an Oracle product, so Oracle HTTP Server is an interesting combination with Oracle XE (currently based on 10g).

Havard

Joel R. Kallman said...

Hi Dietmar,

Yes - it's ready for prime time. It's the same mod_plsql that will be delivered with AS 11g. It was essentially delivered "early" with this version of Oracle HTTP Server with DB 11g.

I've encountered no performance or stability issues. I've been running it on our development server for months now.

I don't believe there were any new features added - it's the same basic functionality, but ported to work with Aapche 2.0. So POSTing to CLOBs or posting more than 32K bytes to a VARCHAR2 variable still remains.

You should be able to use this Oracle HTTP Server with any database version.

Joel

Joel R. Kallman said...

Havard,

I'm no licensing expert. I am a development manager (or as Dimitri incorrectly classifies me, a product manager).

The Oracle HTTP Server that ships with Database 11g is included as part of the license for the Oracle database. If you're licensed for the Oracle database, then you're licensed for the Oracle HTTP Server that is bundled with the Database 11g distribution.

Joel

Anonymous said...

Is the Oracle HTTP Server (OHS) bundled with Oracle 11g currently available for Windows? I can only find a download link for the Linux version of OHS. What happened to the Windows version?

Anonymous said...

The mod_plsql does work with the worker mpm but a load test with 20 users and only 4 httpd worker processes still made 16-20 database connections when I would have only expected 4 connections (ie. 1 connection per httpd.worker process). So it looks like we are getting almost 1 connection per thread which is not very different from what we had before - did anyone notice this too?

Filip Karst said...

Hello everybody,

please could You tell me, whether there is a possibility to switch off the connection pooling or not for small applications? My need are some settings stored in the db-session after calling a pl/sql procedure.

For my purposes it would be fine to have exactly one db-connection per active session (perhaps a reseted one after usage).

Is there a option?

Many thanks!

Filip Karst

Robert Morgan said...

Havard,

It is available for Windows as well at:

Windows Apache 2.0 OHS

Joel and all, thanks again for the great Apex info!

Robert

Joel R. Kallman said...

@Anonymous,

It's all a function of your maximum, truly concurrent throughput. If there was a microsecond in time where your load-tester was actively requesting a page from 20 different clients, then I believe your results are to be expected.

Joel

lucy said...

The chances of contracting erectile dysfunction goes up substantially with age, increasing significantly above the age of sixty-five-which is rapidly approaching for the baby boomers. Although erectile dysfunction becomes more likely with advancing age, there is certainly no age cutoff for a sexually fulfilling life. Some men enjoy sexual activity even in their eighties and nineties. http://www.buy-viagra-with-us.com

yo said...

Hi

very interesting.

I have a problem with APEX and SSL, i need to configure APEX in order to access it via https, some articles say that i have to configure some files under $ORACLE_HOME/Apache but that path doesn't exist, and opmn.xml neither.

I don't know if i have to install something else like OAS' components.

Please give me an idea.

Best Regards, Oscar Palacios.