I'd like to be able to query our server access log files to identify abuse patterns. Log files really aren't very queryable, and it would be fairly easy to do what I wanted if each hit was a row in a MySQL database.
I'd prefer not to change the web-server or use any modules that would slow down the response time of requests. The web-server is optimized to write entries to a text log, and I'd like to let it do just that. ie. Let the web-server write to a file, and move the file entries to DB entries in a batch process later.
PHP is the server side language I am most familiar with, and it would be trivial to open a file, parse it line by line, and insert those lines into a DB. The problem is, the access log is being written to at a machine gun pace. PHP can't be parsing the log at the same time the webserver is trying to write to it. Nor can the web server wait patiently while PHP does it's parsing.
There needs to be some way that both can do their jobs at the same time, without duplicate entries getting imported or missing entries.
So I have two ideas: First, to process only the rotated out logs, ie
access.log.1. This makes it less real-time, but avoids the conflict of two programs competing for the same resources. There's still the issue of the log rotate trying to rotate logs while PHP is reading them, especially since it reuses file names as it cycles through. I would need some way to ensure the same log didn't get read again or missed because the names conflict.
Second, I could use a Queue liek a pipe. I've never used pipes before so I don't know how they work. If:
- The web server saw it as a regular file and wrote to it like one and
- the webserver didn't try and replace it with a rgular file on restart and
- The pipe would just hold text entries in an ordered queue until PHP was invoked and pulled entries out the end, and
- pulling entries out the end deleted them...
Then it might be exactly what I'm looking for. The question is, can PHP invoke, pull the things out of the pipe and then end, to be called again later by a cron? Or does PHP have to run continuously like a dameon in order to use a pipe. Or in other words, does a pipe still hold stuff, just like a file if nothing is on the other end, right then?
Or is there some other way to read logs into a database safely and reliably without slowing page serving time?