What’s more efficient – storing logs in sql database or files?

I have few scripts loaded by cron quite often. Right now I don’t store any logs, so if any script fails to load, I won’t know it till I see results – and even when I notice that results are not correct, I can’t do anything since I don’t know which script failed.

I’ve decided to store logs, but I am still not sure how to do it. So, my question is – what’s more efficient – storing logs in sql database or files?

I can create ‘logs’ table in my mysql database and store each log in separate row, or I can just use php’s file_put_contents or fopen/fwrite to store logs in separate files.

My scripts would approximately add 5 logs (in total) per minute while working. I’ve done few tests to determine what’s faster – fopen/fwrite or mysql’s insert. I looped an “insert” statement 3000 times to make 3000 rows and looped fopen/fwrite 3000 times to make 3000 files with sample text. Fwrite executed 4-5 times faster than sql’s insert. I made a second loop – I looped a ‘select’ statement and assigned it to a string 3000 times – I also opened 3000 files using ‘fopen’ and assigned the results to the string. Result was the same – fopen/fwrite finished the task 4-5 times faster.

So, to all experienced programmers – what’s your experience with storing logs? Any advice?

// 04.09.2011 EDIT – Thank you all for your answers, they helped ma a lot. Each post were valuable, so it was quite hard to accept only one answer 😉

Answer

You can use a component such as Zend_Log which natively supports the concept of writers attached to the same log instance. In that way you can log the same message to one or more different place with no need to change your logging code. And you can always change your code to replace the log system or add a new one in a simple way.

For your question I think that log to files is simpler and more appropriate if you (developer) is the only one who needs to read log messages.

Log to db instead if you need other people needs to read logs in a web interface or if you need the ability to search through logs. As someone else has pointed out also concurrency matters, if you have a lot of users log to db could scale better.

Finally, a log frequency of 5 messages per minute requires almost no cpu for your application, so you don’t need to worry about performances. In your case I’d start with logfiles and then change (or add more writers) if your requisites will change.