# CACHe / MsSQL Storage...

This topic is 3473 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

Hello, I was wondering how you all feel about MS SQL storage. I love bringing up topics on MMORPGs and such (As many may know :P ). Do major MMOs even store at all during updates or do they store it all in CACHe then update at the server downtime. Example, a player log-in, he do some stuff, then logs out. We save. A player logs in, we have to shut down or we get a critical error. What ever is in memory gets massive saved at that time. Do we actually update during the gameplay while a player is logged in? Of course this may be in an issue in the event of a crash, but lets assume its 100% stable.

##### Share on other sites
There are MMOs that work with MS SQL server, so it can be done.

Typically, you load data when the user logs in, commit data when the user logs out, and then also commit various pieces of data at various important times -- say, when the user loots items, or trades with other users, or moves between zones.

Many games also checkpoint each user every so often, to limit the amount of time that could be lost should the server crash.

The Cryptic people gave a good session at GDC on moving to an object-based database for their MMOs, to get faster commits and more consistent checkpoints. They rolled their own, although there are other object databases you can buy, too.

##### Share on other sites
also look at non-sql alternatives - they are usually faster, because they don't have the SQL parsing/executing overhead, but you have to invest more time because you have to write all the SQL queries in your own code.

berkeleydb comes to mind, tokyo cabinet or hamsterdb (spam: i'm the author).

##### Share on other sites
Quote:
 Original post by cruppstahlyou have to write all the SQL queries in your own code.
That sounds like a piece of terrible software design. Unless you've good figures to suggest a normal database (like what they use to power the world's banks) isn't fast enough, I'd stick with that. Any mainstream DB - Oracle, SQLServer, MySQL, etc - is an immensely clever piece of software.

##### Share on other sites
Quote:
 Original post by cruppstahlalso look at non-sql alternatives - they are usually faster, because they don't have the SQL parsing/executing overhead, but you have to invest more time because you have to write all the SQL queries in your own code.

MySQL has prepared statements. I've been using it quite a bit through the C API the past few days, and one thing it has made me realize is just how fast the query parsing is.

Prepared statements allow you to create a query like:

"INSERT INTO user SET name=?,pass=?,exp=?"

etc. Where each ? is, you can set it later with a MYSQL_BIND for each parameter. Since you are assigning the value through a pointer, not the actual value, you don't even have to bind the parameters every time. You could, if you wish, create every MYSQL_BIND set just once then set it later right before using it.

In case you are wondering, no, they are not that bad to use. In the case of blobs and strings, they are actually easier since you specify the length of the data to insert, not using a null-terminated string.

I have also read that prepared statements are becoming more widely available outside of the C API, so if it is really a problem (like languages where all the strings result in a lot of garbage), I suggest checking it out. But I emphasize the point that MySQL's query parser is insanely fast.

##### Share on other sites
Quote:
 Any mainstream DB - Oracle, SQLServer, MySQL, etc - is an immensely clever piece of software.

First, the cost of the databases that the large banks are running is a lot higher than you will want for an MMO -- even a successful one.
Second, the mainstream banks actually don't have the latency nor the transaction volume that a large MMO does, if you count all item use, combat hit, tick heal etc as a transaction.
Third, as I said, the Cryptic people (City of Heroes, etc) gave an interesting talk about why they chose to move away from MySQL to an object database. They wrote their own, but I'm sure Versant or someone like them wouldn't mind selling one to you :-)
Writing an MMO that doesn't cost as much to run as the NASDAQ (and gives combat results faster than NASDAQ trade executes) is a delicate balancing act between consistency and responsiveness, usually executed through a combination of technology and game design. Saying "any large database should suffice" without any other qualifications (like, what kind of filtering the front end is doing) shows a surprising lack of interest in the specifics of the problem domain.

