Persistent Characters and MMORPG Network Architectures

#101 From a great idle, Forrest Gump, I steal the phrase "and that's all I gotta say about that."

#102 @Stefan

Quote:
This thread needs to die.

Thanks, and no, it just needs to stay on topic.
I think a few people just forgot to scroll to the top and read the first post or the topic title.
I've seen the results of DB pissing contests on 4 forums and the subject has been covered pretty good.
Picking a DB flavor is fairly easy when all the info is here, but that wasn't the WHOLE point of this thread and I think a few people stuck to maybe the easiest portion of the topic.

@Thread
Making DB calls only when needed is a huge part of the architecture, no matter what DB you use, you wanna keep traffic low.
There may be some errors in my method, but so far any problems are self inflicted during development and the idea seems pretty sound.
I'm not sure if it's just me, but some of the MMOs I have looked at seem to load/save inventory everytime the GUI is opened/closed.
I only wanted to load the char from DB when it spawns and save on disabled (just before corpse) or disconnected.
It may sound a little scary not saving every few mins or something, but really on disconnect and death work just fine.

Everytime a player is spawned it calls to the DB, like so.

function Armor::onAdd(%this,%obj)
{
   if(%obj.aiPlayer !=true)  //This could be removed to have truely persistent mobs/bots
   {
      charLoad(%this,%obj); //Read from DB and apply to the char.
   }
}

function Armor::onRemove(%this, %obj) //also happens after a disconnect
{
   if(%obj.aiPlayer !=true && %obj.getState() !$= "Dead")
   {
      charSave(%this,%obj); //Read from char and apply to the DB
   }
   
   if (%obj.client.player == %obj)
      %obj.client.player = 0;
}

and another charSave in

function Armor::onDisabled(%this,%obj,%state)

The way the MySQL resource that I use is setup, nearly everything gets it's own connection.
The charSave function (I'll post it, charLoad and it's neighboring inventory functions soon) basically reads the character variable by variable and stores it to the DB.
Dreamer's method of using serialization is probably much faster but I wanted control of each individual variable in the DB via webpage or MySQL Query Browser.
A stored procedure could allow me to have my cake and eat it by breaking up the serialization on save and putting it back together on load.

Ari

#103 Oh, I was a little vague on the saving/loading, but it is one update query (and connection) to save/load the char.
But here are some pieces to help demonstrate what I mean.

Save

sqlUpdate("ruincharacters","XForm = '"@%xForm@"', Life = '"@%life@"', MaxLife = '"@%maxlife@"', Strength = '"@%strength@"', MaxStrength = '"@%maxstrength@"', Stamina = '"@%stamina@"', MaxStamina = '"@%maxstamina@"', Mental = '"@%mental@"', MaxMental = '"@%maxmental@"', CombatXP = '"@%combatxp@"', JumpXP = '"@%jumpxp@"', BlockXP = '"@%blockxp@"', RunXP = '"@%runxp@"', SwimXP = '"@%swimxp@"', ThrowXP = '"@%throwxp@"', ResilienceXP = '"@%toughnessxp@
      "', CommonFP = '"@%commonfp@"', ScavengerFP = '"@%scavengerfp@"', GenoFP = '"@%genofp@"', MysticFP = '"@%mysticfp@"', ZaibatsuFP = '"@%zaibatsufp@"', PuritanFP = '"@%puritanfp@"', HordeFP = '"@%uncleanfp@"', Faction = '"@%Faction@"'","CharName = '"@%charname@"'");

      charSaveInventory(%this,%obj);

Load

