{"id":35259,"date":"2019-10-31T22:03:17","date_gmt":"2019-10-31T19:03:17","guid":{"rendered":"https:\/\/prohoster.info\/blog\/istoriya-odnogo-sql-rassledovaniya\/"},"modified":"2019-10-31T22:03:17","modified_gmt":"2019-10-31T19:03:17","slug":"istoriya-odnogo-sql-rassledovaniya","status":"publish","type":"post","link":"https:\/\/prohoster.info\/es\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"La historia de una investigaci\u00f3n SQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

En diciembre del a\u00f1o pasado, recib\u00ed un informe interesante acerca de un error del equipo de soporte de VWO. El tiempo de carga de uno de los informes anal\u00edticos para un importante cliente corporativo parec\u00eda excesivamente largo. Y como esto es parte de mis responsabilidades, me centr\u00e9 de inmediato en resolver el problema.<\/p>\n

<\/p>\n

Antecedentes<\/h2>\n

<\/p>\n

Para que est\u00e9 claro de qu\u00e9 se trata, contar\u00e9 un poco sobre VWO. Es una plataforma que permite lanzar diversas campa\u00f1as segmentadas en sus sitios web: realizar experimentos A\/B, rastrear visitantes y conversiones, hacer an\u00e1lisis de embudo de ventas, mostrar mapas de calor y reproducir grabaciones de visitas.<\/p>\n

<\/p>\n

Pero lo m\u00e1s importante de la plataforma es la elaboraci\u00f3n de informes. Todas las funciones mencionadas est\u00e1n interrelacionadas. Para los clientes corporativos, un gran volumen de informaci\u00f3n ser\u00eda simplemente in\u00fatil sin una poderosa plataforma que la presente de manera anal\u00edtica.<\/p>\n

<\/p>\n

Usando la plataforma, se puede realizar una consulta arbitraria sobre un gran conjunto de datos. Aqu\u00ed hay un ejemplo simple:<\/p>\n

<\/p>\n

Mostrar todos los clics en la p\u00e1gina \"abc.com\"\nDE <fecha d1> HASTA <fecha d2>\npara personas que\nusaron Chrome O\n(se encontraban en Europa Y usaron iPhone)<\/pre>\n
<\/p>\n
Presta atenci\u00f3n a los operadores booleanos. Est\u00e1n disponibles para los clientes en la interfaz de consultas para hacer consultas tan complejas como deseen para obtener datos espec\u00edficos.<\/p>\n
<\/p>\n
Consulta lenta<\/h2>\n
<\/p>\n
El cliente del que hablamos intentaba hacer algo que intuitivamente deber\u00eda funcionar r\u00e1pido:<\/p>\n
<\/p>\n
Muestre todos los registros de sesiones\npara usuarios que visitaron cualquier p\u00e1gina\ncon una URL que contenga \"\\\/jobs\"<\/pre>\n
<\/p>\n
Este sitio ten\u00eda un gran volumen de tr\u00e1fico, y almacen\u00e1bamos m\u00e1s de un mill\u00f3n de URL \u00fanicas solo para \u00e9l. Y quer\u00edan encontrar un patr\u00f3n de URL bastante simple relacionado con su modelo de negocio.<\/p>\n
\n<\/a><\/noindex><\/p>\n
Investigaci\u00f3n preliminar<\/h2>\n
<\/p>\n
Veamos qu\u00e9 est\u00e1 sucediendo en la base de datos. Aqu\u00ed est\u00e1 la consulta SQL lenta original:<\/p>\n
<\/p>\n
SELECT \n    count(*) \nFROM \n    acc_{account_id}.urls as recordings_urls, \n    acc_{account_id}.recording_data as recording_data, \n    acc_{account_id}.sessions as sessions \nWHERE \n    recording_data.usp_id = sessions.usp_id \n    AND sessions.referrer_id = recordings_urls.id \n    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\\\/jobs%')::text[]   ) \n    AND r_time > to_timestamp(1542585600) \n    AND r_time < to_timestamp(1545177599) \n    AND recording_data.duration >= 5 \n    AND recording_data.num_of_pages > 0 ;<\/code><\/pre>\n
<\/p>\n
Y aqu\u00ed est\u00e1n los tiempos:<\/p>\n
<\/p>\n
Tiempo estimado: 1.480 ms\nTiempo de ejecuci\u00f3n: 1431924.650 ms<\/pre>\n
<\/p>\n
La consulta recorri\u00f3 150 mil filas. El planificador de consultas mostr\u00f3 un par de detalles interesantes, pero ninguna zona de estrangulamiento obvia.<\/p>\n
<\/p>\n
Vamos a investigar la consulta m\u00e1s a fondo. Como se puede ver, est\u00e1 haciendo JOIN<\/code> tres tablas:<\/p>\n
<\/p>\n
    \n
  1. sessions<\/strong>: para mostrar informaci\u00f3n de sesi\u00f3n: navegador, agente de usuario, pa\u00eds, y as\u00ed sucesivamente.<\/li>\n
  2. recording_data<\/strong>: URLs grabadas, p\u00e1ginas, duraci\u00f3n de visitas<\/li>\n
  3. urls<\/strong>: para evitar la duplicaci\u00f3n de URLs extremadamente grandes, las almacenamos en una tabla separada.<\/li>\n<\/ol>\n
    <\/p>\n
    Tambi\u00e9n observe que todas nuestras tablas ya est\u00e1n divididas por account_id<\/code>. De este modo, se evita la situaci\u00f3n en la que un cuenta especialmente grande cause problemas a las dem\u00e1s.<\/p>\n
    <\/p>\n
    En busca de pistas<\/h2>\n
    <\/p>\n
    Al examinar detenidamente, vemos que hay algo extra\u00f1o en esta consulta en particular. Vale la pena mirar esta l\u00ednea:<\/p>\n
    <\/p>\n
    urls && array(\n\tselect id from acc_{account_id}.urls \n\twhere url ILIKE '%enterprise_customer.com\/jobs%'\n)::text[]<\/code><\/pre>\n
    <\/p>\n
    El primer pensamiento fue que quiz\u00e1s, debido a ILIKE<\/code> en todas estas URLs largas (tenemos m\u00e1s de 1.4 millones de URLs \u00fanicas, recopiladas para esta cuenta) el rendimiento podr\u00eda degradarse.\u00a0<\/strong>Pero no, \u00a1no se trata de eso!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 filas)\n\nTiempo: 5231.765 ms<\/p>\n
    <\/p>\n
    La consulta de b\u00fasqueda por patr\u00f3n tarda solo 5 segundos. Buscar por patr\u00f3n en un mill\u00f3n de URLs \u00fanicas claramente no es un problema.<\/code><\/pre>\n
    <\/p>\n
    El siguiente sospechoso de la lista son varias<\/p>\n
    <\/p>\n
    . \u00bfQuiz\u00e1s su uso excesivo est\u00e1 causando la lentitud? Normalmente JOIN<\/code>\u2018s son los candidatos m\u00e1s obvios para problemas de rendimiento, pero no cre\u00eda que nuestro caso fuera t\u00edpico. JOIN<\/code>\u2018Los problemas de rendimiento son los m\u00e1s evidentes, pero no cre\u00eda que nuestro caso fuera t\u00edpico.<\/p>\n
    <\/p>\n
    Y este tampoco fue nuestro caso.<\/code><\/pre>\n
    <\/p>\n
    \u2018s resultaron ser bastante r\u00e1pidos. JOIN<\/code>\u2018Resultaron ser bastante r\u00e1pidos.<\/p>\n
    <\/p>\n
    Estaba listo para comenzar a modificar la consulta para lograr cualquier mejora de rendimiento posible. Con el equipo, desarrollamos 2 ideas principales:<\/h2>\n
    <\/p>\n
    Utilizar EXISTS para la subconsulta de URL<\/p>\n
    <\/p>\n