Data Architecture – Designing for Event Log Metrics

Architecturedatabasemetrics

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.

Best Answer

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!

Related Topic