Writing Apache's Logs to MySQL
by Chris Josephes02/10/2005
In Profiling LAMP Applications with Apache's Blackbox Logs, I discussed using Apache's built-in logging directives to record server performance metrics. By recording performance metrics, web server administrators can have a historical record of how the server handled incoming HTTP requests. This article expands on that concept by adding a couple of logging directives and recording the logging data directly in a MySQL database.
Quick Review of Blackbox
The Blackbox format is a custom log format put together with some of the lesser used Apache logging directives. It records data to give server administrators a better idea of how well their web servers are performing.
LogFormat "%a/%S %X %t \"%r\" %s/%>s %{pid}P/%{tid}P %T/%D %I/%O/%B" blackboxIn order, this format records the remote IP address, the remote port, the connection status, the time, the request line, the return statuses (before and after redirection), the process ID, the thread ID, the length of time to handle the request (in seconds and microseconds), and the number of bytes in, bytes out, and bytes of content.
Extensions to the Current Blackbox Format
There are a couple of additional fields to add to the Blackbox format to satisfy larger web server environments.
Virtual Host (%v)
The Blackbox format records the performance metrics for a given server instance. If your server handles virtual hosts, you can record the Blackbox data for every virtual host.
Here's an example of a configuration file using three virtual hosts.
<VirtualHost app.example.com>
ServerName app.example.com
CustomLog /var/opt/apache2/logs/appserver common
CustomLog /var/opt/apache2/audit/blackbox blackbox
</VirtualHost>
<VirtualHost images.example.com>
ServerName images.example.com
CustomLog /var/opt/apache2/logs/imageserver common
CustomLog /var/opt/apache2/audit/blackbox blackbox
</VirtualHost>
<VirtualHost internal.example.com>
ServerName internal.example.com
CustomLog /var/opt/apache2/logs/intranet common
CustomLog /var/opt/apache2/logs/blackbox blackbox
</VirtualHost>|
Related Reading 
Apache Cookbook |
Every host writes out two logs: one in the custom log file format and another
in the Blackbox format. The Blackbox log for each host is actually the same
file. This makes sense for centralizing performance metrics, but it's difficult
to identify which hits come from which virtual host. The simplest way to fix
this is to add the %v directive to the format.
Unique ID (%{UNIQUE_ID}e or %{forensic-id}n)
Apache has a module known as mod_unique_id. It creates a unique, 19-character,
Base64-encoded string to identify every hit made on the server. The string
comes from the source IP, the process ID, the timestamp, and a subsecond
counter. You can access the value using the environment variable
%{UNIQUE_ID}e.
Adding this directive to the Blackbox format ensures that each hit record is unique. It also provides the ability to match hits in the Blackbox log file to specific hits in another log file.
For example, Apache 2.0.52 introduced a new logging module known as
mod_log_forensic. When a request comes in, the module immediately writes out
the incoming client request headers. Then at the logging phase, the module
writes another entry to show that the request has ended. The module prefixes
each entry with the UNIQUE_ID environment variable.
By combining the mod_log_forensic module with the Blackbox format, you can
record the client input and the server performance metrics. The
UNIQUE_ID gives you a unique key so you can trace the log entries
back to their forensic log input records.
If you decide to use mod_log_forensic, you have two options for recording
the Base64 id string: %{UNIQUE_ID}e or
%{forensic-id}n. The mod_log_forensic module generates the latter.
They will both be the same value, but the Apache documentation makes the vague
suggestion that they might be different in the Apache 2.1 code branch. For now,
I recommend %{UNIQUE_ID}e, because it's usable with or without the
mod_log_forensic module.
Updated format
The updated configuration for the Blackbox format looks like:
LogFormat "%v %a/%S %X %t \"%r\" %s/%>s %{pid}P/%{tid}P \
%T/%D %I/%O/%B %{UNIQUE_ID}e" blackboxThis may look a little strange, but it's an attempt to make the format
similar to the established conventions. The Apache common+virtual host logging
format puts the %v directive first, and all examples showing the
UNIQUE_ID variable put it in the last field of the format.
The Perl regular expression for extracting records from this format is:
$Fmt=<<EOFMT
^(\\S+)\\ ([^/]+)/(\\d+)\\ (\\S)\\ \\[([^:]+):(\\d+:\\d+:\\d+)\\ ([^\\]]+)\\]
\\s "(\\S+)\\ (.*?)\\ (\\S+)"\\ (\\d+)/(\\d+)\\ (\\d+)/(\\d+)\\s
(\\d+)/(\\d+)\\ (\\d+)/(\\d+)/(\\d+) (\\S+)\$
EOFMT
;
