Double Buffered

Update: Some of the thoughts here were elaborated on at GDC 08.

SQL databases suck for being the back end of large MMORPG’s. That statement probably either makes no sense to you, or sounds wrong and counter intuitive. If it doesn’t mean anything to you, you can safely skip the rest of this entry. For everyone else (all 2 of you) I’ll explain. The first thing is that for most programmers, SQL database are the only type they’ve ever used. Oracle, DB2, and MySQL are all SQL databases, and are equivalent for our purposes. Because SQL databases are so ubiquitous, most programmers haven’t heard of Object Databases. In my opinion, object databases are generally more suited to MMORPG development.

There are two basic reasons that SQL sucks: data mapping and latency. Data mapping (also known as Impedance Mismatch) refers to the fact that the structure of data in an SQL database differs very heavily from the structure of C++ game data. SQL requires all data to be placed in simple tables, which is perfect for bank account data. However, game data is very structured and hierarchical. To represent the concept of “A player has a set of powers” in SQL involves creating two separate data tables, setting up foreign key constraints, and manually writing code to combine the data from the two tables for use by C++. To represent this in an object database, you just set this up in your C++ definition, and things work. This mapping is confusing, complicated, and slow. Unless your entire development team is fluent in SQL, you’re going to have to write some layer in between. And those layers inevitably make latency worse.

SQL database latency problems are felt by end users. If you were playing WoW about a year ago and waited 5 seconds to pick up loot, that was SQL lag in action. The problem is that SQL databases are designed for high throughput, not low latency. When you reach a certain number of transactions per second, performance break down. There are two basic solutions to this problem: buy ridiculously expensive hardware, or implement a caching layer. Eve Online helped their performance problems by buying $100,000 solid-state disk drives. That’s not always an option, so caching can be used instead. Basically, this works by doing all of the actual transactions in a caching layer between the database and your game. When doing it this way, the SQL database is used as a glorified hard disk, and you lose most of the safety and reliability of SQL in the process. Even worse, eventually you’ll hit the same performance problems as before. Basically, you want reliable, fast, and easy. With SQL you get to pick one.

So, why are object databases better? Basically, it’s because they structure data the same way as the rest of your code. There is no conversion between the database schema and the C++ class definition because they’re the same thing. Concepts like inheritance and substructures are automatically handled, instead of requiring extensive database hackery. And, because the structure is so close to the C structure, it is much faster to load things to and from disk. You get fast, easy, and reliable.

This site has a bunch of information on object databases, for the interested. There are several commercial vendors of Object Databases that would be happy to sell your their product, or if you can write your own if you’re up to it. I know Versant has a game-focused version of their object database for sale, but I haven’t evaluated it. I’m not going to endorse a specific product, but if you happen to be starting up development on a major MMORPG (or anything with complicated data and tight latency requirements), give object databases a look. Or, just enjoy knowing that you’re not the only person who thinks that SQL sucks.