Apache mod_log_sql (review)
After some reading of consolidation options for Apache logs, I ran across mod_log_sql (we are hating spread) which will take Apache logs and log them off to a MySQL database. Sounded great! We could then run scripts to go through and parse the values and run statistics on. Twas perfect for our needs. RIGHT!!!
At my company we run about 12 vhosts over about 5-6 load balanced web servers. All of them were configured to log to our loganalysis server which is a pretty beefy machine. The logging all in all worked well with a few major exceptions listed below.
1) I used the directive:
LogSQLRequestIgnore .gif .jpg .css .ico .png .js
This directive is supposed to be used to ignore any pages ending with that extension. This did not work at all and I had to create a script to actually delete those before analyzing the logs. Bummer but not that big of a deal.
2) While working with the server to optimize the database, there were various times when I would need to restart the MySQL service and a few times I needed to reboot the server. During these periods of time, the web servers were unable to log to the database which brought them to their knees. The inability of the module to handle a database outage gracefully was a major deal breaker for us. I feel this issue is a result of intense disk IO when the database is down. The server is logging to its Apache logs, the the backup SQL logs, and to the Apache error logs for every failed request. This becomes emense with thousands of requests per second. Should this server die or needing maintenance would have ultimately brought our company to a hault.
Long story short, we’re scrapping mod_log_sql and going with an NFS mount out to all the web services which we can then parse and run statistics on using some custom scripts and/or AWStats or Splunk.