Tarantool: history of search acceleration in 1C

Recently, our good friends from a large retail network had the task of speeding up searches in 1C.

Firstly, it was necessary to search by customers (three directories, 9 text fields, a search like% like%) and just 2.5 million records each. We must say right away that full-text search and morphology are not about Tarantool yet. As a result of a series of experiments, we settled on ElasticSearch, but because He is not in the subject of the article, then we can write a separate one if there is interest. We will only say that the speed has grown by an order of magnitude compared to what we could squeeze out of the full-text MS SQL search.

Secondly, we needed a search and selection of goods with the withdrawal of balances for all warehouses without additional requests. The search speed should have been comparable with the usual response of the interface, that is, about 0.2 seconds instead of the current 5-12 seconds in 1C (depending on the load level). 90 thousand lines, the list of items does not change often, about 10-50 items per day.

Stage 1

- . 1 ( ) 700 90 . 200 .

, Elastic. ( ) - . 50 100 , .

MySQL PostgreSQL, . 10 10 700 . 10 100 insert 0,9 , 20 2,5 . 30 1.

1- , « 1 ». , 1- , «, MS SQL».

MS SQL 45 . , in-memory .

, , 3-5 .

, , , ( ) ( , ).

, 1 .

, RabbitMQ + Memcached / Redis, . 1. — « » .

MS SQL 2017 — Memory Optimized Tables.

Tarantool.

«» . , , , () .

, , , MS SQL Tarantool.

(*) 5 (15 , , , ).

Stage 2

, , Tarantool 2.1 SQL- Tarantool PHP.

- , Tarantool , .
, . Lua ( Tarantool Cartridge Vshard). 0,004 0,21 . 30 100 . 7 .

Tarantool , SQL — . Lua, . 6 . SQL- 20 , Lua 50 , , 1 .

Tarantool ( read only, updated: 3 1,5 ) : MS SQL 50 , Tarantool 120 ( : Tarantool 2.2, , 2.3 , Tarantool 2.3 — ). . 1 300 , (Tarantool memtx).

Lua, Tarantool. .

: ElasticSearch.

---      ,    
local function get_stockBalance_to_queue(spaceIn)
    local x = os.clock();

    local space_name = 'STOCKBALANCES';
    if spaceIn ~= nil and 'string' == type(spaceIn) and spaceIn ~= '' then
        space_name = spaceIn;
    end

    local maxRows = 50000;
    local goods = {};
    local goodsIds = '';

    --- 
    local i = 0;
    local iStock = 0;

    local session = os.time(os.date("!*t"));    
    local borderTime = session — (10); 

    box.begin()
    for _, tuple in pairs(box.space[space_name].index.ISPROCESSED:select(0, { iterator = box.index.EQ, offset = 0, limit = maxRows })) do

        if i == maxRows then break end
        --- tuple[1] — GUID 1 
        --- tuple[2] — GUID 1 
        --- tuple[6] — ,    
        if tuple[6] < borderTime then

            --- ,     ,       —    
            if goods[tuple[1]] == nil then
                goods[tuple[1]] = 1;
                ---  —     
                i = i + 1;
                ---      
                goodsIds = goodsIds .. '"' .. tuple[1] .. '",';
            end
            ---     =  
            pcall(function() box.space[space_name]:update({ tuple[1], tuple[2] }, { { '=', 5, 1 }, { '=', 6, session } }) end);
            ---  —      
            iStock = iStock + 1;
        end
    end

    local stockTmp = {};
    local goodsInStockTmp = {};
    local instock = 0;

    ---   getSumStock       (  ,    ..)
    local stockBalance = _G.Functions.getSumStock(goodsIds);

    ---   getGoodsInStock      in stock — 1/0 (/)
    local goodsInStock = _G.Functions.getGoodsInStock(goodsIds);

    local iGoods = 0;
    for _, tuple in pairs(goods) do

        ---      (   ,        tuple[1],   )
        stockTmp = stockBalance[tuple[1]]['quantityNet'];

        ---      (   ,        tuple[1],   )
        goodsInStockTmp = goodsInStock[tuple[1]];

        ---        — /,      1/0 (/)
        if not stockTmp then
            instock = 0;
        elseif stockTmp > 0 then
            instock = 1;
        elseif stockTmp < 0 then
            instock = 0;
        else
            instock = 0;
        end

        ---           ,       
        if instock ~= goodsInStockTmp then
            ---  —    
            iGoods = iGoods + 1;
            ---  100 —  ( — ISPROCESSED) (/),  ,     
            ---  105 — , / (1/0)
            pcall(function() box.space.GOODS:update(tuple[1], { { '=', 100, 0 }, { '=', 105, instock } }) end);
        end
    end

    box.commit();
    return space_name .. ' for update: ' .. iStock .. '  Goods for update: ' .. i .. ' Updated goods: ' .. iGoods .. ', ' .. (string.format("elapsed time: %.2f", os.clock() — x));
end;

ElasticSearch / , Tarantool .

:

, , . , , .

(, , ) Tarantool box.slab.info. 1 . .

, Mail.ru Group, , Telegram, , .

Stage 3

« , — . — . Tarantool, Bitrix- Bitrix-?» ( , )

« ? — . — , ».

«» — , , Tarantool. , . , , , .

P.S. , . . ,