Persistent Characters and MMORPG Network Architectures

#61 @dishmal

This is not an answer to your "which db is best?" question, but I think you'll be interested anyway... I thought people might be interested in knowing the sort of throughput a database can manage, given the "millions of small transactions" situation that MMP games require.

What I found is this, for MySQL:

When the transaction is one update of a few columns and one insert (eg, update of player position and an event to record that update), MySQL can handle about 2000 transactions per second on my hardware (WinXP on Pentium 3.0GHz, running other stuff at the same time). You can more than double this by batching your updates (eg, by only issuing a commit every hundred updates, or whatever), but of course that is a risky strategy, it all depends on how much you value your individual data items. If you drop the event logging and just do the update, trx/s rises to nearly 3000.

This throughput also scales very nicely, which is as I expected. For the numbers I imagine we're talking about (1 to 100,000 players and with the events table containing some arbitrary number of rows - I tested with 0, 1 million and 10 million), MySQL gives the same throughput on average.

To me, there are two interesting things about this:

1 MySQL performs very well with the data volumes any indie game might possibly aspire to (@Martian, I obviously would have tested with two further orders of magnitude if I were sizing for the kind of userbase a game like Lineage enjoys)

2 This kind of throughput (maybe even slightly better) is achievable in an actual game situation, if you structure your server arrangement correctly. People may think, "That's just an experiment, it's not what would happen in the real world," but it most assuredly *is* what would happen in the real world. If you took this program and made it wait on a queue, made it do nothing but update the database when it was told to, then it would do so at these rates of throughput. The only problem would be feeding it quickly enough...

If people are interested, I may do this experiment with other db's, but I think the basic point is made - if you set up a separate server to do the database work (and I do believe I'd establish separate read and write database servers, in fact), then you can achieve more throughput at the back-end than your game can provide.

And the moral, therefore, is this: you should worry less about the particular database you choose to use, and more about how you're going to feed it...

I expect many enlightening comments from The Martian on this subject :)

Wayne

#62 Do you have the abilities to perform the same test with SQL Server 2000, SQL Server 2005, and Oracle? I'd be interested in a comparison if you had the ability.

#63 I guess I probably can - those products are free to use for non-commercial purposes these days, aren't they? I'll have a look...

#64 Oracle I have no clue about a free version. Maybe they have a demo available? You can download a copy of SQL Server 2005 Express to test the 2005 stuff, however 2000 you'll have to find a copy of somewhere as they have no free options for it.

#65 Anyone can download the full version of Oracle from their website. You just need to register as a developer with them. They are trusting you though to use it only for evaluation/education purposes. I think its a great idea.

#66 I'd be interested in such a test with PostgreSQL... :)

#67 You need to age it, do lots of random inserts, updates, deletes for several hunderd thousand or
more transactions(millions). This gets closer to what actually happens while your running in a production
environment.

also you need pretty sizable tables, should make the tables 20, 30, 50, columns with various
datatypes in them.

a cleaningly inserted table will perform much better then one thats ended up with chaining do to constant thrashing of the table and indexes.

make sure to put a reasonable amount of indexes on the tables as well, possibly even a mixture of
composite, primary key, bitmap indexes. table without any indexes or with just a primary key index
is not very representative of a production system, so good to put some reasonable number of
indexes on it as well.

