I''m planning on doing a multiplayer gpg and thought I''d store the gameworld in a SQL database. The gameworld should not have to be on the same machine that the the server is running on. Is should also be possible to run several servers over the same database. Quesion: Why should/shouldn''t I use SQL for this? /mad.nano

Answer: in my opinion, you shouldn''t use a relational database as these are optimised for batch operations. They are set up to alter/retrieve large numbers of records at a time given a basic set of criteria. This is -not- what most games will ever need to do. Most games will instead have a lot of single operations, all different. So you will end up doing a lot of "SELECT blah FROM blah WHERE UniqueID = x" which is going to just pull out one record from the whole lot... something you could do far faster using a pointer in C++. It gets even worse when you start adding joins and other tables into the equation. Perhaps the best case is when you truly want to do a ''batch'' operation, example, to select a character''s inventory...

"SELECT * FROM Object WHERE carriedBy = " + currentCharacter

Now, that is the kind of thing relational databases are made for... but it''s still not going to be any quicker than storing a list of pointers to objects directly with the character. Even a perfectly indexed table that managed to only check the relevant objects and return them would still not beat a list stored with each character, which will always only hold relevant objects.

Essentially, I think the relational database model, optimised to minimise redundancy and storage requirements, is a pretty poor choice when it comes to the efficiency of individual operations.

Oh well...
The other solution I had in mind is to have a master-server with the DB, then have sub-servers work against the master.
The cients would communicate with the sub-servers, and the the sub-servers in turn with the master.


/mad.nano

There are a lot of persistent-world things that seem a shoe-in for storing in a database -- player data, etc. Ultimately, though, you are going to be using the database for just that -- persisting data. When a char logs in you would read their char info and periodically update it as the game progresses, but you''ve got to keep your working set in memory. Kylotan is right, turing every inventory manipulation into a database query would kill you. However that isn''t the only option. A database basically takes the place of storing game state info in physical files. It also makes for an extremely simple way to plug in new stuff later or dynamically modify state information without any code changes. Done properly, it shouldn''t be hard to manage monthly updates (like in Asheron''s Call) without even bringing down the game servers.

Beyond that, you''ve got two options that really depend on a bunch of factors -- relational or object database. Object databases are nifty because you can just read or write C++/Java classes directly to the database while with relational databases you have to do a bit of translation. At the same time, relational databases are a lot easier to work with and more flexible in how you can relate and manipulate data. From personal experience, object databases tend to work extremely well as a middle-tier between the application and a relational database. Their caching schemes can be very effective and the application gets its classes back directly with no object construction costs (kind of like CORBA). In the background, the object database can synch itself with the relational. The problem is that the ton of added complexity may outweigh any performance benefits. Given a chance, it might be a cool avenue to pursue.

One final advantage of databases (IMO) is that it makes it much easier to keep a multi-machine world in synch -- every box is working off the same physical data. With the proper rules in place it might even help prevent cheating (duping and whatnot). Data doesn''t become corrupted, the database enforces business rules (game rules), and its transactional nature gurantees that you can absolutely hammer the box and everyone''s task will complete in a predictable manner. Okay that all may have sounded a bit idealistic, but all-in-all I beleive it to be true.

Personally I don''t buy the performance argument. Databases perform as well as you design them to. At the job I''m at now, I was asked to make some modifications to this system that was absurdly over-complex. After an assessment I decided it would make more sense (and take less time, in the long run) just to redesign the entire thing from scratch. I did this and when all was said and done, operations that had previously taken over 4 hours to complete were finishing in under 30 seconds. This may be a fairly extreme example, but it illustrates my point: like any other aspect of programming, it all comes down to implementation.

If that isn''t enough... I had a job where I was working with a high-volume telephony system (for a switched LD carrier) that stored all of its data (including some very complex scripts and custom applications that were processed on the fly) on a cluster of SQL servers. The database comprised maybe 200 tables and 10 gigs of data (admittedly not large by corporate standards) and 24/7 served a few racks of PCs simultaneously processing thousands of realtime phone sessions which all interacted heavily with the database. Compared to the relatively low standards of quality that online games have so far been held to I think this makes a decent case for the use of databases as a plausible solution for storing game data.

As with so many other aspects of programming: why reinvent the wheel? Databases have been in development for decades by people with PhD''s and are specifically designed for this kind of thing. I personally doubt that I can come up with a better method and code it in a few months with a handful of other geeks. Sure, enterprise-level databases can be costly but game budgets have gotten quite considerable as well.

Gee... Now what?

But what if you have a system-crash. You will lose your ram based db, would you not? Think of the amount of changes thounsands of players would have made between the db-backups.

quote: Original post by kressilac

We also understand that writing a MMORPG requires an incredible amount of server performance. So much so, that I do not believe we could achieve the performance by keeping all data in the database and not in memory. Remember if you keep part of it in memory and part in the database, you run into two-phase commit problems and potential data loss problems if not addressed for the data in memory. Our plan is to implement a server wide two phase commit strategy between our database and the game server. The game will read every ounce of data from our database into RAM when the game boots. (Our servers are 8Gb RAM servers running Win2K. Actual production ones may go as high as 16Gb if needed.) From there the game will periodically save using flat files. At a smaller interval(say every half hour) a process will execute that will write the flat file data out to the database for backup and analysis. Flat files in this case can be used much more effectively than a database could ever be, because you can maintain file pointers that go directly to the data you are writing and reading without the burden of SQL.

We believe that by using this approach we can ensure the performance needs of the server, and ensure the analysis needs of the world management team and world building team.

I''ve though about this and I would do something a bit similar, but likely without the use of files. Basically, every time an important state-change occurs (like when a player gives another player an item) the transaction is stuck on a queue. It would be the job of another thread or process to just sit on the queue and process each transaction into the database. Now say a crash occurs. Chances are the queue isn''t empty. But it really doesn''t matter because if the database is designed correctly no duping occurs. When the server comes back up, some of the transactions simply never occurred.

I''m doing a lot of distributed database stuff right now and my queries blow through in milliseconds and the CPU load 9from the SQL process) is pratcically nil (since the SQL server is the thing doing all the work). There are actually a few tricks to making SQL an effective realtime system. Besides all the usual tweaking, all your operations should be stored procedures. This provides a HUGE benefit over normal queries, as stored procedures are precompiled.