Em dezembro do ano passado, recebi um relatório interessante sobre um erro da equipe de suporte da VWO. O tempo de carregamento de um dos relatórios analíticos para um grande cliente corporativo parecia excessivamente alto. Como essa é minha área de responsabilidade, imediatamente me concentrei em resolver o problema.
Antecedentes
Para que fique claro sobre o que estou falando, vou contar um pouco sobre a VWO. É uma plataforma que permite lançar diferentes campanhas segmentadas em seus sites: realizar testes A/B, rastrear visitantes e conversões, fazer análise de funil de vendas, exibir mapas de calor e reproduzir gravações de visitas.
Mas o mais importante na plataforma é a geração de relatórios. Todas as funções mencionadas estão interligadas. Para clientes corporativos, uma enorme quantidade de informações seria simplesmente inútil sem uma plataforma poderosa que as apresentasse em uma forma útil para análise.
Usando a plataforma, é possível fazer consultas arbitrárias em um grande conjunto de dados. Aqui está um exemplo simples:
Mostrar todos os cliques na página "abc.com" DE <data d1> ATÉ <data d2> para pessoas que usaram Chrome OU (estiveram na Europa E usaram iPhone)
Observe os operadores booleanos. Eles estão disponíveis para clientes na interface de consulta, permitindo fazer consultas complexas para obter amostras.
Consulta lenta
O cliente em questão estava tentando fazer algo que intuitivamente deveria funcionar rapidamente:
Mostre todos os registros de sessões para usuários que visitaram qualquer página com a URL contendo "/jobs"
Este site tinha um enorme volume de tráfego, e armazenávamos mais de um milhão de URLs únicas apenas para ele. E eles queriam encontrar um padrão de URL bastante simples relacionado ao seu modelo de negócios.
Investigação preliminar
Vamos ver o que está acontecendo no banco de dados. Abaixo está a consulta SQL lenta original:
SELECT
count(*)
FROM
acc_{account_id}.urls as recordings_urls,
acc_{account_id}.recording_data as recording_data,
acc_{account_id}.sessions as sessions
WHERE
recording_data.usp_id = sessions.usp_id
AND sessions.referrer_id = recordings_urls.id
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )
AND r_time > to_timestamp(1542585600)
AND r_time = 5
AND recording_data.num_of_pages > 0 ;Aqui estão os tempos:
Tempo estimado: 1.480 ms Tempo de execução: 1431924.650 ms
A consulta percorreu 150 mil linhas. O planejador de consultas destacou alguns detalhes interessantes, mas não havia gargalos óbvios.
Vamos continuar analisando a consulta. Como pode ser visto, ela faz JOIN três tabelas:
- sessions: para exibir informações da sessão: navegador, agente do usuário, país e assim por diante.
- recording_data: URLs gravados, páginas, duração das visitas
- urls: para evitar a duplicação de URLs extremamente longas, as armazenamos em uma tabela separada.
Observe também que todas as nossas tabelas já estão divididas por account_id. Dessa forma, elimina-se a situação em que um único conta muito grande causa problemas para as outras.
Em busca de pistas
Ao analisar mais de perto, vemos que algo está errado em uma consulta específica. Vale a pena prestar atenção nesta linha:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]O primeiro pensamento foi que talvez, por causa do ILIKE em todas essas URLs longas (temos mais de 1,4 milhão de URLs únicas, coletadas para esta conta) o desempenho poderia estar comprometido. Mas não, não é isso!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 linhas)Tempo: 5231.765 ms
A própria consulta de busca por padrão leva apenas 5 segundos. A busca por padrão em um milhão de URLs únicas claramente não é um problema.O próximo suspeito na lista são alguns
. Será que seu uso excessivo levou a uma desaceleração? Normalmente JOIN‘as são os candidatos mais óbvios para problemas de desempenho, mas eu não acreditava que nosso caso fosse típico. JOINanalytics_db=# SELECT count(*) FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data_0 as recording_data, acc_{account_id}.sessions_0 as sessions WHERE recording_data.usp_id = sessions.usp_id AND sessions.referrer_id = recordings_urls.id AND r_time > to_timestamp(1542585600) AND r_time =5 AND recording_data.num_of_pages > 0 ; count ------- 8086 (1 linha)Tempo: 147.851 ms
E esse também não era o nosso caso.‘as se mostraram bastante rápidas. JOINEstamos reduzindo o número de suspeitos
Eu estava preparado para começar a modificar a consulta para alcançar quaisquer melhorias de desempenho possíveis. Nós da equipe desenvolvemos duas ideias principais:
Usar EXISTS para a subconsulta de URL
- : Queríamos verificar mais uma vez se havia problemas com a subconsulta das URLs. Uma das maneiras de conseguir isso é simplesmente usar: Queríamos verificar mais uma vez se há problemas com a subconsulta para URLs. Uma das maneiras de fazer isso é simplesmente usar
EXISTS.EXISTSmelhorar significativamente o desempenho, pois termina imediatamente assim que encontra uma única linha que atende à condição.
SELECT
count(*)
FROM
acc_{account_id}.urls as recordings_urls,
acc_{account_id}.recording_data as recording_data,
acc_{account_id}.sessions as sessions
WHERE
recording_data.usp_id = sessions.usp_id
AND ( 1 = 1 )
AND sessions.referrer_id = recordings_urls.id
AND (exists(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%'))
AND r_time > to_timestamp(1547585600)
AND r_time =5
AND recording_data.num_of_pages > 0 ;
count
32519
(1 row)
Time: 1636.637 msSim. A subconsulta, quando envolta em EXISTS, torna tudo super rápido. A próxima pergunta lógica é por que a consulta com JOIN-s e a própria subconsulta são rápidas separadamente, mas funcionam muito lentamente juntas?
- Movemos a subconsulta para CTE : se a consulta é rápida por si só, podemos primeiro calcular o resultado rápido e, em seguida, fornecê-lo à consulta principal.
WITH matching_urls AS (
select id::text from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%'
)
SELECT
count(*) FROM acc_{account_id}.urls as recordings_urls,
acc_{account_id}.recording_data as recording_data,
acc_{account_id}.sessions as sessions,
matching_urls
WHERE
recording_data.usp_id = sessions.usp_id
AND ( 1 = 1 )
AND sessions.referrer_id = recordings_urls.id
AND (urls && array(SELECT id from matching_urls)::text[])
AND r_time > to_timestamp(1542585600)
AND r_time =5
AND recording_data.num_of_pages > 0;Mas isso ainda estava muito lento.
Encontramos o culpado
Durante todo esse tempo, uma pequena coisa ficou piscando na minha frente, da qual eu sempre tentei me desfazer. Mas como não havia mais nada a fazer, decidi dar uma olhada nela. Estou falando do && operador. Enquanto EXISTS apenas melhorou o desempenho, && era o único fator comum restante em todas as versões da consulta lenta.
Olhando para , vemos que && é usado quando precisamos encontrar elementos comuns entre dois arrays.
Na consulta original, isso é:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )O que significa que estamos fazendo uma busca por padrão em nossos URLs, e então encontramos a interseção com todos os URLs com registros comuns. Isso é um pouco confuso, pois 'urls' aqui não se refere à tabela que contém todos os URLs, mas à coluna 'urls' na tabela recording_data.
Com o aumento das suspeitas sobre &&, tentei encontrar confirmação delas no plano de consulta gerado EXPLAIN ANALYZE (eu já tinha um plano salvo, mas geralmente prefiro experimentar no SQL do que tentar entender as opacidades dos otimizadores de consultas).
Filtro: ((urls && ($0)::text[]) E (r_time > '2018-12-17 12:17:23+00'::timestamp com fuso horário) E (r_time = '5'::double precision) E (num_of_pages > 0))
Linhas removidas pelo filtro: 52710Havia várias linhas de filtros somente de &&. O que significava que essa operação não apenas era cara, mas também era executada várias vezes.
Verifiquei isso, isolando a condição
SELECT 1
FROM
acc_{account_id}.urls como recordings_urls,
acc_{account_id}.recording_data_30 como recording_data_30,
acc_{account_id}.sessions_30 como sessions_30
WHERE
urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]Essa consulta estava sendo executada lentamente. Como JOIN-s são rápidos e subconsultas são rápidas, só restava && o operador.
Apenas essa é a operação chave. Precisamos sempre pesquisar em toda a tabela principal de URLs para buscar por padrão, e sempre precisamos encontrar interseções. Não podemos pesquisar registros de URLs diretamente, porque são apenas IDs que se referenciam a urls.
No caminho para a solução
&& lento, porque ambos os conjuntos são enormes. A operação será relativamente rápida se eu substituir urls para { "http://google.com/", "http://wingify.com/" }.
Comecei a procurar uma maneira de fazer em Postgres interseções de conjuntos sem usar &&, mas sem muito sucesso.
No final, decidimos simplesmente resolver o problema isoladamente: me dê todas as urls linhas para as quais o URL corresponde ao padrão. Sem condições adicionais, isso será —
SELECT urls.url
FROM
acc_{account_id}.urls como urls,
(SELECT unnest(recording_data.urls) AS id) COMO unrolled_urls
WHERE
urls.id = unrolled_urls.id E
urls.url ILIKE '%jobs%'Em vez de JOIN semântica, eu simplesmente usei uma subconsulta e desenrolei recording_data.urls o array, para que pudéssemos aplicar a condição diretamente em WHERE.
O que é mais importante aqui é que && é usado para verificar se o registro contém a URL correspondente. Piscar os olhos um pouco pode permitir ver nesta operação o deslocamento pelos elementos do array (ou linhas da tabela) e a parada quando a condição (correspondência) é atendida. Isso soa familiar? Aham, EXISTS.
Como recording_data.urls pode ser referenciado fora do contexto da subconsulta, quando isso acontece, podemos voltar ao nosso velho amigo EXISTS e envolvê-lo na subconsulta.
Juntando tudo, obtemos a consulta otimizada final:
SELECIONE
count(*)
DE
acc_{account_id}.urls como recordings_urls,
acc_{account_id}.recording_data como recording_data,
acc_{account_id}.sessions como sessions
ONDE
recording_data.usp_id = sessions.usp_id
E ( 1 = 1 )
E sessions.referrer_id = recordings_urls.id
E r_time > to_timestamp(1542585600)
E r_time =5
E recording_data.num_of_pages > 0
E EXISTE(
SELECIONE urls.url
DE
acc_{account_id}.urls como urls,
(SELECIONE unnest(urls) COMO rec_url_id DE acc_{account_id}.recording_data)
COMO unrolled_urls
ONDE
urls.id = unrolled_urls.rec_url_id E
urls.url ILIKE '%enterprise_customer.com/jobs%'
);
E o tempo final de execução Tempo: 1898.717 ms É hora de comemorar?!?
Não tão rápido! Primeiro, é preciso verificar a precisão. Eu estava extremamente cético em relação à EXISTS otimização, pois muda a lógica para um término mais antecipado. Precisamos ter certeza de que não adicionamos um erro não óbvio na consulta.
Uma verificação simples consistiu em executar count(*) tanto em consultas lentas quanto rápidas para um grande número de diferentes conjuntos de dados. Em seguida, para um pequeno subconjunto de dados, verifiquei a correção de todos os resultados manualmente.
Todas as verificações resultaram consistentemente positivas. Nós consertamos tudo!
Lições Aprendidas
Podem ser extraídas muitas lições dessa história:
- Os planos de consulta não contam toda a história, mas podem dar dicas
- Os principais suspeitos nem sempre são os verdadeiros culpados
- Consultas lentas podem ser divididas para isolar gargalos
- Nem todas as otimizações são, por natureza, redutivas
- Uso
EXIST, onde possível, pode levar a um crescimento exponencial no desempenho
Saída
Passamos de um tempo de consulta de ~24 minutos para 2 segundos — um aumento de desempenho bastante significativo! Embora este artigo tenha se tornado extenso, todos os experimentos que realizamos ocorreram em um único dia, e estimamos que levaram de 1,5 a 2 horas para otimizações e testes.
SQL é uma linguagem maravilhosa, se você não tiver medo dela, mas tentar compreendê-la e usá-la. Com uma boa compreensão de como as consultas SQL são executadas, como o banco de dados gera planos de consulta, como os índices funcionam e apenas do tamanho dos dados que você está lidando, você pode ter um grande sucesso na otimização de consultas. Não menos importante, no entanto, continuar experimentando diferentes abordagens e gradualmente fragmentar o problema, encontrando gargalos.
A melhor parte de alcançar resultados como este é a melhoria visível e notável na velocidade de funcionamento — quando um relatório, que antes não carregava, agora é carregado quase instantaneamente.
Agradecimentos especiais aos meus colegas da equipe Aditya Mishra, Aditya Gaur e pela tempestade de ideias e Dinkar Pandir por encontrar um erro crucial em nossa solicitação final, antes que nos despedíssemos dela!
Fonte: habr.com
