简而言之

我正在开发一个(梦想中的)游戏，我的后端堆栈是Node.js和PostgreSQL(9.6)与Knex。我在这里保存了所有球员的数据，我需要经常请求。其中一个请求需要进行10个简单的选择来提取数据，这就是问题的开始：如果服务器同时只提供1个请求，这些查询非常快(~1ms)。但是，如果服务器服务器并行处理许多请求(100-400)，查询执行时间会急剧下降(每次查询可能长达数秒)

详细信息

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

关于系统

我在同一个conf上运行Digital Ocean 4cpu/8gb液滴和Postgres上的节点代码(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,
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);

解释(分析)

只有索引扫描和<1ms用于计划和执行时间。如果需要可以发布(没有发布以节省空间)

时间本身

(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 }-最后一次选择

我试图将执行时间超过100ms的慢速查询放入日志中，但一无所获。还试图将连接池的大小增加到并行请求的数量，但也没有。

很快找到了一个解决方案，但忘记在此处响应(很忙，抱歉)。

没有慢速查询的魔力，但只有节点的事件循环性质：

所有silimar请求都是并行提出的
我有一个执行时间非常慢的代码块(约150-200ms)
如果您有~800个并行请求，150ms代码块转换为~10000ms事件循环滞后
您将看到的只是慢速请求的可见性，但这只是回调函数的滞后，而不是数据库

结论：使用pgBadger检测慢速查询，使用isBusy模块检测事件循环滞后

我可以在这里看到三个潜在的问题：

400个并发请求实际上相当多，而且您的机器规范没有什么好兴奋的。也许这更多地与我的MSSQL背景有关，但我想这是一个你可能需要加强硬件的情况
两台服务器之间的通信应该很快，但可能会造成一些延迟。一个功能强大的服务器可能是更好的解决方案
我假设您有合理的数据量(400个并发连接应该有很多数据要存储)。也许发布一些实际生成的SQL可能会很有用。这在很大程度上取决于SQL Knex所提供的，并且可能有可用的优化。索引会浮现在脑海中，但需要看看SQL才能确定

您的测试似乎不包括客户端的网络延迟，因此这可能是您尚未考虑的另一个问题。

Knex与PostgreSQL选择查询在多个并行请求上性能极度下降