Jump to content

  • Log In with Google      Sign In   
  • Create Account

"MMO" What to store in database?


Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.

  • You cannot reply to this topic
6 replies to this topic

#1 Olaf Van Schlacht   Members   -  Reputation: 766

Like
0Likes
Like

Posted 21 February 2013 - 05:06 AM

Hey everyone,

 

I recently started a project in my free time, which is about constructing a very very simple mmo 3-tier architecture (for a max player count of 100).

I came to the point where I have to decide what data can be stored in the backend (MySQL). Constantly storing a players position seems unefficient to me.

The same goes for item properties like damage, item level and other.

 

I wonder how this thing would be done by an expert. My idea was to store player data only if something happend (like level up) and save players position every 5 minutes or something.

 

For the item properties part, i thought of a construction, that the item data may be stored in the backend, but initialy loaded once the server starts up. So the item properties would be stored in the memory of the server. If I change something at runtime of an item, this item could be reloaded again.

 

I hope you understand what I am trying to do and maybe you can give me tips or links to some articles.

 

Best Regards

Olaf

 


printf("Hello Signature");


Sponsor:

#2 Kylotan   Moderators   -  Reputation: 3338

Like
2Likes
Like

Posted 21 February 2013 - 07:04 AM

Usually the game is a long-running process with all data being held and modified in memory, and periodically saves important data to the database. eg.:

  • Save player every minute.
  • Save player after significant event (eg. receiving significant experience reward).
  • Save both players in a transaction after a trade.
  • Save player at log-out.
  • Save persistent world state (eg. items in the world that you intend to persist across a reboot) when placed or moved (which is usually rarely)

 

Item properties - and character properties - need some careful consideration before you think about storage. In particular you need to consider:

  • Which aspects are shared across all items of a certain type (eg. material, 3D model, item type), and which aspects are unique for each instance (eg. magical charges left, damage done to the item)
  • How do you want to edit the shared aspects during development?
  • How do you anticipate patching shared aspects after release?

People familiar with databases are often inclined to put everything in the database, maybe with one table for the shared data and one table for the instance data. However this starts to work very poorly when you come to updating the data, because your data on your development machine is likely to have primary keys that clash with data on the live machines, assuming new objects have been created during play. You might get away with it if you only ever add to the shared data table - ie. never delete shared data, and never add instance data.

 

In general, after having experience of working both ways, I prefer to handle as much of the data as possible outside of the relational schema. I load shared data in from whatever format is easiest for designers to work with and which is easy to patch and merge (eg. text files or some derivative), and instance data in the database contains a reference to the relevant part of the shared data.

 

While I'm here I'll mention that you should be careful when considering primary keys for any data (whether in the DB or not). Autoincrements and sequences are a bad idea when it comes to merging. Try to use natural keys rather than surrogate keys if you can, because the natural keys will remain correct across development, test, and live servers.



#3 Olaf Van Schlacht   Members   -  Reputation: 766

Like
0Likes
Like

Posted 21 February 2013 - 07:22 AM

Thanks for the advice!


printf("Hello Signature");


#4 larspensjo   Members   -  Reputation: 1557

Like
0Likes
Like

Posted 21 February 2013 - 07:38 AM

I recently took the step to replace an SQL DB (MySQL) with a NoSQL (MongoDB). It turned out quite a lot of things got much easier to manage, but there are maybe disadvantages I am not yet aware of.

 

A simple comparison between SQL and NoSQL is that a SQL database supports joins and atomic updates of tables, while a NoSQL supports more flexible documents (rows) that can contain sub sets and arrays of items. In my case, I save one player for each document, including an array of inventory objects. The periodic auto save only updates some key facts of the player in this document.


Current project: Ephenation.
Sharing OpenGL experiences: http://ephenationopengl.blogspot.com/

#5 wicked357   Members   -  Reputation: 1278

Like
0Likes
Like

Posted 21 February 2013 - 11:25 AM

Just from a hacking aspect of things... I have wrote memory editors for MMO's that allowed you to teleport across the map to any x,y,z location. I am not sure on the most efficient way of doing this so maybe I am wrong but I would think storing the players position is something you would do regularly to avoid this. By calling a function that doesn't set the position but increments it by how fast your character moves so by changing your x,y,z in memory doesn't actually move you in the server (maybe it would look like it till you moved forward) so you would actually be getting your position from the DB more often then actually storing unless you are moving... Any thoughts on that idea? This was more food for thought to discuss since you stated "Constantly storing a players position seems unefficient to me".



#6 riuthamus   Moderators   -  Reputation: 5779

Like
0Likes
Like

Posted 21 February 2013 - 11:37 AM

We dont even let the player control their position. The server should have authoritative control over this but to do that you have to finesse it a bit since this can cause some lag if latency ever exists.



#7 Kylotan   Moderators   -  Reputation: 3338

Like
2Likes
Like

Posted 21 February 2013 - 01:30 PM

I recently took the step to replace an SQL DB (MySQL) with a NoSQL (MongoDB). It turned out quite a lot of things got much easier to manage, but there are maybe disadvantages I am not yet aware of.
 
A simple comparison between SQL and NoSQL is that a SQL database supports joins and atomic updates of tables, while a NoSQL supports more flexible documents (rows) that can contain sub sets and arrays of items. In my case, I save one player for each document, including an array of inventory objects. The periodic auto save only updates some key facts of the player in this document.

 
This is very similar to my situation. I worked with an SQL database on a commercial MMO and then also with one on my personal MMO project, but recently I moved to MongoDB. I also have one document per player with the inventory embedded. The data often contains IDs to things outside of MongoDB however, eg. unique item identifiers created by designers.
 
Disadvantages of MongoDB (and some other NoSQL databases) include:

  • Arbitrary queries are more complex (eg. look up all documents in one collection which have a sub-field that exists in any of the records in a different
    table - trivial in SQL, tricky with documents) and typically less efficient
  • Reliability is generally not as good (especially with MongoDB, although it has got better recently with journalling etc)
  • Non-portability of the data definition or query languages
  • Atomic transactions not always possible without changing your schema (eg. you can't guarantee that you can amend 2 documents together atomically)

Despite all that however, I prefer MongoDB for game use. Being able to store JSON pretty much straight into the DB is literally orders of magnitude easier than trying to get everything to fit nicely into tables and columns. That said, there's no reason why you can't just use PostgreSQL or MySQL as a key/value store, and you can get back some of the transactional and reliability benefits that way.
 
 

Just from a hacking aspect of things... I have wrote memory editors for MMO's that allowed you to teleport across the map to any x,y,z location. I am not sure on the most efficient way of doing this so maybe I am wrong but I would think storing the players position is something you would do regularly to avoid this.

 

You misunderstand - the position is always 'stored' in memory, but we are talking about a separate relational database. You don't need to regularly store the player's position in that database, because you always have the latest information in memory, which you can check against to prevent hacking. It only needs to be written to the database periodically, or on logout, so that if there's a crash the server will know where to place the player when they next log in.


Edited by Kylotan, 21 February 2013 - 01:30 PM.





Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.



PARTNERS