MMORPG Data Storage
Author
I've been thinking about how MMORPG's handle data storage but I'm not very knowledgeable about the efficiencies of certain methods.
I know that databases are quicker at most things than files, but...
The data storage requirements for a character would have to contain data on say 10,000 quests... Now if you had a column for each quest could that have a negative affect on performance or space in RAM/Disk.
This data would have to be check every time an action is performed related to the quest. Such as the NPC which gives the quest coming into view (whether to flag a ! or ? symbol above them). This is why the data must be quickly accessible.
Would having separate columns for each quest, using BLOBS or flat files be better?
Maybe some combination of them where a table of logged in characters, containing quicker access form and a offline characters table where it is stored in low storage requirement form.
Normalize your database.
CREATE TABLE `character` ( `id` INT NOT NULL AUTO_INCREMENT, `level` INT NOT NULL, `strength` INT NOT NULL, `dexterity` INT NOT NULL, `health` INT NOT NULL, `maxhealth` INT NOT NULL, PRIMARY KEY(`id`));CREATE TABLE `quest` ( `id` INT NOT NULL AUTO_INCREMENT, `text` TEXT NOT NULL, `script` TEXT NOT NULL, PRIMARY KEY(`id`));CREATE TABLE `completed` ( `character` INT NOT NULL REFERENCES(`character`.`id`), `quest` INT NOT NULL REFERENCES(`quest`.`id`), PRIMARY KEY(`character`, `quest`));SELECT COUNT(*) FROM `completed` WHERE `character` = #charid# AND `quest` = #questid#
Author
Genius!
:O
I seem not only to be thinking inside the box today but also only in only a confined part of it! I've made it much more complicated than it really is.
:O
I seem not only to be thinking inside the box today but also only in only a confined part of it! I've made it much more complicated than it really is.
I'd just like to add a caveat to Toohrvyk's excellent advice: a fully normalised schema can turn out to perform suboptimally. On the other hand, it's hard to know how to best flatten your schema until your game is mostly complete and you have some real usage statistics.
As a compromise, I suggest using a fully normalised schema to start with, but using stored procedures as the interface between code and DB. That way you can tune the underlying schema for performance laster, fix up your stored procedures, and not have to change any application code. Also, many DB systems can optimise, cache, and recommend indexing changes based on queries in stored procedures.
As a compromise, I suggest using a fully normalised schema to start with, but using stored procedures as the interface between code and DB. That way you can tune the underlying schema for performance laster, fix up your stored procedures, and not have to change any application code. Also, many DB systems can optimise, cache, and recommend indexing changes based on queries in stored procedures.
Quote:Original post by ganbree
This data would have to be check every time an action is performed related to the quest.
Jost a tiny subset, on the data already in memory.
Quote:Such as the NPC which gives the quest coming into view (whether to flag a ! or ? symbol above them). This is why the data must be quickly accessible.
It's why it's usually done on client-side. Each NPC that comes into view offers a set of quests, which are stored in client-agnostic manner (1 ID per quest). It's the client that classifies what to display.
Even if done fully server-side, the checks itself can be performed once, and then stored in a transient object for each client.
Quote:Maybe some combination of them where a table of logged in characters, containing quicker access form and a offline characters table where it is stored in low storage requirement form.
The client's quest state is loaded on login. The only changes are then performed when quest state needs to be persisted, which would likely be once every 10 minutes, probably even less on average.
Quest logic however is static and loaded once.
Quote:Original post by ganbree
The data storage requirements for a character would have to contain data on say 10,000 quests... Now if you had a column for each quest could that have a negative affect on performance or space in RAM/Disk.
no no and no! this is NOT how databases work! you create another table of quests and have it have a column for the user who the quest belongs to. each quest for each user gets its own row.
Quote:Original post by AntheusQuote:Original post by ganbree
This data would have to be check every time an action is performed related to the quest.
Jost a tiny subset, on the data already in memory.Quote:Such as the NPC which gives the quest coming into view (whether to flag a ! or ? symbol above them). This is why the data must be quickly accessible.
It's why it's usually done on client-side. Each NPC that comes into view offers a set of quests, which are stored in client-agnostic manner (1 ID per quest). It's the client that classifies what to display.
Even if done fully server-side, the checks itself can be performed once, and then stored in a transient object for each client.Quote:Maybe some combination of them where a table of logged in characters, containing quicker access form and a offline characters table where it is stored in low storage requirement form.
The client's quest state is loaded on login. The only changes are then performed when quest state needs to be persisted, which would likely be once every 10 minutes, probably even less on average.
Quest logic however is static and loaded once.
I am currently working on a server for a private mmo project.
I plan to realize this by using a DataObject for each player on the server.
If a player is connected to the server, the server has to know the players information. How big will it be ? Less than 1mb.
So i thought the easiest way is to serialize and deserialize the DataObject on login-logout. Maybe sync it with the client.
I am planning to use xml serialization (the server is written in c#). You could save the xml-data in a database or as a flat file for example.
Quote:Original post by HeftiSchlumpf
So i thought the easiest way is to serialize and deserialize the DataObject on login-logout. Maybe sync it with the client.
What happens if player is logged in for 9 hours, and has just looted The Sword of Thousand Truths, then server crashes?
Even worse than players losing items on a server crash, is that any server crash lets players duplicate items / money if you only update the database on logout:
1. Player A gives all his items to player B.
2. Player B logs out saving himself to the database.
3. Server crashes throwing away the in memory data.
4. When the server is back up player A will still have everything he gave to B.
5. B gives everything back to A. A has now doubled up on everything.
If that process is repeated starting say with 1000 gold, you'll have over 1,000,000 after 10 server crashes. For that reason you must sync with the database directly (and ideally atomically) when one player trades with another. That includes indirect trades such as dropping items on the floor that someone else can pick up.
Stopping players from losing too much progress on a server crash is simpler to fix - just do an occasional save of player state.
1. Player A gives all his items to player B.
2. Player B logs out saving himself to the database.
3. Server crashes throwing away the in memory data.
4. When the server is back up player A will still have everything he gave to B.
5. B gives everything back to A. A has now doubled up on everything.
If that process is repeated starting say with 1000 gold, you'll have over 1,000,000 after 10 server crashes. For that reason you must sync with the database directly (and ideally atomically) when one player trades with another. That includes indirect trades such as dropping items on the floor that someone else can pick up.
Stopping players from losing too much progress on a server crash is simpler to fix - just do an occasional save of player state.
Quote:What happens if player is logged in for 9 hours, and has just looted The Sword of Thousand Truths, then server crashes?
The player becomes unhappy. Tough luck :-) The server should not crash in the first place.
You have to decide on a specific level of consistency, and design to that. Building to an "always persistent, always consistent" level of service is a lot more expensive than building to an "occasionally checkpointed, with consistency in trades" model.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
