From govnokoda in Highload. We use TARANtool. 5 recipes to improve performance

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

The code of the project ... In general, I was left with the impression that an under-educated student wrote it ... And this, despite the fact that partial refactoring was already done by another programmer. The only thing that pleased, is that no framework was used. Of course, this is always the flame question: Jesus or Magomed? To be or not to be? Unix or Windows? To use or not to use? IMHO, My opinion: frameworks are sharpened by a narrow range 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 undertake improvement. At this entry can be completed ...

Probably, only the lazy WEB developer, who knows at least something in this area, didn’t write about the performance improvement and the highload topic. In principle, something new, in this article you will not find. The main ideas for developing highload projects were described by me in a series of HighLoad articles . Three whales. . If you're wondering how I increased the performance of a PHP project using the NoSQL tarantool repository, then welcome under cat.
')
Although, it is fundamentally possible to use another, suitable for this range of tasks, key / value storage, and the implementation of server logic can be in any other scripting language.

Recipe 1. Analyzing the code

All terribly trite. They wrote about it hundreds of times before me, and hundreds more articles will be written ... However, “we are the smartest” and stubbornly step on the same rake. I will not 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 requests. . And how to do it, if in the course of logic the code occurs five times:

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

When profiling queries it comes up that we have executed five identical “selects”: SELECT * FROM users WHERE id = $ uid;
And it is implemented quite simply: use the internal (private) static field or 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 the eye. This is when there are two methods nearby:

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

which leads to the execution of two queries to one table in a row, one after the other:
UPDATE users SET balance = balance + $ sum WHERE id = $ uid;
UPDATE users SET rating = $ rating WHERE id = $ uid;
Although, if we move our brains a little bit, we could easily 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 the field, add operation - addition, operand
$ user -> updateFieldAssign ( 'rating' , $ rating ) ; // remember the field, the operation assign - assignment, operand
$ user -> execUpdate ( ) ; // form and execute the query

Only the introduction of these two simple methods has reduced the number of queries to the database from 10-12 to 3-5. But honestly, the existing code has yet to refactor and refactor. Of course, most Habrouseurs 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 press. As Dima Koterov wrote in his article about the development of a highload project, “we need to cache everything we can.” In this project, there were timid attempts at some caching. However, everything turned out according to Chernomyrdin: they wanted to do it better, but cached, not what is most often used;).

As noted above, in order to reduce the load on the database, it is necessary to reduce the number of queries. And how to reduce them? It's very simple - a part of the unchanged data (reference books on units, tributaries and weapons) is simply to be taken out of the database, for example, into configs. Configs can be either in XML, and are corrected by girls from the gameplay team in any XML editor, or in a ready-made form: in the PHP array — if the developer of the gameplay and code is one person. Parsing XML on the fly is a difficult thing, so I do a preliminary XSLT conversion directly into PHP code (in the form of an associative array, which is loaded along with the main code). However, after each change in the XML config file, you must run the XSLT transformation script or the console utility. Yes, this is not caching, this is a slight improvement, and you should not single it out into a separate recipe, but you should not forget about it either.

Thus, having stuffed all the directories into configs, we still get rid of a couple of queries. Well, what - it became easier? .. At least, after applying recipes 1 and 2, the base stopped falling. Well, at least some result ...

Recipe 3. Data Analysis

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

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

And yet - why TARANtool?

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

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

And, 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 a user profile, a users table. It has mutable and non-mutable data. The editable data includes: balance, rating, pvp points, units, tutorial steps, etc.
Non-editable data includes social_id, login, avatar url, personal codes, etc. ... Among the variable data there are frequently changed and rarely changed. However, non-editable data may be requested frequently.

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

TARANtool. Short review

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

And yet, we note that Tarantool is a data warehouse, and not a memory caching system, such as memcache. All data is in RAM, but is permanently stored (synced from the sync system call) into files (snap 0000..01.snap). Unlike traditional memcached & redis, tarantool has additional features:
- the possibility of imposing secondary indexes on data
- indexes can be composite
- indexes can be of type HASH, TREE or BITSET. Planned introduction of the GEO index.
- sampling on one or several indices simultaneously.
- removal of data in parts (analogue LIMIT / OFFSET).

Tarantool handles data that is combined into spaces (space). Space is the equivalent of a table in MySQL. In tarantool digital numbering of spaces is used (0, 1, 2, 3 ...). In the foreseeable future, we plan to use namespaces (an analogue of table names in MySQL.).

