En diciembre del año pasado, recibí un informe interesante acerca de un error del equipo de soporte de VWO. El tiempo de carga de uno de los informes analíticos para un importante cliente corporativo parecía excesivamente largo. Y como esto es parte de mis responsabilidades, me centré de inmediato en resolver el problema.
Antecedentes
Para que esté claro de qué se trata, contaré un poco sobre VWO. Es una plataforma que permite lanzar diversas campañas segmentadas en sus sitios web: realizar experimentos A/B, rastrear visitantes y conversiones, hacer análisis de embudo de ventas, mostrar mapas de calor y reproducir grabaciones de visitas.
Pero lo más importante de la plataforma es la elaboración de informes. Todas las funciones mencionadas están interrelacionadas. Para los clientes corporativos, un gran volumen de información sería simplemente inútil sin una poderosa plataforma que la presente de manera analítica.
Usando la plataforma, se puede realizar una consulta arbitraria sobre un gran conjunto de datos. Aquí hay un ejemplo simple:
Mostrar todos los clics en la página "abc.com" DE <fecha d1> HASTA <fecha d2> para personas que usaron Chrome O (se encontraban en Europa Y usaron iPhone)
Presta atención a los operadores booleanos. Están disponibles para los clientes en la interfaz de consultas para hacer consultas tan complejas como deseen para obtener datos específicos.
Consulta lenta
El cliente del que hablamos intentaba hacer algo que intuitivamente debería funcionar rápido:
Muestre todos los registros de sesiones para usuarios que visitaron cualquier página con una URL que contenga "\/jobs"
Este sitio tenía un gran volumen de tráfico, y almacenábamos más de un millón de URL únicas solo para él. Y querían encontrar un patrón de URL bastante simple relacionado con su modelo de negocio.
Investigación preliminar
Veamos qué está sucediendo en la base de datos. Aquí está 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 tiempos:
Tiempo estimado: 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 ninguna zona de estrangulamiento obvia.
Vamos a investigar la consulta más a fondo. Como se puede ver, está haciendo JOIN tres tablas:
- sessions: para mostrar información de sesión: navegador, agente de usuario, país, y así sucesivamente.
- recording_data: URLs grabadas, páginas, duración de visitas
- urls: para evitar la duplicación de URLs extremadamente grandes, las almacenamos en una tabla separada.
También observe que todas nuestras tablas ya están divididas por account_id. De este modo, se evita la situación en la que un cuenta especialmente grande cause problemas a las demás.
En busca de pistas
Al examinar detenidamente, vemos que hay algo extraño en esta consulta en particular. Vale la pena mirar 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 quizás, debido a ILIKE en todas estas URLs largas (tenemos más de 1.4 millones de URLs únicas, recopiladas para esta cuenta) el rendimiento podría degradarse. Pero no, ¡no se trata de eso!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 filas)Tiempo: 5231.765 ms
La consulta de búsqueda por patrón tarda solo 5 segundos. Buscar por patrón en un millón de URLs únicas claramente no es un problema.El siguiente sospechoso de la lista son varias
. ¿Quizás su uso excesivo está causando la lentitud? Normalmente JOIN‘s son los candidatos más obvios para problemas de rendimiento, pero no creía que nuestro caso fuera 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 fila)Tiempo: 147.851 ms
Y este tampoco fue nuestro caso.‘s resultaron ser bastante rápidos. JOINReduciendo la lista de sospechosos
Estaba listo para comenzar a modificar la consulta para lograr cualquier mejora de rendimiento posible. Con el equipo, desarrollamos 2 ideas principales:
Utilizar EXISTS para la subconsulta de URL
- : Queríamos verificar nuevamente si había problemas en la subconsulta para las URLs. Una forma de hacerlo es simplemente usarEXISTS
EXISTS.EXISTSmejora significativamente el rendimiento ya que se detiene tan pronto como encuentra la única fila que cumple 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 =5
AND recording_data.num_of_pages > 0 ;
count
32519
(1 row)
Time: 1636.637 msBueno, sí. Una subconsulta, cuando está envuelta en EXISTS, lo hace todo súper rápido. La siguiente pregunta lógica es, ¿por qué la consulta con JOIN-es y la subconsulta en sí son rápidas por separado, pero se ralentizan horriblemente juntas?
- Movemos la subconsulta a CTE : si la consulta es rápida por sí sola, podemos simplemente 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 =5
AND recording_data.num_of_pages > 0;Pero aún así, esto seguía siendo muy lento.
Encontramos al culpable
Durante todo este tiempo, había una pequeña cosa delante de mis ojos de la que siempre me deshacía. Pero como ya no quedaba nada más, decidí echar un vistazo a ella. Me refiero a && el operador. Mientras que EXISTS simplemente mejoró el rendimiento, && era el único factor común restante en todas las versiones de la consulta lenta.
Mirando , vemos que && se utiliza cuando se deben encontrar elementos comunes entre dos matrices.
En la consulta 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 estamos buscando un patrón en nuestras URL, luego encontramos la intersección con todas las URL con registros comunes. Esto es un poco confuso, ya que "urls" aquí no se refiere a una tabla que contenga todas las URL, sino a la columna "urls" en la tabla recording_data.
Con el aumento de las sospechas hacia &&, traté de encontrarles confirmación en el plan de consulta generado EXPLAIN ANALYZE (ya tenía un plan guardado, pero generalmente me resulta más fácil experimentar en SQL que intentar comprender las opacidades de los planificadores de consultas).
Filtro: ((urls && ($0)::text[]) Y (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) Y (r_time = '5'::double precision) Y (num_of_pages > 0))
Filas eliminadas por el filtro: 52710Había varias líneas de filtros solo de &&. Lo que significaba que esta operación no solo era costosa, sino que se ejecutaba varias veces.
Lo verifiqué, 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 se ejecutaba lentamente. Dado que JOIN-s son rápidos y las subconsultas rápidas, quedaba solo && el operador.
Solo esta es la operación clave. Siempre necesitamos buscar en toda la tabla principal de URLs para buscar por patrón, y siempre necesitamos encontrar intersecciones. No podemos buscar directamente en los registros de URLs porque son solo identificadores que se refieren a urls.
En el camino hacia la solución
&& lenta, 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 forma de hacer en Postgres la intersección de conjuntos sin usar &&, pero sin mucho éxito.
Al final, decidimos simplemente resolver el problema de forma aislada: dame todas las urls filas para las cuales la URL coincide con el patrón. Sin condiciones adicionales, esto 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 Y
urls.url ILIKE '%jobs%'En lugar de JOIN la sintaxis, simplemente usé una subconsulta y descompuse recording_data.urls en un array, para poder aplicar la condición directamente en WHERE.
Lo más importante aquí es que && se utiliza para verificar si un registro dado contiene la URL correspondiente. Mirando de cerca, se puede ver en esta operación el movimiento a través de los elementos del array (o filas de la tabla) y la detención al cumplir la condición (coincidencia). ¿No recuerda algo? Ah, EXISTS.
Dado que en recording_data.urls se puede hacer referencia desde fuera del contexto de la subconsulta, cuando esto sucede, podemos volver a nuestro viejo amigo EXISTS y envolverlo en la subconsulta.
Uniendo todo, obtenemos la consulta final optimizada:
SELECCIONAR
count(*)
DE
acc_{account_id}.urls como recordings_urls,
acc_{account_id}.recording_data como recording_data,
acc_{account_id}.sessions como sessions
DONDE
recording_data.usp_id = sessions.usp_id
Y ( 1 = 1 )
Y sessions.referrer_id = recordings_urls.id
Y r_time > to_timestamp(1542585600)
Y r_time = 5
Y recording_data.num_of_pages > 0
Y EXISTE(
SELECCIONAR urls.url
DE
acc_{account_id}.urls como urls,
(SELECCIONAR unnest(urls) COMO rec_url_id DE acc_{account_id}.recording_data)
COMO unrolled_urls
DONDE
urls.id = unrolled_urls.rec_url_id Y
urls.url ILIKE '%enterprise_customer.com/j jobs%'
);
Y el tiempo final de ejecución Tiempo: 1898.717 ms ¿Es hora de celebrar?!?
¡No tan rápido! Primero hay que verificar la corrección. Fui extremadamente cauteloso con respecto a EXISTS la optimización, ya que cambia la lógica hacia un final más temprano. Debemos asegurarnos de que no hemos añadido un error no evidente en la consulta.
Una verificación simple consistió en ejecutar count(*) tanto en consultas lentas como rápidas para una gran variedad de conjuntos de datos. Luego, para un pequeño subconjunto de datos, verifiqué manualmente la precisión de todos los resultados.
Todas las verificaciones dieron resultados consistentemente positivos. ¡Lo hemos arreglado todo!
Lecciones Aprendidas
De esta historia se pueden extraer muchas lecciones:
- Los planes de consulta no cuentan toda la historia, pero pueden dar 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 inherentemente reductivas
- Uso
EXIST, donde sea posible, puede llevar a un aumento drástico en el rendimiento
Salida
Hemos pasado de un tiempo de consulta de ~24 minutos a 2 segundos — ¡un aumento de rendimiento bastante significativo! Aunque este artículo es extenso, todos los experimentos que realizamos ocurrieron en un solo día, y se estima que tomaron entre 1.5 y 2 horas para optimizaciones y pruebas.
SQL es un lenguaje maravilloso, si no se le teme, sino que se intenta comprender y utilizar. Teniendo una buena comprensión de 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 se trata, se puede tener mucho éxito en la optimización de consultas. Sin embargo, igualmente importante es seguir probando diferentes enfoques y dividir lentamente el problema, encontrando cuellos de botella.
La mejor parte de lograr tales resultados es la notable mejora visible en la velocidad de trabajo: un informe que antes ni siquiera se cargaba, ahora se carga casi instantáneamente.
Agradecimientos especiales a mis compañeros del equipo Aditya Mishra, Aditya Gaur y por el brainstorming y Dinkar Pandir por encontrar un error importante en nuestra solicitud final, ¡antes de que nos despidiéramos de ella!
Fuente: habr.com
