Estou inserindo dados da tmp_detailstabela na detailstabela usando uma consulta INSERT em blocos. Também estou modificando os dados enquanto os insiro. A consulta está demorando muito para ser executada. Meu IOPS de leitura está atingindo 2500s, e meu IOPS de gravação está perto de 100. Minha utilização de E/S é 100%, e minha utilização de CPU é menor que 10%. Minha utilização de RAM é menor que 40%. Estou usando ctidpara inserções de blocos de 2.000.000 de linhas. O que posso fazer para melhorar o desempenho das minhas consultas e reduzir a utilização de E/S? Quero aumentar a utilização da CPU para mais de 80%.
Especificações do servidor:
- Versão do PostgreSQL: 15.6
- RAM: 32 GB
- Núcleos: 16
- Espaço em disco: SSD 500 GB
- SO: Linux Ubuntu 22.04
Configuração do PostgreSQL:
max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4
Detalhes da tabela:
| Nome da tabela | Contagem de linhas | Tamanho |
|---|---|---|
| fonte_cbsupi_tmp_detalhes | 60 milhões | 30 GB |
| fonte_npciupi_tmp_detalhes | 60 milhões | 30 GB |
| fonte_cbsupi_tmp_detalhes | 60 milhões | 30 GB |
Há índices nas colunas uniquekey, key_priority_radcs, key_priority_rtdps, is_processed, e key_priority_ratrs. Tive que usar a cláusula DISTINCT ON porque estava obtendo linhas duplicadas devido ao JOIN. Tentei inserir em blocos de 1.000.000 de linhas usando ctid, mas ainda está demorando muito para executar, provavelmente porque ele tem que escanear toda a tabela C e D para cada iteração. Então, inseri os dados como um todo, 60 milhões de linhas e então executei um commit no final. Meu objetivo é executar essas consultas de inserção semelhantes para a tabela C e D em paralelo a partir do servidor de aplicativo de backend, mas será inútil se minha Utilização de E/S for 100%.
Inserir consulta:
EXPLAIN
INSERT
INTO
cbsupi.source_cbsupi_details (codglacct,
refusrno,
key_priority_radcs,
recon_created_date,
dattxnposting,
status,
uniquekey,
coddrcr,
cbsacqiss,
codacctno,
amttxnlcy,
acnotrim,
priority_no,
rrn,
recon_updated_date,
recon_date_1_to_2,
recon_date_1_to_3,
reconciliation_date_time ) (
SELECT
DISTINCT ON
(A.uniquekey) A.codglacct,
A.refusrno,
A.key_priority_radcs,
A.recon_created_date,
A.dattxnposting,
A.status,
A.uniquekey,
A.coddrcr,
A.cbsacqiss,
A.codacctno,
A.amttxnlcy,
A.acnotrim,
A.priority_no,
A.rrn,
'2025-01-07 19:50:41' AS recon_updated_date,
CASE
WHEN C.key_priority_rtdps IS NOT NULL THEN '2025-01-07 19:50:41'
ELSE NULL
END::TIMESTAMP AS recon_date_1_to_2,
CASE
WHEN D.key_priority_ratrs IS NOT NULL THEN '2025-01-07 19:50:41'
ELSE NULL
END::TIMESTAMP AS recon_date_1_to_3,
CASE
WHEN (C.key_priority_rtdps IS NOT NULL
AND D.key_priority_ratrs IS NOT NULL) THEN '2025-01-07 19:50:41'
ELSE NULL
END::TIMESTAMP AS reconciliation_date_time
FROM
cbsupi.source_cbsupi_tmp_details A
LEFT JOIN switchupi.source_switchupi_tmp_details C ON
(A.key_priority_radcs = C.key_priority_rtdps)
LEFT JOIN npciupi.source_npciupi_tmp_details D ON
(A.key_priority_radcs = D.key_priority_ratrs)
WHERE
A.is_processed IS NULL ) ON
CONFLICT (uniquekey) DO
UPDATE
SET
recon_updated_date = EXCLUDED.recon_updated_date,
recon_date_1_to_3 = EXCLUDED.recon_date_1_to_3,
key_priority_radcs = EXCLUDED.key_priority_radcs,
status = EXCLUDED.status,
reconciliation_date_time = EXCLUDED.reconciliation_date_time,
codacctno = EXCLUDED.codacctno,
amttxnlcy = EXCLUDED.amttxnlcy,
recon_date_1_to_2 = EXCLUDED.recon_date_1_to_2,
rrn = EXCLUDED.rrn,
codglacct = EXCLUDED.codglacct,
refusrno = EXCLUDED.refusrno,
dattxnposting = EXCLUDED.dattxnposting,
coddrcr = EXCLUDED.coddrcr,
cbsacqiss = EXCLUDED.cbsacqiss,
acnotrim = EXCLUDED.acnotrim,
priority_no = EXCLUDED.priority_no;
Explique os resultados
"QUERY PLAN"
Insert on source_cbsupi_details (cost=72270111.44..73213761.44 rows=0 width=0)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: source_cbsupi_details_pkey
" -> Subquery Scan on ""*SELECT*"" (cost=72270111.44..73213761.44 rows=62910000 width=811)"
-> Unique (cost=72270111.44..72584661.44 rows=62910000 width=823)
-> Sort (cost=72270111.44..72427386.44 rows=62910000 width=823)
Sort Key: a.uniquekey
-> Hash Left Join (cost=10739152.00..50771187.50 rows=62910000 width=823)
Hash Cond: (a.key_priority_radcs = d.key_priority_ratrs)
-> Hash Left Join (cost=5337191.00..25537830.00 rows=62910000 width=800)
Hash Cond: (a.key_priority_radcs = c.key_priority_rtdps)
-> Seq Scan on source_cbsupi_tmp_details a (cost=0.00..2092124.00 rows=62910000 width=767)
Filter: (is_processed IS NULL)
-> Hash (cost=4118441.00..4118441.00 rows=60000000 width=33)
-> Seq Scan on source_switchupi_tmp_details c (cost=0.00..4118441.00 rows=60000000 width=33)
-> Hash (cost=4124101.00..4124101.00 rows=62910000 width=33)
-> Seq Scan on source_npciupi_tmp_details d (cost=0.00..4124101.00 rows=62910000 width=33)
JIT:
Functions: 24
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
Questões:
- Como posso melhorar o desempenho da consulta e reduzir a utilização de E/S?
- Existe uma maneira de executar essas consultas de inserção semelhantes em paralelo a partir do aplicativo sem atingir o limite de utilização de E/S?
- Inserir dados em blocos me beneficiará ou é melhor inserir todos os dados de uma vez? Porque, pelo que observei, inserir dados em blocos está levando mais tempo do que inserir todos os dados de uma vez.
EDITAR:
Estou anexando o plano de consulta para a instrução select. Aparentemente, a consulta Insert está levando mais de uma hora, enquanto a instrução Select está levando apenas 265 segundos. Acho que meu problema está no commit único no final, possivelmente devido à geração excessiva de log. Funcionaria se eu mantivesse o auto-commit ativado? Existe uma maneira de inserir em pedaços sem fazer um loop pela tabela inteira?
Para o select autônomo, levou mais de 4 minutos, e isso estava usando 9 processos. O insert não usará paralelismo (nem para a parte select dele), então podemos ingenuamente esperar que leve mais de 36 minutos apenas para executar a parte select dessa consulta. E isso não inclui executar gatilhos ou atualizar índices, o que é claro que um INSERT precisa fazer, então sim, podemos facilmente esperar que leve bem mais de uma hora para o INSERT.
Infelizmente, mesmo se você deixar o INSERT rodar até a conclusão com
EXPLAIN (ANALYZE, BUFFERS)e com track_io_timing definido como on, o PostgreSQL ainda não lhe dará nenhuma informação significativa sobre quanto tempo foi gasto na manutenção de índices, seja individualmente ou coletivamente. Mas ele pelo menos lhe dará informações sobre quanto tempo levou para executar gatilhos, e também sobre quantas linhas foram resolvidas por ON CONFLICT.A melhor maneira de investigar o problema do índice seria criar uma tabela fictícia com as mesmas colunas, mas sem gatilhos ou índices e ver quanto tempo leva para inserir nessa tabela fictícia. Claro que neste caso nenhuma linha resultaria em ações ON CONFLICT, então isso não refletiria essa parte da situação. Então você poderia adicionar os índices de volta um por um e ver se um em particular domina o tempo de execução.
Você poderia adicionar um ORDER BY ao select que retornaria linhas em ordem, o que é mais propício para consulta e manutenção de índices. Mas como você tem 5 índices separados, é improvável que haja uma ordem que satisfaça todos eles. Claro que se funcionasse, você nem precisaria fazer isso em paralelo a partir do aplicativo para obter o benefício.
Essas são duas determinações independentes ou você está apenas assumindo que, como 2500 IOPS estão atingindo sua taxa prometida, então ela deve ser 100% utilizada, mas sem avaliação independente desse fato? Observe que a manutenção do índice geralmente gerará IO aleatório, enquanto seu plano SELECT geraria principalmente IO sequencial. A eficiência com que o IO sequencial é agrupado no IOPS depende de detalhes ou do seu sistema kernel/FS/IO, que eu não conheço.
Isso é improvável, tanto por motivos mecanicistas/teóricos, quanto porque, se fosse verdade, você esperaria que seu IOPS fosse dominado por gravações, não por leituras.