🥇La historia de una investigación SQL

En diciembre pasado recibí un interesante informe de error del equipo de soporte de VWO. El tiempo de carga de uno de los informes analíticos de un gran cliente corporativo parecía prohibitivo. Y como esta es mi área de responsabilidad, inmediatamente me concentré en solucionar el problema.

Prehistoria

Para que quede claro de qué hablo, les hablaré un poco sobre VWO. Esta es una plataforma con la que puede lanzar varias campañas específicas en sus sitios web: realizar experimentos A/B, realizar un seguimiento de los visitantes y las conversiones, analizar el embudo de ventas, mostrar mapas de calor y reproducir registros de visitas.

Pero lo más importante de la plataforma son los informes. Todas las funciones anteriores están interconectadas. Y para los clientes corporativos, una gran cantidad de información sería simplemente inútil sin una plataforma potente que la presente en forma analítica.

Al utilizar la plataforma, puede realizar una consulta aleatoria en un gran conjunto de datos. He aquí un ejemplo sencillo:

Mostrar todos los clics en la página "abc.com" DESDE <fecha d1> HASTA <fecha d2> para personas que usaron Chrome O (ubicadas en Europa Y usaron un iPhone)

Preste atención a los operadores booleanos. Están disponibles para los clientes en la interfaz de consulta para realizar consultas arbitrariamente complejas para obtener muestras.

Solicitud lenta

El cliente en cuestión estaba intentando hacer algo que intuitivamente debería funcionar rápidamente:

Mostrar todos los registros de sesión de los usuarios que visitaron cualquier página con una URL que contenga "/jobs"

Este sitio tenía mucho tráfico y almacenábamos más de un millón de URL únicas solo para él. Y querían encontrar una plantilla de URL bastante simple que se relacionara con su modelo de negocio.

Investigación preliminar

Echemos un vistazo a lo que sucede en la base de datos. A continuación se muestra la 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 ;

Y aquí están los horarios:

Tiempo planificado: 1.480 ms Tiempo de ejecución: 1431924.650 ms

La consulta recorrió 150 mil filas. El planificador de consultas mostró un par de detalles interesantes, pero ningún cuello de botella obvio.

Estudiemos la solicitud más a fondo. Como puedes ver, lo hace. JOIN tres mesas:

sesiones: para mostrar información de la sesión: navegador, agente de usuario, país, etc.
grabando datos: URL registradas, páginas, duración de las visitas
urls: Para evitar duplicar URL extremadamente grandes, las almacenamos en una tabla separada.

También tenga en cuenta que todas nuestras tablas ya están particionadas por account_id. De esta manera se excluye una situación en la que una cuenta particularmente grande cause problemas a otras.

buscando pistas

Tras una inspección más cercana, vemos que algo anda mal con una solicitud en particular. Vale la pena echar un vistazo más de cerca a esta línea:

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

El primer pensamiento fue que tal vez porque ILIKE en todas estas URL largas (tenemos más de 1,4 millones unico URL recopiladas para esta cuenta) el rendimiento puede verse afectado.

¡Pero no, ese no es el punto!

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

Time: 5231.765 ms

La solicitud de búsqueda de plantilla en sí tarda sólo 5 segundos. Está claro que buscar un patrón en un millón de URL únicas no es un problema.

El siguiente sospechoso en la lista son varios JOIN. ¿Quizás su uso excesivo ha causado la desaceleración? Generalmente JOINSon los candidatos más obvios para problemas de rendimiento, pero no creía que nuestro caso fuera 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

Y este tampoco fue nuestro caso. JOINHa resultado ser bastante rápido.

Reducir el círculo de sospechosos

Estaba listo para comenzar a cambiar la consulta para lograr posibles mejoras de rendimiento. Mi equipo y yo desarrollamos 2 ideas principales:

Utilice EXISTS para la URL de subconsulta: Queríamos comprobar nuevamente si hubo algún problema con la subconsulta de las URL. Una forma de lograr esto es simplemente usar EXISTS. EXISTS lata mejora enormemente el rendimiento ya que finaliza inmediatamente tan pronto como encuentra la única cadena que coincide con la 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

Bueno, sí. Subconsulta cuando está envuelta en EXISTS, hace que todo sea súper rápido. La siguiente pregunta lógica es por qué la solicitud con JOIN-ami y la subconsulta en sí son rápidas individualmente, pero ¿son terriblemente lentas juntas?

Mover la subconsulta al CTE : Si la consulta es rápida por sí sola, simplemente podemos calcular el resultado rápido primero y luego proporcionarlo a la 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 todavía fue muy lento.

Encontrar al culpable

Durante todo este tiempo, una pequeña cosa pasó ante mis ojos, que constantemente dejaba de lado. Pero como ya no quedaba nada más, decidí mirarla también. estoy hablando de && operador. Adiós EXISTS rendimiento simplemente mejorado && fue el único factor común restante en todas las versiones de la consulta lenta.

