How does mmorpg store data?

Question

I want to use sql database in my server.exe.

lets say 1000 users online. And players will change their data when they play. And server need to save these updates.

But how ?

i think there two way:

1) server will save in ram at run time and sometime or hourly server will write data (update) to sql database from ram. But if electricity goes or somehow the server shuts down , updates won't save. of course i don't want lose updates.

2) server will save updates in database at run time. but what will happen to speed ? I thought of a method. i will show you below with image. Can i get enough speed for 1000 online players with this method ?

Answer 1

Most MMOs (or projects using similar architecture) I've worked on or know of use the first method: the game servers work mainly with the RAM on the machines running the server processes, and only periodically serialize the relevant game data to a SQL database for archival (if one is used at all).

The problems you note regarding power failure are valid, but less likely than issues like crashes in the process (uptime reliability is generally one of those things you'd pay the datacenter for). But still. You mitigate those issues through things like tuning the save interval (so a failure only costs at most a few minutes of progress), distributing the save queues across multiple machines, aggressively scoping the amount of data that needs to be saved, and implementing restartable save queues.

Generally speaking, using the SQL server itself as main memory is going to be unpleasantly slow (and cumbersome). I don't see enough detail in your proposal there to be able to say for sure if it would work for only about 1000 players -- it would probably be fine. But I don't believe you can make it aggressively scalable.

Further, it doesn't solve the problem. A power failure during the save process will still lose or corrupt data unless you do the work to implement the same kind of restartable save queue (which even then, that only severely reduces the likelihood of catastrophic data loss, it doesn't prevent it).

I'd recommend you go with the first approach.

Answer 2

1000 player may or may not be a problem. It depends on how often you need to update the database. However there is a simple solution: put the database on its own server.

---

I had a peek at how the database system works a game that people would call an MMO-Lite – which one I will not disclosure – yet I can tell it consistently has more than 1000 players, this is the abstract:

• They use a document based "No-SQL" database for private character information (inventory, equipment, similar).
• They use a more traditional relational database for public character information that is updated rarely (name, achievements, etc...) and statistics.

Using a document based database turns out to be a good idea for performance in this case. It is good for accessing the data, even though it is bad for doing joins and aggregations... but they won't be doing that with the data that is in there.

On the other hand, when they need to do something like change an object with another object for everybody, the need to run a background script that goes character by character and updates them one by one, it takes noticeable time.

---

The data of the character exists both on the client and on the server, and the system is designed to keep them in sync, doing the same operations on both sides.

Now, when a player does a transaction with the system – say exchange an item for another via NPC or similar – this has these stages:

• The clients checks if the operation is valid
• The client sends the operation to the server (async operation)
• The client updates its model in RAM
• The server checks if the operation is valid
• The server updates its model in RAM
• The server updates the database (async operation)
• The server tells the client that the change happened

Notes:

• The database updates, although async, are done right away. If for whatever reason the game server goes down, not much is lost.
• Performance degradation is not a big deal thanks to placing the databases on their own servers.

Trades between players are handled similarly, with extra rules to prevent players scamming other players, and extra traceability in case a transaction need to be undone. I heard there are special logs of all transactions that are kept for some time (to solve issues when somebody complains), I do not know more of how that part works.

---

Sometimes something goes wrong, this has two possible outcomes:

• If the error happens right away, the server will tell the client, which will revert the operation (the player sees the operation undone).
• The UI says the player has an item, but the server does not agree. If the user tries to use it, it will ask the server and fail, rendering the fake object unusable. The false object goes away when the inventory is reloaded, which is used as an opportunity to synchronize the model.

In either case, the client is aware of the error, and will log it along with all that the player was doing. Logging client side is happening all the time. Then, on logout, if there was an error, the client sends the logs to the server.

---

This game does not use the traditional daily or weekly maintenance down time that most MMORPG has. That scheduled maintenance is often used to reset counters, timers, run database procedures. They are also the opportunity to swap server versions for an update.

Answer 3

Both approaches are used with MMORPGs. Keeping everything in memory and periodically check pointing it to disk seems to be the most popular option, at least for older games. It has the advantage of being fairly simple to implement and scaling fairly well, but making it reliable is completely up the to the developer. SQL databases provide ACID properties that make reliability easier, but are overall more complicated to implement well and can have problems with scaling.

