MMOPRG database

We, and a lot of others, run MMO systems with MySQL. I also know of people that use ORACLE, SQL Server, and PostgreSQL. With the right data federation and update strategy, you can probably also use SQLite.

There are also people who prefer the performance of an ODBMS for back-end storage, and can live with the fact that ad-hoc queries aren't as well supported in such a system. Try something like Versant or Objectivity or Matisse.

I would recommend against an object-action DB though.

Most of your performance will come from de-coupling object updates, and not keeping absolute truth of frequently-changing fields (hitpoints, position, etc) in the database.

Yes, I do agree that MySQL should be good enough, that's what we used. You'll get performance issues I'd say when you start scaling up if you're not too familiar with DB's. Our first problems appeared toward 200 playing users and then 400 but I read a very good book on db performance optimization and that got us going again.

Mainly like hplus said you have to accept a certain difference between the state of your DB and the state of the world on the server. Don't save unnecessary data, don't make too frequent update etc. You'll lose data in case of a server crash of course but our strategy was to have "full" world state backups every 15 minutes so it's not too bad. If it's an event that really, really matters you can make a write to the DB but that's it.

Note: Most of the issues popped up because of that dump every 15 minutes, it was a pretty bad idea but we were stuck with it. As the number of player grew, the time to make that save grew and well... I guess you can see where this is going?

Incidentally, what are people's opinions on reading data? Is it considered necessary to make these asynchronous in some, most, or all cases? Are there are general approaches to caching being used? What about general purpose caches like memcached - are these only generally suitable for high latency situations like web ites?

I think it was Dark Age of Camelot that basically just used a database to store a BLOB of player data much like saving it to a file. :) Not exactly using it entirely as intended, but it worked well enough for them.

Games I have worked with varyed from using XML files per account to using SQL servers for the whole thing. As they've said it is infeasable to save to your database everytime something changes, the hardware simply cannot support this for a lot of people. One thing I have seen is make a timer for everyone, so that every five, fifteen or thirty minutes the player is saved, this is only for the player so the whole sevrer will not save at once. With larger systems this can lead to lag and you might want to spread this out even further to hours. I belive there are also some games that save only once a day.. but I cant recommend that.
Now, I personaly like saving on a account bases becouse I feel it allows the server to make querys faster, but I really havn't given it much thought and am probly wrong..

Quote:

Original post by Kylotan I think it was Dark Age of Camelot that basically just used a database to store a BLOB of player data much like saving it to a file. :) Not exactly using it entirely as intended, but it worked well enough for them.

BLOBs are fine, but they limit usability of database for administration. They do however make certain things simpler.

One approach that wasn't mentioned is deltas. Rather than saving entire player every x minutes, the delta system will usually exist in most MMO-like servers.

This way, just monitor the deltas as they are generated, and cache them. Then, every n seconds, or every m unique deltas, transact them to database. This will minimize the number of database queries needed, and is cache friendly. Data that is changed this way will either change once, or very frequently. The dual flush criteria covers both cases. It can even be used for frequently updated fields, since even if an int value changes 100 times, it'll count as single query.

Downside of this approach is error handling. What happens if some query fails? If database crashes. One can either run for n more intervals, then shutdown the server, or attempt some complex error recovery.


Perhaps a more important downside is that not all data can be handled this way. Some operations really need to be atomic. One solution is splitting data in two tables, one that contains simple properties, and one that requires more complex updates.

It also requires complete mutable state to be kept in memory.

Quote:

Original post by Kylotan I think it was Dark Age of Camelot that basically just used a database to store a BLOB of player data much like saving it to a file. :) Not exactly using it entirely as intended, but it worked well enough for them.

GuildWars uses a similar system. Here's an article on that:

http://www.dbms2.com/2007/06/09/the-database-technology-of-guild-wars/

There are different API-s for Mysql, like MySql++. Which one you guys reccomend?

Quote:

Original post by TiitHns There are different API-s for Mysql, like MySql++. Which one you guys reccomend?

I've heard good things about mysql++, it can offer some really advanced functionality if you want it to. Some might call it a bit bloated though, as it's a pretty big library.


On a similar topic, this article about designing rational databases (in SQL) for MMOG's might be useful: http://www.gamasutra.com/view/feature/2807/relational_database_guidelines_for_.php

Quote:

