3
\$\begingroup\$

I've been trying to build my entity system based on pure SQL data storage as explained in this post series. The idea is that every entity or component is stored in an SQL table. I created my framework to manage that, only to realize that many SQL queries took more than 10ms, which is unsuitable for use in a game loop.

I first thought the delay was I/O-bound, so I created an in-memory MySQL cluster. This was better, but still not real-time enough for a game loop.

Has anyone built an SQL-based entity system? If so, how did you manage to keep query times manageable?

If an SQL database isn't feasible for performance issues, what data structure could I use to retain the entity system idea and the possibilities of direct SQL requests on components?

I've heard of many MMOs running on SQL. Do they use it for only part of the data or a full entity system? How to separate database-stored and classical memory-stored structures in that case?

Edit for clarification: Yes, all game data is stored in the database. If say I run my physics system it will first get data from SQL, then do its stuff, then insert the updated data back to the database.

\$\endgroup\$
11
  • 1
    \$\begingroup\$ maybe worth explaining your infrastructure a little more, are you trying to write to a db on each tick of your game? \$\endgroup\$
    – RoughPlace
    Commented Dec 12, 2013 at 2:12
  • 1
    \$\begingroup\$ Yes. The idea of this entity system is that everything is stored in SQL. That means that running physics on objects implies getting physics components in SQL and then updating them to the DB. I thought storing the DB in memory could be powerful enough, but it seems not. \$\endgroup\$
    – nialna2
    Commented Dec 12, 2013 at 2:14
  • 3
    \$\begingroup\$ Queries should be able to run faster than that. Off the top of my head, lack of indexes, running remotely, poor aggregation, and inner sub-queries could all be culprits. But really, is there really a need to query every frame? That in itself suggests you're doing something wrong. After you query once, why does the data go straight to the garbage collector? There's no point putting the whole db into ram when you could just park the data in game's memory. \$\endgroup\$
    – Seth Battin
    Commented Dec 12, 2013 at 2:19
  • 1
    \$\begingroup\$ Just because something is possible (and this is) doesn't mean it's a good idea though. \$\endgroup\$
    – user1430
    Commented Dec 12, 2013 at 3:11
  • 2
    \$\begingroup\$ Yes but this approach is not how modern MMOs make use of SQL. It's an academically interesting idea but in practice, not an ideal choice. There have been other discussions of this topic here before, if you are interested. See: gamedev.stackexchange.com/questions/40212/… and gamedev.stackexchange.com/questions/16122/… and gamedev.stackexchange.com/questions/40215/… for example. \$\endgroup\$
    – user1430
    Commented Dec 12, 2013 at 18:03

2 Answers 2

Reset to default
6
\$\begingroup\$

Database reads are usually via network and from hard-drive. That means database queries will always take a few ms, no matter how simple they are. Databases can get faster with in-memory techniques and smart database designs with good indexing concepts, but their speed is often still insufficient for real-time processing.

Blocking your process while a database-request takes place is a bad idea. Instead of that you should always try to:

  1. Request data asynchronously. Start the database query in a separate thread of execution and proceed processing the rest of the world while you wait for the response. Many database frameworks support asynchronous queries with callbacks out-of-the-box.
  2. Keep data in memory. There is no need to SELECT attack FROM players WHERE id = 23876 whenever a player attacks. Just parsing that SQL string takes ages compared to directly reading players[23876].attack. Load all of their character-data with a single SELECT * FROM players WHERE id = 23876 when the player logs in and store all the data in a data-structure on the game-server. Likewise, don't do an UPDATE players SET hp=hp-21 WHERE id = 23876 whenever a player takes damage. Do the modification in the game-servers memory and save the whole data when the player disconnects (maybe do regular backup-saves so you don't lose data in case of a server-crash, but not more frequently than every few minutes.).
  3. Think about what you really need to persist in the database. When you have objects which simply don't need persistence (like common mobs in mainstream MMORPGs - nobody will complain when their positions reset after a server restart), there is no reason to store these in the database at all.
  4. To avoid hitting a speed-bump in the opposite direction (running out of RAM on the game-server) also consider when and what you can persist or discard. When you have an area which wasn't visited by players for a while, there is no reason to still keep its state in memory. Persist the state of anything that's worth persisting, discard the state of everything that isn't and re-boot the area when a player comes near.
\$\endgroup\$
6
  • \$\begingroup\$ My problem with the "all in memory" solution is when it comes to selecting parts of the data. For examples if I had an array with all my physics components, and want to select only those that have a special material, I would need to loop through all my components to find it. Of course SQL does the same thing anyway, but I'm curious how to manage these type of complicated queries in a performance-efficient way \$\endgroup\$
    – nialna2
    Commented Dec 12, 2013 at 13:59
  • \$\begingroup\$ What language are you using? Some languages or frameworks provide methods to filter data in arrays or lists almost, but not quite, entirely unlike SQL - for example LINQ in C#, and Python is pretty good at managing arrays and filtering them too. Maybe a local database like SQLite could be kept in memory and used efficiently as well, but I don't know the details about performance of either of those ways. \$\endgroup\$
    – Christian
    Commented Dec 12, 2013 at 14:10
  • 2
    \$\begingroup\$ @Malharhak SQL databases aren't magic. When you need all physics components with a special material in SQL, what does the database do? When you have no index on the material column, it would iterate the whole table with a simple for-loop and add the matching entries to a results-array ("full table scan"). When there is an index, the database would maintain a data-structure optimized for searching, like a hash-table or a binary-tree, and use that. Both can be easily replicated in your application. Details depend on the language and further questions would be more suitable for stackoverflow. \$\endgroup\$
    – Philipp
    Commented Dec 12, 2013 at 14:14
  • \$\begingroup\$ Yes that's what I'm thinking of doing now. I'm in Javascript so there are probably many possibilities, I'm just a bit concerned about the performance of storing that much data in arrays. But well, it can't really be worst than SQL \$\endgroup\$
    – nialna2
    Commented Dec 12, 2013 at 14:23
  • 1
    \$\begingroup\$ Every component will be simple objects without nested properties (which ends up being kind of like a hashtable). I guess this will be fast enough to scan. Then I can use some nosql database to store these objects in a consistent way, in a background process, and I'd probably be good \$\endgroup\$
    – nialna2
    Commented Dec 12, 2013 at 14:31
1
\$\begingroup\$

Keep in mind that no MMO server is just a single process. In fact, they typically are composed a plethora of processes that each handle unique characteristics of the game world.

But specific to game object data, generally these are handled by zone servers that are often overseen by a master world server. These zone servers are separate processes with their own memory space that hold only the pertinent game object data necessary to simulate their portion of the virtual world. This minimizes the RAM requirements and aids in game state update since each zone server manages a subset of the full game world.

Game object data is still loaded from SQL during startup, data which isn't kept in memory can be queried but is done asynchronously as pointed out by others. But generally, component-data that you're talking about isn't persisted to the database each loop but perhaps every few minutes, during logout, or when specific transactions take place in the game world, such as trading items, etc.

\$\endgroup\$

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .