MySQL for MMO-Development... What do i have to consider ?

Question

Im currently developing an little mmo. Its pretty basic, players can walk around... gather some resources... build some simple buildings and craft things.

Before i began to develop i read a lot about benefits using MySQL... Now im not sure if MySQL was the right choice for such a project.

Currently i store player specific data in real time, that means... once a player crafted, builded or gathered something, a server request is send which triggers a MySQL - Query to insert a new Row into the specific table. This happens everytime a player moves, crafts, gathers or builds something.

I dont only store player-specific data in those tables... also world specific data, for example if one player visits a zone which was last updated 6 hours ago, i refresh this zone with resources and mobs. The server is generating them and after that, it inserts them as a bulk into the database.

With only one active player this works fine... but i have no idea if this will still work with dozens or hundreds active players at the same time. The serverside itself is able to handle them... but i have no idea if the database is able to.

I also heard bad stuff regarding table locks... That this is somekind of major iusse using MySQL, how does this effect dozends of database actions per seconds ? And could possible "share" be a solution for this ?

What else do i need to consider while using MySQL for a mmo ?

Answer 1

Overusing the database like this is usually a very bad idea.

Database queries always require a network roundtrip between game server and database server. Even under ideal conditions (both run on the same physical server), those latency times are still magnitudes larger than accessing data you have in RAM.

So only use your database for data at rest. Use it to suspend the states of players who are currently not online. But any data about the players currently in the game should be in the game servers memory.

You might want to regularly suspend the ingame players so you don't lose too much game progress in case of a server crash. But this should happen every few minutes at most.

Answer 2

It's a trade-off between performance and data consistency.

A method that's often used in these kind of situations is saving data on specific intervals. For example, there's no reason to store the player's position in the database every time the player moves. This can easily be updated in the database every minute or every few minutes. The same goes for the player's stats, inventory, bank etc.

For more critical actions you can consider saving data immediately. It just depends on how often a specific action is done and how important the action is.

Also - mobs and depletable resource usually aren't added to the database when they spawn. That's a server state that's usually reset on server restart. When a user enters a zone that's empty of resources and mobs, just spawn them and leave it at that, no need to store this in your database.

Answer 3

I agree with what Basaa and Philipp said, but I'll add this:

A design like you're talking about isn't completely impractical (well, storing every player movement is, but in general i mean writing lots of data to your database often), but MySQL is probably the wrong choice* for such a design.

MySQL and most relational SQL databases are usually optimized to be read-heavy. But the architecture you're talking about is hugely write-heavy. Unless you need the benefits of a relational database (i.e., relations - joining tables, ensuring constraints, etc) then I'd recommend starting with a NoSQL database that's more write-friendly. I'd recommend redis - while it's not perfect for your needs, it's simple to set up and use. It will handle a lot more writes than MySQL.

---

(* The situation is actually a lot better now than it was 5 years ago. There are multiple write-optimized storage backends for MySQL that have come out. But IHMO, those are fixes for an existing problem. Especially if you don't have an experienced DB admin, you'll be setting yourself up for a lot of work just on DB tuning with MySQL.)