Persistent Characters and MMORPG Network Architectures

#21 IMO, Postgres is better than MySQL, even if it's just for transaction support alone.

#22 MySQL has had transaction support for quite some time. There are a number of mainstream MMOs that use MySQL as a back end database server.

While you don't really need a commercial license for most applications of MySQL for MMOs, you will want to look very carefully at the licensing nonetheless.

#23

Quote:There are a number of mainstream MMOs that use MySQL as a back end database server.

since ive worked on a bunch of the latest mainstream mmos I can say that I dont agree with that statement, although there is one large one that does use it and has had lots of problems.

not saying mysql is bad, it has come a long way, but it has always been far from a high transaction
volume DB. But I will say they are catching up. For small persistent games it works just fine,
for large scale, high transaction rates, its not there yet. It doesnt have the backup/replication
functionality needed, it doesnt scale well as the database gets very large, but its on the way.

an alternative might be something like postgreSQL (http://www.postgresql.org/) which is probably
a better choice and its still free.

I believe mysql will eventually catch up but its just not there yet.

the current large MMO games use everything from sqlserver, oracle, postgres, and even custom
home grown file system wrappers(lite weight db). One big one even uses mysql. But even
the biggest one domestically WoW, uses Oracle(expensive on large system, but smaller cpu boxes
its the same price as sqlserver).

so if I were to pick one, even for a big game, I would try out postgres, its the big boy of the
free DB's

#24 Why does everyone freak out when you talk about using SQL Server if some of the mainstream MMOs are using SQL Server?

#25 The MySQL transaction support is a recent (well more recent than PostgreSQL) achievement.

What PostgreSQL has lacked until now is replication. The database has been ACID compliant for a long time now I believe.

I also believe that you _DO_ need to buy a license for MySQL to use it in a closed source application. If you are making and open source app then all you need to do is provide the source code if asked.

Steve

#26 Jonathan,

Why do you say "Why does everyone freak out when you talk about using SQL Server..."?

I didn't read a post saying anything about SQL Server. I personally only have experience with MySQL, PostgreSQL and some Oracle but not Oracle RAC. And this all on UNIX servers.

Steve

#27 Sorry, I didn't mean to hijack the thread, I saw Martian state that some major MMOs were using SQL Server and it reminded me how anytime I say I'm thinking of running an MMO on a Windows/SQL Server platform, they tell me how it's a horrible idea and Linux/MySQL/SQLite/etc are the way to go.

#28 Well, I think that is because many don't really like Microsoft products.

I have no real experience with Windows servers or MS SQL Server so I can't say one way or another. I know I've got some Sun servers with uptimes over 1000 days. That's no downtime or reboots. :)

Steve

#29 It's more of a historical thing than anything else: if you were to ask the question 10-12 years ago: should I use Windows or*nix for a high uptime server, the question only had one answer. That "general knowledge" in the industry persists for quite sometime, even though the (at the time valid) reasons behind it haven't.

At this point it really is based on your company's infrastructure and platform experience.

#30 That's what I was trying to figure out, because every time I say something like 'do you have benchmarks to prove an MS solution isn't as good or security papers or something that proves MS solutions are a bad idea for games or MMOs?' people never have a solid answer. Sure, they'll SAY MS solutions are worse and slower and less secure, but no one ever PROVES it to me. *shrug*

#31 Don't forget firebird!!!!

#32 Well, when you work in a large IT department (fortune 500) with both Windows/UNIX you can get a feel for which one has more issues. Our teams are separate, however, we still know when things happen to one another.

And like Stephen Zepp said, a lot of opinions are based on historical data when NT wasn't quite as stable as Windows 2003 server may be or not be. :)

Steve

#33

Quote:
and it reminded me how anytime I say I'm thinking of running an MMO on a Windows/SQL Server platform, they tell me how it's a horrible idea and Linux/MySQL/SQLite/etc are the way to go

um lineage 1 and lineage 2 which have nearly 8million subscribers(10 million depending on the time of year)
all run on windows, using, SQLServer.

COH/COV all runs in windows using SQLServer.

nothing wrong with SQLServer, its actually a pretty decent and reliable DB. Nothing wrong with running servers in windows (and Im a linux guy so thats a big compliment) latest versions of windows advanced server work perfectly fine. have to remember people think all the problems with there windows desktop translates into problems with a real windows server(it doesnt) on a windows game server you not constantly installing and uninstalling crap, drivers, trojans, spyware, yadda yadda. A plain vanilla advanced server with just your game app running on it, is actually surprisingly reliable. more realiable then the hardware it runs on which over time fails much more often the the OS.

if you have the money SQLServer is a reasonable alternative to something like Oracle, works fine, scales ok,
and can handle fairly high transactions per second in real world applications.

no argument there. my only comments where towards mysql as a primary large scale, high transaction DB.

as for linux versus windows, thats almost a religious argument, but the fact is you dont have to use linux to have a mainstream world wide MMO game. its a fact, games have been there done that and not one has complained that it was a bad choice, in fact operations usually loves it because its much easier to administer
and deploy homogeneous OS's then mixed bag deployments. dont need different sets of tools to administer either.

but in the end you should use what your comfortable with and what you team knows best. both ways(windows or linux) works so do what your team knows best.

as far as windows not working or is less secure or whatever, its a myth, stop living in the 90's and wake up
they have come a long way.

Quote:
I've got some Sun servers with uptimes over 1000 days. That's no downtime or reboots. :)

