Speeding up Database Access with mod_perl

Last month, we started to look at mod_perl, a module for the Apache web server that puts a copy of Perl inside of our HTTP server. Not only does mod_perl save us the overhead of forking a new process and invoking Perl each time a CGI program is run, but it caches programs once they are compiled, reducing start-up time even further.

As with everything else in life, there are trade-offs for using mod_perl. CGI programs can be written in any language and can be run by any web server on any platform. By contrast, mod_perl only works with Apache (which runs under most UNIX versions and is about to be released for Win32), and requires that programs be written in Perl. If you are interested in maximum portability, you might consider sticking with CGI. But if you are like me and spend most of your time working with Apache and Perl anyway, you might seriously consider moving toward mod_perl.

However, this is not the end of the story. Even if mod_perl were infinitely faster than CGI's fork-and-execute method, a major bottleneck would still remain: opening a connection to a database server.

If your web application talks to a relational database server, it has to open a connection before it can send a query. That initial dialogue between your program and the database server can take quite a while, both because of the initial TCP connection that must be established and because database servers were not designed for single-query connections. Many servers expect you to connect once, send a number of requests, receive a number of responses and disconnect when done. Web applications, by contrast, tend to open a connection for each query, which can unnecessarily slow your programs.

Luckily for us, the folks who wrote mod_perl created a module designed to solve this problem. Apache::DBI, as it is called, takes advantage of mod_perl's variable persistence (i.e., the fact that variable values are kept from one invocation of a program to another) to keep database connections open. Each invocation of a program using Apache::DBI has only to send a query to the database and act on the returned response.

This month, we look at the DBI specification in general, and the Apache::DBI module in particular. We also take a quick look at the Benchmark and LWP modules, which can help profile code in general, and which will allow us to see just how much faster mod_perl and Apache::DBI can make our programs.

To get mod_perl working with Apache, you will need to compile and install the latest versions of both programs. (Full instructions are available in last month's “At the Forge”.) To take advantage of all of Apache::DBI's features, you will need to compile mod_perl with more than the default options. These options can be specified by adding OPTIONNAME=1 on the same line as the initial invocation of Makefile.PL, the first stage of the compilation and installation process.

I found it easiest (if a bit wasteful) to compile using the EVERYTHING flag, which turns on all of the mod_perl options, even those that are unnecessary for Apache::DBI. To do this, type the following in the initial mod_perl directory:

perl Makefile.PL EVERYTHING=1

The rest of the mod_perl and Apache configuration continues as described last month. See the INSTALL document that comes with mod_perl to learn how to turn on only those features you need, rather than using EVERYTHING=1.

Apache::DBI works automatically with programs that use mod_perl. In other words, any program that uses Apache::Registry (more or less the mod_perl equivalent of CGI) automatically gets the benefits of Apache::DBI, assuming that the latter is specified in the configuration file (described below). You can configure a directory to use Apache::Registry in much the same way as you can configure it to use CGI, with directives in the srm.conf file such as:

# Deal with mod_perl
<Location /perl-bin>
SetHandler perl-script
PerlHandler Apache::Registry
Options ExecCGI
</Location>

You can then instruct mod_perl to load Apache::DBI in all directories using Apache::Registry by inserting the following directive into srm.conf:

PerlModule Apache::DBI

# killall -v -1 httpd