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:

  1. The web server saw it as a regular file and wrote to it like one and
  2. the webserver didn't try and replace it with a rgular file on restart and
  3. The pipe would just hold text entries in an ordered queue until PHP was invoked and pulled entries out the end, and
  4. 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?

  • 1
    Have you considered something already designed for log processing such as logstash? – jordanm Apr 17 '15 at 13:37
  • 1
    This reads more like a project then a question. Are you really trying to pour all of your webserver log data into a database? that would really create huge I/O work for your server. You can tee your webserver log data to another file. Tee can be used to split the output stream to a second file that PHP could read. – Baazigar Apr 17 '15 at 13:50
  • The question is- what's the best way to get the log data into a database. I don't know how else to do the type of queries I need to do without a database. The rest of the project I have a pretty good idea of how to do myself already. I just need to get from log to DB in the most efficient way possible, that's all I'm asking for. I don't even need the text log at all, (other than as an intermediary if required). – Nick Apr 17 '15 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.