Aggregation, Part One
Jul 9, 04:24 PM
One of the trickiest problems that you can run into when building a metrics system is how you’re going to handle aggregation: that is, the process of taking data that meets certain criteria, and compressing it.
In general, most large-scale metrics systems need aggregation. On a big MMO, metrics databases will grow by hundreds of megabytes a day. Let’s imagine a database that is growing by 200 MB/day. And let’s say we don’t want the database to get much bigger than 50 GB, due to limitations of our hardware. This means we only have about 8 months of data recording we can do before we hit our limit. At this point we have two options: we can either prune the oldest data by deleting it, or we can aggregate the oldest data.
At Orbus, we like approach aggregation by defining a cutoff point where data is considered old, and then setting up rules to deal with that data. For example, let’s say we have a simple event called EXP Earned. This event is stored in a table that looks like
| Date | Player | Level | EXP |
|---|---|---|---|
| 2007-01-01 | Bob | 3 | 50 |
| 2007-01-12 | Alice | 6 | 112 |
| 2007-01-21 | Bob | 3 | 82 |
| 2007-01-06 | Bob | 4 | 99 |
| 2007-01-24 | Charlie | 6 | 111 |
| 2007-02-05 | Charlie | 6 | 120 |
So we set up a rule that says, “On any EXP Earned events that are more than 6 months old, take the events and divide them into one-month chunks. Throw out the individual players: I just want to know the average EXP earned, by level, along with the aggregated sample size.”
The result is an aggregate table that looks like
| Date | Level | AvgEXP | Size |
|---|---|---|---|
| 2007-01 | 3 | 66.0 | 2 |
| 2007-01 | 4 | 99.0 | 1 |
| 2007-01 | 6 | 111.5 | 2 |
| 2007-02 | 6 | 120 | 1 |
The important thing is to design your aggregation so that while you’re essentially throwing away a ton of data, you’re keeping around the good stuff forever. Later this week, I’ll write about how you can figure out what exactly you’re going to aggregate.