Well I don't really care how people are writing their front ends or how they use the DB. The simple fact of the matter is that relational DBs are THE tried and tested solution for all kinds of server-client applications, including many MMOs. So while there may be alternatives that are better performing, I don't see the need to use something that far fewer people understand. It would be better to invest the time making sure you know how to use the DB. All mainstream DBs are highly performant (including MySQL) and the result of centuries of man-work... any thought to WRITE your own alternative is likely to cost $100K+ and should be raising big "am I reinventing the wheel" questions. And I don't see such a big disparity between banking/mmo systems. You shouldn't design your MMO that it has to be doing DB reads every time you move around etc. It shouldn't normally matter if a DB operation takes quite a while to complete, as long as you can do many such operations in parallel. The cost of a banking system is of course more but they have to spend far more making it safe... if their DB goes down for 5min that's major money lost, even if they have a perfect backup. Whereas as long as WoW doesn't lose substantial data, the server being down isn't so crucial. Preventing that gets incredibly expensive very fast. #### Share this post ##### Link to post ##### Share on other sites Quote:  You shouldn't design your MMO that it has to be doing DB reads every time you move around etc. There's nothing wrong with requiring that of a database - but current SQL databases tend to be incapable of that. After all, the logic built on top does just that. But the deciding point should be functionality, not keywords. RDBMS databases tend to be ACID compliant, which solves a large number of potential problems. The question remains, does the "relational" aspect come in handy or not? ORM solutions are handy for developers, but clash with RDBMS fundamentals, requiring de-normalization. With popularity of more dynamic languages, the task of representing dynamic objects (Python, entity design) quickly becomes problematic. Sometimes, it is solved by using SQL solely for ACID, but storing blob per object. This trades in relations and maintainability for performance. There's also practical issues to consider. Assuming a flexible design, let each item stored in database be a tuple (ID-of-owner, value). This can be a very efficient run-time representation, but SQL table with hundreds of billions of such entires will quickly exhibit performance problems. Quote:  it has made me realize is just how fast the query parsing is. Whether or not to parse is more of a question of redundancy. For PHP and the likes, issuing queries directly is fine. But if dealing with compiled language, where data is stored in binary internally, converting to and from text representation is redundant. It basically adds type safety to DB queries. Quote:  I have also read that prepared statements are becoming more widely available outside of the C API Not only that, but sanitizing the DB queries through these and other meeans has been the norm for years, at least ever since little Bobby tables enrolled into school. At very least, every database application developer should consider this to be the norm. #### Share this post ##### Link to post ##### Share on other sites Quote:  You shouldn't design your MMO that it has to be doing DB reads every time you move around etc. Which is why the design of the front-end (by which I mean front-end to the database -- i e, middle tier, or "game server") matters. However, I have to ask you: have you shipped and supported a MMO game? The Cryptic guys have, and they had reasonable justification for going away from RDBMS. I also have, and while we're still on an RDBMS, I understand the justification for the Cryptic direction. If you really know what you're doing, picking an RDBMS over something like an ODBMS is not as clear-cut as you claim. And you don't have to invent your own; there are plenty to choose from in the market. In my mind, the question comes down to: do you need the ad-hoc query capability of your data? We do, so we stick with an RDBMS, but it does have a price (dollar cost). The ID/blob method is pretty poor, though, as it hides all data dependencies inside the blob, which means you have to have game code to understand the relations of your data. Sun Game Server does that, as does Multiverse, but I think that's a mistake. Even if you go with an ODBMS, you should make the DB understand the internal relations of your data. #### Share this post ##### Link to post ##### Share on other sites I don't see a problem with having the game server run updates for players at a given time. As long as you're not updating all the players at the same time it's probably not that big of a deal. (Update players 1-10 at time x then 11-20 at time y instead of all players at time x). Put it this way I manage the servers at my university that run postgresql (awesome DB especially since phppgadmin is better than phpmyadmin) and all printing is routed through smaller cups servers and to our main server which is just a little rack server. During midterms people are printing a ton not to mention there are real-time applications that query the database constantly for data (much like a game server would do). There's about 10 of these real-time programs across campus doing this and adding and removing rows from the database. Some of the tables get to be over a million rows. I think a huge part (and this was already mentioned) is how the game server interacts with the database. If it doesn't assault it with queries for stupid things it should be caching into RAM then you should be fine. If you choose to make your own DB then you have to think about how a webpage or other application might access the data. (Many MMORPG's allow users to look at their stats and inventory and such online). Honestly I'd run benchmarks on the system before jumping to any conclusion that it's too slow. //Edit... did I just compare managing printing to running an mmo server? [Edited by - Sirisian on August 20, 2008 10:20:09 AM] #### Share this post ##### Link to post ##### Share on other sites Quote:  Original post by SirisianDuring midterms people are printing a ton not to mention there are real-time applications that query the database constantly for data Just a little nitpick. Real-time doesn't mean really really fast. It means that system guarantees a deadline by which a task will complete. For example, consider sampling of notches on CD. The system needs to read data at exactly given rate (x GHz). If it's a bit late, or a bit early, the data will appear corrupt. Trivial system, one event, one period, yet it needs real-time guarantee. I remember problems just like that with old serial ports, where, if you couldn't process the signal on time, it was lost, and likely, incoming data became unreadable. If these databases were soft real-time, then as soon as the system would become overloaded, it would start dropping queries immediately. This is a major challenge in such systems, and MMO design. What to do once you exceed the available resources. Trivial solution is simply to crash the server, but any practical solution will need to account for this overload, and either re-balance resources, decrease fidelity, or release part of load (disconnect clients or something) - all while not violating the real-time constraint. The fact that scalable systems exists is just that - a fact. But few such systems have any kind of real-time demands. A 2.4 second delay while spooling a document isn't relevant or noticable. #### Share this post ##### Link to post ##### Share on other sites Quote:  . As long as you're not updating all the players at the same time it's probably not that big of a deal. Not updating all players at the same time can lead to the following problem: 1) I give you$1000 for The Flaming Sword Of Awesomeness
2) The server checkpoints me
3) You give me The Flaming Sword of Awesomeness
4) The server checkpoints you
5) The server crashes