Mirando documentación, vemos eso && Se utiliza cuando necesita encontrar elementos comunes entre dos matrices.

En la solicitud original esto es:

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

Lo que significa que hacemos una búsqueda de patrones en nuestras URL y luego encontramos la intersección con todas las URL con publicaciones comunes. Esto es un poco confuso porque aquí "URL" no se refiere a la tabla que contiene todas las URL, sino a la columna "URL" de la tabla. recording_data.

Con crecientes sospechas respecto &&, intenté encontrar confirmación para ellos en el plan de consulta generado EXPLAIN ANALYZE (Ya tenía un plan guardado, pero normalmente me siento más cómodo experimentando en SQL que tratando de comprender la opacidad de los 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 líneas de filtros solo de &&. Lo que significaba que esta operación no sólo era costosa, sino que además se realizaba varias veces.

Probé esto aislando la 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 fue lenta. Porque el JOIN-s son rápidos y las subconsultas son rápidas, lo único que quedaba era && operador.

Esta es sólo una operación clave. Siempre necesitamos buscar en toda la tabla subyacente de URL para buscar un patrón, y siempre necesitamos encontrar intersecciones. No podemos buscar por registros URL directamente, porque estos son solo ID que hacen referencia a urls.

En camino a una solución

&& lento porque ambos conjuntos son enormes. La operación será relativamente rápida si reemplazo urls en { "http://google.com/", "http://wingify.com/" }.

Comencé a buscar una manera de establecer una intersección en Postgres sin usar &&, pero sin mucho éxito.

Al final, decidimos resolver el problema de forma aislada: dame todo. urls líneas para las cuales la URL coincide con el patrón. Sin condiciones adicionales 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%'

En lugar de JOIN sintaxis Acabo de usar una subconsulta y la expandí recording_data.urls matriz para que pueda aplicar directamente la condición en WHERE.

Lo más importante aquí es que && se utiliza para comprobar si una entrada determinada contiene una URL coincidente. Si entrecierras un poco los ojos, podrás ver que esta operación se mueve a través de los elementos de una matriz (o filas de una tabla) y se detiene cuando se cumple una condición (coincidencia). ¿No te recuerda a nada? Sí, EXISTS.

Desde entonces recording_data.urls Se puede hacer referencia desde fuera del contexto de la subconsulta. Cuando esto sucede, podemos recurrir a nuestro viejo amigo. EXISTS y envuelve la subconsulta con él.

Juntando todo, obtenemos la 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%'
    );

Y el plazo de entrega final Time: 1898.717 ms ¿¡¿Tiempo para celebrar?!?

¡No tan rapido! Primero necesitas verificar la corrección. Tenía muchas sospechas sobre EXISTS optimización ya que cambia la lógica para completarse antes. Necesitamos asegurarnos de no haber agregado un error no obvio a la solicitud.

Una prueba simple fue ejecutar count(*) en consultas tanto lentas como rápidas para una gran cantidad de conjuntos de datos diferentes. Luego, para un pequeño subconjunto de datos, verifiqué manualmente que todos los resultados fueran correctos.

Todas las pruebas dieron resultados consistentemente positivos. ¡Arreglamos todo!

Lecciones aprendidas

Hay muchas lecciones que aprender de esta historia:

Los planes de consulta no cuentan toda la historia, pero pueden proporcionar pistas
Los principales sospechosos no siempre son los verdaderos culpables
Las consultas lentas se pueden dividir para aislar cuellos de botella
No todas las optimizaciones son de naturaleza reductiva
el uso de EXIST, cuando sea posible, puede conducir a aumentos dramáticos en la productividad.

conclusión

Pasamos de un tiempo de consulta de ~24 minutos a 2 segundos: ¡un aumento de rendimiento bastante significativo! Aunque este artículo tuvo un gran éxito, todos los experimentos que hicimos se realizaron en un día y se estimó que tardaron entre 1,5 y 2 horas en optimizarse y probarse.

SQL es un lenguaje maravilloso si no le tienes miedo, pero intenta aprenderlo y usarlo. Si comprende bien cómo se ejecutan las consultas SQL, cómo la base de datos genera planes de consulta, cómo funcionan los índices y simplemente el tamaño de los datos con los que está tratando, puede tener mucho éxito en la optimización de las consultas. Sin embargo, es igualmente importante seguir probando diferentes enfoques y resolver lentamente el problema, encontrando los obstáculos.

La mejor parte de lograr resultados como estos es la mejora notable y visible de la velocidad, donde un informe que antes ni siquiera se cargaba ahora se carga casi instantáneamente.

Agradecimiento especial mis camaradas al mando de Aditya Mishra, Aditya Gauru и Varun Malhotra para una lluvia de ideas y Dinkar Pandir ¡Por encontrar un error importante en nuestra solicitud final antes de que finalmente le dijéramos adiós!

Fuente: habr.com

La historia de una investigación SQL