Imagine a open-world of 500+ players with data changing as fast as 20 updates/player/second. Last time I worked in a similar MMORPG, it used SQL, so obvioulsy it couldn't query the DB all the time. Instead, it loaded all players from the DB to memory as C++ objects and used them. That is, it scaled vertically. Would it be possible to make that server horizontally scalable instead? Is there a database designed to support that amount of updates concurrently?
Test case of 500 players all communicating, that's 250K streams of information flying around at 20Hz. The internal bandwidth for that would be, assuming 100 bytes each message, about 500MB/sec. Sounds ambitious. Especially between processes.
If you segregate players to groups of 100, that lowers to 20MB/sec, and so on. Which is why MMOs have zones, and in those zones little bubbles of influence, and so on downwards until bandwidth becomes reasonable.
The original problem can be stated that if you have 10 people all sharing realtime information, but you want 500 all sharing, that's an exponential growth of communication links and how can we get around that. I'm afraid that there's no magic bullet that I've ever heard of that can magically make geometric progression go away.
Don't use a database to communicate, that's what messaging is for. Use the database to enforce transactions and store info you don't want players to lose. Most MMOs that I'm familiar with only update the database with dynamic player info every 1-10 minutes, or at handy points like zone transitions or entering "safe" zones in the design.
You may have to redesign the game's need for every player, no matter how far away, to have realtime updates of every other player's backpack contents.
Also change the update pattern from 20Hz to a speed based on distance, someone 1 mile away does not need to know that you moved 1 foot at exactly 230.6 seconds, then another foot at 231.4 seconds, they can deal with you moving 15 feet every 10 seconds.
Use area of interest filtering. If a world is broken into 3 servers, and the area on server 1 is nowhere near the area of server 3, there's no reason for them to share information about entities at all.
Likewise, on a single server, only send relevant information to clients. If player A is on the totally opposite end of the map from player B, there's zero reason to send updates about B to A, or vice versa.
When you have multiple servers in a continuous world, you will have entities near an edge on server 2 that are close to entities on server 1. You can send updates from the "authoritative" server for an entity to the other server (when appropriate), and likewise forward any messages to the authoritative server as appropriate.
Yes, in this case, one server will be slightly out of date for particular entities. Don't try to solve that. Just deal with it. Assume that entities may be a little out of date. Do any logic that needs up-to-date information only on the server that authoritatively owns the entities. When an entity affects another, send a message and assume that it may take multiple game logic ticks before it is processed and your view updates.
This design also makes it much easier to thread a single server. No entity should directly modify another, only send messages, and the local per-server/per-thread proxy caches should be assumed to be slightly out of date.
For example, if entity A attacks entity B, don't check the life of B and then send a death message if it hits 0. Just send a "damaged" message, let the authoritative server for B handle it, and then handle any "entity-died" message sent out by server B later if entity A cares about that.
The same applies to any large, scalable non-games application. A central database is not a magical instant-sharing technology. Two servers must communicate with messages, asynchronously, in batches, in order to maintain high throughput. Hence the popularity of technologies like AMPQ and the like. Databases are for storage and support synchronization out of necessity allowing them to be used for communications, not because they are themselves meant for synchronization or communication.
Don't think of the database as some kind real-time shared world model storing everything about everything at all times — as you've noticed, that can't possibly work.
Instead, treat the database more like an automatically-updated save file: you update the database only occasionally, such as when players log in or out or move from one zone to another, or whenever something important happens that you don't want to be lost in case of a server crash.
The actual real-time world state should be held by the game servers, in memory, just as in your original example. Now, the trick for horizontal scaling is that not every server needs to know everything at every moment. For example, if player A is playing in zone A on server A, server B running zone B doesn't usually need to know what player A has in their backpack — and, if it does need to know that for some reason (say, because player B in zone B casts some kind of remote spying spell on A) it can just ask the other server for that information.
This does require you to assign clear responsibilities to the servers, so that when server B wants to know about player A's backpack, it will know which server has the authoritative information on that. You will also likely want to include some kind of update subscription mechanism, so that e.g. server B can just tell server A "I have someone spying on player A, keep me updated about everything they do until I tell you otherwise." You will probably also want to include some kind of global broadcast system for important global events that players may need to know about no matter where they are; of course, such events should also be recorded in the database, but having them actively broadcast to all server means that the servers won't have to keep polling the database for updates.
Other responses have done a good job of pointing out how to use a database, and not to use a database for communication. One other aspect that you might look into is to categorize your updates based on how the information needs to be communicated to other entities. Rather than scope communication to servers, you could distribute your messaging and use pubsub mechanisms for communicating updates between entities. For instance, you might treat location differently based on who's close to you:
- Precise, real-time location might be useful within radius R
- Less-precise, less frequent location updates might be useful within radius 2*R
- No location information might be needed beyond radius 2*R
You might communicate location information for an entity by periodically scanning for entities within radius 2*R (or some multiple of that based on update rate and maximum velocity of an entity), and subscribing the entity to the other entity's precise or imprecise location feed.
You could have different strategies for different types of information, group common things into the same message queues, or have different queues for messages that need to go to different entities (or just send them to the broadest set of entities and have messages discarded if they're not useful).