no arguing that sun stuff rocks and is designed for 24/7 uptime, but its freakn expensive and certainly more reliably then linux running on some pc. but the cost usually outways the uptime concerns and with blade
technology now even pcs can have amazing uptime, something goes wrong swap out the redundant blade
and move on.

but I highly doubt many indie or even well funded projects are going to use suns so thats probably not an option for most.

#34 Well put =)

#35 My *ONLY* reason for suggesting commerical software was approaching it from a "begin with the end in mind" perspective based on where it is we plan to go.

But if we go back through the thread to address the topic, here's the concepts that really might be of help to people:

#1 - Windows or Linux is akin to Chocolate or Vanilla in the 21st century for database management. Argument is more suited towards personal preference rather than actual technical deviation.

#2 - Where you want to go with your project should play more heavily than perhaps it currently does in initial technology design. In most cases, what you don't pay for up front you will pay for later anyway, regardless of the technology selected. PLAN PLAN PLAN and you could save yourself some money no matter how you start off.

#3 - Make your development code flexible enough to accomodate the growth of not just your project, but the technology you have available to implement your project. What is not of great importance now could be something in dire need in 6 months.

#4 - Let's face facts... Few people here have brought a game to market, much less a fully developed MMO. In the VERY least, the most prudent course of action is to find a commerical game that approximates your idea and find out what THEY did. It's damn hard to argue with success.

Good thread... I picked the right week to troll.

#36 On Bryce's last note:

Little known industry fact: obviously, many large scale MMO's have seen issues with their databases--from EQ in the early days to ShadowBane, WoW, and many in between, they've stressed the breaking points of even heavy duty commercial databases, and one big issue has been found:

Commercial databases are not optimized for the millions of transactions at extremely small transaction size and extremely high transaction rate. Many of the neat and advanced (and very important) features such as transactions themselves, rollbacks/journaling, etc. actually cause issues with extremely high transaction rates. All of this is of course debateble, but I think on the whole anyone can agree that db design and management can easily be the most difficult aspect of a true MMOG.

Which leads me to my point: some of the bleeding edge devs are looking closely at going back to basics when it comes to data persistence: in many ways, flat files can actually increase total performance for a large variety of persistence requirements, and with proper development and design can achieve higher total throughput at the very high number of transactions per second MMOG's entail. It's not useful for every task, and in some cases it's simply used as a buffer to a real backend db solution, but simply tying a database engine (even a good, or great one) isn't the end-all solution for the momumental amount of transactions in a truly massive game.

Just food for thought!

#37 It should also be noted that some of the most succesful MMOGs have used MySQL as their database solution.