%objsql = sqlSelect("* FROM ruincharacters WHERE CharName = '"@%charname@"'");
      %result= %objsql.StoreResult();

      echo("");
      echo("\c2-----------------------------------------------------------------------");
      echo(" Loading Character Information for '"@%charname@"'");

      for (%i= 0; %i< %objsql.NumRows(%result); %i++)
      {
       %objsql.FetchRow (%result);
       %charID = %objsql.getrowcell(%result,"CharID");
       %zoning = %objsql.GetRowCell (%result, "Zoning");
       if(%zoning $= "Y")
       {
           %xForm = %objsql.GetRowCell (%result, "ZoneDest");
       }
       else
       {
           %xForm = %objsql.GetRowCell (%result, "XForm");
       }

   ...

       %obj.setTransform(%xForm);

   ...

      %objsql.FreeResult(%result);
      %objsql.Close();
      %objsql.Delete();

      charLoadInventory(%this,%obj);

I post here to help others going down the same road and to get others to rip apart the choices and methods to come up with something better.

Ari

#104 As to this thread dying.. I would hate to see that. This thread has a ton of very very helpful content for people with dreams of starting persistant servers. Alot of dreams need grounding in reality and alot of the people posting here have the experience with reality that up and comers can use.

The Thread isnt dead! Long live the thread!

#105 @TheMartian

Quote:
10)flow control, its not a good design to have all your gameservers talking directly to the database all doing whatever they feel like whenever they like. right a centralized process, that the game servers talk too,
flow control how ofter the game servers send the data to this central process, then the central process can
flow control the requests to the DB(and also provide the caching layer). its easier to manage the data(and concurrency) then alternative.

Good point!
Do you have any resources or examples I could look at?
schedule() is the first thing that came to mind, but I doubt that's even close to a step in the real right direction.
I realize there are applications that do this, but I know nothing about them.
Rather than blindly google around, does anyone have any recommendations (links?)?

Ari

#106 As far as flow control, I would totally agree on this. A database can only process so much. Think of it like watching the bussiest intersection in the biggest city in North America during rush hour trafic through a sped up camera... Look at where you greatest trafic demands are, alleviate the congestion in those areas via caching etc..

In my case I use a flat file system on the zone server that reads the character variables into server memory when the player enters the zone. This includes all of the players inventory, stats and equipment. My target for my zone is 100 players per area comfortably. That means each player record in server memory having 200-250 items in it. Multiply the memory useage by 100 and I am very easily able to run 2-3 zones per physical server.

If I was bold enough to have the database querries hitting the SQL server everytiem a player wanted to ID an item or change his equipment I'd have a much heavier load on the database and for absolutely no reason.

For me the decision on the server flat file caching system was simple. Zoning is something a player does every xxx number of minutes. Sometimes a player will be in the same zone for hours.. Why not have all their "read only" querries handled by the zone server? Makes sense to me. When alterations to the player are made (basically any kind of write querry) the server DB updates the MySQL master DB. The memory is written to a flatfile DB locally on the server as a backup measure incase of a server/client crash.

I was very fortunate to have 3 what I would concider absolute geniuses to consult with while designing my DB connectors. I am not such a genius but I knew enough to get help. I will tell anyone reading this who is not a guru with databases (unless you have 10+ years of professional database structural experiece you are not a guru =) ) to go out to database forums and ask questions untill you get some good people to consult with you. I would be lost without all the awesome DB tuning lectures Ive been given =)

#107 I think that Flybynights last post is very useful and should be noted by anyone trying to build an MMOG. I've been a database adminstrator for the past 8 years, and no matter what solution you wind up going with, your going to bog down the server if it is poorly optimized. The less transactions you can have, the better, so design your system accordingly. And in my experience, MySQL is generally a viable solution unless you have money growing out of your ears. Obviously a solution such as Oracle is top notch, but you pay for it dearly. Most of the people on these forums are independent developers and don't have that kind of money to toss around. They shouldn't be scared to use MySQL. It is a viable alternative.

#108 @broke, hey, yeah that architecture is actually discussed further up in this thread, its a realtively common multi-tier configuration. The middle tier process caches information from the DB server (mostly data that is requested regularly) this helps take the load off of the DB server and inproves response time to your game servers and other processes.

It does incur the overhead of another process or perhaps another machine, but it gives you good control over the activity against the DB.

Flybynight has an extension of that which is really handy as well, not only cache it for quick handling but keep the data some place else in the event of a crash. so you dont lose anything.

An alternative to that might be a write through cache were updates pass through so your less likely to lose data but have a little less flow control. or to snapshot where you take consistent views of the data
and snapshot over the the DB at regular intervals(that combined with local flat file could provide a nice
recoverable system).

