24

When should I use database for logging and when text files?
I see that web servers and web frameworks (that your app uses internally) usually (always?) log requests and errors into text files by default. But I see that people who develop their app around those servers and frameworks sometimes log into database (even the main DB of the app, not some external one).
Also maybe there is a difference between debug logs and audit logs -- I have read this classification somewhere on this site.

15

In very general terms, logging to a text file is much faster than logging to a database. That's the main aspect of logging you need to consider.

The reason you log to a DB is more likely to be because you want to query the results - searching for particular log info is easier in a DB, particularly if you log contextual information that can be used to group log entries together. It is also usually easier to access a central DB than a log file on a server which may be secured and not accessible.

The ideal would be to log locally to a file, and then migrate this data to a DB for inspection if needed afterwards.

Now auditing is a different beast altogether. While it is similar in concept to logging, audit is usually needed to be kept for a long time (unlike log files used for debugging or tracing that may be deleted at a whim). Audits are there to show important information. You log far less audit information and less often than normal logging so performance is not a concern. Its for this reason that the advantages of writing this audit info to a central DB are seen.

  • 1
    Another arrangement I've seen is for logs to be written locally initially and then pushed to the DB by some kind of background job. – Robbie Dee Mar 23 '16 at 11:45
  • @RobbieDee I like this idea. I have a kind of duplicate question: is it common to have a limited duration log files(e.g. last 30 days only) but as logs are pushed into database (e.g. weekly), then all logs are stored in database => the log files act like a buffer only and all read operations are done on the database? As writing in DB is delayed, there is no worry about performance, isn't it. – Al-un Oct 3 '17 at 11:06
8

There is no one size fits all approach and for resiliency, you sometimes want to use multiple approaches. Taking your example, you may want to stash debug logs in a file and store audit logs away in a DB.

Application breadcrumbs

Pros: Easy to implement and visible to the user straight away

Cons: Information only persists while the application is up

Text file

Pros: Easy to implement

Cons: Need to ensure file locking doesn't occur. What to do when disk space runs out on the log drive?

Event log

Pros: Easy to implement

Cons: Event log might become full if not set up correctly or old logs could be lost due to retention policy/cleardown.

Database

Pros: Easy to implement

Cons: More DB traffic. How to log a loss of DB or other DB problem?

Messaging (MQ)

Pros: Fire and forget

Cons: Another layer to go wrong. Requires set up

  • You might also want to include syslog daemon and SNMP log destinations. – gbjbaanb Mar 23 '16 at 11:51
  • @gbjbaanb Well indeed - some OSs have such functionality built in - this certainly isn't an exhaustive list. High availability systems might also send SMSs when they go down. – Robbie Dee Mar 23 '16 at 12:05
2

Audit logs must ensure full traceability of operations over longer time for audit purposes, with the goal of fully justifying the content of your database.

In some cases (e.g. financial applications) these logs may have to ensure compliance with legal requirements such as retention (in some countries for 10 years) or unalterability. As these logs have to justify the content of the db at application level, it's common practice to store them in the db, where access can be controlled to avoid unauthorized alteration.

Other logs, like monitoring logs or security logs frequently have to cope with performance and volume constraints. These are generally written to a file because it's faster to write (no transaction management overhead) easier to archive offline, and easier to integrate with external monitoring SIEM tools.

It shall be noted that, while these kind of logs can be used to demonstrate reliability of audit logs (e.g. no unauthorized access), they generally have shorter retention constraints (for example between 6 month and 2 years for law enforcement purposes fro telecommunication logs) if any constraint at all.

1

One of many reasons to use db for debug logging is When you don't have access to application or web server to view event viewer or text files

Audit logs are different then debug logs in context of web application, as in some applications you may need to show them to end user so they should go into database for easy retrieval.

You can use DB Tools for filtering and easy viewing too.

  • Writing to server log files isn't the only option for high concurrency systems. These logs are often written to the client device and then pushed thru to support in the event of a problem. High concurrency may also make this unsuitable for writing to a single database. – Robbie Dee Mar 23 '16 at 11:43
  • @RobbieDee Is that the case for web servers and web applications too ? as that's what OP is asking for I believe – Muhammad Raja Mar 23 '16 at 12:02

protected by gnat Nov 13 '18 at 19:58

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.