Knex с PostgreSQL выбирает запрос с высокой степенью производительности при нескольких параллельных запросах

Вкратце

Я разрабатываю игру (мечты), а мой backend-стек - Node.js и PostgreSQL (9.6) с Knex. Здесь хранятся данные всех игроков, и мне нужно часто запрашивать их. В одном из запросов необходимо сделать 10 простых выборок, которые будут извлекать данные, и в этом проблема начинается: эти запросы довольно быстрые (~ 1 мс), если сервер одновременно обслуживает только 1 запрос. Но если серверный сервер много запросов параллельно (100-400), время выполнения запросов сильно ухудшается (может быть до нескольких секунд на запрос)

Подробнее

Чтобы быть более объективным, я опишу цель запроса сервера, выберите запросы и результаты, которые я получил.

О системе

Я запускаю код node на капельке Digital Ocean 4cpu/8gb и Postgres на одном и том же conf (две разные капли, одна и та же конфигурация)

О запросе

Ему нужно выполнить некоторые действия геймплея, для которых он выбирает данные для 2 игроков из DB

DDL

Данные игроков представлены 5 таблицами:

CREATE TABLE public.player_profile(
    id integer NOT NULL DEFAULT nextval('player_profile_id_seq'::regclass),
    public_data integer NOT NULL,
    private_data integer NOT NULL,
    current_active_deck_num smallint NOT NULL DEFAULT '0'::smallint,
    created_at bigint NOT NULL DEFAULT '0'::bigint,
    CONSTRAINT player_profile_pkey PRIMARY KEY (id),
    CONSTRAINT player_profile_private_data_foreign FOREIGN KEY (private_data)
        REFERENCES public.profile_private_data (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT player_profile_public_data_foreign FOREIGN KEY (public_data)
        REFERENCES public.profile_public_data (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE public.player_character_data(
    id integer NOT NULL DEFAULT nextval('player_character_data_id_seq'::regclass),
    owner_player integer NOT NULL,
    character_id integer NOT NULL,
    experience_counter integer NOT NULL,
    level_counter integer NOT NULL,
    character_name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    created_at bigint NOT NULL DEFAULT '0'::bigint,
    CONSTRAINT player_character_data_pkey PRIMARY KEY (id),
    CONSTRAINT player_character_data_owner_player_foreign FOREIGN KEY (owner_player)
        REFERENCES public.player_profile (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE public.player_cards(
    id integer NOT NULL DEFAULT nextval('player_cards_id_seq'::regclass),
    card_id integer NOT NULL,
    owner_player integer NOT NULL,
    card_level integer NOT NULL,
    first_deck boolean NOT NULL,
    consumables integer NOT NULL,
    second_deck boolean NOT NULL DEFAULT false,
    third_deck boolean NOT NULL DEFAULT false,
    quality character varying(10) COLLATE pg_catalog."default" NOT NULL DEFAULT 'none'::character varying,
    CONSTRAINT player_cards_pkey PRIMARY KEY (id),
    CONSTRAINT player_cards_owner_player_foreign FOREIGN KEY (owner_player)
        REFERENCES public.player_profile (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE public.player_character_equipment(
    id integer NOT NULL DEFAULT nextval('player_character_equipment_id_seq'::regclass),
    owner_character integer NOT NULL,
    item_id integer NOT NULL,
    item_level integer NOT NULL,
    item_type character varying(20) COLLATE pg_catalog."default" NOT NULL,
    is_equipped boolean NOT NULL,
    slot_num integer,
    CONSTRAINT player_character_equipment_pkey PRIMARY KEY (id),
    CONSTRAINT player_character_equipment_owner_character_foreign FOREIGN KEY (owner_character)
        REFERENCES public.player_character_data (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE public.player_character_runes(
    id integer NOT NULL DEFAULT nextval('player_character_runes_id_seq'::regclass),
    owner_character integer NOT NULL,
    item_id integer NOT NULL,
    slot_num integer,
    decay_start_timestamp bigint,
    CONSTRAINT player_character_runes_pkey PRIMARY KEY (id),
    CONSTRAINT player_character_runes_owner_character_foreign FOREIGN KEY (owner_character)
        REFERENCES public.player_character_data (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

С индексами

knex.raw('create index "player_cards_owner_player_first_deck_index" on "player_cards"("owner_player") WHERE first_deck = TRUE');
knex.raw('create index "player_cards_owner_player_second_deck_index" on "player_cards"("owner_player") WHERE second_deck = TRUE');
knex.raw('create index "player_cards_owner_player_third_deck_index" on "player_cards"("owner_player") WHERE third_deck = TRUE');
knex.raw('create index "player_character_equipment_owner_character_is_equipped_index" on "player_character_equipment" ("owner_character") WHERE is_equipped = TRUE');
knex.raw('create index "player_character_runes_owner_character_slot_num_not_null_index" on "player_character_runes" ("owner_character") WHERE slot_num IS NOT NULL');

Код

Первый запрос

async.parallel([
    cb => tx('player_character_data')
        .select('character_id', 'id')
        .where('owner_player', playerId)
        .limit(1)
        .asCallback(cb),
    cb => tx('player_character_data')
        .select('character_id', 'id')
        .where('owner_player', enemyId)
        .limit(1)
        .asCallback(cb)
], callbackFn);

Второй запрос

async.parallel([
    cb => tx('player_profile')
        .select('current_active_deck_num')
        .where('id', playerId)
        .asCallback(cb),
    cb => tx('player_profile')
        .select('current_active_deck_num')
        .where('id', enemyId)
        .asCallback(cb)
], callbackFn);

Третий q

playerQ = { first_deck: true }
enemyQ = { first_deck: true }
MAX_CARDS_IN_DECK = 5
async.parallel([
    cb => tx('player_cards')
        .select('card_id', 'card_level')
        .where('owner_player', playerId)
        .andWhere(playerQ)
        .limit(MAX_CARDS_IN_DECK)
        .asCallback(cb),
    cb => tx('player_cards')
        .select('card_id', 'card_level')
        .where('owner_player', enemyId)
        .andWhere(enemyQ)
        .limit(MAX_CARDS_IN_DECK)
        .asCallback(cb)
], callbackFn);

Четвертый q

MAX_EQUIPPED_ITEMS = 3
async.parallel([
    cb => tx('player_character_equipment')
        .select('item_id', 'item_level')
        .where('owner_character', playerCharacterUniqueId)
        .andWhere('is_equipped', true)
        .limit(MAX_EQUIPPED_ITEMS)
        .asCallback(cb),
    cb => tx('player_character_equipment')
        .select('item_id', 'item_level')
        .where('owner_character', enemyCharacterUniqueId)
        .andWhere('is_equipped', true)
        .limit(MAX_EQUIPPED_ITEMS)
        .asCallback(cb)
], callbackFn);

Пятый

runeSlotsMax = 3
async.parallel([
    cb => tx('player_character_runes')
        .select('item_id', 'decay_start_timestamp')
        .where('owner_character', playerCharacterUniqueId)
        .whereNotNull('slot_num')
        .limit(runeSlotsMax)
        .asCallback(cb),
    cb => tx('player_character_runes')
        .select('item_id', 'decay_start_timestamp')
        .where('owner_character', enemyCharacterUniqueId)
        .whereNotNull('slot_num')
        .limit(runeSlotsMax)
        .asCallback(cb)
], callbackFn);

EXPLAIN (ПРОАНАЛИЗИРУЙТЕ)

Только сканирование индексов и < 1мс для планирования и времени выполнения. Может публиковать в случае необходимости (не публиковал для экономии места)

Сам по себе

( total - количество запросов, min/ max/ avg/ медиана strong > для времени отклика)

4 одновременных запроса: { "total": 300, "avg": 1.81, "median": 2, "min": 1, "max": 6 }
400 одновременных запросов:
- { "total": 300, "avg": 209.57666666666665, "median": 176, "min": 9, "max": 1683 } - сначала выберите
- { "total": 300, "avg": 2105.9, "median": 2005, "min": 1563, "max": 4074 } - последний выбор

Я пытался помещать медленные запросы, которые выполняются более чем на 100 мс в журналы - ничего. Также попытался увеличить размер пула подключений до нескольких параллельных запросов - ничего тоже.

Ответ 1

Решение было найдено быстро, но забыл ответить здесь (был занят, извините).

Нет магии с медленными запросами, но только node природа цикла событий:

Все запросы силимара были сделаны параллельно;
У меня есть блок кода с очень медленным временем выполнения (~ 150-200 мс);
Если у вас есть ~ 800 параллельных запросов, то код кода 150 мс преобразуется в задержка цикла событий ~ 10000 мс;
Все, что вы увидите, - это видимость медленных запросов, но это просто отставание функции обратного вызова, а не базы данных;

Заключение: используйте pgBadger для обнаружения медленных запросов и модуля isBusy для обнаружения отставаний цикла событий

Ответ 2

Здесь я вижу три потенциальные проблемы:

400 одновременных запросов на самом деле довольно много, и ваша спецификация вашего компьютера не в восторге. Возможно, это больше связано с моим фоном MSSQL, но я бы предположил, что это случай, когда вам может потребоваться усиление аппаратного обеспечения.
Связь между двумя серверами должна быть довольно быстрой, но может объяснять некоторую задержку, которую вы видите. Один мощный сервер может быть лучшим решением.
Я предполагаю, что у вас есть разумные объемы данных (400 одновременных подключений должны иметь много для хранения). Возможно, может быть полезно опубликовать некоторые из фактически сгенерированных SQL. Многое зависит от SQL Knex, и могут быть доступные оптимизации, которые можно использовать. Индексирование приходит на ум, но нужно было бы обязательно сказать SQL.

Ваш тест, как представляется, не включает в себя латентность сети от клиента, поэтому это может быть дополнительная проблема, которую вы еще не учли.