I'm rewriting an MMORPG server engine using some rather esoteric elements (theoretically good but rarely used in practice), and having a bit of doubt. Some elements of this are “solid” — but the point of doing “Yet Another MMO Server” is to test out some of these concepts in production-level code.
I'm hoping someone here might have some practical experience, however, with PostgreSQL partitioning models, and be able to “lend” some expertise to this project, however.
Overview
There's a TL;DR version below.
- The core OS structure is a cluster of Linux instances, possibly on a cloud system. Stubs for monitoring overall system performance and spinning up new instances using an external API are planned for later implementation; for testing purposes, we're looking at probably using Amazon, but we are leaving the door open to make that a pluggable module for e.g. RackSpace and other providers, or even doing some naughty things with reconfiguring a pool of physical, “hot standby” servers on a private rack.
- The main MMO logic is based on a “pure” Entity-Component-System model. Entities “are” long integer ID's; Components are relational data records or sets (lists) of records (JOINable references); Systems are functions. Systems provide metadata about which Components they need access to, and method←→data locality across the cluster is based on a planner that anticipates these accesses, trying to keep running the same system(s) on the same host(s). That is: systems which access the same component of the same entity will tend to be on the same machine.
- The relational data store is backed by a PostgreSQL database. This was chosen based upon being Free Software as well as providing “better” (for our purposes) SQL/ACID services than MySQL in a few ways. Let's assume this is an immutable (it's already been argued about a lot).
- The server host instances represent a single game world, occupying a contiguous coördinate space: there are no discontinuities (e.g. levels; star systems; world instances) in the game world, per se. As such, the entities may be partitioned off onto hosts based upon dynamic regions, the “size” of which might change at runtime. It is a “given” that such regions can be more or less efficiently partitioned within SQL: e.g. we might define a “partition plane at Y=-10” and split the entities based upon their Y coördinates, or similar. The precise mechanism(s) to be used for this will probably go through some experimentation under simulated loads. Changing this partitioning rule would be a relatively infrequent event: perhaps a 5 or 10 minute timer might monitor server loads and attempt to determine a more optimal split.
- It is permitted for a server host to be a database server (clustered), game logic server, or (for cluster size=1 system) both. It's a “given” that we can spin up DB instances under control of the main planner system, and configure them in arbitrarily complex ways to manage joining the cluster and so forth. Potentially, this could include creating underlying RAMdiscs or partitioning rules or so forth.
- Thus, the data set for a given host might be some combination of a certain set of tables (components), but being only interested particularly in a given pool of entities, which were chosen by a separate criterion. For purposes of efficiency, we might store either a column on the
entitiestable indicating which “server locality pool” it belongs to, or a separate table providing that mapping, or something to that effect. - There's an innate assumption that the individual records contain sufficiently enough data to maintain integrity as long as the back-end database journaling isn't demolished. IE: as long as a transaction
COMMITs, we don't terribly care in the face of a crash whether we get the “before” or “after” image of any particular transaction. I think I mangled that concept verbally: put another way, we're not terribly worried, for purposes of crash recovery, whether entire transactions are lost, as long as we lose entire transactions. The possibility of losing a cluster host (e.g. machine catches fire) is handled in the planner level, and the area of responsibility of that host can be reassigned fairly quickly if we detect loss of heartbeat. - Almost every host will be writing about half as much as it's reading, which is a lot higher than many database systems are designed to expect.
Overview (TL;DR)
We have a bunch of Linux boxen with PostgreSQL. We have some functions that take in a subset of data that can be defined using a SELECT or VIEW running on those hosts, and writing out changes almost as much as they read.
Pure Theoretical Model
Here's where it gets flaky:
The Components map directly to relational tables and rows. For example, suppose there ends up being a Position component. The Entity ID would be a primary key on a table, and for consistency checking, also a foreign key to an entities table containing just a PK SERIAL8 field. The Position table then has, let's say, x NUMERIC, y NUMERIC, z NUMERIC columns.
Then, imagine a System for Gravity that will need Position, Mass, and Inertia components, and another System for Collisions that uses these Components as well as a PhysicalVolume Component.
In a perfect (i.e. performance does not matter) world, we know which Entities will be addressed by a System using an SQL SELECT with some criteria. Perhaps the PhysicalVolume might have a bounding box, enabling fast culling of Entities who either don't take up a physical volume, or who are clearly nowhere near any other Entity with which they might collide (some not-too-fancy JOIN of Position and PhysicalVolume). So, we have a System definition that enumerates: which Components from which it will need data, and a SELECT query to obtain that as a point-in-time, immutable record structure. Those records are fed in, one by one, to the System's run function, and it performs whatever changes are necessary. If the System writes to a Component that it does not read, we would have that declared in advance, to preserve data locality.
Reality Sets In
The problem, of course, is that SQL SELECTs from remote discs are not the sort of thing one wants to perform in every “frame” of a main simulation loop. Some of these systems may be running in excess of 10Hz.
Now, I know, “no optimization before its time,” but this seems like a “doomed to failure” model, unless there's a way to use that theoretical model in a realtime way.
As a further, necessary option for larger installations, the planner system would potentially also want to migrate “pools” of entities, most likely those that are “physically located” in a certain region of the game's world, onto individual hosts, to balance the load and keep the “cache misses” that have to hit any general-purpose back-end database reasonably low.
The Question (finally!)
Given
- a pool of data for
- a set of VIEWs and updatable VIEWs
- …which provide input sources and output sinks for:
- a certain set of Systems
- (represented as a list of tables)
- … as applied to an enumerated pool of Entities,
- (represented as a non-contiguous selection of
SERIAL8REFERENCEs)
…is there a reasonable way to create
- an in-RAM PostgreSQL instance (or similar)
- … which maintains primary authority for a the given data…
- without breaking general transactional integrity?
If this is an unreasonable design, my fallback concept is to essentially simulate the same effect by trying to pre-load the views into in-RAM temporary tables or something, although I haven't spent much time contemplating how poorly that might perform.
Any plausible alternative that could serve the underlying theoretical model is appreciated.
