1 of 4

Dissertation of a case where using MySQL resulted in a serious scalability problem for a huge online-game community

This is a follow-up to my previous question: Why use a database instead of just saving your data to disk? While I have understood and agreed with many arguments provided there, I'm still not convinced by the use of databases even for cases where scalability is necessary. For that, I'll describe a case there using MySQL possibly resulted in serious scalability problems for a huge open-source community.

What is an OTServ?

OTServ is an open source MMORPG with a huge community. There are many "servers" online right now: otservlist.com. Those are mainly supported by the commercialization of in-game "items". OTServers use mainly MySQL for data storing.

What is the serious item cloning problem that OTServs have?

Otservs - all of them - have a serious problem: if the server crashes, people can clone items. This is a dirty trick that can be executed because the state of a player is saved when the player logs out. This means the the database is not always in valid state. If 5 different players logged out with the same item, the item would be saved five times on the DB! As result, if the server happened to crash, those would be effectively cloned when it reopened, because a full-save hadn't happened. As many servers were sustained by commercializing in-game items, so you can see how this was a HUGE problem.

How is SQL to blame for this issue?

When I had an OT, I tried approaching the issue by modifying the source directly. My idea was saving the entire server state every few minutes. Guess what? It was slow. The whole process of serializing every player, item, etc to SQL commands and casting them resulted in a noticiable freeze that remained for a few seconds every time the server saved. So once you had too many players, saving the whole data of the server at once on runtime was unfeasible. The solution was not to save the whole state at once, but just single players when they logged out, which resulted on the cloning issue. One can say, thus, that the root of the problem is that the MySQL solution did not scale. It ended up, I had to solve the cloning problem by other, very laborous means. This problem would not exist if the data was simply serialized and saved to disk as JSON, as that operation is much, much faster and could happen asynchronously.

So, the questions and discussion:

Who is to blame on this problem - the design adopted by the OT community or MySQL itselfv? How would you approach this problem if you hosted an OTServ? Would, in this case, just saving data to disk result in a better, faster and more scalable solution, as the OT executable is the only program dealing with the data?

This link provides the source code for the most used OTServ distro, if you want to take a look.