MMO Database Access/Design
Author
Hi Folks,
Just wanted to get some discussion going with people that know more than I do on the subject. I've been reading alot about MMO databases, what techniques different companies use, etc, but still have a few questions.
I'm currently working on an existing MMO server, that uses .NET Serialization to save all the data into simple binary files. One for Guilds, Items, and one for NPCs/Players.
At a specified time (Every hour) the world goes into a "saving" mode, where the world is locked up, and the data is saved out to these files. The problem is, while saving it locks all the players up as well, this is something I want to get rid of.
So what I wanted to do, was convert this to an SQL Database, and re-write the saving functionality so it did "live" saves as opposed to set time saves that lock the world up.
The problem I'm having with this is getting my head around how it will actually work.
Issue A. How do you handle it if Player A changes guilds, you've then got to update and save 3 items (Both Guilds and the Player) together, if one fails and the server crashes or fails to commit one of the actions, there's an inconsistancy.
Same can be said for anything else as well, Player A consumes potion (Both Item and Player). Player B uses a mass-effect spell (Player and any affected NPCs).
Issue B. Going from an OO save/design, into a relational SQL design. This seems to be one of the more difficult challenges and I'm wondering if anybody has any tips, the current design has a class for each creature, item, etc. And some of those classes have custom properties not included in the base object.
Eg... Base Item (Saved Properties) -> Base Clothing (Saved Properties) -> "Generic Magic" Shirt (Might also have Saved properties).
If you were to convert this as is to SQL, there would be an excessive amount of tables.
Issue C. Updating the Database out-of-game. Thinking about it now, this probably isnt the best way of doing things, but if you were to update the database out-of-game, how would one detect and read those changes in? Currently all data is read in on server load, and never read again until the next server start, so only the server can really modify anything in-game and have it take effect.
Would you want to constantly be reading the database? Or would it be better to just not alter the database out-of-game unless the server was offline (Or on certain tables that could be used, such as account information).
I've also read that a few commercial MMO's save BLOB's to the database, this would mean they couldnt be altered out of game anyway, is this feasable? Or is it something that should be avoided.
Issue D. Constantly updating the database. I've read and seen that any sort of "live" update can cause issues with the database if the volume is extremely high. In order to counter this, some events or occurances arent updates immediately, perhaps things like movement. What's the best way to handle these updates? do them on a timed period, or?
Issue E. World saves locking up the server. I'm trying to move to SQL to get rid of the world-locking saves that stall all the players. But if there is perhaps another way around this that I'm not thinking of, feel free to enlighten me.
Lastly, just wondering if anybody knows anywhere that has information on MMO database design, or anything on any of the subjects I've mentioned above. If there is information there, I'm quite willing to read it, I've only really found bits and pieces myself though.
Thanks Folks for any help that can be given.
One prominent commercial MMO I worked on years ago had a similar save cycle as you described originally (saving data to files periodically). To avoid freezing the whole world (players and everything) in order to get a proper snapshot, you could consider forking the game processes at the time you would normally freeze the whole world to save and then have the forked processes save all the data per usual while the main game processes continue with the game. The game continues on while the original data states remain unchanged in the forked process.
This solution does require more server memory (or an OS that only duplicates memory data when data needs changed from the original process), however, it preserves all the existing saving mechanisms with minimal changes to the overall MMO. At the very least it could serve as an interim solution while you reimplement the saving process to a database backend.
This solution does require more server memory (or an OS that only duplicates memory data when data needs changed from the original process), however, it preserves all the existing saving mechanisms with minimal changes to the overall MMO. At the very least it could serve as an interim solution while you reimplement the saving process to a database backend.
Quote:Original post by Belzemus
Issue A. How do you handle it if Player A changes guilds, you've then got to update and save 3 items (Both Guilds and the Player) together, if one fails and the server crashes or fails to commit one of the actions, there's an inconsistancy.
SQL servers tend to support transactions, which are meant to solve that exact problem. Perform all three updates as part of the same transaction. Problem solved.
Quote:
Issue B. Going from an OO save/design, into a relational SQL design. This seems to be one of the more difficult challenges and I'm wondering if anybody has any tips, the current design has a class for each creature, item, etc. And some of those classes have custom properties not included in the base object.
Eg... Base Item (Saved Properties) -> Base Clothing (Saved Properties) -> "Generic Magic" Shirt (Might also have Saved properties).
If you were to convert this as is to SQL, there would be an excessive amount of tables.
That depends on how you convert it to SQL.
Most likely you'd simply have an Item table containing columns for all the generic item attributes.
If you need some kind of special properties that only a few items will have, you can put those in a separate table and make that reference an item ID.
Quote:Issue C. Updating the Database out-of-game. Thinking about it now, this probably isnt the best way of doing things, but if you were to update the database out-of-game, how would one detect and read those changes in? Currently all data is read in on server load, and never read again until the next server start, so only the server can really modify anything in-game and have it take effect.
Depends on what you want, and how big the game is. If you have enough data, then loading everything in at startup is going to be impossible. Then you will have to access the database while running, which means changes and updates will be seen by the game.
If the game doesn't read from the database at all while running, it obviously won't see any changes you make to the DB. Unless you tell the game to check.
But perhaps a more fundamental question is in order:
What kind of updates are you considering making out-of-game while the game is running?
Presumably you'll take the game down for patching, so that's out.
Won't your various tools to manage the game (for GM's and whatnot) interact with the actual game, rather than the database?
Is there actually a need to update the database directly while the game is running?
Quote:I've also read that a few commercial MMO's save BLOB's to the database, this would mean they couldnt be altered out of game anyway, is this feasable? Or is it something that should be avoided.
If some games do it already, then it'd be hard to argue that it isn't feasible. Is it a good idea?
Depends on your needs. On the whole, my gut feeling would be to say no, but perhaps they did it because it offered better performance in their case, or because it was easier (perhaps they were all clueless about SQL). It does eliminate a lot of the advantages to a relational database though.
Quote:Issue D. Constantly updating the database. I've read and seen that any sort of "live" update can cause issues with the database if the volume is extremely high. In order to counter this, some events or occurances arent updates immediately, perhaps things like movement. What's the best way to handle these updates? do them on a timed period, or?
Try it and see when it becomes a problem.
Most likely yes, you'll want to only update the database occasionally. How often? Depends, try it out and see what works.
Quote:Issue E. World saves locking up the server. I'm trying to move to SQL to get rid of the world-locking saves that stall all the players. But if there is perhaps another way around this that I'm not thinking of, feel free to enlighten me.
Keep the database in sync with the game. If you regularly save small changes to the database, it won't stall the game.
If you wipe the database and store *everything* at once, it'll take a while.
fork() would be the perfect solution, if it was available on Windows.
For using a SQL database, you want to block all updates that go together into a transaction. Begin transaction, update guilds and player data, commit transaction. That's what they're for.
If you want a simple database schema, you could have just a few tables -- one for each of guild, item, player, etc. That table could have only three columns; ID, Date Modified, and Data, which would be a blob that's the serialized player data. If you need searching by some other index in addition to ID (say, player name for a player), then add that as a key field to the table where it makes sense.
What I suggest wouldn't be the most "clean" way of using a SQL database, but it would solve your current problem, and it wouldn't suffer as much from normalization performance problems as the third normal form schema would.
When you perform something atomic, such as switching guilds, or doing a player-player trade, then you have the choice of committing everything touched by the atomic transaction at once, or you can build up a list of "all things that changed atomicly," and commit (and empty) that list every so often.
For a small scale MMO, I think that will work just fine. The problem comes when you're trying to scale it up to thousands online at the same time in the same instance, but "flat files" has even more trouble in that scenario :-)
Finally, it's important that, when you decide to commit, you quickly generate the data you need, then push that off to some asynchronous API (which might be a thread), and then go on with the regular server stuff. The async API or thread would then wait for the database to actually commit and return success, but you don't want blocking database calls (or file system calls) in the main loop of your server, because that will turn I/O bottlenecks into CPU bottlenecks, and cause unnecessary server lag.
For using a SQL database, you want to block all updates that go together into a transaction. Begin transaction, update guilds and player data, commit transaction. That's what they're for.
If you want a simple database schema, you could have just a few tables -- one for each of guild, item, player, etc. That table could have only three columns; ID, Date Modified, and Data, which would be a blob that's the serialized player data. If you need searching by some other index in addition to ID (say, player name for a player), then add that as a key field to the table where it makes sense.
What I suggest wouldn't be the most "clean" way of using a SQL database, but it would solve your current problem, and it wouldn't suffer as much from normalization performance problems as the third normal form schema would.
When you perform something atomic, such as switching guilds, or doing a player-player trade, then you have the choice of committing everything touched by the atomic transaction at once, or you can build up a list of "all things that changed atomicly," and commit (and empty) that list every so often.
For a small scale MMO, I think that will work just fine. The problem comes when you're trying to scale it up to thousands online at the same time in the same instance, but "flat files" has even more trouble in that scenario :-)
Finally, it's important that, when you decide to commit, you quickly generate the data you need, then push that off to some asynchronous API (which might be a thread), and then go on with the regular server stuff. The async API or thread would then wait for the database to actually commit and return success, but you don't want blocking database calls (or file system calls) in the main loop of your server, because that will turn I/O bottlenecks into CPU bottlenecks, and cause unnecessary server lag.
Author
Quote:That depends on how you convert it to SQL.
Most likely you'd simply have an Item table containing columns for all the generic item attributes.
If you need some kind of special properties that only a few items will have, you can put those in a separate table and make that reference an item ID
I thought about that, but looking at the classes, I'd say there would be quite a large amount of extra tables. The base tables arent really concerning, its more all the extra data.
Quote:Depends on what you want, and how big the game is. If you have enough data, then loading everything in at startup is going to be impossible. Then you will have to access the database while running, which means changes and updates will be seen by the game.
If the game doesn't read from the database at all while running, it obviously won't see any changes you make to the DB. Unless you tell the game to check.
But perhaps a more fundamental question is in order:
What kind of updates are you considering making out-of-game while the game is running?
Presumably you'll take the game down for patching, so that's out.
Won't your various tools to manage the game (for GM's and whatnot) interact with the actual game, rather than the database?
Is there actually a need to update the database directly while the game is running?
Currently, at the stats below, loading all the data into memory takes around 400-500MB of RAM, which is the method the server currently uses. It reads all the data from the serialized blobs, and uses it al from memory.
Thinking about your question, yeah, I should really be treating the database store as just that, a store. If I want to modify data or perform operations (Online ones, not correctional offline stuff) then it should ideally be going through the server, or a proxy of the server.
The general thought behind this idea was to have a website capable of modifying certain things (Eg, Web admin/GM management). The only concern I had, was if the server was down, you wouldnt be able to send requests to the server to be dealt with, if the website was highly integrated, you'd loose quite a bit of functionality should the server go down (Perhaps? Unless I'm missing something obvious, heh).
Quote:If some games do it already, then it'd be hard to argue that it isn't feasible. Is it a good idea?
Depends on your needs. On the whole, my gut feeling would be to say no, but perhaps they did it because it offered better performance in their case, or because it was easier (perhaps they were all clueless about SQL). It does eliminate a lot of the advantages to a relational database though.
Perhaps feasable wasnt the best word, I perhaps was just looking for opinions on that way of doing it. Personally I didnt think it would be that great either, as like you said, you lose alot of the advantages/mangement of an RDBMS.
Quote:Try it and see when it becomes a problem.
Most likely yes, you'll want to only update the database occasionally. How often? Depends, try it out and see what works.
I'm hoping it won't be a problem. We're talking about averages of 300,000 items, 30,000 npcs, 100-200 players.
Quote:For using a SQL database, you want to block all updates that go together into a transaction. Begin transaction, update guilds and player data, commit transaction. That's what they're for.
What do you mean by block all updates that go together?
And yeah, its pretty small-scale compared to commercial MMO's. This is more a hobby of mine than anything :)
You should go research transactions for sql
heres a link you can use to start.
http://dev.mysql.com/books/mysqlpress/mysql-tutorial/ch10.html
heres a link you can use to start.
http://dev.mysql.com/books/mysqlpress/mysql-tutorial/ch10.html
Quote:Original post by BelzemusQuote:That depends on how you convert it to SQL.
Most likely you'd simply have an Item table containing columns for all the generic item attributes.
If you need some kind of special properties that only a few items will have, you can put those in a separate table and make that reference an item ID
I thought about that, but looking at the classes, I'd say there would be quite a large amount of extra tables. The base tables arent really concerning, its more all the extra data.
What's the problem? If you have data you need to save, then you need to save it. The number of tables you use isn't an issue. It's really about what is practical for you and what meets your design goals.
Quote:What do you mean by block all updates that go together?
Hopefully you've read the link above, but it just basically means that instead of making the calls separate, you start off by beginning a transaction, then do the calls, then end the transaction. The database ensures that the whole lot either goes through as a whole, or doesn't go through at all.
1) Fork() is bad solution of data size is more then 20Mb. Fork system calls and system "freeze" for those process (if server uses threads).
2) You can run save thread, game will be still run. Good solution if you set "SAVED" flag for each "saved" object. Before saving, clear flags and and then item has been saved set flag. Try logical isolate "saving object" for example: save chars and all items linked to chars, NPC and all items linked to NPCs, and all unmarked items. You can improve technology: then items has changed owner (from ONWER1 to OWNER2), when we starting save items linked to OWNER2, current items has been marked as "SAVED". Check and update OWNER1 "saving buffer" for all items that has been changed owner to OWNER2. (I'm using this technology for my game server).
3) And last: DB is slowly. I dump update to file, and this file dump to DB.
2) You can run save thread, game will be still run. Good solution if you set "SAVED" flag for each "saved" object. Before saving, clear flags and and then item has been saved set flag. Try logical isolate "saving object" for example: save chars and all items linked to chars, NPC and all items linked to NPCs, and all unmarked items. You can improve technology: then items has changed owner (from ONWER1 to OWNER2), when we starting save items linked to OWNER2, current items has been marked as "SAVED". Check and update OWNER1 "saving buffer" for all items that has been changed owner to OWNER2. (I'm using this technology for my game server).
3) And last: DB is slowly. I dump update to file, and this file dump to DB.
Quote:Original post by Ramsess
1) Fork() is bad solution of data size is more then 20Mb. Fork system calls and system "freeze" for those process (if server uses threads).
2) You can run save thread, game will be still run. Good solution if you set "SAVED" flag for each "saved" object. Before saving, clear flags and and then item has been saved set flag. Try logical isolate "saving object" for example: save chars and all items linked to chars, NPC and all items linked to NPCs, and all unmarked items. You can improve technology: then items has changed owner (from ONWER1 to OWNER2), when we starting save items linked to OWNER2, current items has been marked as "SAVED". Check and update OWNER1 "saving buffer" for all items that has been changed owner to OWNER2. (I'm using this technology for my game server).
3) And last: DB is slowly. I dump update to file, and this file dump to DB.
I think that you have over complicated this... I could not see dumping to a file and then to a DB to be faster and more robust in this case... Second I think the best bet for the DB related stuff is to have a table full of characters each with there own GUID as well as NPCs and then track based on the GUID... Something like CHARA owns said item... that item is associated with that characters GUID. When i pass the item from CHARA to CHARB i just change the GUID of the item to its new owner in the DB. now sure its a little more complicated than that but really thats the basic idea behind some of the more popular MMO's.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
