🥇A história de uma investigação SQL

Em dezembro passado recebi um relatório de bug interessante da equipe de suporte do VWO. O tempo de carregamento de um dos relatórios analíticos de um grande cliente corporativo parecia proibitivo. E como esta é a minha área de responsabilidade, concentrei-me imediatamente em resolver o problema.

Pré-história

Para deixar claro do que estou falando, vou contar um pouco sobre o VWO. Esta é uma plataforma com a qual você pode lançar diversas campanhas direcionadas em seus sites: realizar experimentos A/B, rastrear visitantes e conversões, analisar o funil de vendas, exibir mapas de calor e reproduzir gravações de visitas.

Mas o mais importante da plataforma são os relatórios. Todas as funções acima estão interligadas. E para clientes corporativos, uma enorme quantidade de informações seria simplesmente inútil sem uma plataforma poderosa que as apresentasse em forma analítica.

Usando a plataforma, você pode fazer uma consulta aleatória em um grande conjunto de dados. Aqui está um exemplo simples:

Mostrar todos os cliques na página "abc.com" DE <data d1> A <data d2> para pessoas que usaram o Chrome OU (localizadas na Europa E usaram um iPhone)

Preste atenção aos operadores booleanos. Eles estão disponíveis aos clientes na interface de consulta para fazer consultas arbitrariamente complexas para obter amostras.

Solicitação lenta

O cliente em questão estava tentando fazer algo que intuitivamente deveria funcionar rapidamente:

Mostrar todos os registros de sessão de usuários que visitaram qualquer página com uma URL contendo "/jobs"

Este site tinha muito tráfego e estávamos armazenando mais de um milhão de URLs exclusivos apenas para ele. E eles queriam encontrar um modelo de URL bastante simples relacionado ao seu modelo de negócios.

Investigação preliminar

Vamos dar uma olhada no 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 < 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 execução: 1431924.650 ms

A consulta rastreou 150 mil linhas. O planejador de consultas mostrou alguns detalhes interessantes, mas nenhum gargalo óbvio.

Vamos estudar mais o pedido. Como você pode ver, ele faz JOIN três tabelas:

sessões: para exibir informações da sessão: navegador, agente do usuário, país e assim por diante.
dados_gravação: URLs gravados, páginas, duração das visitas
URLs: para evitar a duplicação de URLs extremamente grandes, nós os armazenamos em uma tabela separada.

Observe também que todas as nossas tabelas já estão particionadas por account_id. Desta forma, é excluída uma situação em que uma conta particularmente grande causa problemas a outras.

Procurando por pistas

Após uma inspeção mais detalhada, vemos que algo está errado com uma solicitação específica. Vale a pena dar uma olhada mais de perto 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 porque ILIKE em todos esses URLs longos (temos mais de 1,4 milhão único URLs coletados para esta conta) o desempenho pode ser prejudicado.

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 solicitação de pesquisa de modelo em si leva apenas 5 segundos. Procurar um padrão em um milhão de URLs únicos claramente não é um problema.

O próximo suspeito da lista são vários JOIN. Talvez o uso excessivo tenha causado a desaceleração? Geralmente JOINsão os candidatos mais óbvios para problemas de desempenho, mas não acreditei 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. JOINacabou sendo bastante rápido.

Reduzindo 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 2 ideias principais:

Use EXISTS para URL de subconsulta: queríamos verificar novamente se houve algum problema com a subconsulta dos URLs. Uma maneira de conseguir isso é simplesmente usar EXISTS. EXISTS lata melhora muito o desempenho, pois termina imediatamente assim que encontra a única string que corresponde à 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. Subconsulta quando envolvida em EXISTS, torna tudo super rápido. A próxima questão lógica é por que a solicitação com JOIN-ami e a própria subconsulta são rápidas individualmente, mas são terrivelmente lentas juntas?

Movendo a subconsulta para o CTE : Se a consulta for rápida por si só, podemos simplesmente calcular o resultado rápido primeiro e depois 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 < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Mas ainda era muito lento.

Encontrando o culpado

Todo esse tempo, uma coisinha passou diante dos meus olhos, que eu constantemente deixava de lado. Mas como não sobrou mais nada, resolvi olhar para ela também. estou falando sobre && operador. Tchau EXISTS apenas melhorou o desempenho && foi o único fator comum restante em todas as versões da consulta lenta.