cause how many times have we heard about a world rollback right? lol, would be nice to advance the MMO technology and find inexpensive solutions to minmizing it. And the middle tier with the local file cache
is one possible solution.

The best thing is this solution can work for any RDBMS you choose. Which is sort of how this thread got started in the first place, it was all about design and architecture, not which RDBMS to use.

as for which database, pick the one that best first your requirements. Like anything else, figure out what you need, what meets those needs and you can afford it, then pick it and go for it.

Just think if your going to keep mentioning MySQL you should probably mention the other alternatives as well like PostgreSQL, its free and very powerful as well.

Somewhere in the middle I posted as list of common problems that seem to plague MMO here it is.

Quote:
so what goes wrong? 1)no caching layer, sure databases have there own caching mechanisms(well some do)
but it does not necssarily meet the need of your game, so pre-load and cache critical data so its more readily available and you dont have to bother the DB with it all the time (why? because your probably running a bunch of other aggregations, rankings, queries updates, etc) Use the database to hold data, period(for high transaction systems)

All that fancy stuff database can do is cool, but most of it is not intended for high volume transaction use, so keep that in mind you need to use the DB's lower common functions all that lofty cool functionality is often great for data warehouses or your address book, not for huge databases with millions of records that are getting pounded non-stop.

2) crappy sql, improper indexing, extensive use of blobs, out of row storage because your data is too large for the record, too much sorting, ordering, table scans, aggregations, poor concurrency planning on key tables, and on and on. This is one of the bigges reason they have problems btw, poor planning, lack of a functional expert and the too little too late approach to the DB.

3)lack of proper database sizing, most of these MMO game developers cant tell you have big the DB is, how big it will get, how fast it will grow, how much of it will fit in memory, how many transaction per second will your game require. no but they can tell you how many polys are on the screen, what the limits are, how many shaders you can run on some particular hardware. etc. you need to know the same kinds of details for your DB as well.

4)dont save what doesnt change, dont store default values in the DB, seen this add way too much overhead
and greatly increase there estimated size of the DB.(store default data as static data in files)

5)learn the concept of parallel throughput, be surprised how many games use a single connection to the database (good grief).

6)understand concurrency, yes if lots of different processes(you know game process, web process, etc) are all hitting the same database, the same tables, or heaven forbid the same records your going to run into bottlekneck problems. design your system so you dont have that problem.

7)dynamic sql, dont use it period, yes its cool, but like they say cool costs money. this is the slowest form of sql you can right and ive seen games write everything in dynamic sql, learn what a bind parameter is.

8)dont generate the sql in your app if you dont have to, if your DB supports it, use stored procedures, they are faster, and much much easier to manage and maintain.

9)learn how to take advantage of the DB you have chosen, do not, and I repeat, do not, write your database layer with the idea that hey someday we might switch databases so we need to write it all generic.
boooooo, write for the database you have chosen, abstract that layer away if you must but dont generalize
your application for the database.

10)flow control, its not a good design to have all your gameservers talking directly to the database all doing whatever they feel like whenever they like. right a centralized process, that the game servers talk too,
flow control how ofter the game servers send the data to this central process, then the central process can
flow control the requests to the DB(and also provide the caching layer). its easier to manage the data(and concurrency) then alternative.

11)manage your data, why send 10 updates for the same record in a single second when you could wait a few seconds and only send 1 update right?

12)game designers need to understand how there design will impact the database application they are creating. yes to having every single object in the game completely unique with hundreds of attributes
is not only unnecessary, but there are much better ways to design it so that you dont pollute the database
with all that in. (hence a mixture of static data in flat files with dynamic data in the database is a better approach).

#109 We are planning to simply use MySQL, eventhough our tests show that there will be some problems with the amount or players we're having in mind... we don't have to much other choises. I have yet to try out postgre sql.. but for an indie i'd say mysql it's a pretty good choise, plus it's easy to set up admin-like things in php, or even an account system. Once your game gets succesfull and all that, you should start thinking about getting better "equipment".

I wasn't really planning on spending money on things like that if we dont even know if our game is going to work out (i say I because, i'm kinda the funder of the whole project.. and i pay for everything)... this would all have been so much easier if everything was just free. Oh well..