EVE Online is an example of an MMO where everything is stored in an SQL database. I think it peaked at something around 60,000 simultaneous users, and has had to dedicate some expensive hardware to the database servers over the years in order keep up with the load. However EVE stores a lot more data per user than than most MMORPGs and has a lot more frequent and varied transactions. The database's ACID properties allows the entire massive and complicated database to always be kept in a consistent state.

For example, consider the case when someone gives an item to another player. EVE's database guarantees that even in the event of a crash or power failure that the item ends up in only one player's inventory. That is, the database transaction that removes the the item from one player's inventory and adds it to the other player's is atomic. The transaction either fully completes or doesn't happen at all. You can't end up in a state were the item exists in neither players' inventory or in both.

An MMORPG that keeps player data in memory and periodically checkpoints it has to implement this atomicity itself. One way to do this would be checkpoint every player's data at the same time, ensuring that new checkpoint is fully committed to disk before its considered the most recent checkpoint. The game would also have to ensure that player data can't change while it's being checkpointed. With a large amount of active players, the challenge becomes doing all this without causing a delay long enough that the players would notice.

Don't underestimate how important consistency is. When you're developing your game its going to crash a lot. You don't want have to track down why items are disappearing and/or duplicating, not when the problem is an unrelated bug is causing the game to crash at a bad time. Moreover when your game goes live, it'll crash way more than you expect it to. Your server that was operating perfectly under testing, will suddenly expose numerous bugs under full load and players doings thing you didn't expect them to.

Note that most MMORPGs use SQL databases for account related information even if they don't for actual game data. Even more important than keeping the game state in consistent state is keeping the billing state consistent. Worst case for the game database getting corrupt is that you have to restore the database from a daily backup. Worst case for account database getting corrupt is that you go bankrupt because of all the chargebacks.

For your project you can probably go either way. Having 1000 simultaneous users probably won't push the limits of what an SQL server can handle on a commodity PC these days, but a lot will depend on the nature of the transactions. If you're familiar with SQL and relational database design then this can work for you. You'll want to minimize the transactions as much possible, for something like player current hitpoints you might only want to periodically save them to the database since stats like these don't necessarily need to be consistent. (In PvP game they might though...) Don't store things like monster HP in the database at all, in most games only player related data is persistent.

On the other hand if you're not an SQL wizard you might find keeping all data in memory much simpler to get working reliably. SQL databases make consistency and reliability easier, but its not automatic. A badly designed database can perform poorly and lead to inconsistencies. Transactions won't be atomic unless you make them so. If you don't use parameterized statements religiously you'll open yourself to SQL injection attacks.

Answer 4

Various games store things in different ways. Often, game companies create some way to do this, and most of their games use the same way. Of course, different studios often use different ways. SQL is certainly used for games, e.g. CCP (EVE) has (or at least had) a network of SQL servers, I am not sure how they do it now. Others, just use lots of files.

Maybe start by creating an agnostic "data broker mechanism", to handle various game data transactions? Since you are just testing anyways, create a mechanism that enables you to not need to care too much about the storage, from a standpoint of the game itself. Meaning, concentrate on how, from the host application, you are going to handle this.

Personally I think it would be a great asset if you could switch the actual store, without having to rewrite the game and the broker itself. Just switch to another module with the same interface for the broker to talk to.

Storing data in itself is likely not going to be an issue, per se. Efficiently shipping data, to/from that store, between host and all the clients, could be trickier. Do I ship the entire player data set, or if I break it down into parts, what granularity do I choose to partition by, etc. Which one is more resource intensive in which situation, etc.

One format to ship the data in, could be XML. That way you can more easily be dynamic in how it you can chunk it up. One character vs multiple characters, or one item vs a collection of items, etc. You could then either "store" the XML as XML (in SQL), and/or have SQL distribute it in a more transactional fashion from the XML, to how you want the data actually stored.

Another way is binary, which is more efficient in terms of shipping, but can incur more cost in other situations.

With 1,000 clients, you could start with, and easily store 10 MB per client and only use 10 GB of effective RAM + add some system administrative RAM for managing that data, say another GB or two. You could keep that in RAM on the host already in data structure ready for use. And load/save dynamically, depending on who is online, in various frequencies depending on activity, etc.

You could even store each client's information in a separate file, and so on.

Answer 5

Keep *online* players in ram, and push em to a database (SQLite? MySQL?) when they log out. if you're worried about IO stalling during logout, give each logout it's own thread, don't do it in the main/game thread (actually i keep a dedicated player thread for every online player, it made the networking code so much easier than doing the async network io approach)