Original post by Valeranth Games I have worked with varyed from using XML files per account to using SQL servers for the whole thing. As they've said it is infeasable to save to your database everytime something changes, the hardware simply cannot support this for a lot of people.

That's not really true. There are plenty of websites which run DB updates on every single page hit. I'm not suggesting doing an UPDATE query every time a character moves a step, but I do think people are perhaps a little overcautious when it comes to the DB.

Quote:

There are plenty of websites which run DB updates on every single page hit.

An MMO will change multiple properties for each of 5,000 players 30 times a second. Trust me, 5 properties times 30 times a second times 5,000 players is 450,000 updates per second. You're going to have a hard time finding a SQL database that can provide that for you :-)

Regarding being "too cautious" with databases, the guys from Cryptic were giving an interesting talk about how they couldn't get the performance they wanted from SQL solutions, and in the end they ended up writing their own object database. They basically traded queryability (analysis queries had to be written in C) for performance. They also had to scale down the commit frequency depending on what property is changing. You can commit the player position only every 10 minutes or when the player changes zone, say, but commit the results of a trade at the time of the trade.

Quote:

Original post by hplus0603 the guys from Cryptic were giving an interesting talk about how they couldn't get the performance they wanted from SQL solutions

I wondered about that, too. If one stores a blob for each user (and nothing else), SQL actually is not a suitable tool.
Don't get me wrong, SQL is great, and it can certainly do that... but it does a *lot* of stuff that you don't need in this simple case, too.

I haven't tried, but it might for example be perfectly feasible to use one file per user on a ReiserFS. Since ReiserFS is based on B+trees, access times should scale comparably to a database, but there'd be no query parsing and no quoting/unquoting bin data. Of course the database server is normally a different physical machine, so one has to account for some NAS overhead (but then, the SQL database has network overhead, too).

Quote:

They basically traded queryability (analysis queries had to be written in C) for performance.

Did anyone successfully use BerkeleyDB (Sleepycat, now Oracle) for such a thing? My personal experience with Berkeley is a bit biased to negative, having once lost a Subversion repo to it in the past (and no backup, duh), but then you have no guarantee that any other database cannot possibly corrupt...

Quote:

Original post by hplus0603 An MMO will change multiple properties for each of 5,000 players 30 times a second. Trust me, 5 properties times 30 times a second times 5,000 players is 450,000 updates per second. You're going to have a hard time finding a SQL database that can provide that for you :-)

In our experience, the only properties that are changing that often are the location and orientation, and they doesn't need persisting that often. So the number of writes I have in mind is probably 30 to 300 times smaller than you've depicted. There are quite a few more reads, but that is what caching is for.

I just have the suspicion that a lot of game developers, being a bit new to databases, are ending up doing some ridiculous things when it comes to using them because they're a bit naive. I mean, one blob per player is such an example.

Quote:

Original post by Kylotan I just have the suspicion that a lot of game developers, being a bit new to databases, are ending up doing some ridiculous things when it comes to using them because they're a bit naive. I mean, one blob per player is such an example.

I'd agree with that. Although to be fair, many programmers of all types don't understand databases properly... you really don't want to let some programmer with even reasonable SQL skills design a DB for example.

DBs do get accused of being slow, and programmers often try far to hard to avoid making queries by caching everything in the program, but these days DBs are simply incredible.

Also, if you're running a big MMO, it's not fair to say you can't get good performance from DBs if you rely on MySQL. A serious setup with multiple game servers probably needs a dedicated DB server running SQL Server/Oracle/Sybase or even DB2.

Quote:

Original post by samoth I haven't tried, but it might for example be perfectly feasible to use one file per user on a ReiserFS. Since ReiserFS is based on B+trees, access times should scale comparably to a database, but there'd be no query parsing and no quoting/unquoting bin data. Of course the database server is normally a different physical machine, so one has to account for some NAS overhead (but then, the SQL database has network overhead, too).

The problem with blobs is that you cannot do incremental or partial updates. Let's say that something happens in your game, which changes 1 property on thousand objects.

While with proper database structure you still need to issue 1000 queries, with blobs you need to write entire object state, vs. possibly only a bool in case of structured storage.

If nothing else, the difference in network traffic is considerable.

Guild Wars uses BLOBs, but they rare access the DB, they seem to access the data exclusively through cache servers, which are responsible for committing the data as well. But that is better suited for multiplayer-like games, where characters are stored only under limited circumstances, such as when changing the map.


