Databases in online games. From Allods Online to Skyforge

When they talk about game development, it’s usually about shaders, graphics, AI, etc. The server part of game projects is extremely rarely affected, and even less often - databases. Fix this unfortunate misunderstanding: today I will tell you about our experience with databases, which we acquired during the development of Allods Online and our new project Skyforge . Both of these games are client MMORPGs. The first registered several million players. The second is developed by the studio in the strictest secrecy in the depths of the Allods Team.

My name is Andrey Frolov. I am the lead programmer for the Allods Team and work on the server team. My development experience is almost 10 years, but I only got into games in October 2009. I’ve been in the team for more than three years, since March 2010. I started working at Allods Online, and now at Skyforge. I do everything that is somehow connected with the Skyforge server and databases. In this article I will talk about databases in online games on the example of Allods and Skyforge.


')
If you don’t really like to read, I suggest reviewing the article to the end and watching the video of my report from the Game Developers Conference. Those who remain in the post, put a bonus - an important addition to the report in the form of a story about the hybrid NoSQL-JSON and the relational data model.

Evolution

The game base is a typical OLTP system (many small and short transactions). But the use of databases in games is somewhat different from their use in the web, banks and other enterprises. Firstly, this is due to the fact that the data model in games is significantly more complicated than in banks. Secondly, most programmers in game devs came from the harsh world of C ++, taking with them a beard and a love for binary packaging. Absolutely all of them, if they need to save a character to disk, first of all they want to serialize it to a file. That's how it all began in Allods Online. Programmers made file storage, but quickly thought better of it and rewrote everything under MySQL. The project was successfully launched, people played, the experience was saved.

What we had in Allods:

• Java, MySQL
• Shards And each of them was designed for a limited number of online players.
• This number of players issued approximately 200 transactions per second.
• The service that works with the database was one-way, because it was enough for so many transactions

A few years later, Skyforge started. Skyforge had very different requirements, and therefore had to reconsider our approach to working with databases.

These requirements are:

• We no longer have shards. We have one big one world
• We count our server for 100,000 online players, and possibly more.
• According to our estimates, these players must issue more than 7,000 transactions per second.
• We still write in Java, but from MySQL we switched to PostgreSQL

Well, let's look behind the scenes and see what we came to during the evolutionary development of our technological thought.

Architecture

First, our server is distributed. The base is also distributed. Secondly, the architecture of our server is service-oriented. This means that everything is presented in the form of services that exchange messages. There are dozens of services in the game, but only transaction execution services have direct access to the database. In general terms and to the best of my artistic abilities, it looks like this:



It is only necessary to take into account that all the elements shown in the picture exist in several copies.

• On the servers of the game mechanics are avatars. An avatar is a Java object representing our player. There are several times more game mechanics servers than database servers.
• All servers communicate with the database through a special interface. This interface contains hundreds of methods, hides the distributed essence of the base from programmers of game mechanics and provides an understandable contract: one method - one transaction. It is necessary to understand that this is not one class with hundreds of methods, but one class with ten methods, which are given by small “subinterfaces” with ten methods each. Such "packs" of operations.
• The database service (database) performs incoming operations and writes their results to the database. The database service and the database itself are on the same physical server in order not to waste extra time on the network.

Avatar like cache

This simple scheme has one important point. Our avatar is needed for the performance of game mechanics, but as a side effect, it is in fact a cache above the database. All requests of the form “Show me the items of this player” or “Where is the avatar Basil?” Are served by this avatar. When a player enters the game, we upload his avatar, and he lives as long as the player is online. Such a simple trick allows you to remove most of the read requests from the database, and even some of the write requests.

We divide all player data into two categories:

• Unimportant data, the loss of which the player can survive. These include the position on the map, the level of health, etc. We “accumulate such data” at the avatar and periodically, and also once at the exit from the game we throw it into the base.
• Important data, the loss of which will be painful for the player. These include items, money, quests and similar things. With this data, everything is much more complicated. We try to make the player never lose this data, because he spent a lot of time and effort on them. Therefore, they must be saved to the database synchronously. It is the preservation of important data that creates the main load on our base.

So, how do we synchronize the state of our important data in the database and the avatar, which is located on another server? Everything is actually quite simple. Consider the scheme of taking the subject.

• The game mechanics server sends a request to the database service "take the item XXX".
• The database server performs the necessary checks (is there enough space in the bag, is it not necessary to “glaze” this thing, and so on). After that, it saves the updated state of the avatar bag to the base.
• Only if the save was successful, the avatar is sent an update of the status of its bag. The avatar, in turn, sends updates to the game client. As a result, the player will see that he has an item, only when the item is safely stored in the database.

PostgreSQL

At Skyforge, we abandoned MySQL for a combination of the reasons listed below.

• In MySQL, all features are spread over various storage engines. Something was in InnoDB, something in MyISAM, something in the MEMORY engine. This made life very difficult.
• In MySQL, the distributed transaction mechanism is broken, which we really wanted to use. MySQL developers promised to fix it only for the sixth version, which is not even in the plans.
• MySQL has broken the group commit mechanism. It was repaired in version 5.5, and this item is no longer relevant.
• In MySQL there are actually quite a few bugs, strangely working features and a very limited query optimizer.

PostgreSQL solved all these problems, instead giving only the problem with the auto-vacuum. We decided not to take the NoSQL base, since we have very high requirements for data consistency, and no NoSQL-base in the world can consistently and transactionally transfer an object from one avatar to another. Eventual consistency in this case did not suit us very much, because This greatly spoils the game experience.

Hybrid data schema

The fact that we use PostgreSQL does not mean that we have to store data in a relational form. Relational database can be used as key-value storage

Fully relational model does not suit us, because contains several performance bottlenecks. For example, we have a player, and he has quests. A player can complete hundreds of quests, and when entering the game we will need to show them all. If you use a relational model, you will have to make a request for issuing hundreds of lines from the database, and this is slow. On the other hand, the non-relational model has many drawbacks: lack of constraints, inability to partially update data, etc.

After various experiments, we agreed that we are satisfied with a bunch of the relational model, in which some of the fields contain non-relational data. In Allods and until recently in Skyforge, we have partly serialized some of the data and stored them as fields in tables. But just three weeks ago, we finally understood everything and now store the data in a relational schema with JSON inserts.

It looks like this:

# select * from avatar limit 1;
id | 144115188075857124
position |
{"point":{"x":7402.2793,"y":6080.2197,"z":51.42402},"yaw":0.0,"map":"id:132646944","isLocal":false,"isValid":true}
death_descriptor | {"deathTime":-1,"respawnTime":-1,"sparkReturnDelay":-1,"recentDeathTimesArray":[]}
health | 1250
mana_descriptor | {"mana":{"8":300}}
avatar_client_info | \x
character_race_class_res_id | 26209282
character_sex_res_id | 550995
last_online_time | 1371814800726

JSON. , PostgreSQL 9.3 JSON. , — PostgreSQL MongoDB PostgreSQL.

Virtual shards

, . ID , , .

ID : — , — ID .

long id = <shard_id> <account_id>

. . . -, virtual shards . , , 15 . , 5 . 215 310. , .

SSD

SSD. RAID-. , , , fsync.

. , 200 , SSD. , SSD ó . SSD WAL PostgreSQL, , . SSD !

, .

• PostgreSQL — .
• SSD .
• -.
• , . .

, .



, , . . , master—slave , , .

,

.

C .

- .