Olhando documentação, nós vemos que && usado quando você precisa encontrar elementos comuns entre duas matrizes.

Na solicitação original é:

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 padrões em nossos URLs e, em seguida, encontramos a interseção com todos os URLs com postagens comuns. Isso é um pouco confuso porque "urls" aqui não se refere à tabela que contém todos os URLs, mas à coluna "urls" da tabela recording_data.

Com suspeitas crescentes em relação &&, tentei encontrar confirmação para eles no plano de consulta gerado EXPLAIN ANALYZE (Eu já tinha um plano salvo, mas geralmente fico mais confortável experimentando SQL do que tentando entender a opacidade dos planejadores de consultas).

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 várias linhas de filtros apenas de &&. O que significava que esta operação não era apenas cara, mas também realizada várias vezes.

Eu testei 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[]

Esta consulta foi lenta. Porque o JOIN-s são rápidos e as subconsultas são rápidas, a única coisa que restou foi && operador.

Esta é apenas uma operação chave. Sempre precisamos pesquisar toda a tabela subjacente de URLs para procurar um padrão e sempre precisamos encontrar interseções. Não podemos pesquisar diretamente por registros de URL, porque estes são apenas IDs referentes a urls.

A caminho de uma solução

&& lento porque ambos os conjuntos são enormes. A operação será relativamente rápida se eu substituir urls em { "http://google.com/", "http://wingify.com/" }.

Comecei a procurar uma maneira de definir interseção no Postgres sem usar &&, mas sem muito sucesso.

No final, decidimos apenas resolver o problema isoladamente: me dê tudo urls linhas para as quais 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 de JOIN sintaxe, acabei de usar uma subconsulta e expandi recording_data.urls array para que você possa aplicar diretamente a condição em WHERE.

O mais importante aqui é que && usado para verificar se uma determinada entrada contém um URL correspondente. Se você apertar um pouco os olhos, poderá ver que esta operação se move através dos elementos de uma matriz (ou linhas de uma tabela) e para quando uma condição (correspondência) é atendida. Não te lembra nada? Sim, EXISTS.

Desde então recording_data.urls pode ser referenciado fora do contexto da subconsulta, quando isso acontece podemos recorrer ao nosso velho amigo EXISTS e envolva a subconsulta com ela.

Juntando tudo, obtemos a consulta final otimizada:

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 prazo final Time: 1898.717 ms Hora de celebrar?!?

Não tão rápido! Primeiro você precisa verificar a exatidão. Eu estava extremamente desconfiado EXISTS otimização, pois altera a lógica para terminar mais cedo. Precisamos ter certeza de que não adicionamos um erro não óbvio à solicitação.

Um teste simples foi executar 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 manualmente se todos os resultados estavam corretos.

Todos os testes deram resultados consistentemente positivos. Consertamos tudo!

Lições aprendidas

Há muitas lições a serem aprendidas com esta 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 divididas para isolar gargalos
Nem todas as otimizações são de natureza redutora
Usar EXIST, sempre que possível, pode levar a aumentos dramáticos na produtividade

Jogar aviator online grátis: hack aviator funciona

Passamos de um tempo de consulta de aproximadamente 24 minutos para 2 segundos - um aumento de desempenho bastante significativo! Embora este artigo tenha saído grande, todos os experimentos que fizemos aconteceram em um dia e foi estimado que demoraram entre 1,5 e 2 horas para otimizações e testes.

SQL é uma linguagem maravilhosa se você não tiver medo dela, mas tente aprender e usá-la. Ao ter 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, você pode ter muito sucesso na otimização de consultas. É igualmente importante, no entanto, continuar a tentar abordagens diferentes e, lentamente, decompor o problema, encontrando os estrangulamentos.

A melhor parte de obter resultados como esses é a melhoria perceptível e visível na velocidade - onde um relatório que antes nem carregava agora é carregado quase instantaneamente.

Agradecimentos especiais meus camaradas sob o comando de Aditya Mishra, Aditya Gauru и Varun Malhotra para brainstorming e Dinkar Pandir por encontrar um erro importante em nosso pedido final antes de finalmente nos despedirmos dele!

Fonte: habr.com

A história de uma investigação SQL