Web Access Logs with DBI
By Randal L. Schwartz
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.
