Speeding up Database Access with mod_perl

If we want to retrieve matching rows from the database, we need to modify the syntax just a bit. After all, we expect to not only receive a report on whether the database was able to perform our requested action, but also see the results.

Assuming we are connected to the database, we set $sql to our SQL query:

$sql = "SELECT id,contents FROM test_insert";

We then use the prepare method to send our query, as follows:

$sth = $dbh->prepare($sql);

&log_and_die($sth->errstr) unless $sth;

$sth->execute || &log_and_die($sth->err);

# Loop through returned rows
while (@row = $sth->fetchrow)
{
# Grab the columns from the row
$id = $row[0];
$contents = $row[1];
# Print the ID and the contents
print "<P>$id:\"$contents\"</P>\n";
}

$sth->finish;

mysql test

CREATE TABLE test_insert
 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY
        KEY, contents VARCHAR(50) NOT NULL,
        UNIQUE (contents));

The CGI program in Listing 1 demonstrates all of the above, first inserting a number of rows into the table and then retrieving them. Most DBI programs are as simple as this one, although many either store or retrieve information, rather than do both.

One of the problems with a standard like DBI is that the interface follows the least common denominator. That is, there are differences between database packages in addition to their administration and speed; just about every package includes a number of non-standard SQL commands and features in order to differentiate itself from the competition. If you are interested in using such features, you might have to use the func DBI method, which enables proprietary database extensions. Of course, doing so means that your program is no longer portable to other databases, which might be a concern if you switch to another vendor.

We have now compiled Apache to use mod_perl, configured a perl-bin directory for serving mod_perl programs and configured Apache to insert the Apache::DBI module for all programs within the perl-bin directory. We are all set to take our sample DBI program and use it with mod_perl.

How must we modify the program in order to get it to work with mod_perl? Actually, we needn't make any modifications at all, if we have configured our copy of Apache as described above. All we need to do is copy our program into perl-bin, set the appropriate permissions and give it a whirl. Here's what I wrote on my computer, for example:

~httpd/cgi-bin% cp dbi-demo.pl ../perl-bin/
~httpd/cgi-bin% chmod ug+x ../perl-bin/dbi-demo.pl

I changed the URL in an open browser window such that it pointed to perl-bin rather than cgi-bin, and—voilà--it all just worked.

When I first started to use mod_perl and Apache::DBI, I wasn't sure how much faster programs would run. The execution certainly seemed faster, but I wasn't sure how much of an improvement I was seeing. I decided to use Perl's Benchmark module, comparing the execution speed of two different programs. I would try to insert 100 random text strings into a database, first using a CGI program and then using an Apache::DBI version of the same program (which, as we now know, simply means a version of the program placed in the perl-bin directory).

Benchmarking is a tricky and subtle business, and there are undoubtedly factors which I neglected when calculating these results. Even so, they seem to bear witness to the amazing performance difference between CGI and mod_perl. I'm sure if I were to spend a great deal more time working on my Apache and/or MySQL configuration, I could get even better performance out of my lowly 75 MHz Pentium running Red Hat 4.2. However, the relative numbers should speak for themselves.

First, let's examine the test I performed. I used the same test_insert table in MySQL as we saw before. I then wrote a CGI program, similar to the one we saw before, which connects to the database and inserts a random value into the contents column. The resulting program is shown in Listing 2.