Dawn of the Geeks

On Lamp has an article titles “Writing Apache’s Logs to MySQL” which explains how to log directly to a MySQL database. This seems like a good idea on the surface. There’s nothing better than a database to store large amounts of information for processing. However, it’s not necessary or even advisable to try to store a bunch of log information from Apache directly into MySQL.

If you want your Apache server to run efficiently you need to have it do as little work as possible. For small sites the novelty of putting logs directly into MySQL may seem like a good thing. But as your site gets more and more popular the time it takes to insert rows is suddenly going to be noticable.

The better approach is to stick to file based logging and do post processing on log files. It’s rather trivial to write a PHP or Perl script to parse logs files and populate a database with the information. I currently use a free PHP script (the “Apache Log Parser” script) which happily chugs away reading the Apache “combined” log format. The class is instantiated into a simple script which takes the lines read by the parser script and stuffs them into a database table based on the year and month the request was made. This keeps MySQL happy by not storing millions of rows spanning 8 or 9 years of access logs into a single table.

Log reports are generally done on a monthly basis anyway. If your server is really popular then creating tables based on the year month and day is easy enough to do.

In order to improve efficiency even more, my server stores all the log files on a dedicated hard drive. I have yet to find a good log rotater for Apache. Every time I try to rotate logs I end up with runaway processes and server errors. So to rotate logs manually simply stop the server, rename the current log folder, create a new log folder, restart the server. A few seconds of downtime and you’re free to process the log files without interrupting the server any further.

You could probably write a bat file to do that process and set up a cron to do it automatically every week or so.