this with a single connection to the db versus multiple connections? In a production system you have a tendencey to get a lot of concurrent activity, lots of people logging in, logging out, chat being logged,
characters being created, worlds being loaded up, stats being saved, world objects being created, saved,
deleted etc. This can greatly decrease the throughput as you generate a bunch of contention (this
is where some DB's handle things well and some fall flat on there faces).

so for example while doing a 10k or 100k update, do some 2k, 5k, inserts to the same table and run
a bunch of 5k record queries while running the same test. run against the same table, then run it again with them running against different tables, again more in line with what happens
in a production running MMO game. numbers maybe a little exagerated but still a good test.

good start, but be careful about running an ideal configuration (1 connection, running one sql statement
as fast as it can against a small sized table with few indexes). youll get higher numbers then when you really load the machine up with more varied activity.

Quote:
And the moral, therefore, is this: you should worry less about the particular database you choose to use, and more about how you're going to feed it...

very true!

MMO games have a tendency to grow into the gigs in size and so it becomes critical to limit contention, keep indexes to a minimum, and to limit the amount of extraneous requests (like doing a lot of sorting or ordering)
when its a very high transaction DB.

For high volume transation DB's you really need to keep it minimal, think of it as a digital camera, its a snapshot of your world at some given point in time, it is not for data warehousing, aggregating data,
performing huge table scans, sorting megabytes of data, etc. Not on the production
DB, if you want to do those things(like for marketing reports) you replicate (or export/import)
you data to another DB and run your reports against that.

In terms of speed I had done a lot of tests and out of all of them, Oracle was the fastest. I wont put the numbers up as Id like to see someone else try but I could get 10 times your transactions per second
on a desktop pc. In fact, when using a DBServer(middletier server that bunches data) I could saturate the bandwidth before I could overload the DB. I couldnt send enough transaction requests
over the wire to bury it. something thats very specific to Oracle. But Oracle is very expensive
for indie DB's so it would certainly be great to find something comparible thats free.
Its definately the best at batching records together and slamming then into the tables.

As for free DB's not sure the best yet, been awhile since ive run the tests, so nice to see someone
else trying it out in the context of a game.

as with Dennis Im most interested in PostgreSQL as well.

#68 I have to say your posts are very helpful and interesting, TheMartian.

Thanks

#69 Thanks glad I could help :) I just hope creators learn from it, ive done presentations, written articles in books and yet I see the sme mistakes made on almost all MMO games when it comes to backend infrastructure
and database design.

as for testing I do caution you. running a single sql statement against a database as fast as it can go is not very represtative of a real production enviroment.

in production settings on fat hardware ive seen MySQL and SQLServer get maybe a few hundred
transactions per second (in the neighborhood of 400~600 per second) because there is a lot more
going in then a single query, or a single type of SQL statement.

when you send the same request over to the DB and then continue to resend it, it caches
that statement and the second and subsequent times you run it, it will run faster. But in a production
enviroment there may be dozens, hundreds, thousands of different sql statements running against
the DB. The same statement rarely ever gets run multiple times a row, and when it does its
almost never with the same data.

This is what I was getting at in my other posts about why its so hard to do a comprehensive test
to figure out which DB is right for your game.

The link I provided in my other posts in this thread is really the best resource, they tried to
figure just what the DB can do based on a fairly standardized measurement.

look at the list and see if you see something close to the hardware you would use then scan down and
look at the performance.

http://www.tpc.org/tpcc/detail.asp

takes a lot of reading and digging but you can see how things compare.

I noticed no one ever did answer my question about which successful MMO game uses mysql.
The only big one I know that was toying with it a couple years back was DAOC, which I believed
used a flat file system orignally and were kind of hating it because they couldnt mine the data
very well and wanted to move over to a RDBMS, not sure if they stuck with mysql or not but thats
the only one I know.

The rest of the successful (ie big fat MMO games, use SQLServer and Oracle, couple old ones use flat files
with custom db style wrapper over it (dont do this please avoid the need to write your own db).

I suspect soon we will see some that try out things like PostgreSQL, unless Oracle drops there prices (which they have on 2 and 4 cpu machines, its the same price as SQLServer now). still expensive for indies but cheap for larger comanies.

dont want to poke too hard but mysql is still not there yet for large enterprise scale type systems.
awesome for indie developers though, free, easy to install, easy to use, no doubt. id even use
it for prototyping stuff. But when it comes to really large DB's with really high activity
ive seen mysql fall down hard too many times.

case in point, we were putting up a forums sit for a large game (we are talking 100k concurrent just on the forums alone) we launched with mysql, and had mysql people install it, tune it, etc.
it tanked almost immediately, it could handle the load, it couldnt handle the concurrency, it couldnt
manage the sheer number of transactions. we did a plain vanilla install of oracle 9i at the time
and plugged it into the forums. presto worked fine, never had a problem since.

now the application could be to blame for a lot of that, but the fact is even with the application
being flawed the big mainstream db handled it just fine and the cool free one choked miserably.

so thats my reason im interested in Postgres as I think its better designed and has the potential
to really hang with the big boys.

Its free, and its built with large scale in mine. in fact in many ways its modeled after oracle. (wouldnt surprise me if an oracle engineer/designer was working on it).

I would suggest that if your looking at mysql that you take a gander at Postgres as well.
They iron out a few more features that im looking for and I think I would give it a fair shake
at making a large game with it.

#70 Here's a question for you Martian... Would it be faster to run sprocs in the database that were called from a web service that talked to the game servers, or would it be faster to just directly access the database using T-SQL?

Also, does anyone know of a resource that has a generic outline for an MMO game's db table structure? I know dreamer's tutorials have somewhat of this accomplished, but nowhere near the amount I'd want in an MMO such as a place to store chat logs, real life account info, etc.

#71 Generally stored procedures are faster, epecially when dealing with more complex SQL.

super simple queries dont necessarily get any faster moving from a well design prepare/declare type
of thing over to a stored procedure. but there are other benefits, its easier to maintain a stored
procedure, easier to modify (no game code changes needed), can optimize them without
having to do a new build of your game.(if your tying gameserver to db)

but for more complex situations ive seen as much as 20~50% faster using stored procedures
over SQL statements being built as a string and passed off to the DB.

but overall, id say use stored procedures and keep the SQL out of your gameserver.
(also will make it a lot easier to migrate to a 3 tier architecture should you need to support
a larger scale game).

as for using a web service, as long as you dont bottlekneck it with large amounts of data.
although they can handle lots of request not sure how they respond to lots of requests
with lots of data in them. but that is pretty close to the 3 tier design as well
where you have an intermediate process that handles all the request for you
preventing you from having direct DB connections between you gameservers and the DB.

probably a good interim solution. for larger scaled projects you would want to
potentially write your own and add in a write through caching later to minimize
the constant access to the DB for data you have already asked for in a prior query.

the middle tier caching layer can greatly improve performance and reduce the load
on the DB tremendously.(which can have positive results like not needing as much hardware
for your db machine).

#72 Wouldn't the overhead of the webservice calls make the sproc speed gain diminish into nothing and even make that solution slower? By caching in the middle do you mean OUT of DB caching of the actual data maybe even persisting it to XML or something?

I often thought about game data that would need to be kept on the server such as a player's inventory being stored in say SQLite on the game server itself and anytime it changes, it would reget/put the info to/from the database as needed and into SQLite.

That make sense?

#73 Let me go into a little more depth with my server structure. Keep in mind that each 'server' is NOT necessarily on a seperate physical server:

Game server calls the web service which runs a stored proc on the database server. the database server sends the info to the web service which then writes it to the SQLite or MySQL database. the game server then pulls that info out of the SQLite/MySQL database.

Game server calls teh web service and if the MySQL/SQLite database already has the info then it pulls it from there instead of from the original database.

#74 Dishmal, I think your server structure is a perfectly sound implementation of 3-tier principles. You don't mention how the middle database will regulate itself to hang onto popular data at the expense of the other stuff it holds, but other than that I reckon you're right on the money. (Cue for Martian to come in and tell you five reasons I'm right, 12 things I've overlooked, and 29 ways to make it all better!)

However, I am as sceptical as you seem to be about the use of web services as your transport. I presume you must mean proper, standards-based XML web services? If so, I can't see how web services could be viable for a game - aren't we talking about repackaging a condensed binary datastream as a long-winded bunch of text, transmitting that windy text over the S-L-O-W internet, and then crunching it up again at the other end? I don't mean to sound sarcastic - as far as I'm aware, that is exactly what *every* web service transation involves. Most web applications can live with it, because who cares about 10, 20, or even 100 milliseconds? But how could a game live with that?

Incidentally, since people seem interested, I've decided to do a proper job of the db timings. I'll post a new thread tomorrow detailing the method I intend to use, for comments.

#75 Interesting. Here's my problem, I'm not a DBA. I'm a developer who has always had DBAs at his disposal to deal with this stuff. Now that I'm going to be making these decisions on my own, I have to finally start thinking like a DBA.

I guess the whole reason I'm even thinking about a web service is because I'm not quite sure how to get stored proc calls from within the TGE engine. If I figured that part out, then I wouldn't even consider the web service at all.

#76 Wow lots of reading here. Nice stuff.

I am not going to wade into the forray with all of my knowledge and experience with developing MMO databases but I would like to respond to the last couple posts here about using web services..

It sounds like everyone here is really doing a great job if designing or at least attempting to design their DB structure very carefully in advance and that gentlemen is hands down the #1 most important thing you can do. Everyone will have suggestions as to how you should do it and whos way is "best" but it all boils down to "use the right tool for the right job" and "make do with what you've got".

Let's fact it, as indies most of us dont have $10k to drop on a bunch of alpha boxes or oracle systems.. better yet most indies dont have the know how to support those platforms and if they did, they wouldn't be makign indie mmo's they'd be making $100k a year programming =). So I think before you mire yourself too deep in what is best, Look at what is readily available to you, what will work with the hardware/servers you have and what your target transaction rate and data rates will be and what products will satisfy all those criteria.

-Could you use MSSQL server? Sure you could. I don't condone piracy though and MS SQL is a couple grand. It's also got very high overhead (out of the box) and requires a trememndous ammount of tuning to optimize. When you tune up an MSSQL server it will always carry a higher CPU and memory footprint than most other database engines out there leeching away "Bang for the buck" on how much performance you can squeeze out of your server.

-MySQL, is it truely the best? Hey, its a free solution (please read the licensing and abide by it its not hard to do) that out of the box has a very small cpu/memory footprint and has a proven track record to handle triple A MMO loads if properly configured.

-Web based communnication services? Done right, web based PHP communication scripts can be extremely effective. If you arent sure HOW effective load up a PHP service on your PC and do some benchmarks. I fould once you have the scripts cached properly (and lets face it you are only going to have maybe 50ish different scripts) your transactions will be instant. That said, I am talking about INTRAnet transactions not INTERnet transactions. If you want to use PHP database communications across the INTERnet to run an MMO I suggest you look at the benchmarks and your target datastream / transaction rates.

It took me a year or so of fiddle farting around with MySQL / PHP / SQLlite and many many hours of sonsulting with some very super cool database geniuses to come up with a hybrid flatfile / mysql combination that is smoking hot. Flat files work great as long as you take the necessary precautions (redundancy in the file data NO I dont mean a mirrored hard drive lol) and caching flatfiles into memory using an SQLlite type of system makes data access about as fast as you could ever dream for even the most robust MMO traffic. (OK I would be scared to take on the blizzard traffic stream for WoW but thats a bit extreme ;) )

