Accelerating DBI for the Web
When a Web site is using persistent Perl connections, improving database access time isn't difficult. The persistent environment provides a new layer of continuity between Web server requests, and this layer can be used to maintain database connections and other cached information about the database.
Performance blocks can be removed from Web applications at many levels:
The database connection architecture can be made more efficient using Apache::DBI.
Database query preparing and execution can be streamlined using placeholders and cached statements.
Perl's internal representation of the resultant data can be reduced and accelerated by using references.
Using Apache::DBI
The Apache::DBI module has few features, but the most important is the simple redefinition of DBI's connect method, which can improve Web application performance immensely. Normally, a database connection is opened by DBI each time the connect method is called, and the connection is closed when the disconnect method is called, usually at the end of a program or module.
In a Web Common Gateway Interface (CGI) context, this means that database connections are opened and closed every time a CGI program is run, which could be hundreds of times per second. Because a database connection takes up to ten seconds to be established, this means that the database connection time is a thousand times too slow to be acceptable.
In a persistent environment, Apache::DBI can be used to cache open database connections based on the database accessed and the database user specified when connecting. The connect method within DBI defers to the same method within Apache::DBI if the environment is persistent, which enables the Apache::DBI connect method to keep track of open connections and to refresh them as needed.
Apache::DBI also overloads the disconnect method to keep open database connections from being closed inadvertently by programs that are written without Apache::DBI in mind. Because of this, no programs need be modified and no tables need be restructured to use Apache::DBI. In fact, it's better to invoke Apache::DBI outside the body of your program code to ensure that it is used by all Web applications across the board. This can be done by including a directive in the httpd.conf configuration for Apache or the startup scripts for mod_perl, VelociGen, and other persistent Perl environments, as shown in Listing 1.
Listing 1-Load Apache::DBI for all Applications
01 # add one to httpd.conf file for 02 # Apache-based servers with mod_perl 03 PerlModule Apache::DBI 04 PerlRequire Apache/DBI.pm 05 06 # add one to startup.pl, vep_startup.pl 07 # or similar Perl engine startup scripts 08 use Apache::DBI; 09 require Apache::DBI; 10 11 # add a connect string to startup.pl, etc. 12 # to open a connection when the server starts 13 Apache::DBI->connect_on_init($datasource, $user, $pass);
Because of the unique relationship between DBI and Apache::DBI, the DBI module checks when it is loaded to see if Apache::DBI is already in use. Thus, if Apache::DBI is loaded before all occurrences of DBI in a Web application, database connections are cached regardless of whether an individual script uses Apache::DBI.
Additional performance gains can be achieved by creating a database connection when the server starts each Perl engine. This is done by calling the connect_on_init method in the startup script with the same parameters that are used in the Web application, as in line 13 of Listing 1. When doing this, make sure that the arguments passed to connect_on_init are exactly the same as those used in the Web application, including preference arguments such as AutoCommit or RaiseError. Otherwise, Apache::DBI assumes that the connections are different, and the initial connection are cached but never used.
Note that the Apache server is not required to use Apache::DBI. Any Web server that supports a persistent Perl environment can use Apache::DBI by placing the use statement in the Perl engine's startup script.