Hello everyone here, I am very interested in the architecture design of MMORPGs. I have read a lot on this subject and if the management of a big continuous world using several servers is fairly easy, I have found very little about the database usage. If there is no real problem when dealing with a small amount of players, it''s not the same if you think in term of scalability. So now, let''s imagine a game allowing, let''s say, 100.000 players at the same time on the same world out of a number of 1 million potential gamers (that would be achieved by having 10 to 20 game servers, each dealing with a part of those people). In this case, the underlaying database would be a very large one ! But my guess is that only one database wouldn''t be able to deal with the number of transactions needed in this case... The only solution then must be to have the "database" served by more than one server ! Then has anyone some clues on the means to achieve that (I have already thought of separating the login/billing database from the real game one, but I think that the problem still remains for this last one) ? Thanx in advance I can do anything... and I can do it better than anyone... Be like me : just be the best ! ;-)

Depends on your SQL server.

With MS SQL Server I would have replicated boxes in front of the datastore serving =]

The only time you are going to be doing a transaction is when people login/logout. I mean theres no point submitting queries and updates whenever a player drops an item or their hitpoints change. All that stuff should stay in main memory (yea you better have a lot of it) and only change "on disk" when they start/save/quit. Each character is independant of one another so you could easily distribute them evenly across a number of machines and databases. The transactions to intially load and store character data isn''t really time critical either.

This is a huge question that requires a lot more information about your game in order to make an informed suggestion.

For instance, do you plan to store dynamic state data in the SQL database during run-time? In my opinion this is a bad idea. I prefer adhering to low-level temporal/locality driven database design so that my boxes, as well as server processes can be distributed (and re-distributed as necessary) along with the data they serve.

If you plan to use the database for information that is not so fluid (e.g. account/payment information), then yeah, you could conceivably utilize one database to serve many, many players. But if you are thinking about saving dynamic character stats in a DB everytime they change (i.e. a player lost some hitpoints in a battle) then you are going to need a much more complex infrastructure than what has been described. I would suggest local disk caching (RAID, or some other redundancy) and less-frequent round-robin database writes. It keeps your database from being overwhelmed, in addition to preserving data locality with server processes.

Good luck.

You can search 1 record from 1mil records in database with ease, just a flash of seconds.

However, if u got 100,000 online players, the network is the bottleneck, not the database.

Assuming a typical kind of MMORPG, players playing characters, interacting with NPCs in worlds made up of zones... Depending on your server architecture a large single database server could handle the load. You''d want to throw a lot of hardware at it but it''s viable unless you plan on doing something atypical of an MMORPG.

The trick is to not store anything in the database that you will need to access in a time critical situation. Or to put it another way, anything you store in the database that will need to be accessed in a time critical situation should be loaded at initialization of an object and stored on the game server in memory until it''s needed. Think of the database as nothing more than persistent storage and you shouldn''t run into any problems.

The idea of a good database is to store loads of data that can be retrieved very quickly. If you design it properly and use indexes whereever appropriate it should not a problem.
To suggest that the databases only stores it on disk and to load it all into memory defeats the idea of a database. This is ok for things like a level in BSP format, but not for players, inventory, items etc.

Excessive database queries will slow down your application no matter what it is, game or otherwise. A game with hundreds of thousands of users will slow down to a crawl if all state information is constantly updated and retrieved while playing the game. As someone suggested earlier, ideally the database is read from ONCE and updated ONCE when the player logs in and out.

insert witty signature here

What you really want is for your data to be periodically dumped off to whatever persistent medium you choose (file, database, etc.) Otherwise, you are going to annoy an awful lot of people when the server crashes (and it will). Set up your data in RAM so that it can be tagged with a dirty flag and timestamp. When the flag is set and the timestamp has expired, the data should be written as soon as possible (preferably while CPU load is low).

In the early beta of AC2, we would lose approximately 10 minutes worth of gametime whenever the server crashed. Even this was annoying, but if your server is stable, it might be acceptable.

In Everquest ive seen cases where people have lost 30-60 min of work as a result of a server crash.

Whenever a player zones in EQ the database is updated to the current state. Also periodically (around 10 min or so I think) the player is saved off to the database. Finally, any time the player makes a trade, the database is updated.

I worked as a network admin for Paypal for a bit and for the most part all 10million+ users were all served off the same database. Now you can imagine how many transactions a site like Paypal has to deal with and the load on the server never really got above 0.3. The server the DB ran on was fairly beefy but there were still plenty of servers out there that were superior to it.

Limiting the number and frequency of transactions is certainly in your best interests, but I think limiting it to one read at login and one write when the user logs out is way overboard. 100,000 simultaneous users is quite a lot of users even for 10-20 servers sharing the load and I think the actual game servers themselves will run into load issues long before the database does. Also if you have 1million users playing your game all paying 10 bucks a month to play, you''re going to be able to afford some bomb ass hardware. =)

-=[ Megahertz ]=-