Nos processadores modernos, existem muitos núcleos. Durante anos, aplicações enviaram solicitações para bancos de dados em paralelo. Se é uma solicitação de relatório para várias linhas em uma tabela, ela é executada mais rapidamente quando utiliza vários processadores, e no PostgreSQL isso é possível a partir da versão 9.6.
Foram necessários 3 anos para implementar a função de consultas paralelas — foi preciso reescrever o código em diferentes etapas de execução das consultas. Na versão 9.6 do PostgreSQL, surgiu a infraestrutura para melhorias adicionais no código. Em versões posteriores, outros tipos de consultas também são executados em paralelo.
Restrições
- Não ative a execução paralela se todos os núcleos já estiverem ocupados, caso contrário, outras consultas ficarão lentas.
- O mais importante, o processamento paralelo com valores elevados de WORK_MEM utiliza muita memória — cada conexão de hash ou ordenação consome memória na quantidade de work_mem.
- Consultas OLTP com baixa latência não podem ser aceleradas por execução paralela. E se a consulta retorna uma linha, o processamento paralelo só irá desacelerá-la.
- Os desenvolvedores gostam de usar o benchmark TPC-H. Talvez você tenha consultas semelhantes para execução paralela ideal.
- Apenas consultas SELECT sem bloqueio de predicado são executadas em paralelo.
- Às vezes, a indexação correta é melhor do que a varredura sequencial da tabela em modo paralelo.
- Suspensão de consultas e cursores não são suportados.
- Funções de janela e funções agregadas de conjuntos ordenados não são paralelas.
- Você não ganha nada na carga de trabalho de entrada e saída.
- Não existem algoritmos de ordenação paralela. Mas consultas com ordenações podem ser executadas em paralelo em alguns aspectos.
- Substitua CTE (WITH …) por um SELECT aninhado para habilitar o processamento paralelo.
- Encapsulamentos de dados de terceiros ainda não suportam processamento paralelo (mas poderiam!)
- FULL OUTER JOIN não é suportado.
- max_rows desabilita o processamento paralelo.
- Se a consulta contiver uma função não marcada como PARALLEL SAFE, ela será unifio.
- O nível de isolamento de transação SERIALIZABLE desabilita o processamento paralelo.
Ambiente de teste
Os desenvolvedores do PostgreSQL tentaram reduzir o tempo de resposta das consultas do benchmark TPC-H. Baixe o benchmark e . Este é um uso não oficial do benchmark TPC-H — não para comparação de bancos de dados ou hardware.
- Baixe TPC-H_Tools_v2.17.3.zip (ou uma versão mais recente) .
- Renomeie makefile.suite para Makefile e altere conforme descrito aqui: . Compile o código usando o comando make.
- Gere os dados:
.\/dbgen -s 10cria um banco de dados de 23 GB. Isso é suficiente para observar a diferença no desempenho entre consultas paralelas e não paralelas. - Converta os arquivos
tblemcsv com foresed. - Clone o repositório
pg_tpche copie os arquivoscsvempg_tpch\/dss\/data. - Crie consultas com o comando
qgen. - Carregue os dados no banco com o comando
.\/tpch.sh.
Varredura sequencial paralela
Ela pode ser mais rápida não por causa da leitura paralela, mas porque os dados são distribuídos por muitos núcleos de CPU. Em sistemas operacionais modernos, arquivos de dados do PostgreSQL são bem armazenados em cache. Com a leitura antecipada, é possível obter do armazenamento um bloco maior do que o solicitado pelo daemon PG. Portanto, o desempenho da consulta não é limitado pela entrada e saída do disco. Ele consome ciclos de CPU para:
- ler linhas uma a uma das páginas da tabela;
- comparar os valores das linhas e as condições
WHERE.
Vamos executar uma consulta simples select:
tpch=# explain analyze select l_quantity as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on lineitem (cost=0.00..1964772.00 rows=58856235 width=5) (actual time=0.014..16951.669 rows=58839715 loops=1)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 1146337
Planning Time: 0.203 ms
Execution Time: 19035.100 msA varredura sequencial fornece muitas linhas sem agregação, portanto, a consulta é executada em um único núcleo de CPU.
Se adicionar SUM(), é possível ver que dois processos de trabalho ajudarão a acelerar a consulta:
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 msAgregação paralela
O nó "Parallel Seq Scan" gera linhas para a agregação parcial. O nó "Partial Aggregate" reduz essas linhas utilizando SUM(). No final, o contador SUM de cada processo de trabalho é reunido pelo nó "Gather".
O resultado final é calculado pelo nó 'Finalize Aggregate'. Se você tiver suas próprias funções de agregação, não se esqueça de marcá-las como 'parallel safe'.
Número de processos de trabalho
O número de processos de trabalho pode ser aumentado sem reiniciar o servidor:
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 msO que está acontecendo aqui? O número de processos de trabalho dobrou, enquanto a requisição ficou apenas 1,6599 vezes mais rápida. Os cálculos são interessantes. Tínhamos 2 processos de trabalho e 1 líder. Após a alteração, passamos a ter 4+1.
Nossa aceleração máxima com processamento paralelo: 5/3 = 1,66(6) vezes.
Como funciona?
Processos
A execução da requisição sempre começa com o processo líder. O líder realiza toda a parte não paralela e parte do processamento paralelo. Os outros processos que executam as mesmas requisições são chamados de processos de trabalho. O processamento paralelo utiliza a infraestrutura (desde a versão 9.4). Como outras partes do PostgreSQL utilizam processos e não threads, uma requisição com 3 processos de trabalho poderia ser até 4 vezes mais rápida que o processamento tradicional.
Interação
Os processos de trabalho se comunicam com o líder através de uma fila de mensagens (baseada em memória compartilhada). Cada processo tem 2 filas: para erros e para tuplas.
Quantos processos de trabalho são necessários?
O limite mínimo é definido pelo parâmetro . Em seguida, o executor de requisições obtém os processos de trabalho do pool, limitado pelo parâmetro . O último limite é o , ou seja, o número total de processos em segundo plano.
Se não for possível alocar um processo de trabalho, o processamento será feito de forma sequencial.
O planejador de requisições pode reduzir o número de processos de trabalho com base no tamanho da tabela ou do índice. Para isso, existem os parâmetros e .
set min_parallel_table_scan_size='8MB'
8MB tabela => 1 trabalhador
24MB tabela => 2 trabalhadores
72MB tabela => 3 trabalhadores
x => log(x / min_parallel_table_scan_size) / log(3) + 1 trabalhadorCada vez que a tabela é 3 vezes maior que min_parallel_(index|table)_scan_size, o Postgres adiciona um processo de trabalho. O número de processos de trabalho não se baseia em custos. A dependência circular dificulta implementações complexas. Em vez disso, o planejador utiliza regras simples.
Na prática, essas regras nem sempre são adequadas para ambientes de produção, então é possível alterar o número de processos de trabalho para uma tabela específica: ALTER TABLE … SET (parallel_workers = N).
Por que o processamento paralelo não é utilizado?
Além de uma longa lista de limitações, existem também verificações de custo:
— para evitar o processamento paralelo de consultas curtas. Este parâmetro estima o tempo para preparar a memória, iniciar o processo e realizar a troca inicial de dados.
: a comunicação do líder com os trabalhadores pode se prolongar proporcionalmente ao número de tuplas dos processos de trabalho. Este parâmetro calcula os custos de troca de dados.
Junção de Laços Aninhados — Nested Loop Join
O PostgreSQL 9.6+ pode executar loops aninhados em paralelo — é uma operação simples.
explain (custo desligado) select c_custkey, count(o_orderkey)
from customer left outer join orders on
c_custkey = o_custkey and o_comment not like '%specialposits%'
group by c_custkey;
PLANO DA CONSULTA
--------------------------------------------------------------------------------------
Finalizar GroupAggregate
Chave do Grupo: customer.c_custkey
-> Gather Merge
Trabalhadores Planejados: 4
-> Partial GroupAggregate
Chave do Grupo: customer.c_custkey
-> Nested Loop Left Join
-> Parallel Index Only Scan usando customer_pkey em customer
-> Index Scan usando idx_orders_custkey em orders
Condição do Índice: (customer.c_custkey = o_custkey)
Filtro: ((o_comment)::text !~~ '%specialposits%'::text)A coleta acontece na última fase, então a Junção de Laços Aninhados — Nested Loop Left Join — é uma operação paralela. O Parallel Index Only Scan apareceu apenas na versão 10. Ele funciona de maneira semelhante ao escaneamento sequencial paralelo. A condição c_custkey = o_custkey lê uma ordem para cada linha de cliente. Portanto, não é paralelo.
Junção de Hash — Hash Join
Antes do PostgreSQL 11, cada processo de trabalho criava sua própria tabela de hash. E se houver mais de quatro desses processos, o desempenho não aumenta. Na nova versão, a tabela de hash é compartilhada. Cada processo de trabalho pode usar WORK_MEM para criar uma tabela de hash.
selecione
l_shipmode,
somatório(caso
quando o_orderpriority = '1-URGENTE'
ou o_orderpriority = '2-ALTO'
então 1
senão 0
fim) como high_line_count,
somatório(caso
quando o_orderpriority '1-URGENTE'
e o_orderpriority '2-ALTO'
então 1
senão 0
fim) como low_line_count
de
orders,
lineitem
onde
o_orderkey = l_orderkey
e l_shipmode em ('MAIL', 'AIR')
e l_commitdate < l_receiptdate
e l_shipdate = data '1996-01-01'
e l_receiptdate < data '1996-01-01' + intervalo '1' ano
grupo por
l_shipmode
ordem por
l_shipmode
LIMIT 1;
PLANO DE CONSULTA
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limite (custo=1964755.66..1964961.44 linhas=1 largura=27) (tempo real=7579.592..7922.997 linhas=1 laços=1)
-> Finalizar GroupAggregate (custo=1964755.66..1966196.11 linhas=7 largura=27) (tempo real=7579.590..7579.591 linhas=1 laços=1)
Chave do Grupo: lineitem.l_shipmode
-> Junção de Reunião (custo=1964755.66..1966195.83 linhas=28 largura=27) (tempo real=7559.593..7922.319 linhas=6 laços=1)
Trabalhadores Planejados: 4
Trabalhadores Lançados: 4
-> GroupAggregate Parcial (custo=1963755.61..1965192.44 linhas=7 largura=27) (tempo real=7548.103..7564.592 linhas=2 laços=5)
Chave do Grupo: lineitem.l_shipmode
-> Classificações (custo=1963755.61..1963935.20 linhas=71838 largura=27) (tempo real=7530.280..7539.688 linhas=62519 laços=5)
Chave de Classificação: lineitem.l_shipmode
Método de Classificação: mesclagem externa Disco: 2304kB
Trabalhador 0: Método de Classificação: mesclagem externa Disco: 2064kB
Trabalhador 1: Método de Classificação: mesclagem externa Disco: 2384kB
Trabalhador 2: Método de Classificação: mesclagem externa Disco: 2264kB
Trabalhador 3: Método de Classificação: mesclagem externa Disco: 2336kB
-> Junção Hash Paralela (custo=382571.01..1957960.99 linhas=71838 largura=27) (tempo real=7036.917..7499.692 linhas=62519 laços=5)
Condição Hash: (lineitem.l_orderkey = orders.o_orderkey)
-> Varredura Sequencial Paralela sobre lineitem (custo=0.00..1552386.40 linhas=71838 largura=19) (tempo real=0.583..4901.063 linhas=62519 laços=5)
Filtro: ((l_shipmode = QUALQUER ('{MAIL,AIR}'::bpchar[])) E (l_commitdate < l_receiptdate) E (l_shipdate = '1996-01-01'::date) E (l_receiptdate < '1997-01-01 00:00:00'::timestamp sem fuso horário))
Linhas Removidas pelo Filtro: 11934691
-> Hash Paralela (custo=313722.45..313722.45 linhas=3750045 largura=20) (tempo real=2011.518..2011.518 linhas=3000000 laços=5)
Baldes: 65536 Lotes: 256 Uso de Memória: 3840kB
-> Varredura Sequencial Paralela sobre orders (custo=0.00..313722.45 linhas=3750045 largura=20) (tempo real=0.029..995.948 linhas=3000000 laços=5)
Tempo de Planejamento: 0.977 ms
Tempo de Execução: 7923.770 msA Consulta 12 do TPC-H mostra claramente uma junção hash paralela. Cada worker participa na criação de uma tabela hash global.
Junção de Mesclagem — Merge Join
A junção de mesclagem é não paralela por natureza. Não se preocupe se esta for a última etapa da consulta, pois ela ainda pode ser executada em paralelo.
-- Consulta 2 do TPC-H
explique (custos desativados) selecione s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from part, supplier, partsupp, nation, region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 36
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AMÉRICA'
and ps_supplycost = (
selecione
min(ps_supplycost)
from partsupp, supplier, nation, region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AMÉRICA'
)
order by s_acctbal desc, n_name, s_name, p_partkey
LIMIT 100;
PLANO DA CONSULTA
----------------------------------------------------------------------------------------------------------
Limite
-> Ordenar
Chave de Ordenação: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
-> Junção de Mesclagem
Condição de Mesclagem: (part.p_partkey = partsupp.ps_partkey)
Filtro de Junção: (partsupp.ps_supplycost = (SubPlano 1))
-> Coletar Mesclagem
Trabalhadores Planejados: 4
-> Varredura de Índice Paralela usando <strong>part_pkey</strong> em parte
Filtro: (((p_type)::text ~~ '%BRASS'::text) E (p_size = 36))
-> Materializar
-> Ordenar
Chave de Ordenação: partsupp.ps_partkey
-> Laço Aninhado
-> Laço Aninhado
Filtro de Junção: (nation.n_regionkey = region.r_regionkey)
-> Varredura Sequencial na região
Filtro: (r_name = 'AMÉRICA'::bpchar)
-> Junção por Hash
Condição de Hash: (supplier.s_nationkey = nation.n_nationkey)
-> Varredura Sequencial no fornecedor
-> Hash
-> Varredura Sequencial na nação
-> Varredura de Índice usando idx_partsupp_suppkey em partsupp
Condição de Índice: (ps_suppkey = supplier.s_suppkey)
SubPlano 1
-> Agregar
-> Laço Aninhado
Filtro de Junção: (nation_1.n_regionkey = region_1.r_regionkey)
-> Varredura Sequencial na região region_1
Filtro: (r_name = 'AMÉRICA'::bpchar)
-> Laço Aninhado
-> Laço Aninhado
-> Varredura de Índice usando idx_partsupp_partkey em partsupp partsupp_1
Condição de Índice: (part.p_partkey = ps_partkey)
-> Varredura de Índice usando supplier_pkey em supplier supplier_1
Condição de Índice: (s_suppkey = partsupp_1.ps_suppkey)
-> Varredura de Índice usando nation_pkey em nation nation_1
Condição de Índice: (n_nationkey = supplier_1.s_nationkey)O nó 'Merge Join' está acima do 'Gather Merge'. Portanto, a mesclagem não utiliza o processamento paralelo. Mas o nó 'Parallel Index Scan' ainda ajuda com a segmentação. part_pkey.
Junção por Seções
No PostgreSQL 11 desativado por padrão: seu agendamento é muito custoso. Tabelas com particionamentos semelhantes podem ser unidas seção por seção. Assim, o Postgres usará tabelas hash menores. Cada junção de seções pode ser paralela.
tpch=# set enable_partitionwise_join=t;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
QUERY PLAN
---------------------------------------------------
Append
-> Hash Join
Hash Cond: (t2.b = t1.a)
-> Seq Scan on prt2_p1 t2
Filter: ((b >= 0) AND (b <= 10000))
-> Hash
-> Seq Scan on prt1_p1 t1
Filter: (b = 0)
-> Hash Join
Hash Cond: (t2_1.b = t1_1.a)
-> Seq Scan on prt2_p2 t2_1
Filter: ((b >= 0) AND (b <= 10000))
-> Hash
-> Seq Scan on prt1_p2 t1_1
Filter: (b = 0)
tpch=# set parallel_setup_cost = 1;
tpch=# set parallel_tuple_cost = 0.01;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
QUERY PLAN
-----------------------------------------------------------
Gather
Workers Planned: 4
-> Parallel Append
-> Parallel Hash Join
Hash Cond: (t2_1.b = t1_1.a)
-> Parallel Seq Scan on prt2_p2 t2_1
Filter: ((b >= 0) AND (b <= 10000))
-> Parallel Hash
-> Parallel Seq Scan on prt1_p2 t1_1
Filter: (b = 0)
-> Parallel Hash Join
Hash Cond: (t2.b = t1.a)
-> Parallel Seq Scan on prt2_p1 t2
Filter: ((b >= 0) AND (b <= 10000))
-> Parallel Hash
-> Parallel Seq Scan on prt1_p1 t1
Filter: (b = 0)Principalmente, a junção por seções pode ser paralela, apenas se essas seções forem grandes o suficiente.
Anexo Paralelo — Parallel Append
pode ser utilizado em vez de diferentes blocos em diferentes processos de trabalho. Normalmente, isso acontece com consultas UNION ALL. A desvantagem é a menor paralelização, pois cada processo de trabalho processa apenas 1 consulta.
Aqui, 2 processos de trabalho foram iniciados, embora 4 tenham sido ativados.
tpch=# explain (costs off) select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day union all select sum(l_quantity) as sum_qty from lineitem where l_shipdate Parallel Append
-> Aggregate
-> Seq Scan on lineitem
Filter: (l_shipdate Aggregate
-> Seq Scan on lineitem lineitem_1
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)As variáveis mais importantes
- WORK_MEM limita a quantidade de memória para cada processo, não apenas para as consultas: work_mem processos conexões = muita memória.
- — quantos processos trabalhistas o programa em execução usará para o processamento paralelo a partir do plano.
- — ajusta o número total de processos trabalhistas com base no número de núcleos de CPU no servidor.
- — o mesmo, mas para processos trabalhistas paralelos.
Resultados
A partir da versão 9.6, o processamento paralelo pode melhorar significativamente o desempenho de consultas complexas que escaneiam muitas linhas ou índices. No PostgreSQL 10, o processamento paralelo é habilitado por padrão. Não se esqueça de desativá-lo em servidores com alta carga de trabalho OLTP. Escaneamentos sequenciais ou de índices consomem muitos recursos. Se você não estiver executando um relatório sobre um conjunto de dados completo, as consultas podem ser mais eficientes, simplesmente adicionando os índices ausentes ou usando particionamento adequado.
Links
Fonte: habr.com