Since we're also working on a similar thing, i will make some sort of thread or resource to explain our way, once it's done. Maybe it's usefull to anybody.. and we could use the comments to improve it.

#110

Quote:
Once your game gets succesfull and all that, you should start thinking about getting better "equipment".

Uhm. Except that switching databases is a pain in the ass.

#111

Quote:Uhm. Except that switching databases is a pain in the ass.

It doesn't have to be. Use stored procedures and an ODBC connection and you can easily swap between mysql, ms sql, and oracle (and many more) quite easily. Of course, OPTIMIZATIONS and CONFIGURATIONS would need to be in place, and you'd have to reprogram some sprocs and the like, but at least you dont' have to make many if any code changes in the game itself.

#112 It indeed doesn't have to be. Just make sure you keep the same names of the exposed functions and variables to the scripts. Then all you have to do is rewrite that. Or even easier, you could write a wrapper (if you're lazy haha)

offcourse it's work... but atleast it saved you tons of money. Money i'm not willing to spend yet.

#113 If you have a moderate amount of records in the database, reconfiguring and optimizating a new one/converting over is a nightmare, at least I feel that way.

I would be tons happy to hear about your conversations between databases so I can learn, though! Then I won't have to consult people anymore. ;)

#114 Switching databases now there is a fun topic.

Its a pain, and mostly because of the obvious, different DBs use different datatypes (unless you tried to keep to the more common types), different databases use different stored procedure languages(if the DB you choose even supports stored procedures), the syntax can be different, the built in stored procedure functions can be different, etc.

also what a particular rdbms is good at and has problems with vary greatly between DBs. Typically you wind up building your DB implementation that best suites the particular DB you choose. Which inherently means its not going to be ideal anymore if you try to move it to another.

ODBC is awful for some implementations (its a standard left dead on the road years ago), so many rdbms's provide there own protocol. This custom protocol lets you use all the advanced features of that particular DB (which odbc often times does not support since its a generic protocol).

This of course can bind you to the particular rdbms you picked. That isnt necessarily a bad thing either, taking advantage of the DB you picked can reward you with a faster implementation and much better results
then trying to write a completely generic implementation that works on any DB.

Now of course you can go halfway, try to isolate the key DB calls into a generic wrapper so that your interfaces do not change just the calls underneath them. But on the DB side of things you most likely going to have some RDBMS specific stuff that your going to have to figure out how to port.

And it can be painful, especially if you have a very large DB with millions of records that have to be converted
to different datatypes in the new DB. argh. certainly it can be done but boy is that a long painful weekend.

if you wind up using things like triggers, replication, partitioning, etc to optimize your DB you can run into even more major problems when you try to move to another one. not all support all that functionality, or others have the same capability but do not do it as well.

from my experience its been better to stick with one, and make it work, optimize the hell out of it, learning every quirk you can, take advantage of every little setting you can and run with it.

it does mean you need to seriously evaluate your choice of rdbms, or try to keep your first implemention super generic, only using common datatypes, no special extensions, no special types, and let it be a bit slow with the intention of moving to something else. but if you not going to do that then dont build your system
thinking your just going to drop it into another RDBMS. at least in my opionion. it rarely works
well and its generally always a huge hassle.

especially if your people are experts at the first DB and you switch to a different one, you have to worry about getting expert knowledge in the new RDBMS, making sure your DB tools work on the new one,
and relearn all the tweaks, adjustments, tuning, all over again.

so yes you can do it, but why would you go into a project thinking thats what your going to do?
if you already know your DB of choice wont work pick something else (if possible) otherwise you
will really need to plan it out more to make switching easier later on when you make the move.

#115 Well, my thoughts about it are basically this: We can't afford MS SQL at this point, so design it on MySQL and when we get to a point where funding isn't the issue anymore, we'll move to MS SQL. There's a lot more to a database comparison than 'can MySQL handle the same amount of transactions'. MySQL doesn't support all of the datatypes that MS SQL does. MySQL doesn't allow you to call web services DIRECTLY inside sprocs. MySQL doesn't have built in encryption for the DATA inside the tables, etc. etc. etc. There are tons of reasons why I want to use MS SQL over MySQL, but if we ended up with 5 total customers ever then obviously I don't want to have wasted $$ on a license for MS SQL.

