1
\$\begingroup\$

I am considering using MySQL as a database for storing the character states or the world for that matter.

The thing is if I will save the character data on an interval, thus giving rest to the mysql database, it means that if the server crashes some game time would be lost.

So I was planning to save everything as it happens and make the database the real source of the states.

But the problem is, this would mean thousands of write per second. How can I alleviate that performance problem?

Additional info:

  • This is a MMORPG
  • 2000 simultaneous players (so far in beta)
  • 3D world
\$\endgroup\$
2
  • \$\begingroup\$ Not enough info. Is this online? How many simultaneous users? What kind of info are you storing, simple key+value or more elaborate setups? What's the balance between writing all these updates and reading back, how complex are the queries? Have you investigated what other games of your type are doing? Many, major, multi-million player games think nothing of 5 minute or longer delays in staging writes... \$\endgroup\$ Commented Jun 16, 2014 at 4:05
  • \$\begingroup\$ question updated, I was also once told about entity systems. \$\endgroup\$
    – majidarif
    Commented Jun 16, 2014 at 4:11

1 Answer 1

5
\$\begingroup\$

it means that if the server crashes some game time would be lost.

Constantly saving all character data immediately for all players isn't particularly feasible. Fortunately it isn't particularly necessary. This is really a scenario where you want to avoid crashing in the game server as much as possible, because that far more serious long-term implications for your game than just a few minutes of progress being reset.

In general, you can get by with a system that keeps character/account data resident in the server's memory and saves either periodically or at certain "critical" moments (such as a level up, or the completion of a quest). It's true that if the game server crashes, you could lose some amount of non-critical progress; this is generally considered acceptable (for example, Guild Wars 2 works this way; this is the phenomenon people often incorrectly call a "server rollback").

You can increase the frequency with which you can make saves by building database cache servers in front of the actual database server. Your game servers enqueue save requests to the cache servers, which distribute the pressure on the game server over time. This also allows you to tune the rate at which the caches flush to the final server without having to directly manipulate the game servers, as you'll likely have many games servers and fewer cache servers.

Try to keep the data you store as simple as possible for the database to consume; if you do anything but store binary blobs (which is what Guild Wars 1 did), design your table schema in such a way as to minimize relationships between tables that might cause locks that slow down an update. Consider your indices carefully, because every index you add can increase the complexity of inserting into a table. Et cetera.

You also need to consider the hardware/machine configuration options for dealing with the problem: if your entire database cannot be kept in memory on the server, you'll want to have very fast disks.

Other than distributing the load as much as possible and finding the appropriate amount of throttling you can make, this is primarily a database optimization problem. There are whole books written on SQL server optimization (and lots of lore on the SQL-alternatives, should you choose to elect to explore one of those as well). I'd recommend going in search of that material.

For most hobby "MMOs" though, this kind of scalability won't be needed.

\$\endgroup\$
3
  • \$\begingroup\$ by cache servers, you mean something like redis or memcache? \$\endgroup\$
    – majidarif
    Commented Jun 16, 2014 at 5:26
  • \$\begingroup\$ or even rabbitmq to handle the saving of data? \$\endgroup\$
    – majidarif
    Commented Jun 16, 2014 at 6:33
  • \$\begingroup\$ Perhaps; or perhaps something custom. It's going to depend on your specific needs and available technology. The point is to reduce the instantaneous stress on the actual primary DB; anything that does that is helpful. \$\endgroup\$
    – user1430
    Commented Jun 16, 2014 at 6:44

You must log in to answer this question.

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