🥇La història d'una investigació SQL

El desembre passat vaig rebre un informe d'error interessant de l'equip de suport de VWO. El temps de càrrega d'un dels informes d'anàlisi d'un gran client corporatiu semblava prohibitiu. I com que aquesta és la meva àrea de responsabilitat, de seguida em vaig centrar a resoldre el problema.

prehistòria

Per deixar clar de què parlo, us parlaré una mica de VWO. Aquesta és una plataforma amb la qual podeu llançar diverses campanyes dirigides als vostres llocs web: realitzar experiments A/B, fer un seguiment de visitants i conversions, analitzar l'embut de vendes, mostrar mapes de calor i reproduir enregistraments de visites.

Però el més important de la plataforma és informar. Totes les funcions anteriors estan interconnectades. I per als clients corporatius, una gran quantitat d'informació seria simplement inútil sense una plataforma potent que la presenti en forma d'anàlisi.

Amb la plataforma, podeu fer una consulta aleatòria en un conjunt de dades gran. Aquí teniu un exemple senzill:

Mostra tots els clics a la pàgina "abc.com" DES de <data d1> A <data d2> per a les persones que han utilitzat Chrome O (situada a Europa I utilitzant un iPhone)

Presta atenció als operadors booleans. Estan disponibles per als clients a la interfície de consulta per fer consultes arbitràriament complexes per obtenir mostres.

Petició lenta

El client en qüestió estava intentant fer alguna cosa que intuïtivament hauria de funcionar ràpidament:

Mostra tots els registres de sessió dels usuaris que han visitat qualsevol pàgina amb un URL que conté "/jobs"

Aquest lloc tenia un munt de trànsit i estàvem emmagatzemant més d'un milió d'URL únics només per a això. I volien trobar una plantilla d'URL bastant senzilla que estigués relacionada amb el seu model de negoci.

Investigació preliminar

Fem una ullada al que passa a la base de dades. A continuació es mostra 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 ;

I aquí teniu els horaris:

Temps previst: 1.480 ms Temps d'execució: 1431924.650 ms

La consulta va rastrejar 150 mil files. El planificador de consultes va mostrar un parell de detalls interessants, però sense colls d'ampolla evidents.

Estudiem més la petició. Com podeu veure, ho fa JOIN tres taules:

sessions: per mostrar la informació de la sessió: navegador, agent d'usuari, país, etc.
dades_enregistrament: URL registrats, pàgines, durada de les visites
url: per evitar duplicar URL extremadament grans, els desem en una taula separada.

Tingueu en compte també que totes les nostres taules ja estan particionades per account_id. D'aquesta manera, s'exclou una situació en què un compte especialment gran causa problemes als altres.

Buscant pistes

Després d'una inspecció més detinguda, veiem que alguna cosa no funciona amb una sol·licitud concreta. Val la pena mirar més de prop aquesta línia:

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

