6

I am developing a web application as well as the API the web application uses. I'm trying to determine whether it is better to log events (to determine the path that leads to an error as well as to determine whether the site has been compromised) in a database or simply by writing to a file.

On the one hand, I don't want to bog down my database with a queries every single time I need to log an error. On the other hand, I don't want to have to dig through a giant event / error log file. I'm thinking of turning into re-write mode once the file / table reaches 10,000 or 100,000 events.

I know the general factors I should consider:

  1. Performance (both DB and general--in my case I'm using PHP and Postgres)
  2. Ease of finding the path a user took to create a bug or error (I'm almost certain DB is better for this)
  3. Scalability - Is this the same solution I'm going to be using with 20 users as I am with 100,000 users?

Can you tell me how these two possible solutions fit in with the above--really, I think the most important one for me is performance. How demanding is it to write to a file vs. writing to a DB and if every single event and command sent by the user (and there will be many) is logged (before a re-write limit), which solution will end up being faster?

Improve this question

7 Answers 7

Reset to default
4

If logging to the DB, where would you log db failures, like the DB being unavailable?

Yes, if logging to the file system, the file system could become unavailable, but my guess is that you then would have other things to worry about...

In a server application (not a web server, but one under heavy load anyway), we log to files (several), using a specialist logging component. And while performance is a crucial factor in many parts of the code, we have yet to feel the need to decrease our logging because of performance considerations.

Probably because most logging is done outside of the time critical code. Which is only "natural" because time critical code often would produce way too many log messages to be of any practical use.

Improve this answer
2
  • All the answers were very solid and helpful, but this really contains a critical point that makes file-writing the only way to go: It's much more likely that the DB will go down than being unable to write to files. Ethan Post has a great point as well, which is during a particular action / server request, temporarily store the events (queries, etc.) and then if an error is encountered, throw them all to the logfile, so you have the path when there's an error but aren't storing a bunch of unnecessary events. Commented Sep 29, 2011 at 14:08
  • In addition, Codism makes the point that file writing is faster and is just as easy to search through a file using regex. While Emmad suggested the many points that one must consider when writing to files (and in turn suggested using a DB). In any case, just because I checked this as the answer, I do not want readers to neglect reading the other excellent answers. Commented Sep 29, 2011 at 14:11
3

Some databases like Oracle and MySQL allows you to treat a regular txt file as a table, so you can run SELECT statements on it.

If you intend to use a file, then you need to consider:

0-Concurrency issues (what if 2 records need to go to the file at the same time)?

1-How to search it?

2-How large will you allow it to grow - You may need a special script for this

3-Where to store it?

4-How to back it up (if needed) - You may need special script for this.

I see that using a regular database table is the most practical approach. It reduces the complexity of managing data. That is what databases are for. To enhance the performance of your log table, place it away from your database space and build no index on it.

Improve this answer
2

Only if most of your team members don't know how to handle text file in command line can you justify the use of a db based log. For concerns you have listed:

  1. Performance: for appending information row by row, I think no db does it faster than a text file.
  2. Easy query: how are you so sure db is better in this case if most likely it will do table scan anyway? I would avoid writing sql if regular expression does it better for filtering logs, which, from my experience is almost certain.
  3. Scalability: not sure what's your concern here but for logging, anything db can do, text file can do it faster, which should address any scalability problem.

UPDATE: Just show an example that you may want to do with your log but db sucks in that case: list the last 20 log entries for user id 28465283 before the null reference exception log entry:

grep -B 20 "28465283.*NullReferenceException" log.txt > to-the-grease-monkey.txt

I guess it will take me a while to figure out how to write a SQL for the same query.

Improve this answer
3
  • I guess the SQL for the above goes something like this: SELECT * FROM myLog WHERE msg LIKE "%NullReferenceException%" and userid="28465283" Commented Sep 29, 2011 at 5:17
  • @Emmad: your query does not give the last 20 entries before the exception. There are many ways to do this in SQL but all of them suck in both performance and verbosity. Commented Sep 29, 2011 at 15:58
  • OOPs, I forgot to do that, you are correct! Commented Sep 29, 2011 at 21:35
1

I would take the database logging approach. If you are throwing so many errors that it affects database performance, then you most definitely need to revisit the source of these errors.

Storing logging (and auditing) data in the database allows for easy access, ad hoc queries (as well as stored procs and/or functions) to pull often-queried error reports.

Improve this answer
1
  • Storing common queries is not something unique to database. Commented Sep 29, 2011 at 4:50
1

Your application should be capable of doing both. Then tweak accordingly. If you are hitting the database too much (really can't see how this would happen unless you are planning on a ton of traffic) you can switch over to .log file. In addition to this you could also log to a format such as JSON and off load the data load into another database in the background/later.

With the applications I write I usually keep the last N debug calls in an array of something and dump it when I encounter an error.

Improve this answer
1

For events inside your application, I'd go the DB route. This will let you more easily search, sort, and report on them.

I dont know about Postgres, but MySQL has an 'INSERT DELAYED' command, which tells the db to commit the inserted row when the server is idle. This is great for logging as you arent holding up your web page/application by waiting for the insert to complete. So I'd check into whether postgres supports something like that.

Improve this answer
1

I log to a central log file using syslog, as well as a local file per-server (in case syslog fails). Then offline I import the centrally logged files into a database for analysis (usually applying some kind of transformation - e.g. tallying events, etc), depending on requirements.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.