#38 As seen on MMORPG theres quite some pw's/mmog's out there and more in the pot, so I assume that most here are aware of the fact that some of them uses some of the backend solutions.....

It would be much more interesting to follow the threads initial path, and hear what people here actually have experienced torque related using DB's of any kind as backend/storage for their games/projects!

We plan for a postgres solution, because we think that MSSql, Oracle, DB5, Siebel, etc. are overkills(budgetwise for us), and our experience tells us that MySql have not the power in its Views, Transactions etc...

*My old COBOL teacher allways said "Move corrosponding, data to new position". He never imagined the speed and amount of small transactions like todays creditcards or mmorpg's I bet!

#39

Quote:Sorry, I didn't mean to hijack the thread

Dude, this is exactly why I posted this! :)

Got a question.
I use SQL for projects but I'm still very "green" to large scale deployments.
Is it better to use ODBC and take the speed loss (internal LAN) so that I can choose different databases as I need to scale up or pick it now and be stuck with it?

Ari

#40

Quote:
It should also be noted that some of the most succesful MMOGs have used MySQL as their database solution

oh yeah which ones? The most successful I know if, lineage1, lineage2, WoW, UO, Everquest, etc
didnt use it, they used SQLServer, Oracle, and flat files(the older ones) how about
an example of a "successful mmo" that used mysql? (and you may need to define "successful").

as for going back to flat files, I personally believe thats a bad idea, you lose all the advantages that a database can give you(replication, backups, referential integrity, concept of transactions, stored procedures, concurrency handling etc) and believe me when I say you wont be able to offline load your flat files into a db(which is what I know some of you are thinking).

instead you need to learn alternative architectures, you need to fully understand what you can take advantage of with a particular DB.

I disagree that commercial DB's cant handle large volumes of transactions (even ones that are small). just because a few of the large MMO's had problems doesnt mean its because the database cant handle it.

Ill tell you why they didnt work, 99% of the time a MMO development team doesnt hire a functional expert in DB technology until way late in the project. They think they are making a game and then they are going to just dump the data to a database. wrong.

your making a database application, and you need to start off thinking that way. a database application that happens to also be a game.

there is very limited DB experience in the gaming industry compared to say graphics programmers, or client programmers, and it shows.

These commerical databases can handle WAY MORE then what MMO's put out.

There problem is they didnt know how to use it, there architecture is weak and there SQL skills are horrible(I know I get contracted all the time to come fix there stuff after the fact).

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).

case in point about understanding your DB. Im a knowlegable Oracle developer, I can make Oracle easily outperform any other DB you pick, easily, why? because I understand some serious tricks about Oracle and I have done it before. Other database have tricks as well, learn them, you learn all the tricks about your video cards why not the DB? you figure out how to get higher frame rates, you should also learn how to get
higher transaction throughput.

its why you find an expert, you get that DB expert involved from the ground up, you let the DB expert feedback to the game designer (yes the DB is going to place some constraints on the game design *gasp*, or at least it should unless you want to spend millions just on DB hardware).

every single MMO game out there has made some or all of the above mistakes. Even WoW, they are not pushing what a commercial DB can do, there pushing there weak DB design to the limits (I know they were looking for people like me for quite awhile to come fix there stuff).

if it was as simple as pushing the limits you could fix it with bigger, faster hardware, but when its really broken even that wont help. Blizzard knows how to make games, they dont know how build database applications. so they spend a lot of money on shards, and database people to come in and fix there broken implementation. fortunately for them they have the money to do so.

but I do agree that if your making a MMO game you really need to focus on the persistence side of things as its the piece that has bitten even the biggest MMO games in the butt, and costs them hundreds to millions of dollars.

oh and please dont revert to using flat files, there are a few flat file MMOs out there just hating it because they cant do anything with the data, marketing cant get an info, its a pain in the rump to fix bad data, you have no referential integrity, and your not likely to build a flat file database thats better then whats readily available (even for free). dont waste your game development time writing your own database, that would be like writing your own game engine when you can just use torque :)