The first example is the DBI module. DBIworks with many database drivers from the DBD:: category (e.g., DBD::mysql). If you want to minimize memory use after Apache forks its children, it's not enough to preload DBI—you must initialize DBI with the driver(s) that you are going to use (usually a single driver is used). Note that you should do this only under mod_perl and other environments where sharing memory is very important. Otherwise, you shouldn't initialize drivers.

You probably already know that under mod_perl you should use the Apache::DBI module to get persistent database connections (unless you open a separate connection for each user). Apache::DBI automatically loads DBI and overrides some of its methods. You should continue coding as if you had loaded only the DBI module.

As with preloading modules, our goal is to find the configuration that will give the smallest difference between the shared and normal memory reported, and hence the smallest total memory usage.

To simplify the measurements, we will again use only one child process. We will use these settings in httpd.conf:

MinSpareServers 1
MaxSpareServers 1
StartServers 1
MaxClients 1
MaxRequestsPerChild 100

We always preload these modules:

use Gtop( );
use Apache::DBI( ); # preloads DBI as well

We are going to run memory benchmarks on five different versions of the startup.pl file:

Version 1
Leave the file unmodified.

Version 2
Install the MySQL driver (we will use the MySQL RDBMS for our test):

DBI->install_driver("mysql");

It's safe to use this method—as with use( ), if it can't be installed, it will die( ).

Version 3
Preload the MySQL driver module:

use DBD::mysql;
Version 4
Tell Apache::DBI to connect to the database when the child process starts (ChildInitHandler). No driver is preloaded before the child is spawned!

Apache::DBI->connect_on_init('DBI:mysql:test::localhost', "", "",
    {
     PrintError => 1, # warn( ) on errors
     RaiseError => 0, # don't die on error
     AutoCommit => 1, # commit executes
     # immediately
    }
) or die "Cannot connect to database: $DBI::errstr";
Version 5
Use both connect_on_init( ) from version 4 and install_driver( ) from version 2.

The Apache::Registry test script that we have used is shown in Example 10-10.

Example 10-10. preload_dbi.pl

use strict;
use GTop ( );
use DBI ( );

my $dbh = DBI->connect("DBI:mysql:test::localhost", "", "",
    {
     PrintError => 1, # warn( ) on errors
     RaiseError => 0, # don't die on error
     AutoCommit => 1, # commit executes immediately
    }
) or die "Cannot connect to database: $DBI::errstr";

my $r = shift;
$r->send_http_header('text/plain');

my $do_sql = "SHOW TABLES";
my $sth = $dbh->prepare($do_sql);
$sth->execute( );
my @data = ( );
while (my @row = $sth->fetchrow_array) {
    push @data, @row;
}
print "Data: @data\n";
$dbh->disconnect( ); # NOOP under Apache::DBI

my $proc_mem = GTop->new->proc_mem($$);
my $size  = $proc_mem->size;
my $share = $proc_mem->share;
my $diff  = $size - $share;
printf "%8s %8s %8s\n", qw(Size Shared Unshared);
printf "%8d %8d %8d (bytes)\n", $size, $share, $diff;

The script opens a connection to the database test and issues a query to learn what tables the database has. Ordinarily, when the data is collected and printed the connection would be closed, but Apache::DBI overrides thsi with an empty method. After processing the data, the memory usage is printed. You will already be familiar with that part of the code.

Here are the results of the five tests. The server was restarted before each new test. We have sorted the results by the Unshared column.

  1. After the first request:

    Test type                              Size   Shared    Unshared
--------------------------------------------------------------
(2) install_driver                   3465216  2621440   843776
(5) install_driver & connect_on_init 3461120  2609152   851968
(3) preload driver                   3465216  2605056   860160
(1) nothing added                    3461120  2494464   966656
(4) connect_on_init                  3461120  2482176   978944

  2. After the second request (all the subsequent requests showed the same results):

    Test type                              Size   Shared    Unshared
--------------------------------------------------------------
(2) install_driver                   3469312  2609152   860160
(5) install_driver & connect_on_init 3481600  2605056   876544
(3) preload driver                   3469312  2588672   880640
(1) nothing added                    3477504  2482176   995328
(4) connect_on_init                  3481600  2469888  1011712

What do we conclude from analyzing this data? First we see that only after a second reload do we get the final memory footprint for the specific request in question (if you pass different arguments, the memory usage will be different).

But both tables show the same pattern of memory usage. We can clearly see that the real winner is version 2, where the MySQL driver was installed. Since we want to have a connection ready for the first request made to the freshly spawned child process, we generally use version 5. This uses somewhat more memory but has almost the same number of shared memory pages. Version 3 preloads only the driver, which results in less shared memory. Having nothing initialized (version 1) and using only the connect_on_init( ) method (version 4) gave the least shared memory. The former is a little bit better than the latter, but both are significantly worse than the first two.

Notice that the smaller the value of the Unshared column, the more processes you can have using the same amount of RAM. If we compare versions 2 and 4 of the script, assuming for example that we have 256 MB of memory dedicated to mod_perl processes, we get the following numbers.

Version 2:

Figure

Version 4:

Figure

As you can see, there are 17% more child processes with version 2.