So my suggestion guys is dont get bogged down in the theoretical "best" way to do some. Look at what you've got to work with and design something that will exceed your transaciton rate requirements and you datastream requirements substantially (so you dont get dissapointed later as overhead creeps in) and enjoy yourself. Databases facinate me so I spent more time than I should have on it but I am very happy with where I'm at now.

Good luck all.

Mark

#77 Went with MySQL.
Holding up great.
Check at www.brokeassgames.com for a deployed example if anyone is interested.

Ari

#78 Checking it out today. Sorry it took me so long to get 'here' but I have been busting my you know whats converting all my work over to TSE. I have not made any resources available yet but I do plan to. I still have all the classics working as follows.

None of the following is 'live' but here is what I have rolling so far. If you visit my site that is still the TGE version of my game.

Login Server : I was using SQLite but I have prepared the switch over to MySQL due to a change in my webhosting that is still in progress. Now a user logs into my game with their account that they use to register on the website.
Character Creation : This has recently been taken to a whole new level in my project. The only 2d GUI's that are present in my game are the auto-patcher, account login, and loading screens. Once you successfully login you are placed in a room as a camera object with no name and no world broadcast message. There you navigate around based on your gui selections and the camera pans from monitor to monitor while you select/create your character, edit your account, and we have plans for characters to even become NPC type objects if they choose to express hostility twords other players.
Character Creation and Character Progression has always been one of my favorite processes to go through as a gamer, so I have attempted to put as many elements into this as possible. I have eleminated the 'leveling' type system and decided to go with a skill based progression. Characters currently have 10 stats to manage (not counting ones that you can't put points into), "per slot" armor configurations, resistances, and some more goodies. The way skills work is a little different; every time you use a skill a usage bar increases, then it maxes out you are then allowed to put another skill point into it and the bar resets back to zero.
Gameplay : Being tired of the current situation that the gaming world is experiencing with current RPG and FPS style gaming, I am doing my best to cross the two together and mix in a little strategy and actual skill to the game. Currently I have decided that the game will be first person only (so if you want to stare at your character you either have to do it at character selection or while infront of a mirror). This was done for a few reasons, the first being the game genre and how it effects the "role playing" value of the experience. Your character will have equipment obviously and one of the pieces is for your "face" slot. This is where your different visors will go. Visors will place a full screen effect that can be toggled on and off over your entire screen. I don't want to toss out too many spoilers but here are two for you to chew on : Mechanical Vitals Detection Visor (this visor will render green brackets around the vital section of a mechanical object, if a successfull attack is made to this area it will do more damage/have a higher chance of critical hit), Humanoid Healing Aid (this visor will display the health of all PC's in your viewing vield and automatically target the one with the lowest health). I have also had some great idea's for dual monitor setups but have yet to put them into motion due to me not having dual monitors myself to test it out on. NPC encounters will be something that needs work on but I have designs for single player encounters, small group encounters, advanced small group encounters, and raid encounters. A system has been planned out that will enable a type of "instancing" to occur that will be used only when needed.

And if we were playing 7 card stud, 4 of my cards would be face up..

Due to my current server issues my actual game programming has been at rest for the last two days, which is driving me nuts. I really don't want to use Windows Server for my Apache/MySQL/PHP5/FTP server but this linux box is being a turd about opening up to the public.

Take care all.

#79 I just now got a little time to sit back and read some of this thread..

@ The Martian : I define success on how pleased your developers, artists, and community is with your game. And just a footnote, there is nothing wrong with MySQL as a database solution for any torque product. One of the tricks is spreading your server load, which Broke Ass Games clearly outlines in the first post. Knowing when to open/close databases, what databases need to be open at all times, and periodically scheduled backups and comparisons should prevent any anomalies from occuring.

#80 Not to rain on your parade Kyle, but TheMartian has worked with this in the industry and I'm pretty sure he knows his stuff.

You give some valueable points, however it's not as simple as it sounds. For instance:spreading your server load - It's obvious. The tricky part is how you do it.