realtime multiplayer game database design

Question

I'm creating a real time client/server game, where each match is a 1v1. I'm a little lost as how to implement this. I want it to be viable for 1,000 simultaneous matches, but as I'm expecting around 100 db entries per match (I need to log players' past states), that would be a 100k row table.

I don't know if I should use an RDBMS that indexes the table by match ID, and if that would be fast enough.

I'm also contemplating using plain files with my own extension for each match, but that would be 1,000 of them on my machine, and I don't know if that would be too slow.

If anyone has any suggestions on how I can implement this, I'd really appreciate it.

Answer 1

Databases usually only make sense for data "at rest". As in data which someone might or might not look at at a much later point in time and which you want to be searchable by different keys. If you aren't going to do that, using a database usually doesn't make any sense.

When you have a real-time game, you should store all data you need for your ongoing games in the server's memory. The reason is that fetching a row from a database in the same datacenter under absolutely ideal conditions (database is an ultra-fast magic NoSQL store and has the value cached in memory) takes at least 5000 times as long as reading a value from memory. In the worst case, the database won't have the data in memory and will have to do a hard-drive seek, which takes 10,000 times as long as reading from memory. With hundreds of ongoing games, you might have thousands of hard drive accesses per second. This will become a bottleneck.

So you should better keep the data of all ongoing matches in your game-server process and only persist match results to a database.

A scenario where it can make sense to persist complete matches with all their ticks to the database is if you want the ability to watch replays of past matches or maybe even run some data-mining to analyze games to find out how to improve your game (or catch cheaters). How your database schema would look, depends on what exactly you want to do:

1. Replays, complete playback only: You need two columns, match and data, where data is a BLOB of your internal match representation. Unpack that whole blob when you want to replay the match. You might want to add some more columns with meta-data which helps you to find interesting matches (when was the match, who vs. who, who won, etc.)
2. Replays, frame-by-frame playback: Three mandatory columns, match, tick and data. The data is again a BLOB of your internal representation of the match-state.
3. Everything you want to data-mine: Now you will have to normalize the BLOB and split it into multiple database columns with readable values. That means whenever you have something you have multiple of, you will need to create another database table with match, tick, thingID and all the properties of that "thing".

Answer 2

Although @Philipp makes a good point, I don't fully agree with his assertions.

Now this rally depends on what you intend to do, but databases are good at efficiently persisting data and work through huge volume of data efficiently.

There are three levels of data access you need to consider. First you have your objects fully formed in memory. This generally quick access, but if you need to look through large swaths of objects to find something you need to implement the appropriate access algorithms (e.g. indexing).

In memory databases can make life a bit easier when the amount of data you handle surpasses your ability to organize efficiently. You can to it yourself, but if you are juggling a few GB of data, you are effectively re-implementing a database, just use an existing one.

Disk backed databases should be used when your data surpasses the RAM capacity or need long term storage. Databases generally are good at keeping the relevant bits in memory and hot; but yes if you get a cache miss, it will hurt bigtime. But if you use your database mostly for write only storage, they can be quite efficient. The key feature is that the write will be quick to memory and the database backend will later persist it when time allows.

Although you are not entirely clear on the details, you can have the best of both worlds. Why not keep your current game state in objects and each move / tick you write the game state changes to the database before updating to the next.