using a database for logging

Question

Is there a reason that most logs seem to be in plain text, as opposed to being put in a MySQL/other sort of database?

It seems to me that putting them into a database would make analysis much, much easier…but would that come at the sacrifice of speed or something else?

(I'm not that concerned with portability, and obviously you'd have text logs for your database connection.)

Answer 1

Historically, databases were expensive, and you would certainly never want to waste your precious database licenses on logs. However databases today are relatively cheap and so is processing. Using a database for logs probably wouldn't kill you financially.

The advantage of a log file is that you keep writing to the end of it. That is a relatively efficient operation compared to using a database server.

The advantage of a database is that you can structure your log data in data relations, which can then be analyzed using SQL. This can provide you some great insight into the operation of your software.

You could have the best of both worlds by using SQLite as your log database. SQLite is a library with an SQL engine that you link into your program. Instead of fopen/fwrite/fclose, you use the SQLite API to open the database, run SQL and close the database. There is no database server because the SQLite engine operations run in your application's process...just like fopen/fwrite/fclose. Once you capture your data in a SQLite database (all stored in a simple file), you can use SQL to analyze your log data. Check out http://www.squidoo.com/sqlitehammer#module5800826 for an example.

-------- EDIT August 2010 ------------

The developers of SQLite have implemented writeahead logging as of SQLite version 3.7.0. This enables much faster writes. Check out this video for more details. With faster writing, SQLite is even more useful as a logging database.