Recipe 21.8 Speeding Up Database Access

21.8.1 Problem

You use the DBI module within your mod_perl handler, but connecting to the database server for each request is slowing down your web application unacceptably.

21.8.2 Solution

To cache database connections transparently, load the Apache::DBI module before the DBI module:

use Apache::DBI;
use DBI;

21.8.3 Discussion

Many sites load the Apache::DBI module from the httpd.conf file to ensure it's loaded before anything else:

PerlModule Apache::DBI

The Apache::DBI module intercepts the DBI->connect method, returning a previously opened handle if the handle had the same connection parameters as the current request. The module also prevents $dbh->disconnect from closing connections. This lets you add use Apache::DBI to the start of an existing program without having to touch the rest of your code.

The Apache::DBI module uses an open database connection for each different database login in each Apache child process. You might need to change your database server's configuration to increase its maximum number of connections. With commercial database systems, you might even need to buy more client licenses.

This proliferation of connections can lead to situations where Apache::DBI isn't the best choice. For example, if each user of your site has his own database login, you'll need as many concurrent database connections as the number of active users multiplied by however many httpd processes are running worth of database connections, which might well be more than your server supports! Similarly, if you have many Apache child processes running concurrently, this could open more simultaneous database connections than your server supports.

One strategy for optimizing database access is to batch requests where possible. For example, if you're logging to a database, consider accumulating log information and updating the database only after every 5 or 10 hits.

Another strategy is to cache information in the client. For example, if you use a database to map user IDs to real names and that mapping never changes, then use a persistent hash in your handler to store real names for user IDs you've encountered—this avoids repeated database lookups on unchanging information. To prevent the hash from consuming too much memory, you might want to use the Tie::Cache::LRU module from CPAN or reimplement some other form of flushing the least-recently-used entries once the cache reaches a particular size.

21.8.4 See Also

Documentation for the Apache::DBI and Tie::Cache::LRU modules from CPAN; the Apache.pm manpage; Recipes 2.11 and 2.12 in mod_perl Developer's Cookbook