Índices no Banco de Dados #1156
Comments
|
@filipedeschamps e @aprendendofelipe vcs têm habilitado em produção o slow query log? Ele é uma boa fonte de informações, para sabermos melhor quais queries precisam ser otimizadas, se tiverem, podem disponibilizar por favor? Caso precisem de ajudar para habilitar, a referência que linkei explica e posso ajudar também. Essa é uma otimização que já fiz no passado e gostaria de ajudar aqui :) |
|
@FabricioFFC muito massa te ver aqui no repositório 😍 para quem não sabe eu e o Fabrício já trabalhamos juntos no passado 🤝 E não está habilitado, o único tabnews.com.br/infra/provisioning/modules/database/main.tf Lines 90 to 93 in 2a12cbc |
|
@filipedeschamps @aprendendofelipe show! abri uma PR habilitando, qq coisa só me avisar. |
|
@FabricioFFC segue os principais logs: duration: 2619.477 ms execute <unnamed>:
WITH
latest_published_root_contents AS (
SELECT
contents.id,
contents.owner_id,
contents.parent_id,
contents.slug,
contents.title,
contents.status,
contents.source_url,
contents.created_at,
contents.updated_at,
contents.published_at,
contents.deleted_at,
get_current_balance('content:tabcoin', contents.id) as tabcoins
FROM contents
WHERE
parent_id IS NULL
AND status = 'published'
AND published_at > NOW() - INTERVAL '1 week'
),
ranked_published_root_contents AS (
SELECT
*,
COUNT(*) OVER()::INTEGER as total_rows
FROM latest_published_root_contents
WHERE tabcoins > 0
ORDER BY
tabcoins DESC,
published_at DESC
),
group_1 AS (
SELECT
*,
1 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '36 hours'
AND tabcoins > 11
ORDER BY
published_at DESC
LIMIT 10
),
group_2 AS (
SELECT * FROM group_1
UNION ALL
SELECT
*,
2 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '24 hours'
AND tabcoins > 6
AND id NOT IN (SELECT id FROM group_1)
ORDER BY
rank_group,
published_at DESC
LIMIT 20
),
group_3 AS (
(SELECT
*,
3 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '12 hours'
AND id NOT IN (SELECT id FROM group_2)
ORDER BY
published_at DESC
LIMIT 5)
UNION ALL
SELECT * FROM group_2
),
group_4 AS (
(SELECT
*,
4 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '3 days'
AND tabcoins > 11
AND id NOT IN (SELECT id FROM group_3)
ORDER BY
published_at DESC
LIMIT 10)
UNION ALL
SELECT * FROM group_3
),
group_5 AS (
(SELECT
*,
5 as rank_group
FROM ranked_published_root_contents
WHERE
published_at > NOW() - INTERVAL '72 hours'
AND tabcoins > 2
AND id NOT IN (SELECT id FROM group_4)
ORDER BY
published_at DESC
LIMIT 10)
UNION ALL
SELECT * FROM group_4
),
ranked AS (
SELECT * FROM group_5
UNION ALL
SELECT
*,
6 as rank_group
FROM ranked_published_root_contents
WHERE id NOT IN (SELECT id FROM group_5)
ORDER BY
rank_group,
tabcoins DESC,
published_at DESC
LIMIT $1
OFFSET $2
)
SELECT
ranked.id,
ranked.owner_id,
ranked.parent_id,
ranked.slug,
ranked.title,
ranked.status,
ranked.source_url,
ranked.created_at,
ranked.updated_at,
ranked.published_at,
ranked.deleted_at,
ranked.tabcoins,
ranked.rank_group,
ranked.total_rows,
users.username as owner_username,
(WITH RECURSIVE children AS
(SELECT id,
parent_id
FROM contents as all_contents
WHERE
all_contents.id = ranked.id
AND all_contents.status = 'published'
UNION ALL
SELECT
all_contents.id,
all_contents.parent_id
FROM contents as all_contents
INNER JOIN children ON all_contents.parent_id = children.id
WHERE all_contents.status = 'published'
)
SELECT count(children.id)::integer
FROM children
WHERE children.id NOT IN (ranked.id)
) as children_deep_count
FROM ranked
INNER JOIN users ON ranked.owner_id = users.id
ORDER BY
rank_group,
tabcoins DESC,
published_at DESC;DETAIL: parameters: $1 = '30', $2 = '0'
duration: 3006.857 ms execute <unnamed>:
WITH content_window AS (
SELECT
COUNT(*) OVER()::INTEGER as total_rows,
id
FROM contents
WHERE contents.parent_id IS NOT DISTINCT FROM $3 AND contents.status = $4
ORDER BY contents.published_at DESC
LIMIT $1 OFFSET $2
)
SELECT
contents.id,
contents.owner_id,
contents.parent_id,
contents.slug,
contents.title,
contents.body,
contents.status,
contents.source_url,
contents.created_at,
contents.updated_at,
contents.published_at,
contents.deleted_at,
users.username as owner_username,
content_window.total_rows,
get_current_balance('content:tabcoin', contents.id) as tabcoins,
-- Originally this query returned a list of contents to the server and
-- afterward made an additional roundtrip to the database for every item using
-- the findChildrenCount() method to get the children count. Now we perform a
-- subquery that is not performant but everything is embedded in one travel.
-- https://github.com/filipedeschamps/tabnews.com.br/blob/de65be914f0fd7b5eed8905718e4ab286b10557e/models/content.js#L51
(
WITH RECURSIVE children AS (
SELECT
id,
parent_id
FROM
contents as all_contents
WHERE
all_contents.id = contents.id AND
all_contents.status = 'published'
UNION ALL
SELECT
all_contents.id,
all_contents.parent_id
FROM
contents as all_contents
INNER JOIN
children ON all_contents.parent_id = children.id
WHERE
all_contents.status = 'published'
)
SELECT
count(children.id)::integer
FROM
children
WHERE
children.id NOT IN (contents.id)
) as children_deep_count
FROM
contents
INNER JOIN
content_window ON contents.id = content_window.id
INNER JOIN
users ON contents.owner_id = users.id
ORDER BY contents.published_at DESC
;DETAIL: parameters: $1 = '30', $2 = '900', $3 = NULL, $4 = 'published'
duration: 2312.163 ms execute <unnamed>:
WITH content_window AS (
SELECT
COUNT(*) OVER()::INTEGER as total_rows,
id
FROM contents
WHERE contents.parent_id IS NOT DISTINCT FROM $3 AND contents.status = $4
ORDER BY contents.published_at DESC
LIMIT $1 OFFSET $2
)
SELECT
contents.id,
contents.owner_id,
contents.parent_id,
contents.slug,
contents.title,
contents.body,
contents.status,
contents.source_url,
contents.created_at,
contents.updated_at,
contents.published_at,
contents.deleted_at,
users.username as owner_username,
content_window.total_rows,
get_current_balance('content:tabcoin', contents.id) as tabcoins,
-- Originally this query returned a list of contents to the server and
-- afterward made an additional roundtrip to the database for every item using
-- the findChildrenCount() method to get the children count. Now we perform a
-- subquery that is not performant but everything is embedded in one travel.
-- https://github.com/filipedeschamps/tabnews.com.br/blob/de65be914f0fd7b5eed8905718e4ab286b10557e/models/content.js#L51
(
WITH RECURSIVE children AS (
SELECT
id,
parent_id
FROM
contents as all_contents
WHERE
all_contents.id = contents.id AND
all_contents.status = 'published'
UNION ALL
SELECT
all_contents.id,
all_contents.parent_id
FROM
contents as all_contents
INNER JOIN
children ON all_contents.parent_id = children.id
WHERE
all_contents.status = 'published'
)
SELECT
count(children.id)::integer
FROM
children
WHERE
children.id NOT IN (contents.id)
) as children_deep_count
FROM
contents
INNER JOIN
content_window ON contents.id = content_window.id
INNER JOIN
users ON contents.owner_id = users.id
ORDER BY contents.published_at DESC
;E nosso maior inimigo é o
|
Em se tratando de performance, também acho que criar o |
|
@aprendendofelipe e @filipedeschamps muito bom! Vou focar essa semana nesse tópico, avaliando a criação do Obrigado pela ajuda de vcs! |
|
Show @FabricioFFC! 💪 Sobre a criação do Mas lá não é só otimizar a consulta, pois precisa modelar melhor os dados e mudar a regra de negócio, então se preferir lidar com os índices, também é algo que vai ajudar bastante. Eu só acho melhor começar por lá porque eliminar essa carga vai facilitar depois medir o impacto dos índices no desempenho. 👍 |
|
Me parece também melhor começar pela #1169, pois a criação de índice só valeria agora se tivermos com uma dor latente em produção, que os índices aliviaria (não me parece ser o caso, se for me avise), já que a criação de índice provavelmente será impactada com a criação do |
|
@filipedeschamps, como estão os logs agora? Ainda está pegando algo acima de 2s? |
@filipedeschamps, quando for olhar os logs, aproveita e tenta ver se houve algum aumento significativo no uso do banco de dados com a mudança da revalidação da página de conteúdos de 10s para 1s. Subi para produção às 13:44 e parece que levou um tempo maior para diminuir o número de conexões abertas com o banco, mas já voltou para um patamar só um pouco acima de antes do deploy. |
aprendendofelipe
modified the milestones:
Milestone 6: Performance e Segurança,
Milestone 7: Revenue Share
|
Turma, os logs não vem registrando mais nenhuma query acima de 2s. E a utilização de CPU do banco dificilmente passa de 10%. A gente pode diminuir o valor de O que acham? |
|
Acho que faz sentido diminuir o valor do Então, se houver uma query significativamente lenta, acredito que podemos criar um issue dedicado para ela. Você consegue ver como estão os endpoints? Pode ser que algum seja lento por executar várias queries separadas, e assim nenhuma query chama a atenção no log. |
Olhando os tempos médios de execução das funções lambdas, nenhuma query poderia estar passando de 1s. Podemos deixar para baixar o valor do
Vou fechar 🤝
No momento, não há! 🎉
Se fala dos tempos de resposta das requisições para cada endpoint, não temos essa métrica. Isso seria algo para ser coletado e enviado pelo client. Mas acredito que não fala disso, pois não seria muito útil para analisar a performance do banco de dados, já que boa parte das requisições são atendidas pelo cache. Se fala do tempo de execução das lambdas, isso eu dei uma olhada. Não temos problemas de perfomance com as queries, mas, se fosse o caso de diminuir a utilização de recursos na Vercel (usamos cerca de 25% da cota mensal), deveríamos atacar nesta ordem:
Agora, olhando para UX, precisamos atacar nesta ordem:
O RSS só fica no topo aqui porque eu desconsiderei todos os Por fim, olhando para SEO, acho que o foco seria:
Com tudo isso, se for para dar atenção para algum endpoint, começaria pelo RSS. Mas nada disso envolve problemas com alguma query específica, mas sim com o que é executado na lambda, então de fato essa issue está concluída. 🎉🎉🎉 |
Era isso mesmo. Obrigado pelos dados 🤝, excelente resumo. |
Contexto
Antes do lançamento, se eu não me engano o TabNews não tinha índices voltados a performance (ele tinha apenas voltado para o
uniquenessde um conteúdo), e foi muito interessante ver o comportamento disto em produção 😅 O gráfico abaixo é oloaddo Postgres no dia do lançamento:No dia seguinte, o problema de processamento estava acontecendo de novo, e então eu apliquei em produção a segunda parte desse PR que trazia índice para o
balance_operation:O que fez o
loaddo gráfico li de cima voltar a ficar saudável, literalmente foi aplicar isso aqui abaixo:É um índice de múltipla coluna entre
balance_typeerecipient_idpor conta disso ser usado na procedureget_current_balance:tabnews.com.br/infra/migrations/1655868441752_create-table-balance-operations.js
Lines 47 to 84 in 0bbb0f6
Mais especificamente nesta parte:
tabnews.com.br/infra/migrations/1655868441752_create-table-balance-operations.js
Lines 75 to 77 in 0bbb0f6
Então qualquer
WHEREque queira aproveitar este cache, precisa pedir os dados nesta exata mesma sequência.Bom, escrevi tudo isto para nos animar a procurar mais oportunidades de criar índices, pois tem em todos os lugares do TabNews, desde criação de usuário, criação de conteúdo, leitura de conteúdo, verificação de sessão ativa, literalmente tudo tem margem para otimização.
Execução
Eu sou zero especialista em criar índices, mas devemos tomar cuidado para não criar índices que não tragam resultados reais e para isto sugiro utilizar ferramentas auxiliares para comprovar que, o que estamos fazendo, está de fato trazendo resultado, e tudo isto pode ser testado com a instância local do Postgres que rodamos com o Docker. As ferramentas auxiliares que coletei foram a seguinte, mas não deixem de complementar com outras sugestões:
Sugiro também tomar cuidado com índices de múltiplas colunas, pois pelo que estudei, as vezes o Postgres consegue se dar muito bem combinando vários índices separados, e isto é ótimo, pois nos dá mais flexibilidade nas queries.
Fora isso, para certas coisas precisaremos rearquitetar a modelagem, como por exemplo, para o
findChildrenTree()docontent, vamos provavelmente adicionar uma colunaroot_ide indexar tudo por ali.Bom, qualquer novo PR que for merged sobre índice, vamos listar ele aqui nessa seção de Execução, então por hora vou listar somente o que já foi feito:
findChildrenTreeebalance_operation#882The text was updated successfully, but these errors were encountered: