Por que todos os trabalhadores do Postgresql estão esperando por um único processo?

Estamos usando o PostgreSQL-13 como nosso servidor principal e encontramos um gargalo de desempenho.

O hardware inclui 2 CPUs (AMD EPYC9754 com 128 núcleos e 256 threads cada), 128 GB de memória, hardware RAID0 inclui 2 * 4T SSD (Samsung990Pro).

Acho que o servidor PG não consegue usar todo o potencial do hardware.

Antes de fazer esta pergunta aqui, eu fiz as seguintes coisas:

1. Verifique minha consulta SQL (usando exatamente a chave primária, sem varredura de sequência);
2. Confirmar se meu programa roda corretamente (embora eu tenha escrito a mesma lógica em Python e C++, ambos rodam lentamente);
3. Configurar meu servidor PG para usar o HugePage (configurei 80 GB de memória HugePage no total e permiti que o servidor PG use 64 GB shared_buffers);
4. Aumentar os limites de memória de cada trabalhador PG( work_mem, temp_buffers,...);
5. Aumentar o número de trabalhadores paralelos( max_worker_processes, max_parallel_workers_per_gather, max_parallel_workers,...);
6. Ligar force_parallel_mode;
7. Diminua o custo de io e o custo da CPU cfg( random_page_cost=1.01, cpu_tuple_cost=0.0025, cpu_index_tuple_cost=0.001,...);
8. Maximizado default_statistics_targetpara 1000;
9. Desabilitar replicações;
10. 32 ou 64 processos de inicialização se conectam paralelamente ao servidor e consultam diferentes linhas de uma mesma tabela com diferentes chaves primárias.
11. Todas as consultas são somente leitura e NÃO há operações de inserção/atualização/DDL durante os testes;

O que estou esperando:

1. O servidor PG usa pelo menos 50% dos recursos de CPU/memória;
2. Não há nenhum processo único bloqueando outras consultas;
3. O rendimento total basicamente aumenta linearmente conforme a quantidade de recursos que aloquei ao servidor PG (talvez eu seja ingênuo?);

Meus testes:

1. A definição da tabela:

   CREATE TABLE IF NOT EXISTS ob_lots(
       t_stamp INT8 NOT NULL,
       trd_sec INT4 NOT NULL,
       o_level INT2 NOT NULL,
       i_olots INT4 NOT NULL,
       f_olots FLOAT4 NULL,
   CONSTRAINT prk_ob_lots PRIMARY KEY( t_stamp, o_level ) );
   

2. A pergunta:

   SELECT f_olots, t_stamp, trd_sec FROM ob_lots
   WHERE t_stamp BETWEEN $1 AND $2
   ORDER BY t_stamp DESC, o_level DESC
   LIMIT 4096;
   

3. Programa de teste: Inicie muitas instâncias do meu programa que consultam aleatoriamente linhas da tabela acima paralelamente com o SQL de consulta acima. Quando o número de clientes estava aumentando de 2 para 4, ou de 4 para 8, ou de 8 para 16, observamos que o rendimento total quase dobrou todas as vezes. Mas de 16,32 ou mais, o rendimento total nunca mudou.

