From Govnokod to Highload. We use TARANtool. 5 recipes to increase productivity

One head of a social game startup contacted me with a request to increase the productivity of his project. At this stage, a prototype project was made and launched. And we must pay tribute to the developers that the project worked and even brought some kind of profit. But, launching an advertising company did not make sense, since the project could not withstand any loads. MySQL fell (35% of errors).

Project code ... In general, I had the impression that his undereducated student wrote ... And this, despite the fact that a partial refactoring by another programmer has already been done. The only thing that pleased was that no framework was used. Of course, this is an eternally flaming question: Jesus or Magomed? To be or not to be? Unix or Windows? Use or not use? IMHO, My opinion: frameworks are sharpened for a narrow circle of typical tasks. A social project is usually not a typical task ... But, in general, the project seemed interesting to me and I decided to take up the improvement. This entry can be completed ...

Probably, only the lazy WEB developer who knows at least something in this area did not write about the performance improvement and the topic of highload. Fundamentally, you will not find anything new in this article. The main ideas for developing highload projects have been outlined by me in a series of HighLoad articles . Three whales. . If you are interested in how I increased the productivity of a PHP project using the NoSQL tarantool repository , then welcome to cat.

Although, in principle, you can use another, suitable for this range of tasks, key / value storage, and server logic implementation can be in any other scripting language.

Recipe 1. Analyzing the code

Everything is horribly corny. They wrote about this hundreds of times before me, and hundreds more articles will be written ... However, “we are the smartest” and persistently step on the same rake. I won’t discover America if I say that the bottleneck in 99% of all WEB projects is the database. And which of this should be concluded?
That's right - you need to minimize the number of queries. . And how to do this if the code occurs five times in the course of logic:

$ user = new User ();
$ userData = $ user-> getById ($ uid);

When profiling queries, it comes out that we performed five identical “selects”: SELECT * FROM users WHERE id = $ uid;
And this is implemented quite simply: we use an internal (private) static field or a property of the User object:

    class User {
 
        private static $ userData = NULL;
 
        private function getById_loc ($ uid) {
        // some code to access the database. 
        }
 
        public function getById ($ uid) {
           if (self :: $ userData) return self :: $ userData;
           self :: $ userData = $ this-> getById_loc ($ uid); 
           return self :: $ userData;
        }
    }

The second thing that immediately catches your eye. this is when two methods stand nearby:

$ User-> updateBalance ($ sum);
$ User-> updateRating ($ rating);
 

which leads to the execution of two queries to one table in a row, one after another:
UPDATE users SET balance = balance + $ sum WHERE id = $ uid;
UPDATE users SET rating = $ rating WHERE id = $ uid;
although, if we slightly move our brains, then we could well form one request:

UPDATE users SET 
  balance = balance + $ sum,
  rating = $ rating  
WHERE id = $ uid;
 

This can be done in two ways: either we write another method $ user-> updateBalanceAndRating ($ sum, $ rating), or we implement something universal, as they say, for all occasions:

$ user-> updateFieldAdd ('balance', $ sum); // remember field, add operation - addition, operand
$ user-> updateFieldAssign ('rating', $ rating); // remember the field, assign operation - assignment, operand
$ user-> execUpdate (); // form and execute the request 

Only the introduction of these two simple methods allowed to reduce the number of database queries from 10-12 to 3-5. But to be honest, the existing code has yet to be refactored and refactored. Of course, most Habrousers are far from being losers, but profiling and analysis is always "God help us."

Recipe 2. Caching

What is Caching, I hope you do not need to scatter. As Dima Koterov wrote in his article about the development of a highload project: “you need to cache everything you can.” In this project, there were timid attempts at some kind of caching. However, everything turned out according to Chernomyrdin: they wanted the best, but cached, not what is most often used;).

As noted above, to reduce the load on the database, it is necessary to reduce the number of queries. And how to reduce them? Yes, it’s very simple - you can simply remove some of the immutable data (manuals on units, tributes and weapons) from the database, for example, in configs. Configs can be either in XML, and edited by the girls from the gameplay team in any XML editor, or in the finished form: in the PHP array - if the developer of the gameplay and code is one person. ParsingXML on the fly is a difficult thing, so I do the preliminary XSLT conversion directly to PHP code (in the form of an associative array, which is loaded with the main code). However, after each change in the XML config file, you must run the XSLT conversion script or the console utility. Yes, this is not caching, this is a slight improvement, and it should not be allocated as a separate recipe,