Now, I remember reading a post on GG about MS SQL Express allowing you to use it for commercial applications, but I haven't been able to verify that yet.

#116 The EULA for SQL Server 2005 Express is at www.microsoft.com/sql/editions/express/redistregister.mspx (click the dropdown for your language and hit 'go')

I read that thing like three times and I don't see any limitations of use at all as far as commercial versus personal use. If this is the case, then Express can be used for an indie game, for free, and EASILY upgraded to the full blown version if your game warrents it.

#117 That's actually not such a bad idea. Did somebody write up something for this and is willing to share? Saves me a lot of time implanting it myself hehe. If nobody came up with anything for it, i'll write it myself.

#118 @TheMartian
I did read it all, in fact I quoted part of it. ;)

Still looking for a commercial "middle-man" app (in between DB and game server).
I understand the theory, but is there any commercially available software someone could point me at?

Ari

#119 Jonathon:

Why are you wanting to use MS SQL?

That is the question you should be asking yourself. I am not saying not to use it but my own performance testing as well as alot of other peoples testing shows that for standard MMO communications MySQL is generally a better performer.. (Less overhead and less memory)

I again am not suggesting that MS SQL is not right for your app but maybe you should read this thread through a little bit more. The only MMO that I am aware of that used MS SQL was "Horizons" and their database layer was... not even in the ballpark of what an MMO shouldve been. That said they didnt have a clue on how to properly set up their Database but still.. you would be in the minority if MS SQL actually turned out to be the better solution for your game over MySQL

Cheers

#120 I'm pretty sure that MS SQL can handle an MMO if designed, setup and optimized correctly (just like the other major database providers). I work for a company that develops casino games (slots, video poker, etc) and casino systems software here in Las Vegas (as a "software engineer" on a "data interface" product). Without going into too much detail, Microsoft SQL Server 2000 (and now 2005 which is already being rapidly adopted) runs a significant portion of the Las Vegas strip (as well as hundreds of casinos around the world). One large Las Vegas company (who I won't name but if you visit the strip, there's about a 50% chance you will be in one of their casinos) is running a database that exceeds 100 terrabytes. These systems are ALL highly transactional (several million transactions per day). They are also HIGHLY regulated (not only do they need to deal with a large volume of data, but there needs to be absolute accuracy and fidelity as well).

Any MMO game is ultimately going to be a database application. You can't effectively use a database as if it were the file system. You need to use a RDBMS as a RDBMS. Each vendor has its own means of optimization and their own specific version of the querying language (ANSI SQL + whatever vendor specific enhancements they have). That being said, if you don't put in the work in learning how to correctly design and use your database a file system storage solution may work just fine (its worked fine for a majority of the MUDs out there for the past 30+ years).

I've used MySQL for my own personal projects for the past 5 years as well (for a heavily modified persistent NWN world) and it has worked well enough. At its peak the NWN server running MySQL only had around 100 regular players, several thousand casual / one time players, and about 10,000 total characters stored on the server (and a few million rows of statistics collected), but even with such a small amount of data some areas that were poorly designed significantly bogged down the system. Resolving the way the database was being utilized as well as improving some table designs / relationships ultimately eliminated any noticable "lag" and completely eliminated server crashes.

My personal preference is for SQL Server but any of the 3 major vendors (SQL Server, Oracle and MySQL) most likely CAN be made to work if you know what you are doing. Trying to pick one vendor or another without having used them or without knowing much or anything about SQL or databases in general is much like trying to pick Windows vs Mac when you know absolutely nothing about them either. Would you let the deciding factor of using a Mac server be based on the style of the case and the shiny GUI? Or a Windows server because someone on the internet says that Microsoft r0xx0rs? Both have their pros and cons, and most likely both can be made to do what you want. You need to learn how to do it best on whatever you decide on.

As with most things there's several possible solutions. Pick one and then use it, learn about it, read about it, test it out and make it do what you need to do. Make mistakes and then fix them. Just because everyone else is using one thing or another doesn't mean its the right thing for you.