Thoughts on database management in MMORPGs such as Guild Wars

Following up on my three prior blog posts, and the discussion thread here around them, I have added some new thoughts on how database technology could enhance the Guild Wars experience.

Excerpt:

Rather, where I think the Guild Wars developers at ArenaNet may be greatly missing out is in the areas of business intelligence, data mining, and associated game control. Here are some examples of analyses they surely would find it helpful to do.

From http://www.dbms2.com/2007/06/12/thou...playing-games/

Thanks to those who posted in the other thread, especially GranDeWun and ArcaneMacabre.

Not necessarily.

All it means that rather than converting game objects into SQL representation, they stored serialized data.

The difference is in that in order to access this data, they need to write code, rather than use SQL.

The non-SQL aproach is very common in games, since several queries per player can mean an unmanagable performance hit. And any kind of ORM is almost always out of question, unless hardware and administrative teams are readily available.

There's a lot of compromises to be made. The Blob aproach also seems somewhat redundant, they could just drop the each player into a file.

Also, seeing as Anet had a job posting for data mining analyst, I'd conclude that gameplay activities are logged - just not in the same database. It seems somewhat unimaginable that they'd have the game running for 2+ years, and not have a single record of any activity that was going on.

The examples you list, for example, are all those unrelated to object store, but are only created during gameplay (it's unrelated to the parts discussed in the articles). So I wouldn't go as far as to say they have no clue what's going on in the game.

Good point.

What kind of research or experience are you speaking from?

The question is how MUCH they have logged. And there are marketing databases as well as game-record ones.

Indeed, the statistical analysis can be harder if the data is weaker.

Only about half-true. Look again.

A conversation with another experienced DB persion:

Me: So Guild Wars stores character data as a blob, wow that's dumb.
Him: How did you think they were going to do it? It's common to put them in flat files for games.
Me: No, they put the blob in the DB.
Him: Wow, that **is** dumb.

I guess now we know why it takes so long to get retroactive changes to existing equipment. Preserving data integrity through foreign keys is a lost concept to them.

I haven't read any of the blogs or discussion threads but I was under the impression that the decision to run with the current system was based on having op costs at a level consistent with the fee structure rather than more expensive popular database alternatives. Low costs don't come without sacrifices and I'm sure these were weighed accordingly.

My knowledge of databases is limited to an undergrad computer science education I left behind years ago, but the examples from the excerpt are basic and obvious questions raised during the preliminary stages of design and planning. Certainly fundamental issues like these were addressed fairly early? Regardless, since Anet received the financial backing necessary to launch GW that means they were able to successfully defend their choice to those with much more expertise (and money) than myself.

Do you believe they could have pulled off the GW experiment with an option less profitable for investors?

I've worked on a trading system that stored its results as BLOBS, and reporting was a total nightmare. Every report was a program that had to de-BLOB the entire set and run the query. No ad-hoc reports were possible, because someone had to code every one.

By contrast, if you had a true relational database, you could idly run queries to find out things like: "How many Mo/Ws are currently in area x?" "How many 100 plat cash trades took place last week where no item was traded".

Basically, all the db capabilities are lost...

And by the way...Sneaky is not following the argument. You don't 'send something' as a BLOB, that IS the way it is stored. It is like you stuck all the columns of a spreadsheet in the first column. And serialization is another topic completely.

The blob approach also explains why it's not possible to rollback individual players accounts or recover them if deleted. It may also be why things such as hairdressers/apperance changers are so difficult to implement.

I'm sure it has some advantages e.g security but it does seem very limiting in the above aspects. I hope they will revisit the design for GW2 to allow a more flexible design that has increased data integrity and yet the same or greater data security.

As to other games, I'm pretty sure the EVE developers have a fully relational database, IIRC the discussions on that forum. Maybe data warehousing as well. And I don't buy the claim that SQL queries from players are a performance problem per se; after all you could always do one big query at the beginning which would be just like retrieving the BLOB.

I would be interested to see how many games started with a DBA and a good data model versus using the database as just storage. I've seen a lot of web-based apps that ignored the database until they were way down the road, with many painful issues later.

I look forward to further research, but at the moment my firm knowledge as to what non-GW games do for database management is sparse, and indeed pretty well limited to:

1. The GW guys previously worked on WOW, and I got the impression from talking with them the WOW has a straightforward RDBMS approach.

2. Everquest is in the process of being ported from Oracle to the the Oracle-plug-compatible products from EnterpriseDB.

My other question would be to what extent stored procedures are used, which in my experience make all the difference in performance. To illustrate in a simple way, pulling 1000 rows from the database to identify the ten you want (not uncommonly done) , is infinitely worse that running a proc to do it on the dbase side and returning just the ten.

This so far as I can tell, is not easily feasible with the current system.

Note: This is from a client perspective.

The current profession and hair style are stored in a single byte. This will be looked at in hexadecimal(base 16, 0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f) The minimum value of a byte is 0x00 and the maximum is 0xFF. For a female Ranger with the first hair style, the byte would be 0x21. Changing it to 0x31 would change the character to a Monk and using the female Monk's first hair style. So the first half controls the profession and the second half controls what hair style. This effectively limits the max number of professions to 15 (profession 0 does not exist), and the max number of hairstyles for a profession to 16. Ever wonder why those selection boxes are 4X4?

The way the game accommodates far more than sixteen hair styles is by making the hair style number relative to the character's campaign, profession, and sex.

Campaign(Prophecies, Factions, Nightfall, etc.) -> Profession(Warrior,Ranger,Monk,etc.) -> Sex(male, female) -> max 16 faces/hair styles.

Now an appearance/hair stylist would have to stay within the bounds of the faces available to that particular campaign->profession->sex. This should be easily possible as evidenced by the April Fools joke. That was likely executed by modifying the byte that controls the height and sex. 0xA1 would make the character 10(A) units tall and be female. I've found that an odd value is female and male is even. For the joke all they had to do was change an odd value to even and vice-versa.

I think the greater problem with hairdressers/appearance changers would be the demand the community would have for cross campaign/profession hair styles and faces. Which is not possible without adding more bytes. With character data being stored as a BLOB it would require changing the code that interprets this data which is probably quite a lot.

You could do it by adding 2 bits to the stored BLOB to represent a 'campaign shift', which would allow you to say appearance is a function of shifted campaign, profession, sex, 1-16. 2 bits to handle a shift of 0,1,2 campaigns. Client code would change slightly as well, of course.