Data Storage in a Multiplayer RPG

[OldRod]

I'm currently working in Unity, creating a small co-op RPG, 6-8 players max on the server and you can host your own server (project #338 of my never-ending soon-to-be-dead projects list ) Got a few questions.

I am using a server/client setup and have a login/account creation/character creation system saving info in a MySQL database.

Now I am ready to start on the actual game itself. Each player can have more than one character if they want, so characters are stored in their own table, linked to the accounts table. Do I store all their stats and health, etc. in this character table too? Or should I just store basic creation information (name, etc.) in that and have a separate table to handle game play info that will change continually as the game is played? Should I only update this information when it changes? Or when it changes + every so many game ticks?

Is there a good document or tutorial on how to set up efficient data storage in a multiplayer game? Like how far down to break a relational database into separate tables, vs. how much speed you give up, that kind of thing.

Also, if anyone is here who used to work on Minions of Mirth back when it was going, could you send me a PM please?

Thanks!

[MikuzA]

Hello,

I do not have experience on working with data storage with gaming.
But I do happen to have some experience of data warehousing and optimizations of it.

What the question usually is when it comes to this is that how it will be operated in the future..
If your data is most likely one-to-one, like both tables would contain only one row that are relational, then you should probably stick to one table from database point of view.

And, you can always later on split it up to two tables if it starts getting messy or you change your code so that it's actually one-to-many.

Of course, when developing, you might want to keep things organised and parts different from each other, let's say..
You make the char creation just to handle like select * from player_chars table. (This sort of queries should not be done anyway if the game is close on finalization, but as an example)

If you tend developing further and build your game further, you start adding more columns to that table.. Eventually the original player creation query gets all the unnecessary rows also, which caches up and slows up the query.

However, if all is built right and you have the indexes fine, then it should be ok.
And by having two tables work as one-to-one isn't really that of a performance issue if your row amounts are small, as in small, below 1-10 million.

1. You can have them all in one table, if you relation is one-to-one.
2. You can always reset your tables to be either 1 or 2, if you have the relation setup. (PK/FK)
3. When row amounts starts slowing things down, check your query with EXPLAIN and check if the INDEX's are used right. (PK is usually an index, but if for some reason you are querying with different where-sentence from a huge table, then an extra index doesn't hurt.. too many extra indexes does hurt thought, or so I've heard)

Well, hope this helps..

[Jackolantern]

Should I only update this information when it changes? Or when it changes + every so many game ticks?

Mikuza gave good info on database design, but I thought I would tackle this question.

In many cases you can't update the database for every change, or directly query the database. That is typically too slow. As part of the server start-up process, anything needed in the database to run the game world needs to be loaded into memory. Then part of each player logging in, you load all of their player data into memory. Changes and querying are all made against memory. Every so often, the server backs-up the memory back into the database. Memory is orders of magnitude faster than a database engine, and even though 6 - 8 players probably could directly hit the database, it is quite a big bottleneck against further expansion. And depending on how recklessly the game hits the database for info (such as for each player coordinate movement), even 6 - 8 players could bring the database to its knees. You can hit memory thousands of times a second, though.

How long you wait for database memory dumps is a balancing act. Everything that is only in memory will be lost on a server crash. Only the state recorded in the database can be restored once the server is restarted. Many MUDs would dump back to the db every 3 - 10 minutes. Commercial MMORPGs do it more often because players are less accepting of data loss on server crashes for a game they are paying for. I think most commercial MMORPGs create a multi-tiered db dump strategy, with easily lost pieces of memory (such as gold and items) being backed up very often, probably every 30 seconds, while pushing back things that don't matter much (like each player's coordinates in the world) being pushed back to every few minutes.

[OldRod]

Thanks for the discussion guys

If I remember right, Everquest would write to the server every 5 minutes automatically. But if you acquired money/items or killed something or changed zones (plus a few other conditions I'm sure), it would write immediately.

On the subject of single vs. multiple tables, does it affect performance if you write to a large table, vs. writing to a smaller table? I had originally planned to have separate tables for various parts of character information, like current stats, position/rotation info and separate tables for npc factions/quest interactions, history info (locations visited, mobs killed, etc).

Would putting all of that in one large table, with a lot of fields per record, affect performance? Or does it just not matter on a small scale like 8 people per server?

[Jackolantern]

Would putting all of that in one large table, with a lot of fields per record, affect performance? Or does it just not matter on a small scale like 8 people per server?

Yes, it would affect performance. Joins are probably the biggest factor, with more joins slowing down performance because every record has to be combined with every other record of the other table, so the number of rows in the virtual query table created can become exponentially large once so many tables are joined.

However, performance is not the reason you split tables. The reason you split tables into discreet "entities" (such as player account info, character base info, stats, equipment, inventory, etc.) is because not doing so is misusing SQL. When you pile everything into the same table, you are going to be repeating data all over the place. Repeating data is a waste of space. But worse than that, is that you will find you are locking yourself into a certain design and will find it extremely hard to change things later.

There are more issues than that, going as far as finding that there are entire queries you can't form because everything is piled into one table with one key. This entire concept is referred to as "normalization". Normalization gets pretty crazy as you move up the forms, and I really can't suggest going above 4th normal form, but second normal form should always be a goal to have a healthy database.

[MikuzA]

good points, can always trust on Jacko's expertise in everything !

Also keep in mind that I usually design my tables in a way that the 'not-so-often-changing' data is in one place and 'often-changing-data' in one place..
Datatypes of the columns can also cause performance issues in multiple column tables..

And if you are doing multiple joins, that can cause performance issues when rows reach big amounts, however, if indexed right, it's still in milliseconds.

My suggestion would be, do how you feel comfortable, do you how you 'think' it should be.. When problems occur, slowness occur, investigate and have a moment to review your vision on how it should be.

In any ways, it's really hard to make a relational database perform badly, if you have some clue on how to set up a database properly (Integer Primary Keys etc) then you should be fine
Don't waste too much time polishing db design, since often it's quite easy to re-do the design.. and if that would happen, the biggest impact might be in the queries..

You rarely have to change a database design so much that you need to alter the core of your game because of it.