My service has a large ongoing number of user events, and we would like to do things like "count occurrence of event type T since date D."

We are trying to make two basic decisions:

  1. What to store? Storing every event vs. only storing aggregates

    • (Event log style) log every event and count them later, vs.
    • (Time-series style) store a single aggregated "count of event E for date D" for every day
  2. Where to store the data

    • In a relational database (particularly MySQL)
    • In a non-relational (NoSQL) database
    • In flat log files (collected centrally over the network via syslog-ng)

What is standard practice / where can I read more about comparing the different types of systems?

Additional details:

  • The total event stream is large, potentially hundreds of thousands of entries per day
  • But our current need is only to count certain types of events within it
  • We don't necessarily need real-time access to the raw data or aggregation results

IMHO, "log all events to files, crawl them at a later time to filter and aggregate the stream" is a pretty standard UNIX Way, but my Rails-y compatriots seem to think that nothing is real unless it's in MySQL.

  • 1
    Any luck on this project?
    – Waylon
    Sep 20, 2012 at 13:46
  • 2
    @hiwaylon We've ended up using a hybrid system: 1) MySQL where possible (low volume) (makes aggregation easy using SELECT...GROUP BY, can easily store the results of SELECTs), 2) using Graphite for simple large-scale aggregation and visualization, and 3) logging full events for reference, and for watching details of the data flow in in real time. Each has actually been valuable in different ways.
    – elliot42
    Jan 8, 2013 at 3:47
  • That sounds like a great solution, quite similar with what we are doing as well.
    – Waylon
    Jan 10, 2013 at 16:57
  • 1
    UPDATE over a year later we built a system that logged everything, and periodically iterated over the logs counting things, and then stored those counted numbers into a database (could/should have been a a time-series database, but MySQL sufficed). This was a few weeks of work but ended up being a surprisingly powerful/fast approach--when it's just your code iterating over logged JSON, it's easy to add a lot of metadata, and easy for your code to have flexible rules for exactly what it wants to count.
    – elliot42
    Mar 7, 2014 at 18:06
  • 1
    Update 2016: Kafka can do these kinds of things these days, at least for raw storage. Then you can either stick them into a big MapReduce or Spark job, or a big warehouse like Vertica etc. if you want to query/aggregate over them.
    – elliot42
    May 18, 2016 at 0:41

4 Answers 4


It always depends, I'll give you my advice to offer you a new perspective

What to store? Storing every event vs. only storing aggregates

(Event log style) log every event and count them later, vs.

If you plan to don't miss any detail, even though now they are not relevant, on my eyes that's the best approach, because sometimes, as the results comes, then you find some other events that for X or Y they were not relevant, or they didn't bring any extra information, but after some analysis, it simply does, and you need to also track that one, then because its recorded but not accounted it would take you some time before you can add it to the picture.

(Time-series style) store a single aggregated "count of event E for date D" for every day

If you want to implement and use it tomorrow, it can work, but then if you have a new requirements, or you find a correlation with another event that you omitted for any reason, then you need to add this new event and then wait some long time to have nice aggregation levels

Where to store the data

In a relational database (particularly MySQL)

The first option can be heavy for a DB if you go for recording all events, so MySQL I'm afraid can become too small, and if you want to go for RDBMS solutions you may think bigger, like PostgreSQL or proprietary like Oracle or DB2.

But for the aggregation would be a good choice, depending of the load generated you can aggregate in code and insert those aggregations in the DB.

In a non-relational (NoSQL) database

If you go for this solution, you need to see which approach you want to follow nice read on wikipedia may help you, I can't help you much on that topic because I simply don't have enough experience, I mostly use rdbms.

In flat log files (collected centrally over the network via syslog-ng)

I personally would discourage you to go for that option, If the file grows too much, it would be more difficult to parse, but still I don't know the main purpose, is to follow up on a system, or simply check a log file ...

Hope it helps!

  • 1
    Log files should be rotated on size or length. I don't think the last concern would be an issue then.
    – Waylon
    Sep 20, 2012 at 13:51

I think that your idea to parse logs, count and store results in a DB is valid. Not sure you'd want all those raw logs in the DB anyway (I think that's what you said your compatriots are suggesting). You've already got the logs in files, correct? You could just archive those. I suppose that bit really depends on your use case(s).

Also agree with @Thorbjørn Ravn Andersen about moving your "comment answer" to the question.


Depends on your intended usage. If you have a standard graph or report showing aggregate values, then you'll want to simply filter the events as they come in and aggregate them into the appropriate bucket. If you need to drill down into specific events, or if you think you might want to go back and re-analyze / re-categorize events later, then you should store the individual events.

If you've got the time and space, what I typically like to do is aggregate the data, but store the details in a (compressed) file. The details don't have to be easily accessible, since I almost never need them, but they're available for bulk re-processing if the classification criteria change.

  • "aggregate the data, but store the details in a (compressed) file". Great thought in particular, thanks!
    – elliot42
    Sep 17, 2012 at 18:31
  • Are there concerns with the volume of logging the OP mentioned and doing filtering + aggregating as they come in? It seems like it might be a dangerous bottleneck if the log volume is high and/or aggregation is non trivial.
    – Waylon
    Sep 20, 2012 at 13:53
  • 1
    OP mentioned volumes of "hundreds of thousands of events a day". One million events a day is less than seven hundred a minute, or about eleven a second. Unless the input is some lengthy XML, your average server should be able to handle that without breaking a sweat. It's definitely something that should be considered when designing (and deploying) the solution, though.
    – TMN
    Sep 20, 2012 at 15:29

Any architecture decisión should be driven by business needs. In your case, you should have a more clear idea of what information do you want to obtain from your log system and in order to decide how to store, how often you will require this info and how much time you can wait to get the result. This is what drives the design of log collectors, event correlators and similar applications.

Rather than giving you my opinion, I suggest you look at some applications similar to what you try to develop. Some of them may be way more powerful that what you pretend to develop but it won't hurt if you look at the architecture and storage policies followed. On the professional side, you have SIEM applications like RSA and Arcsight and in the Open Source side you have initiatives like Kiwi or OSSIM (that has also a professional appliance based version).

Another thing to consider is that when you start using the results obtained by the tool, you will start receiving very likely many requests from your management for more information and more detailed one. So... use it carefully and plan with your view in the horizon. It may give you more work, but definitely you may get a lot of support and visibility (pressure comes in the package)....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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