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

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

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.