Writing Apache's Logs to MySQL

by Chris Josephes
02/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" blackbox

In 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 By Ken Coar, Rich Bowen Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

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" blackbox

This 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
;

Pages: 1, 2, 3

Next Page