javascript – Knex与PostgreSQL选择查询在多个并行请求上性能退化极差

我正在开发一个游戏(梦想),我的后端堆栈是具有Knex的Node.js和Postgresql(9.6).我在这里持有所有玩家的数据,我需要频繁请求.
其中一个请求需要进行10个简单的选择,这将提取数据,这是问题开始的地方：这些查询速度相当快(〜1ms),如果服务器只同时提供1个请求.但是如果服务器服务器多个并行请求(100-400),则查询执行时间会非常恶化(每个查询最多可能需要几秒钟)

细节

为了更客观,我将描述服务器的请求目标,选择查询和我收到的结果.

关于系统

我在数字海洋上运行节点代码4cpu / 8gb的液滴和Postgres在同一个conf(2个不同的液滴,相同的配置)

关于请求

它需要做一些游戏操作,他从DB中选择2名玩家的数据

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,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,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),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',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);

三,

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',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',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',enemyCharacterUniqueId)
        .whereNotNull('slot_num')
        .limit(runeslotsMax)
        .asCallback(cb)
],callbackFn);

EXPLAIN(分析)

只有索引扫描和< 1ms规划和执行时间.如果需要可以发布(没有发布以节省空间) 时间本身 (总数为请求数,最小/最大/平均/中位数为响应时间)
> 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} – 最后选择

我试图将执行超过100ms的缓慢查询放入日志中 – 没有.还试图将连接池大小增加到并行请求的数量 – 也没有.