Web Techniques: Programming With Perl – Web Access Logs with DBI

“A few months back, I was flipping through the wonderful Writing
Apache Modules with Perl and C, by Doug MacEachern and fellow Web
Techniques columnist Lincoln Stein. While trying to think of more
bells and whistles to throw at my mod_perl-enabled Web server for
PerlTraining.Stonehenge.com, I stumbled upon the section that
talks about using a database instead of a flat file for the access
log. Now, I had just recently upgraded and cleaned up the MySQL
database for my ISP, and thought it would be nice to have more
advanced statistics and reporting. So I jumped in.

“This was quite a jump, because I hadn’t ever used any of
Apache’s custom log features. I had a simple NCSA-compatible access
log, and a few specialized logs in a few directories via
server-side include (SSI) loggers. So I decided to go whole hog the
other way. Not only did I decide to log the usual things (host,
user agent, bytes sent, and so on), but I also wanted to log the
CPU times for each transaction. Doing so would let me see which of
my URLs were burning up more CPU so I could figure out ways to
cache them better. This is especially important on a machine that
is shared with other users.”

“So now the log is being written immediately to a DBI-based
MySQL database. The power here is that I can use Structured Query
Language (SQL) statements to generate ad-hoc reports on an
up-to-the-minute basis, as well as get the general reports canned
up into a Perl data structure with relative ease. No more parsing
flat files (not that this was much trouble with Perl anyway). The
downside is that I’m now burning 1MB per day for storage inside the
MySQL database, but my sys admin said to just let them burn,
because disk space is cheap.”