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 para un gran cliente corporativo parecía prohibitivo. Y como esta es mi área de responsabilidad, inmediatamente me concentré en resolver el problema.
Antecedentes
Para aclarar de qué estoy hablando, te contaré un poco sobre VWO. Esta es una plataforma con la que puede ejecutar varias campañas específicas en sus sitios: realizar experimentos A / B, rastrear visitantes y conversiones, analizar embudos de ventas, mostrar mapas de calor y reproducir grabaciones de visitas.
Pero lo más importante en la plataforma son los informes. Todas las funciones anteriores están interconectadas. Y para los clientes corporativos, una gran variedad de información sería simplemente inútil sin una plataforma poderosa que los presentara en forma de análisis.
Con la plataforma, puede realizar una solicitud arbitraria en un conjunto de datos grande. Aquí hay un ejemplo simple:
Mostrar todos los clics en abc.com
DE <fecha d1> A <fecha d2>
para personas que
cromo usado O
(estaban en Europa y usaban el iPhone)
Presta atención a los operadores booleanos. Están disponibles para los clientes en la interfaz de consulta para realizar consultas arbitrariamente complejas para recuperar muestras.
Solicitud lenta
El cliente en cuestión estaba tratando de hacer algo que intuitivamente debería funcionar rápidamente:
Mostrar todas las notas de la sesión
para usuarios que visitan cualquier página
con url donde hay "/ jobs"
Había mucho tráfico en este sitio, y almacenamos más de un millón de URL únicas solo para ello. Y querían encontrar una plantilla de URL bastante simple relacionada con su modelo de negocio.
Investigación preliminar
Veamos qué pasa en la base de datos. La siguiente es 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
Plazo de ejecución: 1431924.650 ms
La solicitud omitió 150 mil líneas. El planificador de consultas mostró un par de detalles interesantes, pero sin cuellos de botella obvios.
Estudiemos más la consulta. Como puede ver, hace que tres tablas JOIN :
- sesiones : para mostrar información de la sesión: navegador, agente de usuario, país, etc.
- grabación_datos : URL grabadas, páginas, duración de las visitas
- URL : para evitar la duplicación de URL extremadamente grandes, las almacenamos en una tabla separada.
También tenga en cuenta que todas nuestras tablas ya están divididas por account_id . Por lo tanto, se excluye una situación cuando, debido a una cuenta especialmente grande, las otras tienen problemas.
Buscando evidencia
En una inspección más cercana, vemos que algo en una solicitud particular no está bien. Vale la pena ver 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 URL largas (tenemos más de 1.4 millones de URL únicas recopiladas para esta cuenta), el rendimiento podría disminuir.
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í misma toma solo 5 segundos. La búsqueda de un patrón en un millón de URL únicas claramente no es un problema.
El próximo sospechoso en la lista son algunos JOIN . ¿Quizás su uso excesivo condujo a una desaceleración? JOIN ser los candidatos más obvios para los 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. JOIN resultó ser bastante rápido.
Estrechamos 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:
- Use EXISTS para la URL de la subconsulta : Queríamos verificar nuevamente si hubo algún problema con la subconsulta para las URL. Una forma de lograr esto es simplemente usar
EXISTS . EXISTS puede mejorar en gran medida el rendimiento, ya que termina inmediatamente tan pronto como encuentra una sola línea por 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
Pues si. La subconsulta, cuando está envuelta en EXISTS , hace que todo sea súper rápido. La siguiente pregunta lógica es ¿por qué la consulta con JOIN y la subconsulta en sí son rápidas individualmente, pero terriblemente lentas juntas?
- Movemos la subconsulta al CTE : si la solicitud es rápida por sí sola, simplemente podemos calcular el resultado rápido primero y luego proporcionarlo a la solicitud 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ún era muy lento.
Encuentra al culpable
Todo este tiempo, una pequeña cosa apareció ante mis ojos, de la cual constantemente me aparté. Pero como no quedaba nada, decidí mirarla. Estoy hablando del operador && . Si bien EXISTS simplemente mejoró el rendimiento, && fue el único factor común restante en todas las versiones de la consulta lenta.
Mirando la documentación , vemos que && usa 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 plantilla para nuestras URL, luego encontramos la intersección con todas las URL con registros compartidos. Esto es un poco confuso, ya que "urls" aquí no se refiere a una tabla que contiene todas las URL, sino a una columna "urls" en la tabla recording_data .
A medida que && sospechas de && , traté de encontrar confirmación en el plan de consulta generado por EXPLAIN ANALYZE (ya tenía un plan guardado, pero generalmente es más conveniente experimentar con SQL que tratar 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 unas pocas líneas de filtros de && solamente. Lo que significaba que esta operación no solo era costosa, sino que también se realizaba varias veces.
Lo comprobé 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 solicitud fue lenta. Como JOIN rápido y las subconsultas son rápidas, solo queda el operador && .
Esta es solo una operación clave. Siempre necesitamos buscar en toda la tabla principal de URL para buscar por patrón, y siempre necesitamos encontrar intersecciones. No podemos buscar entradas de URL directamente, porque estos son solo identificadores que enlazan con las urls .
Hacia una solución
&& lento porque ambos conjuntos son enormes. La operación será relativamente rápida si reemplazo las urls con { "http://google.com/", "http://wingify.com/" } .
Comencé a buscar una manera de hacer una intersección de conjuntos en Postgres sin usar && , pero sin mucho éxito.
Al final, decidimos simplemente resolver el problema de forma aislada: dame todas las urls cadena 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 la sintaxis JOIN , simplemente utilicé una subconsulta y expandí la matriz recording_data.urls para que la condición pudiera aplicarse directamente a WHERE .
Lo más importante aquí es que && usa para verificar si una entrada determinada contiene una URL apropiada. Al entrecerrar los ojos un poco, puede ver en esta operación moverse a través de los elementos de la matriz (o filas de la tabla) y detenerse cuando se cumple la condición (coincidencia). ¿Se parece a algo? Sí, EXISTS .
Ya que se puede hacer referencia a recording_data.urls desde fuera del contexto de la subconsulta cuando esto sucede, podemos regresar a nuestro viejo amigo EXISTS y envolverlos con una subconsulta.
Combinando todo junto, 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 tiempo de ejecución final Time: 1898.717 ms Es hora de celebrar?
¡No tan rápido! Primero debe verificar la corrección. EXISTS extremadamente de la optimización EXISTS , ya que cambia la lógica a un final anterior. Debemos estar seguros de que no hemos agregado un error no obvio a la solicitud.
Una simple verificación fue realizar el count(*) en consultas lentas y rápidas para una gran cantidad de conjuntos de datos diferentes. Luego, para un pequeño subconjunto de datos, verifiqué la corrección de todos los resultados manualmente.
Todos los controles dieron resultados consistentemente positivos. Lo arreglamos!
Lecciones aprendidas
Hay muchas lecciones que aprender de esta historia:
- 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 romper para aislar los cuellos de botella
- No todas las optimizaciones son de naturaleza reductiva.
- El uso de
EXIST , donde sea posible, puede conducir a un fuerte aumento de la productividad.
Conclusión
Pasamos de un tiempo de solicitud de ~ 24 minutos a 2 segundos, ¡un aumento de rendimiento muy serio! Aunque este artículo resultó ser extenso, todos los experimentos que hicimos ocurrieron el mismo día y, según las estimaciones, las optimizaciones y las pruebas demoraron entre 1,5 y 2 horas.
SQL es un lenguaje maravilloso, si no le tiene miedo, pero trate de aprender y usar. Tener 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 está tratando, puede tener mucho éxito en la optimización de consultas. Sin embargo, es igualmente importante continuar probando diferentes enfoques y disolver lentamente el problema, encontrando cuellos de botella.
La mejor parte para lograr tales resultados es una notable mejora visible en la velocidad, cuando un informe que ni siquiera se había descargado antes ahora se carga casi instantáneamente.
Un agradecimiento especial a mis compañeros de equipo Aditya Misra , Aditya Gauru y Varun Malhotra por su lluvia de ideas y Dinkar Pandir por encontrar un error importante en nuestra solicitud final antes de que finalmente nos despidiéramos de él.