# realtime multiplayer game database design

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.

• Is this an action-oriented game? Then your data should be in the game server's memory, not in a databse table. Databases are too slow to handle the gamestate of real-time games. Just the network latency alone is a problem. They do, however, make sense if you want to persist the game-state to the database for later retrieval for some reason. – Philipp Jun 23 '17 at 14:01
• will a server be able to handle that much data in memory? It's not action, but it is still real time, so a latency of 0.5-1 is fine. In the game it's 2 people racing and their actions don't directly affect the other player, but they still need to be able to see where the other player is – Jamal H Jun 23 '17 at 14:06
• you aren't telling us how much memory you need per ongoing match. We can't predict that. Only you have that information. – Philipp Jun 23 '17 at 14:07
• – Philipp Jun 23 '17 at 14:08
• I'd say 2-4 kb. – Jamal H Jun 23 '17 at 14:11

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".
• Thanks for the answer. For storing the data in memory, are there databases that run on the same machine which handle it or is it handled in the server code? – Jamal H Jun 23 '17 at 14:40
• @JamalH Do it in the server code. An in-memory database on the same server is quite pointless, because it uses the same memory resources as the game-server. – Philipp Jun 23 '17 at 14:42
• Ok. Are there libraries for that or should I just make a dictionary of lists with all my matches? – Jamal H Jun 23 '17 at 14:45
• @JamalH That's hard to answer without knowing anything about what technology you are using to develop your game-server. But if it's something object-oriented, you would likely have some class Connection which references, has a or is owned by a Player which references or is owned by a Match which holds all the data relevant for that match (except that for the player-objects, which hold their own data). – Philipp Jun 23 '17 at 14:50
• Ok. for the record i'm using java with a framework called netty – Jamal H Jun 23 '17 at 14:59

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.