Game log format for MMO servers

A log of game events (as opposed to error / debug logs) for an entire cluster/shard is very useful for a commercial MMO that is in a live production environment, providing vital support for customer service, and the means for historical analytics.

The project that I am currently working on uses a relational database to store all of the game event logs, and while that method works fine it seems to me that the read-only, chronological nature of the logs would allow for a more efficient storage format.

I'm not sure where to begin learning about building custom binary log formats, however. What are your experiences with creating custom log formats, or any recommended papers / articles on the subject?

Answer

In Stendhal we solved the performance issue by adding game events to a queue and then processing them asynchronously in the background.

In our case the events are not just records but objects which have a little bit of logic because in some cases we need to do two inserts with a link between them. For example the first time a item is handled in game, it needs to be inserted into the item table first before an item-event can be logged.

But writing the log is only one side of the problem:

What questions do you want to answer with the logs?

It is easy to just read the complete log in chronologically order; or to filter it for one player.

But there might be questions like:

• Which items has Anton put on the ground that have been picked up by Beth yesterday? Which player owns them now? (Anton complained about his item being stolen)


• How much time does an average player need to reach level 100? Which players have been significantly faster? only for first characters?


• Are there players that handle huge amounts of game-money? To which players is it passed? Without anything valuable in return?


• Are weak players able to kill strong creatures that they should not be able to kill legally?



• ...

In Stendhal we use a relational database for game logs because that is the easiest way to allow performant ad-hoc queries. If you use a custom log format, you basically have to code all those queries when need arises. And doing that with sufficient performance gets rather difficult.

Our gameEvents table has 51,429,139 rows (last year) and we have a dedicated itemlog table which has 60,360,657 rows (all time) for 15,893,831 items.