23

I have a multi-user application that keeps a centralized logfile for activity. Right now, that logging is going into text files to the tune of about 10MB-50MB / day. The text files are rotated daily by the logger, and we keep the past 4 or 5 days worth. Older than that is of no interest to us.

They're read rarely: either when developing the application for error messages, diagnostic messages, or when the application is in production to do triage on a user-reported problem or a bug.

(This is strictly an application log. Security logging is kept elsewhere.)

But when they are read, they're a pain in the ass. Grepping 10MB text files is no fun even with Perl: the fields (transaction ID, user ID, etc..) in the file are useful, but just text. Messages are written sequentially, one like at a time, so interleaved activity is all mixed up when trying to follow a particular transaction or user.

I'm looking for thoughts on the topic. Anyone done application-level logging with an SQL database and liked it? Hated it?

Clinton Pierce
  • 12,859
  • 15
  • 62
  • 90
  • Really you mean any RDBMS. No? I mean either you log to files or you log to a database, right? –  Oct 16 '08 at 20:01
  • 1
    Sure are a lot of powerful distributed logging technologies that have been created this since question, and almost all of the answers, were posted. – user7817808 May 20 '20 at 23:14

11 Answers11

26

I think that logging directly to a database is usually a bad idea, and I would avoid it.

The main reason is this: a good log will be most useful when you can use it to debug your application post-mortem, once the error has already occurred and you can't reproduce it. To be able to do that, you need to make sure that the logging itself is reliable. And to make any system reliable, a good start is to keep it simple.

So having a simple file-based log with just a few lines of code (open file, append line, close file or keep it opened, repeat...) will usually be more reliable and useful in the future, when you really need it to work.

On the other hand, logging successfully to an SQL server will require that a lot more components work correctly, and there will be a lot more possible error situations where you won't be able to log the information you need, simply because the log infrastructure itself won't be working. And something worst: a failure in the log procedure (like a database corruption or a deadlock) will probably affect the performance of the application, and then you'll have a situation where a secondary component prevents the application of performing it's primary function.

If you need to do a lot of analysis of the logs and you are not comfortable using text-based tools like grep, then keep the logs in text files, and periodically import them to an SQL database. If the SQL fails you won't loose any log information, and it won't even affect the application's ability to function. Then you can do all the data analysis in the DB.

I think those are the main reasons why I don't do logging to a database, although I have done it in the past. Hope it helps.

Ricardo Reyes
  • 13,256
  • 4
  • 27
  • 19
21

We used a Log Database at my last job, and it was great.

We had stored procedures that would spit out overviews of general system health for different metrics that I could load from a web page. We could also quickly spit out a trace for a given app over a given period, and if I wanted it was easy to get that as a text file, if you really just like grep-ing files.

To ensure the logging system does not itself become a problem, there is of course a common code framework we used among different apps that handled writing to the log table. Part of that framework included also logging to a file, in case the problem is with the database itself, and part of it involves cycling the logs. As for the space issues, the log database is on a different backup schedule, and it's really not an issue. Space (not-backed-up) is cheap.

I think that addresses most of the concerns expressed elsewhere. It's all a matter of implementation. But if I stopped here it would still be a case of "not much worse", and that's a bad reason to go the trouble of setting up DB logging. What I liked about this is that it allowed us to do some new things that would be much harder to do with flat files.

There were four main improvements over files. The first is the system overviews I've already mentioned. The second, and imo most important, was a check to see if any app was missing messages where we would normally expect to find them. That kind of thing is near-impossible to spot in traditional file logging unless you spend a lot of time each day reviewing mind-numbing logs for apps that just tell you everything's okay 99% of the time. It's amazing how freeing the view to show missing log entries is. Most days we didn't need to look at most of the log files at all... something that would be dangerous and irresponsible without the database.

That brings up the third improvement. We generated a single daily status e-mail, and it was the only thing we needed to review on days that everything ran normally. The e-mail included showed errors and warnings. Missing logs were re-logged as warning by the same db job that sends the e-mail, and missing the e-mail was a big deal. We could send forward a particular log message to our bug tracker with one click, right from within the daily e-mail (it was html-formatted, pulled data from a web app).

The final improvement was that if we did want to follow a specific app more closely, say after making a change, we could subscribe to an RSS feed for that specific application until we were satisfied. It's harder to do that from a text file.

Where I'm at now, we rely a lot more on third party tools and their logging abilities, and that means going back to a lot more manual review. I really miss the DB, and I'm contemplated writing a tool to read those logs and re-log them into a DB to get these abilities back.