An index can be applied to each space. Indices can be superimposed, both on a numeric (int32 or int64), and on a character field. As with spaces, digital indexing is defined in tarantool.

The exchange between the client and the server takes place tuples . A tuple is an analogue of a row in a MySQL table. In mathematics, a tuple is an ordered finite set of length n. Each element of a tuple is a data element or field. Basically, the tarantula does not distinguish between the types of data fields. For it 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 indices are registered in the config, which is human-perceivable: YAML. Example of the config part:
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 indices for each space. If we make a sample only by PRIMARY KEY, then the description of only the primary index is enough (see the example above). If we want among our users to choose the best rated or pvp-battles, 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 selections on operations more and less
space [ 0 ] .index [ 1 ] .unique = 0 // remove the identity
space [ 0 ] .index [ 1 ] .key_field [ 0 ] .fieldno = 1 // specify the number of the field to be indexed
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 64-key. The index type will be HASH, and the data type is 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, as long as you cannot search for it using a 32-bit value. It should be packaged as a 64-bit key in the package. Now the driver does this only if the value exceeds the 32-bit range. Therefore, it is safer to work with the STRING type.

Memory calculation

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

Before each tuple, a variable of the varint type (analogous to the perl 'w' in pack) and 12 bytes from the header for each tuple will be stored. Specifically, for pro data, you can read, having studied the protocol or after reading the article Tarantool Data and Protocol .

Additionally, about 15 percent is occupied by data for allocators. If we, for example, have 10 fields and the size of the user data fit into 256 bytes, then for 1.5M there will be approximately the following calculation:
(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 stores 68 bytes of service information in the tree
- for one node in the hash stores 56 bytes of service information

To store the index for 1.5M users, a little more than 80Mb is enough, just together, to store 1.5 M profiles you need a little more than half a gigabyte. If we add another key (type TREE), then this is an additional 90M of RAM.

To whom how, but by today's standards - not quite so much.

Recipe 4. Getting rid of MySQL in the foreground

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

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

The tarantula has a queue implementation github.com/mailru/tntlua/blob/master/queue.lua and recommend it for use.
However, it was implemented a little bit 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. Superimposed primary index type TREE.
- type - type of operation, some numeric constant, which is used to determine the number of the SQL statement template.
- data - some data to insert / update.

In the foreground script will be the following code:
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 data tuple primary key value - max + 1. Parameters:
- the number of the space where the data will be inserted
- the data itself
- optional parameter flag, default is set to “return a new key”
It should be noted that the type of the variable, the number of the space (the constant TBL_QUEUES ) must be cast to the STRING type. This script calls the lua procedure, which writes the data to the FIFO queue (auto-increment number, the type of task being performed, and the data itself).

Further, the background script, which can be executed even on another remote machine, takes 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 then returns:
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 ;

default:
throw new Exception ( 'unknow task type' ) ;
break ;
}

$ this- > execSQL ( $ sql ) ;

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

However, this is not all. To run 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 who actively play

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

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

In other words, those users who do not play, i.e. did not go into the game, for example more than a week, you can delete from the operational cache. Since we have an up-to-date copy in the database, we can always restore it in the operational cache. The code of the classes using the operational 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 done in several ways:
- select the cron script with the list of all “expired” records from the database and delete them in the tarantula
- set up a cleaning broker in a tarantula (I did not do this myself) github.com/mailru/tarantool/wiki/Brokers-ru
- write a stored procedure on lua to remove all the "expired" records and run its call to the crown.

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

Instead of 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 manifolds can be used as a NoSQL online cache data store:
- memcached, there is no persistence and you have to worry a little about the implementation of the queues, but this can be solved using the APPEND operation
- membase, not very successful development and it seems to have ceased to be supported by its creators.
- a bunch of memcacheDb & memcacheQ
- radish, in principle there is everything to implement this functionality
- TokyoTyrant, KyotoTyrant - in principle, queues can be implemented on lua procedures
- LevelDb Daemon, decent development of the guys from the Mamba. Small finished and the queue you have in your pocket.
- we offer something special in the comments

Well, in the conclusion of a little about pears or a little bit of PR.
About demons, and specifically their possible tasks, the interaction and the subtleties of their implementation, I plan to tell at DevConf 2013 " PHP demons in social games ." And also I will highlight some features that allowed me to increase the productivity of implemented projects. If interested, then I will touch on 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, possible achapotki ... pliiiz in private - I will immediately correct. Thanks in advance.