The total DBMS approach (was: [MUD-Dev] Unique items vs. item references)

[Russ Whiteman]

From: "Derek Licciardi"

> I believe you could build a database based architecture like you
> describe in scenario one.  I also think that it would be cost
> prohibitive to do so for anything larger than a normal sized MUD.
> If you're talking about MMOG size, then you're looking at severe
> costs.(relative to a typical MMOG or game project) In effect you
> would be building something the size of SAP, PeopleSoft, or Oracle
> Applications for your transaction system.  Like SAP, given enough
> hardware, it runs amazingly fast.  Oracle is capable of handling
> the NASDAQ stock market.  It is certainly capable of handling the
> processing requirements of a MUD or even an MMOG.  The question
> quickly becomes a money one.  SAP, installations run into the 20 -
> 50 million dollar range and higher to achieve MMOG style subsecond
> performance for thousands of users.  The benefit of having a
> standardized interface to the game data through SQL simply does
> not outweigh the significant increase in cost.

  <snip>

> For a MUD, this might be totally different.  If anyone gives the
> approach a try, I'd love to hear how it came out and would be very
> willing to offer database design advice if needed.  Even with a
> MUD, you're probably not going to be able to do it without a
> nearly midrange server.  We're talking about SCSI RAID arrays,
> Multiple processors, Gobs of memory, and a qualified DBA that can
> tune like a madman.

I'm not sure I can agree with you on the cost to run such a system.
We do a significant portion of our storage with an Oracle db running
on a not extremely expensive server.  We haven't bothered moving
everything over to the database primarily because the older,
in-memory/write to disk-cache system works well enough that it's not
worth the effort to replace it at this time.  When we finally
decided to make this move, we decided to start with the systems that
would most obviously benefit from the new capabilities, primarily a
high-volume, high-detail logging system for player and game-system
activities, that could be queried by our in-game staff for research
and/or CS issues.  Admittedly, our db traffic is not the same kind
of balance that you'd see for the "pure db" option that David was
discussing (our traffic is 90%+ writes, we load the character data
on login, and only in rare system calls at other times), but I don't
have any reason to think that that particular fact would cause any
huge change in performance.

Yes, we do have a SCSI RAID array, but the server itself was, until
recently, actually a dual Pentium-Pro running at ~200 MHz.  We
upgraded to a Quad Xeon server primarily to allow for some room to
handle some rather CPU intensive reporting we began offering our
in-game staff.  The system handles 11 games, and a total in-game
population of 2200-2300 on average with peaks of well over 3000.
Although we have a license for Oracle Enterprise edition (a
perpetual license at an older, more reasonable cost than today's
licenses), the system doesn't actually use anything not available in
Oracle Standard...which was available for a few hundred dollars per
named user last time I checked.  Total cost for the operating system
and database software < $20K (not at all out of reach for a MMOG,
although it could be a bit pricy for an everyday MUD).  Last time I
checked, we had ~1.5 million entities (characters and accounts) and
~50 million log entries, and we're handling 1200-1500 transactions
per second at times, without even coming close to bottlenecking
anywhere, so it could ramp up much higher than we're using it...hard
to tell without actually running the load up high enough to start
seeing some significant delays.  The CS reports are by far the
biggest load, and they generally run in less than 5 seconds (but as
the game doesn't have to wait for these, who cares?).

Admittedly, it took a fair amount of time, effort, and
experimentation to get things as well tuned as they are, but you're
going to have to spend that on -any- system you build for the game.
<g>

As for safety, we currently mirror the database submissions into XML
files at each transmission point between the game and the db, so we
stand to lose no more than a few seconds of data, no matter where
the system might break (and likely none at all in any single point
of failure).  Worst case would be a really bad db corruption at the
same time that we suffer a HD crash in the game...if we lose the XML
-and- the current db and have to restore to backups, we might
actually lose up to a day of activity...but the more likely scenario
is that we get a few duplicate entries (which, for logs, just isn't
a big deal).

> No matter how you position the database, you're going to want to
> write an interface in front of the database that allows
> communication with the game client.  The game client should never
> have the ability to fire SQL straight to the database.

I'll certainly agree with you here, and go you one farther...nobody
outside of your database coders should be writing sql straight to
the database, unless you've got an -extremely- simple schema (which
isn't going to be very flexible in the long run).  User-written SQL
has that nasty tendency to tie up large amounts of CPU trying to
process extremely unoptimized code...which is simply unacceptable
for a system like this.  Even db-aware programmers can have a hard
time getting some queries to run well (and of course, those are the
queries that would be most useful to the in-game staff), and I do
think that replicating the data in a warehouse -will- drive a system
like this into the red pretty quickly.

Bottom line, I believe that it is quite feasible to store all
persistant data in a real db, although you'd best have some
reasonably competent db coders and a decent DBA-type to make it work
well.


_______________________________________________
MUD-Dev mailing list
MUD-Dev at kanga.nu
https://www.kanga.nu/lists/listinfo/mud-dev