Jump to content
  • Advertisement
Sign in to follow this  
ganbree

MMORPG Data Storage

This topic is 3604 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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.

Share this post


Link to post
Share on other sites
Advertisement
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#

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Quote:
Original post by Antheus
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.


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.




Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!