Your checkpoint is now not consistent -- in fact, it's caused an economic loss. However, it could just as easily have been a win, in which case players would have an incentive to crash the servers to dup items.

And it doesn't have to be trade. It could be things like you using a rare key to get me into some special area where I can kill some mob that you can then get the loot rights for, or something like that.

Designing a MMO technical system that supports consistent trades, consistent player behaviors, and large scalability while allowing for business queriability (for game balance, etc), as well as low cost of operations, is a special challenge that isn't really solved by solutions for other challenges (although learnings from other challenges can of course be applied, if modified to the new challenge). If someone says something different, either he hasn't actually shipped and operated an MMO, or he's trying to sell you something.

##### Share on other sites
Quote:
Original post by hplus0603
Quote:
 . As long as you're not updating all the players at the same time it's probably not that big of a deal.

Not updating all players at the same time can lead to the following problem:
** Snip **

No I meant don't queue up all the changes and thrash the server. If you do a trade make the transaction right there and then. Don't wait and perform a huge update all at once.

I've noticed that databases seem really fast because the transactions for most programs are very infrequent even though there might be a lot of them so you're never handling say 10000 transactions at the same time. How many times do things like trades and transactions happen? But you know someone out there is going to try to iterate all the players and run the database persistence in one go. I was just pointing out that you shouldn't do it like that. Unless I'm mistaken and that is the right way? (I use flat files at the moment for my stuff).

##### Share on other sites
Quote:
 Original post by SirisianUnless I'm mistaken and that is the right way? (I use flat files at the moment for my stuff).

Same problem with transactions remain. How do you ensure integrity of writing to files?

Consider the above trade case - you write first part of transaction, then something happens (out of disk space, server crash, etc.). Or half-way during this write, another part of application reads one of player's data, obtaining partial and logically invalid state.

And that's just the simple, possibly single-threaded model. Except that these situations are rare enough to go unnoticed, but only start showing up as unexplained and unnoticed errors later on as load increases.

##### Share on other sites
Quote:
 How do you ensure integrity of writing to files?

You can use fsync() and journaling to implement transactions in files.
First, have a header which points at the actual data.
Second, don't overwrite the old data. Instead, append the new data at the end.
Third, flush the new data, so you know it's in the file.
Fourth, re-write the header to point at the new data instead of the old data.
Fifth, flush the file header -- this is the "commit" step.

If you crash anytime before Fifth, you will end up with a file containing the old data.

If the data is fixed size, you can ping/pong between an "old" and "new" area. If data is variable size, you can do used-range management, and re-use concatenated old areas when the size available is enough.

Another option (very common for UI applications) is to re-write the entire file to a new, temporary file. When you are done, you "rename(temp, name)" the file in a single, synchronous file system operation, which will clobber the old with the new. This is atomic on UNIX, but unfortunately rename() fails if the destination exists on Windows.

All of this just re-writes some part of the database, though. It doesn't change the fact that designing and implementing an MMO is a holistic system, and includes both the application server and the database back-end, and you have to be very careful in the design to make sure everything reaches the level of consistency you want/need, without stupidly slamming the database.

##### Share on other sites
Quote:
Original post by hplus0603
Quote:
 How do you ensure integrity of writing to files?

You can use fsync() and journaling to implement transactions in files.
First, have a header which points at the actual data.
Second, don't overwrite the old data. Instead, append the new data at the end.
Third, flush the new data, so you know it's in the file.
Fourth, re-write the header to point at the new data instead of the old data.
Fifth, flush the file header -- this is the "commit" step.

