CACHe / MsSQL Storage...
Author
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.
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.
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.
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).
berkeleydb comes to mind, tokyo cabinet or hamsterdb (spam: i'm the author).
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.
Quote:Original post by cruppstahl
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.
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.
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.
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.
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.
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.
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]
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]
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
