ProHoster > Blog > Administración > A historia dunha investigación SQL

A historia dunha investigación SQL

O pasado mes de decembro recibín un informe de erro interesante do equipo de soporte de VWO. O tempo de carga dun dos informes de análise dun gran cliente corporativo parecía prohibitivo. E como esta é a miña área de responsabilidade, centreime inmediatamente en resolver o problema.

prehistoria

Para que quede claro do que falo, vouvos falar un pouco de VWO. Esta é unha plataforma coa que podes lanzar varias campañas dirixidas nos teus sitios web: realizar experimentos A/B, rastrexar visitantes e conversións, analizar o funil de vendas, mostrar mapas de calor e reproducir gravacións de visitas.

Pero o máis importante da plataforma é informar. Todas as funcións anteriores están interconectadas. E para os clientes corporativos, unha gran cantidade de información sería simplemente inútil sen unha poderosa plataforma que a presente en forma de análise.

Usando a plataforma, pode facer unha consulta aleatoria nun gran conxunto de datos. Aquí tes un exemplo sinxelo:

Mostra todos os clics na páxina "abc.com" DE <data d1> A <data d2> para as persoas que usaron Chrome OU (situadas en Europa E usaron un iPhone)

Preste atención aos operadores booleanos. Están dispoñibles para os clientes na interface de consulta para realizar consultas arbitrariamente complexas para obter mostras.

Solicitude lenta

O cliente en cuestión estaba tentando facer algo que intuitivamente debería funcionar rapidamente:

Mostrar todos os rexistros de sesión dos usuarios que visitaron calquera páxina cun URL que contén "/jobs"

Este sitio tiña moito tráfico e estabamos almacenando máis dun millón de URL únicos só para iso. E querían atopar un modelo de URL bastante sinxelo relacionado co seu modelo de negocio.

Investigación preliminar

Vexamos o que está a suceder na base de datos. A continuación está a consulta SQL lenta orixinal:

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 aquí están os horarios:

Tempo previsto: 1.480 ms Tempo de execución: 1431924.650 ms

A consulta rastrexou 150 mil filas. O planificador de consultas mostrou un par de detalles interesantes, pero sen embotellamentos obvios.

Imos estudar máis a solicitude. Como podes ver, faino JOIN tres táboas:

  1. sesións: para mostrar información da sesión: navegador, axente de usuario, país, etc.
  2. datos_gravación: URL rexistrados, páxinas, duración das visitas
  3. URL: Para evitar duplicar URL extremadamente grandes, almacenámolos nunha táboa separada.

Teña en conta tamén que todas as nosas táboas xa están divididas por account_id. Deste xeito, exclúese unha situación na que unha conta especialmente grande cause problemas a outras.

Buscando pistas

Tras unha inspección máis atenta, vemos que hai algo mal nunha solicitude en particular. Paga a pena botar unha ollada máis atenta a esta liña:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

O primeiro pensamento foi que quizais porque ILIKE en todos estes URL longos (temos máis de 1,4 millóns único URL recompilados para esta conta) o rendemento pode sufrir.

Pero non, ese non é o punto!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

A solicitude de busca do modelo en si só leva 5 segundos. Buscar un patrón en un millón de URL únicos claramente non é un problema.

O seguinte sospeitoso da lista son varios JOIN. Quizais o seu uso excesivo provocou a desaceleración? Normalmente JOINSon os candidatos máis obvios para problemas de rendemento, pero non cría que o noso caso fose 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 tampouco foi o noso caso. JOINresultou ser bastante rápido.

Reducindo o círculo de sospeitosos

Estaba preparado para comezar a cambiar a consulta para conseguir calquera posible mellora de rendemento. O meu equipo e eu desenvolvemos dúas ideas principais:

  • Use EXISTS para o URL da subconsulta: Queriamos comprobar de novo se había algún problema coa subconsulta dos URL. Unha forma de logralo é simplemente usar EXISTS. EXISTS lata mellorar moito o rendemento xa que remata inmediatamente en canto atopa a única cadea que coincide coa condición.

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

Pois si. Subconsulta cando se envolve EXISTS, fai que todo sexa super rápido. A seguinte pregunta lóxica é por que a solicitude con JOIN-ami e a propia subconsulta son rápidos individualmente, pero son terriblemente lentos xuntos?

  • Mover a subconsulta ao CTE : Se a consulta é rápida por si mesma, poderemos calcular primeiro o resultado rápido e despois proporcionalo á 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;

Pero aínda foi moi lento.

Atopar o culpable

Durante todo este tempo, unha pequena cousa brillou diante dos meus ollos, que constantemente apartou. Pero como xa non quedaba nada máis, decidín mirala tamén. Estou falando de && operador. Adeus EXISTS só mellorou o rendemento && foi o único factor común restante en todas as versións da consulta lenta.

