12
\$\begingroup\$

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?

|improve this question
\$\endgroup\$
7
\$\begingroup\$

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.

|improve this answer
\$\endgroup\$
  • \$\begingroup\$ How fast is searching through your databse? I've got similiar databse with logs and we've got around 100,000,000 rows afters three months. We use MySQL as storage and its performance is bad. Simple query that lists all actions of a player (only 20,000) rows takes often more than 60 seconds. \$\endgroup\$ – Balon Jan 11 '11 at 16:38
  • 1
    \$\begingroup\$ Simple queries on index columns are instant. Complex queries may take a little, 60 seconds queries do happen, but they are very rare. We indexed the table very heavily and compensated the penalty on insert by doing them asynchronously. \$\endgroup\$ – Hendrik Brummermann Jan 11 '11 at 19:23
  • \$\begingroup\$ Hmmm I think that my problem is that the result set is quite big, often between 3000 and 150000 records for one player. So this may be reason why it takes so long time, because it works very fast for small result sets. \$\endgroup\$ – Balon Jan 30 '11 at 16:40
4
\$\begingroup\$

What do you mean by efficiency? Whether it's size on disk or query speed, a relational database is almost certainly going to beat or equal your proprietary binary format, and be much easier and more flexible to use.

Each table you use in a relational DB pretty much lets you specify to the exact byte how much space per row you're going to allow. If you're not logging plain text - and "log of game events (as opposed to error / debug logs)" implies that you're not, or at least don't need to - then the fixed-width field approach of a relational database is rather close to optimal in terms of space, which makes them pretty fast in the first place. On top of that relational databases are pretty handy at making indexes for very fast access, and optimising queries to make the most of them.

So I'd recommend sticking with what you've got.

|improve this answer
\$\endgroup\$
  • \$\begingroup\$ Thanks for the response (and thanks to the others who submitted answers below)! The more that I think about it the more it seems like an RDBMS is the proper fit for this particular type of logging. It wouldn't be too difficult to design a custom log format that was well indexed for basic kinds of searches, but with the sort of complicated queries that are often used by CSRs and game analytics a more general approach is necessary -- at which point an established product is going to outperform in most cases. \$\endgroup\$ – Charles Ellis Oct 14 '10 at 6:29
  • \$\begingroup\$ Custom event log setup would be handy for strictly chronological playback ala FPS demo recordings, but that's a very different problem to solve. Does anyone have experience with developing something similar to demo recordings for massively multiplayer client-server games? \$\endgroup\$ – Charles Ellis Oct 14 '10 at 6:31
  • \$\begingroup\$ Depending on your server-side logic processing model, it may be possible just to store every input, stamped with the time of arrival on the server, which can be replayed. The problem tends to come in playback, since you need to replay every single input as well as modelling any other factor (eg. random seeds, implied input such as stuff that alters based on latency, etc). But there is no one-size-fits-all system here - it depends on how your server works. \$\endgroup\$ – Kylotan Oct 15 '10 at 9:20
3
\$\begingroup\$

It is true you could probably save a few bytes with a custom format, or ever just gzipped text, storage is cheap so it really isn't worth optimizing for anymore. What is more important is deal with things like I/O buffering and querying, both of which an off-the-shelf SQL server probably does pretty well. If it is working for you on those fronts, I would run with it. We wrote our own buffering log server which writes to custom files and then has a separate parser program to read it into a database for queries, I wouldn't recommend it.

|improve this answer
\$\endgroup\$
0
\$\begingroup\$

Relational databases these days are knocked for being inefficient, but when storing the type of logs you're talking about, you don't really need efficiency because they won't be constantly accessed by the game or its users - only your team will need to read the data.

So "efficiency" doesn't matter that much. What matters more is ordering the data in a way that makes it easy to tell the story of what users are doing in the game. Your developers will generally need to consume this data and display it in an interface that's easy to read for analysts and analysts will sometimes need to query the data to dig deep into user behavior. For instance, if players are purchasing a certain item before an update, but stop purchasing it after an update, an analyst will benefit by writing certain queries that expose certain numbers about behavior surrounding that purchase to determine why users no longer buy it. It's best if they have a standard query language to work with that is well documented. If they have to make these queries into a custom binary format their jobs will be made MUCH harder, and that's INEFFICIENT.

Generally game events look something like this (this is DeltaDNA's format in particular) 

{
 "eventName":"specific event code – eg. gameStarted",
 "userID":"ABCD1-4321a879b185fcb9c6ca27abc5387e914",
 "sessionID":"4879bf37-8566-46ce-9f3b-bd18d6ac614e",
 "eventTimestamp":"yyyy-mm-dd hh:mm:ss.SSS",
 "eventParams":
  {
   "platform":"WEB",
   "param1":"stringParam",
   "param2":true,
   "param3":1234,
   "param4":["a","b","c"]
  },
}

The event usually includes an event name, a userID, a session ID, timestamp, and parameters that allow you to record whatever data you find useful to record surrounding that event. And in my experience, relational database formats are the best for recording such a structure.

|improve this answer
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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