22
\$\begingroup\$

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 ? enter image description here

\$\endgroup\$
  • 11
    \$\begingroup\$ Have you done any benchmarks? What makes you think database operations would be an issue for your game? \$\endgroup\$ – congusbongus Jun 27 at 1:52
  • 3
    \$\begingroup\$ If your game state actually relational? Are you sure you want a SQL database? \$\endgroup\$ – Stop Harming Monica Jun 27 at 9:27
  • 6
    \$\begingroup\$ Also please read how to handle passwords using hashes. Or at least notify the user that their password is being saved as plain text, so they are aware not to use another secure password. \$\endgroup\$ – TomTsagk Jun 27 at 9:52
  • 24
    \$\begingroup\$ As a side note, if you want to build a commercial MMO and you're asking questions like this, you should probably start with something smaller. MMOs are incredibly hard to do at all, let alone doing them right, and they're not that great from a business point of view. If this is just a personal thing and you want to go high-player-count, start with something like 64 players, then work your way up. It'll be much easier than trying to design the "perfect" architecture from scratch with no experience. See this. \$\endgroup\$ – Nic Hartley Jun 27 at 17:38
  • 1
    \$\begingroup\$ You can write a general API that is storage agnostic. Then write an adapter which caches data in RAM for delayed storage. Or you can write adapter which caches data in temporary file before to flush it in SQL DB. That way you can switch on and off caching to find out which is best. You don't need to write too musch extra code, If you your architecture is built right. \$\endgroup\$ – 0xC0DEGURU Jun 29 at 15:16
36
\$\begingroup\$

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.

\$\endgroup\$
  • 10
    \$\begingroup\$ Regarding "tuning the save interval", the last MMO I worked on had a save interval based on the number of active players. We knew it could handle saving X players' data per second without noticeable bottlenecking, so we just had it save a bit under X players per second on a rotating basis. It usually ended up being a save for each player every two minutes or so on busy days, and maybe twice a minute during the slow times. \$\endgroup\$ – Meanbits Jun 27 at 13:35
  • 4
    \$\begingroup\$ "Restartable save queue" sounds like a journal, as implemented by many filesystems and database engines? \$\endgroup\$ – grawity Jun 27 at 16:31
  • 6
    \$\begingroup\$ These issues are not unique to MMOs, or even video games, at all. Nearly every ORM in existence provides some sort of caching/batching mechanism. There's no need to roll your own solution. \$\endgroup\$ – BlueRaja - Danny Pflughoeft Jun 27 at 18:24
  • 3
    \$\begingroup\$ If you do end up going with saving to a disk at a certain interval, I'd recommend designing a system where things can be saved immediately, for when people get a rare drop/die/etc. That way, if there is a server crash or something, people who accomplished something big will not be too upset. \$\endgroup\$ – Jon Jun 27 at 19:26
  • 5
    \$\begingroup\$ @Jon, and then you run into the problem of people being able to duplicate objects. Ideally, you save the entire game state periodically as a single transaction. Once you start saving piecewise, you wind up with situations where a crash can cause inconsistent data; players who can trigger a crash on demand, or even predict when a crash is likely to occur, can exploit this to their advantage. \$\endgroup\$ – Mark Jun 27 at 19:52
15
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Thanks, but what about players movements for example, if a player moves from his current position : x:10,y:10,z:10 To x:11,y:11,z:11, should this be saved in the database right away ? what if the player keeps running, this means we save his current position every 1sec or less, and if there are thousands of players, that's millions of queries to the DB every second. Is this how it works ? \$\endgroup\$ – dwix Jun 27 at 9:52
  • 2
    \$\begingroup\$ The position of the player should be saved very rarely in the database. You would only want that on special occassions like opening the menu, starting a campfire, resting etc. Depending on the game it won't even be saved that detailed. Some games make you always spawn in the nearest town etc, so that would save the hassle of saving the position too. But that drifts into opinion based. \$\endgroup\$ – Nico Jun 27 at 11:52
  • 1
    \$\begingroup\$ @dwix in the case of the game I talk about, the position of the player is not even saved. If you log out and log in, or if the server goes down, the player will be placed back at safe known position (edit: it is a private instance, "house" if you will, but it is the same for everybody). However, some games do keep the exact position of the player, even across accidental disconnection. To do it, the usual aproach is store that information less frequently. Regardless, you do not what the server waiting on the database. \$\endgroup\$ – Theraot Jun 27 at 12:04
  • 1
    \$\begingroup\$ @dwix when talking about the position of the avatar, you do not really care about historical values, do you? Well, if the database is a bottle neck, then throttle the data. Instead of storing each server tick, store less frequently. It means that the database will not have the most up to date value, but it will not impact the performance so heavily. Meanwhile the server will still keep the update value in RAM, and that is what it works with. Addendum: honestly I would advice against storing the positions, but some games do that. \$\endgroup\$ – Theraot Jun 27 at 16:14
  • 1
    \$\begingroup\$ @dwix see meanbits comment \$\endgroup\$ – Theraot Jun 27 at 16:18
7
\$\begingroup\$

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.

\$\endgroup\$
0
\$\begingroup\$

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.

\$\endgroup\$
0
\$\begingroup\$

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)

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.