Thus, having crammed all the directories into configs, we are freed from a couple more queries. Well, what - has it become easier? .. At least after applying recipes 1 and 2, the base has stopped falling. Well, at least some result ...

Recipe 3. Data Analysis

Here you really have to analyze the code and think ... And there is, by the way, something ... You need to find out what data the user changes, which of the user data is unchanged, which is most often requested. Here you need to visually go over the code and understand the logic of the project.

In our project, the most frequently requested information was the user's game profile, gifts and awards. All this data was placed in NoSQL storage, and all other data, especially related to payments, remained in MySQL. Tarantool was chosen as NoSQL storage.

And yet - why TARANtool?

At the Highload ++ Conference 2011 Tarantool Development Manager Kostya Osipova was asked:
- Why is your name so poisonous?
- Well, you can consider the name as a ram and tools, i.e. as a tool (tool) for ramming your projects.

So, the factors influencing the choice of NoSQL storage were:
- My personal acquaintance with the team lead of the project Kostya Osipov, who promised support and consultation
- Experience in implementing this storage in a previous project. Unfortunately, the project did not take off :(, but it was interesting.
- Exploring the new tarantool features, it has been almost two years since its previous use
- High performance of this NoSQL storage and high data availability.
- The persistence of the data, in the event of a fall on the disk there is an actual copy that can always be raised.
- well, and to be not very modest, I myself am the author of the first version of the PHP extension for Tarantool, so that if necessary I can patch something or fix the bug.

But to be more serious, I just like the unique features of this NoSQL data warehouse: using secondary keys and manipulating data space on the server side using stored procedures.

Data Analysis (continued)

Consider the user profile, the users table. It has mutable and non-mutable data. Variable data includes: balance, rating, pvp points, units, tutorial steps, etc.
Social data, including login_name, avatar url, personal codes, etc. are among the mutable data. Among the mutable data there are frequently changed and rarely changed. However, non-volatile data may be requested frequently.

Highlight frequently requested data. We will cache them in tarantool. Now a little about NoSQL storage itself ...

TARANtool. Short review

Tarantool is, as mentioned above, a high-performance key / value NoSQL storage. All data is in RAM, and presented in the form of tuples, therefore, their extraction in speed is not inferior to redis or slightly slower (6-7 milliseconds per 1000 operations) memcached.

Still, note that Tarantool is a data warehouse, not a caching system in memory such as memcache. All data is in RAM, but is constantly saved (synced from the sync system call) to files (snapshots 0000..01.snap). Unlike traditional memcached & redis, tarantool has additional features:
- the ability to superimpose secondary indexes on data
- indexes can be composite
- Indexes can be of type HASH, TREE or BITSET. The introduction of the GEO index is planned.
- sampling for one or more indices at the same time.
- data extraction in parts (analogue of LIMIT / OFFSET).

Tarantool operates on data that is combined into spaces. Space is an analog of a table in MySQL. Tarantool uses digital space numbering (0, 1, 2, 3 ...). In the foreseeable future, it is planned to use namespaces (an analog of table names in MySQL.).

An index can be superimposed on each space. Indexes can be superimposed both on a numerical (int32 or int64), and on a character field. As with spaces, tarantool defines digital index numbering.

The exchange between the client and the server occurs in tuples . A tuple is an analog of a row in a MySQL table. In mathematics, the concept of a tuple is an ordered finite set of length n. Each element of a tuple represents a data element or field. Basically, the tarantula does not distinguish between data types of fields. For him, this is a set of bytes. But if we use an index, i.e. impose an index on this field, then its type must match the type of the field. There is another name for the tuple: tuple.

All indexes are registered in the config, which is human-perception: YAML. An example of a part of the config:
space [0] .enabled = 1 
space [0] .index [0] .type = "HASH"
space [0] .index [0] .unique = 1
space [0] .index [0]. key_field [0] .fieldno = 0
space [0] .index [0] .key_field [0] .type = "NUM"
In the config, we describe the primary and secondary indexes for each space. If we select only by PRIMARY KEY, then a description of only the primary index is sufficient (see the example above). If we want to choose the best by rating or pvp battles among our users, then we impose a secondary index on these fields. Let the second field be indexed (fieldno = 1, counting from zero) int32_t - rating:

space [0] .index [1] .type = "TREE" // makes the type TREE, which allows you to make more and less samples on the operation
space [0 ] .index [1] .unique = 0 // remove the originality
space [0] .index [1] .key_field [0] .fieldno = 1 // specify the number of the indexed field
space [0] .index [1] .key_field [0] .type = "NUM" // type int32_t

Since we have a Social Game project, the primary key will correspond to social_id. For most social networks, this is a 64-key. The index type will be HASH, and the data type will be STR. Ideally, I would like NUM64, but unfortunately PHP does not work well with the long long type. The driver does not recognize the type and size of the primary key of the used space. At the moment, if you use a 64-bit key, you cannot search for it using a 32-bit value. It must be packaged as a 64-bit key. Now the driver does this only if the value exceeds the 32-bit range. Therefore, it is more reliable to work with the type STRING .

Memory calculation

It must be remembered that tarantool is a memory only solution , therefore it is important to calculate the estimated amount of RAM used. The calculation is as follows:

Before each tuple, a variable of type varint will be stored (analog of the perl 'w' in pack) and 12 bytes from the header for each tuple. Specifically, about data, you can find it by studying the protocol or by reading the Tarantool Data and Protocol article .

Additionally, about 15 percent is occupied by data for allocators. If we, for example, have 10 fields and the user data size fits in 256 bytes, then for 1.5M the following calculation will be approximately:
(10 * 1 + 256 + 12) * 1.15 * 1,500,000 = 921150000 ~ = 440 Mb per day

Also, all indexes are in memory, which occupies:
- for one node in the tree stores 68 bytes of service information
- for one node in the hash stores 56 bytes of service information

To store the index on 1.5M users, just over 80Mb is enough, total together, for Storage of 1.5 M profiles will require just over half a gigabyte. If we add another key (TREE type), then this is an additional 90M of RAM.

Someone like that, but by today's standards - not really too much.

Recipe 4. Getting rid of MySQL in foreground

As we already said, when transferring user profile data to tarantool, we want to have their current copies in MySQL. Therefore, all operations related to UPDATE have to be performed. As a result, having done caching, we did not achieve much. But, they still achieved the main effect: MySQL stopped falling. So, how can you speed up the script several times? This is possible if you get rid of MySQL queries at all. But how is it possible? It is necessary to transfer the information about the change to the database to some other, background script that will perform the INSERT / UPDATE operations.

This information is transmitted through the queue. There are several industrial solutions that allow you to run remote tasks: Gaerman, Celery, and you can also adapt RabbitMQ, ZMQ, redis and other queue servers. But why introduce some new entity into the project if you can use tarantool as a queue server.

The tarantula has an implementation of the queue github.com/mailru/tntlua/blob/master/queue.lua and recommend it for use.
However, it was implemented a little easier. Create a new space:

space [1] .enabled = 1 
space [1] .index [0] .type = "TREE"
space [1] .index [0] .unique = 1
space [1] .index [0]. key_field [0] .fieldno = 0
space [1] .index [0] .key_field [0] .type = "NUM"
 

In this space we will write the following fields:
- id, auto-increment field. Must be indexed. The primary index of type TREE is superimposed.
- type - type of operation, some numerical constant by which the SQL operator pattern number is determined.
- data - some data to insert / update.

The following code will be in the foreground script:
define ('UPDATE_SPIN_COUNT', 1);
define ('UPDATE_USER_BALANCE', 2);
...
$ res = $ tnt-> call ('box.auto_increment', array ((string) TBL_QUEUES, UPDATE_SPIN_COUNT, $ spinCount, $ uid));
The stored procedure box.auto_increment is built-in; it inserts tuple data; the primary key value is max + 1. Parameters:
- the number of the space where the data will be inserted
— the data itself
— optional flag; the default is “return a new key”.
It should be noted that the type of the variable and the number of the space (constant TBL_QUEUES) must be cast to STRING . This script calls the lua procedure, which writes data to the FIFO queue (auto-increment number, type of task being performed, and the data itself).

Next, the background script, which can even be executed on another remote machine, pulls data from the queue and executes SQL:

define ('UPDATE_SPIN_COUNT', 1);
define ('UPDATE_USER_BALANCE', 2);
...
$ res = $ this-> callProc ('my_pop', array ((string) TBL_QUEUES));
 
/ *
if empty, it will return: 
array (2) {
  ["count"] => int (0)
  ["tuples_list"] => array (0) {}
}
 
* /
if (! $ res ['count']) return
 
$ tuple = $ res ['tuples_list'] [0];
switch ($ tuple [1]) {
case UPDATE_SPIN_COUNT:
$ sql = "UPDATE users SET spinCount = {$ tuple [2]} WHERE uid = {$ tuple [3]}";
break;
 
case UPDATE_USER_BALANCE:
$ sql = "UPDATE users SET money = money + {$ tuple [2]} WHERE uid = {$ tuple [3]}";
break;
 

throw new Exception ('unknow task type');
break;
}
 
$ this-> execSQL ($ sql);

As a result, our front-end script works only with a fast tarantula, and the background script hangs as a daemon or runs on the crown and saves data in MySQL on a separate server without wasting resources on the WEB server. As a result, you can win more than 30% in productivity. The theme of background scripts is worth a separate article.

However, this is not all. To start the lua procedure my_pop, it must be initialized. To do this, the following code must be placed in the init.lua file, which must be in work_dir or script_dir.

function my_pop (spaceno)
    spaceno = tonumber (spaceno)
    local min_tuple = box.space [spaceno] .index [0] .idx: min ()
    local min = 0
    if min_tuple ~ = nil then
        min = box.unpack ('i' , min_tuple [0])
    else
        return 
    end
 
    local ret = box.select (spaceno, 0, min)
    box.delete (spaceno, min)
 
    return ret
 
end

The value of work_dir is specified in tarantool.conf.

Recipe 5. Caching only those profiles that are actively playing

As we have already implemented, all our profiles are stored in tarantool, and all changes made by the background script are recorded in MySQL. We always have relevant data, in accordance with the CAP theorem, with a slight delay.

But what if our project gained not 1.5 million, but three or 5 million users? The user logged in, the game did not like - left. But the data in the tarantula remained, they occupy memory and are not used ... Therefore, for more efficient use, and just for faster data extraction, it makes sense to store only those users who constantly play.

In other words, those users who do not play, i.e. did not enter the game, for example, more than a week, you can remove it from the online cache. Since we have an actual copy in the database, we can always restore it in the operational cache. The code of classes using the online cache is built according to the standard type of caching:

class User extends DbModel {
 
  public function getByUid ($ uid) {
 
        $ result = this-> getFromCache ($ uid);
 
        if (! is_null ($ result)) {
            return $ result;
        }
 
        $ result = $ this-> execSQL ("SELECT * FROM users WHERE uid = $ uid");
        $ this-> setToCache ($ result);
 
        return $ result;
  }
....
}

Cleaning can be performed in several ways:
- select a cron script a list of all the "non-performing" records from the database and delete them tarantula
- customized tarantula cleaning broker (he did not do it) github.com/mailru/tarantool/wiki/Brokers-ru
- write stored procedure on lua to delete all "expired" records and run its call on the crown.

We look forward to a new type of storage from the development team, so that not all data (tuples) are raised from the disk to RAM, but only the most requested. Approximately like in Mongo DB. Then recipe 5 disappears by itself.

Instead of a conclusion

All of the above can be implemented in any of the languages ​​in which your social project is implemented (PHP, Perl, Python, Ruby, Java).
Any key / value storage from the following variety can be used as NoSQL storage of operational cache data:
- memcached, there is no persistence and you have to smack your head over the implementation of queues, but this can be solved using the APPEND operation
- membase, not very successful development and it seems like already ceased to be supported by its creators
- a bunch of memcacheDb & memcacheQ
- radish, basically there is everything to implement this functionality
- TokyoTyrant, KyotoTyrant - in principle, queues can be implemented on lua procedures
- LevelDb Daemon, a decent development of guys from mamba. A small finish and the queue is already in your pocket.
- offer something in the comments

Well, in conclusion, a little about pears or a little PR.
About the demons, and specifically their possible tasks, interaction and the subtleties of their implementation, I plan to tell you at "DevConf 2013" " PHP demons in social games ." As well as highlight some features that allowed me to increase the productivity of completed projects. If it’s interesting, I’ll touch upon the topic of the tarantula (for this I used the vote) So, see you at DevConf 2013.

PS. already the third hour of the night, achapotki are possible ... pliiiz in private - I will correct right away. Thanks in advance.