For general object storage, I've found that splitting between scene-graph table and object property table works fairly well. Changes in ownership need to be transacted anyway (trades), so putting that into a light-weight parent/child table makes sense.

Everything else can then be on a more complex table.

Quote:

the only properties that are changing that often are the location and orientation, and they doesn't need persisting that often

Well, you said "don't worry about it" and "just write everything" -- I pointed out that that's not a good starting point.

For Cryptic, they also had things like hitpoints, experience points, and even skill cool-down status that changed all the time. If you've played CoH, it really is fast paced, and you get new experience points all the time. There is a desire to commit each change in exp, because it's a "significant" event, but for all the players in a single shard, they couldn't get SQL to keep up with that.

Now, I asked them, and they weren't federating their data -- they preferred to move away from SQL and make a single database serve their current needs, rather than verticalize their data and apply more than one database to the problem, apparently mostly because they weren't as familiar with data federation.

Quote:

Original post by hplus0603 Quote: the only properties that are changing that often are the location and orientation, and they doesn't need persisting that often Well, you said "don't worry about it" and "just write everything" -- I pointed out that that's not a good starting point.

I certainly didn't say "just write everything"! I said "I'm not suggesting doing an UPDATE query every time a character moves a step". For your typical RPG-like game I would think it could handle updating the important data for 5000 players without trouble. The throughput would be fine; I'd just be concerned about latency.

Quote:

If you've played CoH, it really is fast paced, and you get new experience points all the time. There is a desire to commit each change in exp, because it's a "significant" event, but for all the players in a single shard, they couldn't get SQL to keep up with that.

That's fair enough. I think it's an isolated use case though. And are they committing so frequently for fear of data loss? Or committing because that is necessary for the way they do subsequent reads?

In the general case, I'm still interested in what people think about database usage, regarding blocking reads/writes, what caching is done, what sort of performance people get out of typical RDBMSs, etc.

Quote:

Original post by hplus0603 For Cryptic, they also had things like hitpoints, experience points, and even skill cool-down status that changed all the time. If you've played CoH, it really is fast paced, and you get new experience points all the time. There is a desire to commit each change in exp, because it's a "significant" event, but for all the players in a single shard, they couldn't get SQL to keep up with that.

Sounds like a lack of understanding of DB use or to much fear of a server crash. In a fast paced game like that you should load the character at login and store it back to the db at logoff, and a dump somewhere else for data loss concerns. if getting exp happens more than once a min. it isn't significant enough to hit the db by a long shot. Now to lower overhead per player you might try off loading part of their inventory to the db and loading when they open it, but mostly you would make heavy use of the fly weight pattern to reduce ram per character.

Quote:

Original post by stonemetal Sounds like a lack of understanding of DB use or to much fear of a server crash. In a fast paced game like that you should load the character at login and store it back to the db at logoff, and a dump somewhere else for data loss concerns. if getting exp happens more than once a min. it isn't significant enough to hit the db by a long shot. Now to lower overhead per player you might try off loading part of their inventory to the db and loading when they open it, but mostly you would make heavy use of the fly weight pattern to reduce ram per character.

Here's the problem. You have 5 health potions. You consume one potion, and it restores 250 health. Potion count is committed to database, but your health isn't (frequent updates). Then server crashes.

When it's back, player has gained no health, but is missing one potion.

Better yet. You use the potion on another player. Complicates things even more with these tests.

Or, you fire 500 bullets (frequent update) at a player on another zone. Then, your zone crashes. When it's back, the other player is dead, killed by 500 (now) duplicate bullets, yet you still have them in inventory.

Quote:

Original post by Antheus Here's the problem. You have 5 health potions. You consume one potion, and it restores 250 health. Potion count is committed to database, but your health isn't (frequent updates). Then server crashes. When it's back, player has gained no health, but is missing one potion. Better yet. You use the potion on another player. Complicates things even more with these tests. Or, you fire 500 bullets (frequent update) at a player on another zone. Then, your zone crashes. When it's back, the other player is dead, killed by 500 (now) duplicate bullets, yet you still have them in inventory.

You roll back to the last crash save. inventory and health are consistent. A few min of game play are lost. shots across server boundaries either don't happen or the crash save is charged with keeping cross zone consistency.