4. Explicação SQL:

   Gather  (cost=1000.28..1002.41 rows=1 width=18) (actual time=6.840..9.232 rows=0 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   Buffers: shared hit=8
   ->  Limit  (cost=0.28..2.31 rows=1 width=18) (actual time=0.033..0.033 rows=0 loops=1)
           Buffers: shared hit=8
           ->  Index Scan Backward using prk_ob_lots_sc5555 on ob_lots_sc5555  (cost=0.28..2.31 rows=1 width=18) (actual time=0.031..0.031 rows=0 loops=1)
               Index Cond: ((t_stamp >= 123) AND (t_stamp <= 456))
               Buffers: shared hit=8
   Planning:
   Buffers: shared hit=109
   Planning Time: 0.759 ms
   Execution Time: 9.274 ms
   

As seguintes coisas me parecem estranhas:

1. Há um processo PG que usa quase 100% de um único núcleo de CPU, e os outros usam muito menos;
2. NÃO há io, NENHUMA troca durante o teste, e muitos recursos de memória/CPU/io estão livres/ociosos. Algo parece que todos os outros trabalhadores estão esperando pelo processo PG mestre (imagino que um use 100% da CPU), e resulta em um gargalo;
3. O banco de dados de teste usou cerca de 4 GB de espaço em disco, pequeno o suficiente para ser mantido inteiramente na memória pelo PG. De fato, como observamos, não há nenhuma operação de io;

O que está fazendo? Por que todos os trabalhadores estão esperando por um único processo?

Esta é a parte principal da minha configuração PG (eu apenas listo as linhas que alterei):

max_connections = 2048

buffers compartilhados = 64 GB

huge_pages = ligado

temp_buffers = 256 MB

max_prepared_transactions = 256

work_mem = 256 MB

manutenção_trabalho_mem = 16 GB

memória_de_trabalho_de_vácuo_automático = -1

tipo_de_memória_compartilhada_dinâmica = posix

simultaneidade_io_efetiva = 1000

manutenção_io_concorrência = 1000

max_worker_processes = 256

max_parallel_maintenance_workers = 256

max_parallel_workers_per_gather = 256

parallel_leader_participation = ligado

max_parallel_workers = 256

fsync = desligado

synchronous_commit = desligado

full_page_writes = desativado

wal_compression = ativado

wal_buffers = -1

atraso_do_escritor_wal = 10000ms

wal_writer_flush_after = 1 GB

atraso_de_commit = 100000

commit_siblings = 128

tempo_limite_do_ponto_de_verificação = 1d

max_wal_size = 128 GB

tamanho_min_wal = 32 GB

meta_de_conclusão_do_ponto_de_verificação = 1.0

checkpoint_flush_after = 0

aviso_do_ponto_de_verificação = 0

max_wal_senders = 0

custo_da_página_seq = 1,0

custo_da_página_aleatória = 1,01

custo_tupla_da_cpu = 0,0025

custo_da_tupla_do_índice_da_cpu = 0,001

custo_operador_cpu = 0,00125

tamanho_do_cache_efetivo = 64 GB

meta_estatística_padrão = 1000

force_parallel_mode = ativado

autovacuum = ligado

E as saídas de tope iotop:

topo - 16:38:16 acima 4:09, 2 usuários, média de carga: 14,16, 9,14, 3,97

Número: 1581 no total, 2 em execução, 1573 dormindo, 0 parado, 6 zumbis

%Cpu(s): 3,5 us, 4,3 sy, 0,0 ni, 92,1 id, 0,0 wa, 0,0 hi, 0,1 si, 0,0 st

GiB Mem: 125,6 total, 34,6 grátis, 82,9 usado, 9,1 buff/cache

GiB Swap: 1,0 total, 1,0 grátis, 0,0 usado. 42,6 disponível Mem

进程号 USER PR NI VIRT RES SHR SWAP %CPU %MEM TIME+ COMMAND

31159 leon 20 0 4654,2m 105,1m 12,5m 0,0m S 152,7 0,1 7:09,93 carga

3186 postgres 0 -20 66,7g 18,6m 16,2m 0,0m R 99,5 0,0 5:03.16 postgres #é o mestre?

3192 postgres 0 -20 80,4m 6,8m 3,6m 0,0m S 8,2 0,0 0:24,97 postgres

32218 postgres 0 -20 66,7g 13,5m 9,9m 0,0m S 5,8 0,0 0:12,90 postgres

31217 postgres 0 -20 66,7g 13,4m 9,9m 0,0m S 5,3 0,0 0:12,74 postgres

31234 postgres 0 -20 66,7g 13,5m 9,9m 0,0m S 5,3 0,0 0:12,74 postgres

(muitos muitos processos postgres...)

Total de LEITURA DE DISCO: 0,00 B/s | Total de GRAVAÇÃO DE DISCO: 0,00 B/s

LEITURA DE DISCO atual: 0,00 B/s | GRAVAÇÃO DE DISCO atual: 0,00 B/s

TID  PRIO  USER     DISK READ DISK WRITE>    COMMAND

  1 be/4 root        0.00 B/s    0.00 B/s init

  2 be/4 root        0.00 B/s    0.00 B/s [kthreadd]

  3 be/0 root        0.00 B/s    0.00 B/s [rcu_gp]

  4 be/0 root        0.00 B/s    0.00 B/s [rcu_par_gp]

  5 be/0 root        0.00 B/s    0.00 B/s [slub_flushwq]