OTServs are open source MMORPGs with a huge community. Mostly 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 to the SQL database 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! So a crash would effectively clone the items. The OTServ core is several years old and the main branches still don’t provide a solution.
As online games, they consist of a world of dynamic data that changes at a very fast pace. There are thousands of online players and millions of registered players. The data is stored in MySQL. There are tables such as players, player_items, player_skills, player_storage and so on, some with millions of rows. I believe the problem could be that overhead brought by SQL makes it impossible to save all the data at the same pace it happens – hundreds of updates per player per second.
Why OTServs have that problem, what’s the impact of the use of SQL databases on the issue and how could it be solved?
21
Well, to some extent database design was the problem, since giving each item a unique ID from the start would have solved your particular problem.
However, your bigger issue is that the SQL solution wasn’t the fastest possible. That will pretty much always be true. SQL is designed to go as fast as possible while preserving ACID guarantees and while following a relational model. If you don’t care about those things then it may well not be the best solution.
For raw speed, it would be hard to beat dumping your in-memory structure in a giant block to disk. Then you don’t even have to serialize/deserialize. If you are always going to save/load the whole world state at once and can deal with the pain of integrating that with the parts done with SQL, and you really need that speed, it might be worth it.
If you want to take everything out of the database and only use JSON, you will get to solve all the concurrency problems the database handles for you (unless it is still feasible to save/load everything every time, which I doubt). That really won’t be easy.
Blame this one both the design adopted by the OT community and MySQL.
This is really not somewhere that I would consider any kind of SQL database server, and especially not one with as many flaws as MySQL. Possibly your problems could be solved with PostgreSQL but even that is not a good fit to this problem. You would do better to look for a solution based on message queueing where all transactions are messages. Make sure that you use/configure an MQ solution that guarantees delivery of messages. Then the app only has to get its transaction into an MQ broker, and your internal systems can take care of writing data to a datastore if necessary, and resolving conflicts.
However, be warned, that if you move to message queuing you will likely have to deal with issues such as serial numbers (similar to DNS zone serial numbers), timestamps, and resolving synchronization corner cases. But at least this is a well documented art. Usually this kind of code is hidden inside database server replication software and never revealed to the customer, but the computer science community has developed all the algorithms in the open, at conferences on Very Large Data Bases, and so on. Hunt for information on CiteSeer, follow up the citations in the papers you read, and you will find everything that you need to implement this yourself.
But please implement this in a modern language like Python or Scala first, and only if you really need to, translate parts of it to lower level C.
More on why I feel that MySQL is a risky choice for this use case.
You have asked about a scenario where synchronization of replicas is an important issue. I have seen so many issues with just master-slave replicas on MySQL 5.5 that I simply don’t trust it. But don’t trust me. Try two or thtree solutions and test them under load with your application. MySQL is fine for webapps that use the ActiveRecord design pattern, but other open source dbs do exist and are better for many scenarios. For anything as important as a dbms, you need to do comprehensive testing if you want to avoid suffering later.
A lot of people use a simple form of MySQL replication which basically sends a stream of commands from the master to the replica server. You can even chain this together so that the slave sends a stream of commmands onward to yet another server, perhaps one that is considered the “backup” copy and is never used for select queries or to replace a crashed master db. The problem seems to arise because not all commands will produce the same result when run on different servers. Commands using temporary files, random number functions and others, cannot be replicated. In addition I don’t believe that MySQL enforces identical configuration on the replicas so you could get DDL that creates tables with different default character sets and sorting rules. Other databases have more replication systems but even Oracle can go haywire if its sequence numbers get munged up.
You are building a banking system and it could easily end up handling a much higher volume of transactions than the biggest New York banks, so the common Enterprise solution of an SQL database is probably not right for you. Tell your developers that they are working on a banking system and that it must be IMPOSSIBLE for money/objects to replicate themselves in multiple accounts. Give them the time and resources to build this system and tell them to read the research that is openly available, i.e. they do not have to reinvent the wheel, just implement it in a way that gives more control than a prepackaged db replication system.
5