Again, we did this with text files as a fallback, and it's the new abilities that really make the database worthwhile. If all you're gonna do is write to a DB and try to use it the same way you did the old text files, it adds unnecessary complexity and you may as well just use the old text files. It's the ability to build out the system for new features that makes it worthwhile.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • How are you doing logging in database, I want to implement similar kind of logging mechanism and wanted to see what is the standard or best way of logging in database, any architecture guidance would be highly appreciated. – Rachel Apr 03 '12 at 17:39
  • 1
    @Rachel All I did was implement a [TraceListener](http://msdn.microsoft.com/en-us/library/system.diagnostics.tracelistener.aspx) that wrote messages to the database. Then all the logging was just [System.Diagnostics.Trace](http://msdn.microsoft.com/en-us/library/system.diagnostics.trace.aspx) calls: easy peasy. Part of the common framework in our apps was code to load both our custom tracelistener and a TextWriterTraceListener for the proper log file. – Joel Coehoorn Apr 03 '12 at 19:10
  • Thanks for the inputs but do you know similar way to do it in Java? – Rachel Apr 04 '12 at 13:38
  • 1
    Looking at this again a few years later, now that I've spent more time as a sysadmin, rather than a developer... this worked for the team I was on when we built it because we were all developers. The sysadmins were a separate team, with their own logs to review. The development team only had to review logs for stuff we wrote. Now that I'm doing more sysadmin stuff... there are existing items for handling logs, like Splunk, OpServer, etc, that may be more relevant. No need to re-invent the wheel. Better to write to an existing platform/SIEM vs creating your DB schema and platform. – Joel Coehoorn Nov 16 '16 at 15:20
  • @JoelCoehoorn thank you for coming back and re-visiting this. I was just about to comment. Do you feel that a hand-spun automated process could also provide the same advantages that a database offers? It seems like the advantages of database-provided log evaluation mechanics could easily be coded into an application that just scrapes the files in a folder (and by "seems" I mean I've actually helped architect and watch one be developed) - if for some reason someone wanted to avoid a Splunk-like application. – Thomas Feb 22 '18 at 14:54
  • No. I feel the DB is superior to a home grown scraper, because of the ability to do queries against when something unusual happens. But there are log scraper products that get too close to this, and you might want to write a scraper to re-log entries from files to the DB when you're dealing with a product that only writes files. – Joel Coehoorn Feb 22 '18 at 17:37
14

yeah, we do it here, and I can't stand it. One problem we have here is if there is a problem with the db (connection, corrupted etc), all logging stops. My other big problem with it is that it's difficult to look through to trace problems. We also have problems here with the table logs taking up too much space, and having to worry about truncating them when we move databases because our logs are so large.

I think its clunky compared to log files. I find it difficult to see the "big picture" with it being stored in the database. I'll admit I'm a log file person, I like being able to open a text file and look through (regex) it instead of using sql to try and search for something.

The last place I worked we had log files of 100 meg plus. They're a little difficult to open, but if you have the right tool it's not that bad. We had a system to log messages too. You could quickly look at the file and determine which set of log entries belonged which process.

kemiller2002
  • 113,795
  • 27
  • 197
  • 251
4

We've used SQL Server centralized logging before, and as the previous posted mentioned, the biggest problem was that interrupted connectivity to the database would mean interrupted logging. I actually ended up adding a queuing routine to the logging that would try the DB first, and write to a physical file if it failed. You'd just have to add code to that routine that, on a successful log to the db, would check to see if any other entries are queued locally, and write those too.

I like having everything in a DB, as opposed to physical log files, but just because I like parsing it with reports I've written.

SqlRyan
  • 33,116
  • 33
  • 114
  • 199
  • How have you implemented it, what is the architecture behind it? – Rachel Apr 03 '12 at 17:40
  • @Rachel: It was pretty straightforward - I had a two-step logging process, where it would attempt to log to the remote database first, writing to an XML file if that failed, and then the second step was that it would check the local XML file for any entries and send those to the database (assuming the initial call had succeeded). That way, the Catch block for the database logging attempt handled saving it locally, and then after a successful database call, it always checked to see if there was something else to do. Since I write to the log on a background thread, I never held up the app. – SqlRyan Apr 04 '12 at 04:31
  • If possible, i would like to see some code or pseudocode to understand how this is being done as i have similar requirement, i was thinking of just creating entity and store it in database table and using hibernate for interaction and then have function that would take in string and save that string in entity and save in table and call this function from other function and pass logging information, i am not 100% sure if this is good way of doing it or even if it's feasible and so wanted to learn about your approach – Rachel Apr 04 '12 at 13:36
3

I think the problem you have with logging could be solved with logging to SQL, provided that you are able to split out the fields you are interested in, into different columns. You can't treat the SQL database like a text field and expect it to be better, it won't.

Once you get everything you're interested in logging to the columns you want it in, it's much easier to track the sequential actions of something by being able to isolate it by column. Like if you had an "entry" process, you log everything normally with the text "entry process" being put into the "logtype" column or "process" column. Then when you have problems with the "entry process", a WHERE statement on that column isolates all entry processes.

hova
  • 2,811
  • 20
  • 19
2

Here are some additional pros and cons and the reason why I prefer log files instead of databases:

  1. Space is not that cheap when using VPS's. Recovering space on live database systems is often a huge hassle and you might have to shut down services while recovering space. If your logs is so important that you have to keep them for years (like we do) then this is a real problem. Remember that most databases does not recover space when you delete data as it simply re-uses the space - not much help if you are actually running out of space.

  2. If you access the logs fequently and you have to pull daily reports from a database with one huge log table and millions and millions of records then you will impact the performance of your database services while querying the data from the database.

  3. Log files can be created and older logs archived daily. Depending on the type of logs massive amounts of space can be recovered by archiving logs. We save around 6x the space when we compress our logs and in most cases you'll probably save much more.

  4. Individual smaller log files can be compressed and transferred easily without impacting the server. Previously we had logs ranging in the 100's of GB's worth of data in a database. Moving such large databases between servers becomes a major hassle, especially due to the fact that you have to shut down the database server while doing so. What I'm saying is that maintenance becomes a real pain the day you have to start moving large databases around.

  5. Writing to log files in general are a lot faster than writing to DB. Don't underestimate the speed of your operating system file IO.

  6. Log files only suck if you don't structure your logs properly. You may have to use additional tools and you may even have to develop your own to help process them, but in the end it will be worth it.

ccellar
  • 10,326
  • 2
  • 38
  • 56
Donatello
  • 988
  • 7
  • 7
2

we do it in our organization in large volumes with SQL Server. In my openion writing to database is better because of the search and filter capability. Performance wise 10 to 50 MB worth of data and keeping it only for 5 days, does not affect your application. Tracking transaction and users will be very easy compare to tracking it from text file since you can filter by transaction or user.

You are mentioning that the files read rarely. So, decide if is it worth putting time in development effort to develop the logging framework? Calculate your time spent on searching the logs from log files in a year vs the time it will take to code and test. If the time spending is 1 hour or more a day to search logs it is better to dump logs in to database. Which can drastically reduce time spend on solving issues.

If you spend less than an hour then you can use some text search tools like "SRSearch", which is a great tool that I used, searches from multiple files in a folder and gives you the results in small snippts ("like google search result"), where you click to open the file with the result interested. There are other Text search tools available too. If the environment is windows, then you have Microsoft LogParser also a good tool available for free where you can query your file like a database if the file is written in a specific format.

1

I've been reading all the answers and they're great. But in a company I worked due to several restrictions and audit it was mandatory to log into a database. Anyway, we had several ways to log and the solution was to install a pipeline where our programmers could connect to the pipeline and log into database, file, console, or even forwarding log to a port to be consumed by another applications. This pipeline doesn't interrupt the normal process and keeping a log file at the same time you log into the database ensures you rarely lose a line. I suggest you investigate further log4net that it's great for this.

http://logging.apache.org/log4net/

Maximiliano Rios
  • 2,196
  • 2
  • 20
  • 34
1

You could log to a comma or tab delimited text format, or enable your logs to be exported to a CSV format. When you need to read from a log export your CSV file to a table on your SQL server then you can query with standard SQL statements. To automate the process you could use SQL Integration Services.

0

I could see it working well, provided you had the capability to filter what needs to be logged and when it needs to be logged. A log file (or table, such as it is) is useless if you can't find what you're looking for or contains unnecessary information.

Lieutenant Frost
  • 541
  • 2
  • 7
0

Since your logs are read rarely, I would write them on file (better performance and reliability).

Then, if and only if you need to read them, I would import the log file in a data base (better analysis).

Doing so, you get the advantages of both methods.

Pietro
  • 12,086
  • 26
  • 100
  • 193