Mirando documentación, vemos iso && úsase cando precisa atopar elementos comúns entre dúas matrices.

Na solicitude orixinal esta é:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

O que significa que facemos unha busca de patróns nos nosos URL e despois atopamos a intersección con todos os URL con publicacións comúns. Isto é un pouco confuso porque aquí "urls" non se refire á táboa que contén todos os URL, senón á columna "urls" da táboa recording_data.

Con crecentes sospeitas respecto &&, tentei buscar confirmación para eles no plan de consulta xerado EXPLAIN ANALYZE (Xa tiña un plan gardado, pero normalmente estou máis cómodo experimentando en SQL que tentando comprender a opacidade dos planificadores 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

Había varias liñas de filtros só de &&. O que significaba que esta operación non só era cara, senón que tamén se realizaba varias veces.

Probei isto illando a condición

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 son rápidos e as subconsultas son rápidas, o único que quedaba era && operador.

Esta é só unha operación clave. Sempre necesitamos buscar en toda a táboa subxacente de URL para buscar un patrón e sempre necesitamos atopar interseccións. Non podemos buscar directamente por rexistros de URL, porque estes son só IDs aos que se refire urls.

De camiño a unha solución

&& lento porque os dous conxuntos son enormes. A operación será relativamente rápida se o substitúo urls en { "http://google.com/", "http://wingify.com/" }.

Comecei a buscar unha forma de facer unha intersección de conxunto en Postgres sen usar &&, pero sen moito éxito.

Ao final, decidimos resolver o problema de forma illada: dáme todo urls liñas para as que o URL coincide co patrón. Sen condicións 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%'

Pola contra JOIN sintaxe Acabo de usar unha subconsulta e a expandín recording_data.urls matriz para que poida aplicar directamente a condición en WHERE.

O máis importante aquí é iso && usado para comprobar se unha determinada entrada contén un URL coincidente. Se obrigas un pouco, podes ver que esta operación se move polos elementos dunha matriz (ou filas dunha táboa) e detense cando se cumpre unha condición (coincidimento). Non che lembra a nada? Si, EXISTS.

Dende en diante recording_data.urls pode ser referenciado desde fóra do contexto de subconsulta, cando isto ocorre podemos recurrir ao noso vello amigo EXISTS e envolve a subconsulta con ela.

Xuntando todo, obtemos a consulta optimizada 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 final Time: 1898.717 ms Hora de celebrar?!?

Non tan rápido! Primeiro cómpre comprobar a corrección. Desconfiaba moito EXISTS optimización xa que cambia a lóxica para completar antes. Debemos asegurarnos de non engadir un erro non obvio á solicitude.

Era executar unha proba sinxela count(*) tanto en consultas lentas como rápidas para un gran número de conxuntos de datos diferentes. Despois, para un pequeno subconxunto dos datos, verifiquei manualmente que todos os resultados fosen correctos.

Todas as probas deron resultados constantemente positivos. Arreglamos todo!

Leccións aprendidas

Hai moitas leccións que se poden aprender desta historia:

  1. Os plans de consulta non contan toda a historia, pero poden proporcionar pistas
  2. Os principais sospeitosos non sempre son os verdadeiros culpables
  3. As consultas lentas pódense dividir para illar os pescozos de botella
  4. Non todas as optimizacións son de natureza redutiva
  5. Usar EXIST, sempre que sexa posible, pode levar a aumentos dramáticos da produtividade

Saída

Pasamos dun tempo de consulta de ~24 minutos a 2 segundos, un aumento de rendemento bastante significativo. Aínda que este artigo saíu grande, todos os experimentos que fixemos ocorreron nun día, e estimouse que tardaron entre 1,5 e 2 horas para optimizar e probar.

SQL é unha linguaxe marabillosa se non lle temes, pero intenta aprendelo e usalo. Ao ter unha boa comprensión de como se executan as consultas SQL, como a base de datos xera plans de consulta, como funcionan os índices e simplemente o tamaño dos datos que está a tratar, pode ter moito éxito á hora de optimizar as consultas. Non obstante, é igualmente importante seguir probando enfoques diferentes e desfacendo lentamente o problema, atopando os pescozos de botella.

A mellor parte de conseguir resultados como estes é a notable e visible mellora da velocidade, onde un informe que antes nin sequera se cargaba agora se carga case ao instante.

Agradecemento especial a meus compañeiros ás ordes de Aditya Mishra, Aditya Gauru и Varun Malhotra para brainstorming e Dinkar Pandir por atopar un erro importante na nosa solicitude final antes de que finalmente nos despedimos del!

Fonte: www.habr.com

Engadir un comentario