If you crash anytime before Fifth, you will end up with a file containing the old data.

I mentioned this because I once had to deal with this very problem with a 100+ page Word Perfect document.

I believe it was one of first WYSIWG versions, and the working copy was on floppy. During save, there wasn't enough space. Although error was reported, it was no longer possible to save the file elsewhere, resulting in an error message. Apparently, part of file was stored on disk, and the initial failure to save corrupted the working copy.

File was comprised of descriptor header, and actual data block. One of members of header was length of data block.

The usual algorithm for updating such headers where total length of data is unknown before it's actually written is to set header length to 0, then use it as counter while serializing the data.

But for a reason unknown to me, the value of 0 was actually written on the disk first. When writing the data block there wasn't enough space, so the rest of the process failed, leaving file corrupted.

After figuring out the data format, the fix was trivial - manually deducing the length and setting that repaired the format.

I still find it surprising though, how many similar types of errors exist in many applications, since such corner cases are rare enough to pass as good enough. It also demonstrates a fairly common scenario (disk space) that can cause a fairly complex error situation when dealing with distributed resources (part monolithic data, part stored on disk, part determined during run-time).

##### Share on other sites
(oh I didn't mean anything by the flat file comment. It's just a hack really quick while I test things. It has no transaction system).

Sweet idea hplus. I have some ideas for some data structures. Allocate a 0.5 MB file for each player (or one huge file with all the characters). Then from the top just create the headers that point to objects and expand those to point to other objects. Loading the data structure into memory would quickly allow it to be defragmented. Also using your idea to allow it to not lose data upon a crash. Sounds like a flat file database (I've never actually made one of those) except the Wikipedia article says "There are no structural relationships" and the one I just explained has those (definitely more along the lines of an object DB). Sounds very interesting. I think I'll try to make something like what I explained later when I have the time :)

##### Share on other sites
Quote:
Original post by hplus0603
Quote:
 You shouldn't design your MMO that it has to be doing DB reads every time you move around etc.

Which is why the design of the front-end (by which I mean front-end to the database -- i e, middle tier, or "game server") matters.

However, I have to ask you: have you shipped and supported a MMO game? The Cryptic guys have, and they had reasonable justification for going away from RDBMS.

I thought some of their justifications looked like "we understand our game far better than we understand RDBMSs". It's sort of admirable if you have a certain design and can't compromise on it in order to meet persistence requirements, eg. wanting to use the bandwidth associated with thousands of players while keeping the minimal latency you'd see in a single player game. But I think 95% of people could simply alter their game slightly to accommodate the way the DB works.

We save stuff continuously and asynchronously while a player is logged in and performing actions, and performance so far is fine. The code is a bit cumbersome in places, but I guess you win some, you lose some.

##### Share on other sites
Quote:
 Original post by hplus0603If you really know what you're doing, picking an RDBMS over something like an ODBMS is not as clear-cut as you claim. And you don't have to invent your own; there are plenty to choose from in the market.In my mind, the question comes down to: do you need the ad-hoc query capability of your data? We do, so we stick with an RDBMS, but it does have a price (dollar cost).
I don't necessarily say ODBMS is worse, but I think the fact there are a lot of people out there who are very skilled with traditional DBs makes using another option more risky... if you are going to get a proper DB guy and not let your programmer do the DB of course. If I was doing a big MMO I would want a specialist DB guy on board, and I imagine it's easier to find such a guy for a standard relational DB. To me, using something else would be more risky and probably incur extra cost up front investigating it... even if it might reduce hardware costs once the number of users starts to ramp up.

##### Share on other sites
Quote:
 the fact there are a lot of people out there who are very skilled with traditional DBs makes using another option more risky

The biggest problems I've seen have been exactly from people who know RDBMS-es, but don't understand the specific demands that games make. The current issue of the Communications of the ACM actually has an article written by Sun engineer Jim Waldo, chronicling what he learned in two years of working on games after having done a lifetime of business applications.

Btw: I don't necessarily agree with everything Jim writes on the technical solution side (just as I don't think Sun Darkstar is a great virtual world back-end platform), but the perspective of how games are different from business apps is very valuable and worth repeating.

##### Share on other sites
I'd still rather have a specialist DB guy than a C# developer who knows SQL and has even written databases. There's a lot of complexity to a DB that I don't know much about for instance, how to make it perform well etc.

A decent DB guy should be able to design a DB based on the application requirements... I guess the problem is making sure he knows what those are. But that's no different than an application coder moving to game coding really.