El primer pensament va ser que potser perquè ILIKE en tots aquests URL llargs (tenim més d'1,4 milions únic URL recollits per a aquest compte) el rendiment pot patir.

Però no, aquest no és el punt!

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

Time: 5231.765 ms

La sol·licitud de cerca de plantilla només triga 5 segons. La cerca d'un patró en un milió d'URL únics és evident que no és cap problema.

El següent sospitós de la llista són diversos JOIN. Potser el seu ús excessiu ha provocat la desacceleració? Generalment JOINEls 's són els candidats més evidents per problemes de rendiment, però no creia que el nostre cas fos típic.

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

I aquest tampoc va ser el nostre cas. JOINha resultat ser bastant ràpid.

Reduint el cercle de sospitosos

Estava preparat per començar a canviar la consulta per aconseguir qualsevol millora de rendiment possible. El meu equip i jo vam desenvolupar 2 idees principals:

Utilitzeu EXISTS per a l'URL de la subconsulta: Volíem comprovar de nou si hi havia cap problema amb la subconsulta dels URL. Una manera d'aconseguir-ho és simplement utilitzar EXISTS. EXISTS llauna millora molt el rendiment, ja que finalitza immediatament tan bon punt troba l'única cadena que coincideix amb la condició.

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

Bé, sí. Subconsulta quan s'embolica EXISTS, fa que tot sigui molt ràpid. La següent pregunta lògica és per què la sol·licitud amb JOIN-ami i la subconsulta en si són ràpides individualment, però són terriblement lentes junts?

Moure la subconsulta al CTE : Si la consulta és ràpida per si sola, només podem calcular primer el resultat ràpid i després proporcionar-lo 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;

Però encara va ser molt lent.

Trobar el culpable

Durant tot aquest temps, una petita cosa va aparèixer davant els meus ulls, que constantment vaig apartar. Però com que no quedava res més, vaig decidir mirar-la també. Estic parlant de && operador. Adéu EXISTS només ha millorat el rendiment && va ser l'únic factor comú restant a totes les versions de la consulta lenta.

Mirant a documentació, ho veiem && s'utilitza quan necessiteu trobar elements comuns entre dues matrius.

A la sol·licitud original això és:

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

Això vol dir que fem una cerca de patrons als nostres URL i després trobem la intersecció amb tots els URL amb publicacions comunes. Això és una mica confús perquè "urls" aquí no es refereix a la taula que conté tots els URL, sinó a la columna "urls" de la taula recording_data.

Amb sospites creixents respecte &&, he intentat trobar-los una confirmació al pla de consultes generat EXPLAIN ANALYZE (Ja tenia un pla desat, però normalment em sento més còmode experimentant amb SQL que intentant entendre l'opacitat dels planificadors de consultes).

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

Hi havia diverses línies de filtres només de &&. El que significava que aquesta operació no només era cara, sinó que també es realitzava diverses vegades.

Ho vaig provar aïllant la condició

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[]

Aquesta consulta va ser lenta. Perquè el JOIN-s són ràpides i les subconsultes són ràpides, l'únic que quedava era && operador.

Aquesta és només una operació clau. Sempre hem de cercar a tota la taula subjacent d'URL per cercar un patró i sempre hem de trobar interseccions. No podem cercar directament per registres d'URL, perquè només són identificadors als quals es refereixen urls.

En el camí cap a una solució

&& lent perquè els dos conjunts són enormes. L'operació serà relativament ràpida si substitueixo urls en { "http://google.com/", "http://wingify.com/" }.

Vaig començar a buscar una manera de fer una intersecció establerta a Postgres sense utilitzar &&, però sense gaire èxit.

Al final, vam decidir resoldre el problema de manera aïllada: donar-me-ho tot urls línies per a les quals l'URL coincideix amb el patró. Sense condicions addicionals 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 comptes de JOIN sintaxi Acabo d'utilitzar una subconsulta i la vaig expandir recording_data.urls matriu perquè pugueu aplicar directament la condició a WHERE.

El més important aquí és això && s'utilitza per comprovar si una entrada determinada conté un URL coincident. Si mireu una mica els ulls, podreu veure que aquesta operació es mou pels elements d'una matriu (o files d'una taula) i s'atura quan es compleix una condició (concordança). No et recorda res? Sí, EXISTS.

Des d’aleshores recording_data.urls es pot fer referència des de fora del context de subconsulta, quan això succeeix podem recórrer al nostre vell amic EXISTS i emboliqui la subconsulta amb ella.

Ajuntant-ho tot, obtenim la consulta optimitzada 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%'
    );

I el termini final Time: 1898.717 ms Hora de celebrar?!?

No molt ràpid! Primer heu de comprovar la correcció. Vaig desconfiar moltíssim EXISTS optimització, ja que canvia la lògica per acabar abans. Hem d'assegurar-nos que no hem afegit cap error no evident a la sol·licitud.

S'havia de fer una prova senzilla count(*) tant en consultes lentes com ràpides per a un gran nombre de conjunts de dades diferents. Aleshores, per a un petit subconjunt de dades, vaig verificar manualment que tots els resultats fossin correctes.

Totes les proves van donar resultats positius constantment. Ho hem arreglat tot!

Lliçons apreses

Hi ha moltes lliçons per aprendre d'aquesta història:

Els plans de consulta no expliquen tota la història, però poden proporcionar pistes
Els principals sospitosos no sempre són els veritables culpables
Les consultes lentes es poden desglossar per aïllar els colls d'ampolla
No totes les optimitzacions són de naturalesa reductiva
Utilitzar EXIST, sempre que sigui possible, pot provocar augments espectaculars de la productivitat

Sortida

Vam passar d'un temps de consulta d'aproximadament 24 minuts a 2 segons, un augment de rendiment força important! Tot i que aquest article va sortir gran, tots els experiments que vam fer van passar en un dia, i es va estimar que van trigar entre 1,5 i 2 hores per a les optimitzacions i les proves.

SQL és un llenguatge meravellós si no li tens por, però intenta aprendre i utilitzar-lo. Tenint una bona comprensió de com s'executen les consultes SQL, com la base de dades genera plans de consultes, com funcionen els índexs i simplement la mida de les dades amb les quals esteu tractant, podeu optimitzar les consultes amb molt èxit. No obstant això, és igual d'important continuar provant diferents enfocaments i trencar lentament el problema, trobant els colls d'ampolla.

La millor part d'aconseguir resultats com aquests és la millora notable i visible de la velocitat, on un informe que abans ni tan sols es carregava ara es carrega gairebé a l'instant.

Agraïment especial a els meus companys a les ordres d'Aditya Mishra, Aditya Gauru и Varun Malhotra per a la pluja d'idees i Dinkar Pandir per trobar un error important a la nostra sol·licitud final abans que finalment ens acomiadéssim!

Font: www.habr.com

La història d'una investigació SQL