mmo database design
Author
has anyone ever published a book or even an article on this. I'd like to see a good database template and tips and hints on performance as well as niptucking for different apps like Oracle and MSSQL
the database design for any well designed MMO will obey the same rules and science of any other kind of database design. You should pick up one or many books on database design. The rules of how databases work and how to optimize them don't change just because it's MMO data that's being stored. The best place to start is just understanding how databases work and what their bottlenecks are. Any good bookstore will likely have ~50 books on database programming.
-me
-me
I've been wondering about this as well, mainly because I'm not sure what role a relational database fills when it comes to MMOs. I think most people just like the idea of having a relational database so they get to create interfaces for mapping from their own internal structure to the DB. This provides hours of entertainment for the otherwise bored developer.
http://www.gamasutra.com/resource_guide/20030916/lee_01.shtml and http://www.gamasutra.com/resource_guide/20040920/lee_01.shtml
A good database provides durable, ordered transactional update to your data, so you can make sure that your on-disk world state is always consistent. Whether it's relational or not doesn't really make much difference in this context, but all the good databases are anyway, and there's tons of documentation on SQL out there, so you might as well pick an RDBMS.
I'd recommend looking into the free (as in beer) systems like MySQL and Postgres as serious contenders. Oracle can be insanely expensive, and Microsoft... well, they are a single-vendor, single-platform solution, and it shows ;-)
Regarding the actual database design, it depends on what you're doing. If you're building a world with N zone servers, each of which talks to a single database instance, then minimizing expensive query and update traffic is probably most important -- if every player generates a query per second, and you have 10,000 players connected, you do the math ;-)
I'd recommend looking into the free (as in beer) systems like MySQL and Postgres as serious contenders. Oracle can be insanely expensive, and Microsoft... well, they are a single-vendor, single-platform solution, and it shows ;-)
Regarding the actual database design, it depends on what you're doing. If you're building a world with N zone servers, each of which talks to a single database instance, then minimizing expensive query and update traffic is probably most important -- if every player generates a query per second, and you have 10,000 players connected, you do the math ;-)
Depend a lot of query to the database would be a problem only if you constanly keep the data in your database in synch with what's happening in the game.
I believe a lot of mmorpg don't do that, rathery they do cyclic saves of the game world data and players. I can't speak for all of them since I haven't played all of them but most of those I played I could tell they worked this way. The mmorpg I maintain does this too. That's why when a server goes down, you often have a rollback of a couple of minutes, back to the last save cycle.
I believe a lot of mmorpg don't do that, rathery they do cyclic saves of the game world data and players. I can't speak for all of them since I haven't played all of them but most of those I played I could tell they worked this way. The mmorpg I maintain does this too. That's why when a server goes down, you often have a rollback of a couple of minutes, back to the last save cycle.
There's a thread on the Vanguard forums regarding databases in MMO's. Vanguard is an up and comming MMORPG whose company is founded by Brad McQuaid one of the original developers of Everquest.
http://www.vanguardsoh.com/forums/showthread.php?threadid=7098
A post by one of the Dev's in the thread:
"Regardless of what database software package you choose your schema design will have a much bigger impact on performance than the database software or the hardware its running on, especially with an mmog. The reason I say "especially with an mmog" is building an mmog schema design is pretty much completely different than anything else. You break a lot of rules building a database for an mmog. That's what makes it fun"
-=[ Megahertz ]=-
http://www.vanguardsoh.com/forums/showthread.php?threadid=7098
A post by one of the Dev's in the thread:
"Regardless of what database software package you choose your schema design will have a much bigger impact on performance than the database software or the hardware its running on, especially with an mmog. The reason I say "especially with an mmog" is building an mmog schema design is pretty much completely different than anything else. You break a lot of rules building a database for an mmog. That's what makes it fun"
-=[ Megahertz ]=-
As dark rain said earlier, most MMO's use a cycle of saving. Those that do not tend to have problematic situations. For example. World of Warcraft uses database directly for storage and retrieving of information. Talk to any world of warcraft player and you will have a story of problems caused by it. From inability to even log out of the game down to being unable to pick up an item off a body. The database gets hit with too many queries to handle. Blizzard's position on that is to throw bigger computers at the problem. There's nothing wrong with having everything in ram on a server then periodically dumping to storage mediums (like databases). After all, Ram is much faster ;)
Actually, there is something wrong with just keeping all data in RAM and dumping it every so often. What happens when the server crashes? (and it will crash)
If ALL the data for an entire world instance is in RAM on that single machine, then time will roll back to the last atomic check-point, which might be OK.
If there's more than one server on the system, and only one crashes, then the other servers will have data that's newer than the machine that crashed. All kinds of badness can happen because of this -- from simple duping bugs, to subtle data corruption that etches away at your data store, until there's nothing left.
If ALL the data for an entire world instance is in RAM on that single machine, then time will roll back to the last atomic check-point, which might be OK.
If there's more than one server on the system, and only one crashes, then the other servers will have data that's newer than the machine that crashed. All kinds of badness can happen because of this -- from simple duping bugs, to subtle data corruption that etches away at your data store, until there's nothing left.
Yes well that's a trade off I guess. It's true that all the duping bugs i've heard of in a mmog are related to doing backups this way.
We had a data corruption bug exactly like you mentionned. When we restarted the server, we had a cascade effect that just erased all the data for player teams and then the server went into an unstable state and crashed. Fun. We saved the day by using another backup.
On the other hand it *is* easier to implement this model than keeping the game state synched with the DB at all time. I won't pretend I begin to be a good enough sql developper and oracle/mysql db admin to know how much is involved but I'm guessing it's not a piece of cake from what I know.
If you're planning to make an indy mmog this is a good strategy IMHO. When we have to bring the server back up after a crash it's at the maximum a 20 minutes rollback but it's often something in between. Of course when we do bring the server down ourselves there's no rollback.
We had a data corruption bug exactly like you mentionned. When we restarted the server, we had a cascade effect that just erased all the data for player teams and then the server went into an unstable state and crashed. Fun. We saved the day by using another backup.
On the other hand it *is* easier to implement this model than keeping the game state synched with the DB at all time. I won't pretend I begin to be a good enough sql developper and oracle/mysql db admin to know how much is involved but I'm guessing it's not a piece of cake from what I know.
If you're planning to make an indy mmog this is a good strategy IMHO. When we have to bring the server back up after a crash it's at the maximum a 20 minutes rollback but it's often something in between. Of course when we do bring the server down ourselves there's no rollback.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
