Em dezembro passado, recebi um relatório de erro interessante da equipe de suporte do VWO. O tempo de carregamento de um dos relatórios analíticos para um grande cliente corporativo parecia proibitivo. E como essa é minha área de responsabilidade, imediatamente me concentrei em resolver o problema.
Antecedentes
Para deixar claro o que estou falando, vou falar um pouco sobre o VWO. Esta é uma plataforma com a qual você pode executar várias campanhas direcionadas em seus sites: realizar experimentos A / B, acompanhar visitantes e conversões, analisar funis de vendas, exibir mapas de calor e reproduzir gravações de visitas.
Mas a coisa mais importante na plataforma é reportar. Todas as funções acima estão interconectadas. E para clientes corporativos, uma enorme variedade de informações seria simplesmente inútil sem que uma plataforma poderosa as apresentasse na forma de análise.
Usando a plataforma, você pode fazer uma solicitação arbitrária em um grande conjunto de dados. Aqui está um exemplo simples:
Mostrar todos os cliques em abc.com
DE <data d1> A <data d2>
para pessoas que
Chrome usado OU
(estavam na Europa e usaram o iPhone)
Preste atenção aos operadores booleanos. Eles estão disponíveis para os clientes na interface de consulta para fazer consultas arbitrariamente complexas para recuperar amostras.
Pedido lento
O cliente em questão estava tentando fazer algo que intuitivamente deveria funcionar rapidamente:
Mostrar todas as notas da sessão
para usuários que visitam qualquer página
com URL onde há "/ empregos"
Havia muito tráfego neste site e armazenamos mais de um milhão de URLs exclusivos apenas para ele. E eles queriam encontrar um modelo de URL bastante simples relacionado ao modelo de negócios.
Investigação preliminar
Vamos ver o que acontece no banco de dados. A seguir 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 < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ;
E aqui estão os horários:
Tempo planejado: 1.480 ms
Tempo de espera: 1431924.650 ms
A solicitação ignorou 150 mil linhas. O planejador de consultas mostrou alguns detalhes interessantes, mas sem gargalos óbvios.
Vamos estudar mais a consulta. Como você pode ver, ele cria três tabelas JOIN :
- sessões : 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 grandes, os armazenamos em uma tabela separada.
Observe também que todas as nossas tabelas já estão divididas por account_id . Assim, uma situação é excluída quando, devido a uma conta especialmente grande, as outras apresentam problemas.
À procura de evidências
Em uma inspeção mais detalhada, vemos que algo em uma solicitação específica não está certo. Vale a pena olhar para esta linha:
urls && array( select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%' )::text[]
O primeiro pensamento foi que, talvez devido ao ILIKE em todos esses URLs longos (temos mais de 1,4 milhão de URLs únicos coletados para esta conta), o desempenho pode diminuir.
Mas não - esse não é o ponto!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 rows) Time: 5231.765 ms
A própria solicitação de pesquisa de modelo leva apenas 5 segundos. Procurar um padrão em um milhão de URLs únicos não é claramente um problema.
O próximo suspeito da lista são alguns JOIN . Talvez seu uso excessivo tenha levado a uma desaceleração? Geralmente, os JOIN os candidatos mais óbvios para problemas de desempenho, mas não acreditava que nosso caso fosse típico.
analytics_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 < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ; count ------- 8086 (1 row) Time: 147.851 ms
E este também não foi o nosso caso. JOIN acabou sendo bastante rápido.
Estreitamos o círculo de suspeitos
Eu estava pronto para começar a alterar a consulta para obter possíveis melhorias de desempenho. Minha equipe e eu desenvolvemos duas idéias principais:
- Use EXISTS para o URL da subconsulta : queremos verificar novamente se houve algum problema com a subconsulta para URLs. Uma maneira de conseguir isso é simplesmente usar
EXISTS . EXISTS pode melhorar muito o desempenho, pois termina imediatamente assim que encontra uma única linha por 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 < to_timestamp(1549177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ; count 32519 (1 row) Time: 1636.637 ms
Bem sim. A subconsulta, quando envolvida em EXISTS , torna tudo super rápido. A próxima pergunta lógica é por que a consulta com JOINs e a própria subconsulta são rápidas individualmente, mas terrivelmente lentas?
- Movemos a subconsulta para o CTE : se a solicitação for rápida por si só, podemos simplesmente calcular o resultado rápido primeiro e depois fornecê-lo à solicitação 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 < to_timestamp(1545107599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0;
Mas ainda estava muito lento.
Encontre o culpado
Todo esse tempo, uma coisinha brilhou diante dos meus olhos, da qual eu constantemente me afastava. Mas como não havia mais nada, decidi olhá-la. Estou falando do operador && . Enquanto EXISTS simplesmente melhorou o desempenho, && foi o único fator comum restante em todas as versões da consulta lenta.
Observando a documentação , vemos que o && usado quando você precisa encontrar elementos comuns entre duas matrizes.
Na solicitação original, é isso:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )
O que significa que fazemos uma pesquisa de modelo para nossos URLs e, em seguida, encontramos a interseção com todos os URLs com registros compartilhados. Isso é um pouco confuso, já que "urls" aqui não se refere a uma tabela que contém todos os URLs, mas a uma coluna "urls" na tabela recording_data .
À medida que && suspeitas de && , tentei encontrar confirmação no plano de consulta gerado pelo EXPLAIN ANALYZE (eu já tinha um plano salvo, mas geralmente é mais conveniente experimentar o SQL do que tentar entender a opacidade dos planejadores de consulta).
Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0)) Rows Removed by Filter: 52710
Havia algumas linhas de filtros somente do && . O que significava que essa operação não era apenas cara, mas também executada várias vezes.
Eu verifiquei isso isolando a condição
SELECT 1 FROM acc_{account_id}.urls as recordings_urls, acc_{account_id}.recording_data_30 as recording_data_30, acc_{account_id}.sessions_30 as sessions_30 WHERE urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]
Este pedido foi lento. Como as JOIN rápidas e as subconsultas são rápidas, apenas o operador && permanece.
Esta é apenas uma operação importante. Sempre precisamos pesquisar em toda a tabela principal de URLs para pesquisar por padrão e sempre precisamos encontrar cruzamentos. Não podemos pesquisar entradas de URL diretamente, porque esses são apenas identificadores que apontam para urls .
Rumo a uma solução
&& lento porque os dois conjuntos são enormes. A operação será relativamente rápida se eu substituir urls por { "http://google.com/", "http://wingify.com/" } .
Comecei a procurar uma maneira de fazer a interseção de conjuntos no Postgres sem usar && , mas sem muito sucesso.
No final, decidimos simplesmente resolver o problema isoladamente: forneça todos os urls string para a qual o URL corresponde ao padrão. Sem condições adicionais, será -
SELECT urls.url FROM acc_{account_id}.urls as urls, (SELECT unnest(recording_data.urls) AS id) AS unrolled_urls WHERE urls.id = unrolled_urls.id AND urls.url ILIKE '%jobs%'
Em vez da sintaxe JOIN , usei apenas uma subconsulta e expandi a matriz recording_data.urls para que a condição pudesse ser aplicada diretamente a WHERE .
O mais importante aqui é que o && usado para verificar se uma determinada entrada contém um URL apropriado. Apertando os olhos um pouco, é possível ver nesta operação movendo-se pelos elementos da matriz (ou linhas da tabela) e parando quando a condição (correspondência) é atendida. Não se parece com nada? Sim, EXISTS .
Como recording_data.urls pode ser referenciado de fora do contexto da subconsulta quando isso acontece, podemos retornar ao nosso velho amigo EXISTS e envolvê-los em uma subconsulta.
Combinando tudo, obtemos a consulta otimizada final:
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 r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 AND EXISTS( SELECT urls.url FROM acc_{account_id}.urls as urls, (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) AS unrolled_urls WHERE urls.id = unrolled_urls.rec_url_id AND urls.url ILIKE '%enterprise_customer.com/jobs%' );
E o tempo de execução final Time: 1898.717 ms É hora de comemorar?!?
Não é tão rápido! Primeiro você precisa verificar a correção. Eu suspeitava muito da otimização EXISTS , pois ela altera a lógica para um fim anterior. Devemos ter certeza de que não adicionamos um erro não óbvio à solicitação.
Uma verificação simples era realizar a count(*) em consultas lentas e rápidas para um grande número de conjuntos de dados diferentes. Então, para um pequeno subconjunto de dados, verifiquei todos os resultados manualmente.
Todas as verificações deram resultados consistentemente positivos. Nós consertamos!
Lições aprendidas
Há muitas lições a serem aprendidas nesta história:
- Os planos de consulta não contam toda a história, mas podem fornecer pistas
- Os principais suspeitos nem sempre são os verdadeiros culpados
- Consultas lentas podem ser interrompidas para isolar gargalos
- Nem todas as otimizações são de natureza redutiva
- O uso do
EXIST , sempre que possível, pode levar a um aumento acentuado da produtividade.
Conclusão
Passamos de um tempo de solicitação de ~ 24 minutos para 2 segundos - um aumento de desempenho muito sério! Embora este artigo tenha sido grande, todas as experiências que fizemos ocorreram no mesmo dia e, segundo estimativas, foram necessárias de 1,5 a 2 horas para otimizações e testes.
SQL é uma linguagem maravilhosa, se não tiver medo, mas tente aprender e usar. Tendo um bom entendimento de como as consultas SQL são executadas, como o banco de dados gera planos de consulta, como os índices funcionam e, simplesmente, o tamanho dos dados com os quais você está lidando, é possível obter sucesso na otimização de consultas. É igualmente importante, no entanto, continuar a tentar abordagens diferentes e acabar com o problema lentamente, encontrando gargalos.
A melhor parte para alcançar esses resultados é uma notável melhoria visível na velocidade - quando um relatório que nem havia sido baixado antes agora é carregado quase instantaneamente.
Um agradecimento especial aos meus colegas de equipe Aditya Misra , Aditya Gauru e Varun Malhotra pelo brainstorming e Dinkar Pandir por encontrar um erro importante em nosso pedido final antes de finalmente nos despedirmos dele!