🥇 Historien om én SQL-undersøgelse

Sidste december modtog jeg en interessant fejlrapport fra VWO supportteamet. Indlæsningstiden for en af analyserapporterne for en stor virksomhedskunde virkede uoverkommelig. Og da dette er mit ansvarsområde, fokuserede jeg straks på at løse problemet.

forhistorie

For at gøre det klart, hvad jeg taler om, vil jeg fortælle dig lidt om VWO. Dette er en platform, hvormed du kan lancere forskellige målrettede kampagner på dine hjemmesider: gennemføre A/B-eksperimenter, spore besøgende og konverteringer, analysere salgstragten, vise varmekort og afspille besøgsoptagelser.

Men det vigtigste ved platformen er rapportering. Alle ovenstående funktioner er indbyrdes forbundne. Og for erhvervskunder ville en enorm mængde information simpelthen være ubrugelig uden en kraftfuld platform, der præsenterer dem i analytisk form.

Ved hjælp af platformen kan du lave en tilfældig forespørgsel på et stort datasæt. Her er et simpelt eksempel:

Vis alle klik på siden "abc.com" FRA <dato d1> TIL <dato d2> for personer, der brugte Chrome OR (beliggende i Europa OG brugte en iPhone)

Vær opmærksom på booleske operatorer. De er tilgængelige for klienter i forespørgselsgrænsefladen til at lave vilkårligt komplekse forespørgsler for at opnå prøver.

Langsom anmodning

Den pågældende klient forsøgte at gøre noget, der intuitivt skulle fungere hurtigt:

Vis alle sessionsposter for brugere, der har besøgt en side med en URL, der indeholder "/jobs"

Dette websted havde et væld af trafik, og vi gemte over en million unikke URL'er kun for det. Og de ønskede at finde en ret simpel URL-skabelon, der var relateret til deres forretningsmodel.

Forundersøgelse

Lad os tage et kig på, hvad der foregår i databasen. Nedenfor er den originale langsomme SQL-forespørgsel:

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 ;

Og her er tidspunkterne:

Planlagt tid: 1.480 ms Udførelsestid: 1431924.650 ms

Forespørgslen gennemgik 150 tusind rækker. Forespørgselsplanlæggeren viste et par interessante detaljer, men ingen åbenlyse flaskehalse.

Lad os studere anmodningen nærmere. Som du kan se, gør han det JOIN tre borde:

sessioner: for at vise sessionsoplysninger: browser, brugeragent, land og så videre.
recording_data: registrerede URL'er, sider, varighed af besøg
urls: For at undgå duplikering af ekstremt store URL'er gemmer vi dem i en separat tabel.

Bemærk også, at alle vores borde allerede er opdelt efter account_id. På denne måde udelukkes en situation, hvor en særlig stor konto forårsager problemer for andre.

Leder efter spor

Ved nærmere eftersyn ser vi, at der er noget galt med en bestemt anmodning. Det er værd at se nærmere på denne linje:

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

Den første tanke var, at måske pga ILIKE på alle disse lange URL'er (vi har over 1,4 mio unik URL'er indsamlet til denne konto) kan lide.

Men nej, det er ikke meningen!

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

Time: 5231.765 ms

Selve skabelonsøgningen tager kun 5 sekunder. At søge efter et mønster i en million unikke URL'er er tydeligvis ikke et problem.

Den næste mistænkte på listen er flere JOIN. Måske har deres overforbrug forårsaget afmatningen? Som regel JOIN's er de mest oplagte kandidater til præstationsproblemer, men jeg troede ikke på, at vores sag var typisk.

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

Og det var heller ikke vores tilfælde. JOINDet viste sig at være ret hurtigt.

Indsnævring af kredsen af mistænkte

Jeg var klar til at begynde at ændre forespørgslen for at opnå eventuelle forbedringer af ydeevnen. Mit team og jeg udviklede 2 hovedideer:

Brug EXISTS til underforespørgsels-URL: Vi ville tjekke igen, om der var problemer med underforespørgslen til URL'erne. En måde at opnå dette på er blot at bruge EXISTS. EXISTS kan forbedre ydeevnen markant, da den slutter med det samme, så snart den finder den eneste streng, der matcher betingelsen.

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

Altså ja. Underforespørgsel, når den er pakket ind EXISTS, gør alt super hurtigt. Det næste logiske spørgsmål er, hvorfor anmodningen med JOIN-ami og selve underforespørgslen er hurtige hver for sig, men er frygtelig langsomme sammen?

Flytter underforespørgslen til CTE : Hvis forespørgslen er hurtig alene, kan vi blot beregne det hurtige resultat først og derefter give det til hovedforespørgslen

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;

Men det gik stadig meget langsomt.

At finde den skyldige

Hele denne tid blinkede en lille ting for mine øjne, som jeg hele tiden børstede til side. Men da der ikke var andet tilbage, besluttede jeg også at se på hende. Jeg taler om && operatør. Farvel EXISTS blot forbedret ydeevne && var den eneste tilbageværende fælles faktor på tværs af alle versioner af den langsomme forespørgsel.

Ser på dokumentation, det ser vi && bruges, når du skal finde fælles elementer mellem to arrays.

I den oprindelige anmodning er dette:

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

Hvilket betyder, at vi laver en mønstersøgning på vores URL'er og derefter finder skæringspunktet med alle URL'erne med almindelige indlæg. Dette er lidt forvirrende, fordi "webadresser" her ikke refererer til tabellen, der indeholder alle webadresserne, men til kolonnen "webadresser" i tabellen recording_data.

Med voksende mistanke vedr &&, jeg forsøgte at finde bekræftelse for dem i den genererede forespørgselsplan EXPLAIN ANALYZE (Jeg havde allerede en plan gemt, men jeg er normalt mere komfortabel med at eksperimentere i SQL end at prøve at forstå uigennemsigtigheden af forespørgselsplanlæggere).

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

Der var flere linjer med filtre kun fra &&. Hvilket betød, at denne operation ikke kun var dyr, men også udført flere gange.

Jeg testede dette ved at isolere tilstanden

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

Denne forespørgsel var langsom. Fordi JOIN-s er hurtige og underforespørgsler er hurtige, det eneste tilbage var && operatør.

Dette er blot en nøgleoperation. Vi skal altid søge i hele den underliggende tabel med URL'er for at søge efter et mønster, og vi skal altid finde skæringspunkter. Vi kan ikke søge direkte på URL-poster, fordi disse kun er ID'er, der refererer til urls.

På vej mod en løsning

&& langsom, fordi begge sæt er enorme. Operationen vil være relativt hurtig, hvis jeg udskifter urls på { "http://google.com/", "http://wingify.com/" }.

Jeg begyndte at lede efter en måde at lave sæt kryds i Postgres uden at bruge &&, men uden den store succes.

Til sidst besluttede vi bare at løse problemet isoleret: giv mig alt urls linjer, for hvilke URL'en matcher mønsteret. Uden yderligere betingelser vil det være -

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%'

I stedet for JOIN syntaks Jeg har lige brugt en underforespørgsel og udvidet recording_data.urls array, så du direkte kan anvende betingelsen i WHERE.

Det vigtigste her er det && bruges til at kontrollere, om en given post indeholder en matchende URL. Hvis du skeler lidt, kan du se, at denne operation bevæger sig gennem elementerne i et array (eller rækker i en tabel) og stopper, når en betingelse (match) er opfyldt. Minder du dig ikke om noget? ja, EXISTS.

Siden den recording_data.urls kan refereres uden for underforespørgselskonteksten, når dette sker, kan vi falde tilbage på vores gamle ven EXISTS og omslut underforespørgslen med den.

Når vi sætter alt sammen, får vi den endelige optimerede forespørgsel:

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%'
    );

Og den endelige leveringstid Time: 1898.717 ms Tid til at fejre?!?

Ikke så hurtigt! Først skal du kontrollere rigtigheden. Jeg var ekstremt mistænksom mht EXISTS optimering, da det ændrer logikken til at fuldføre tidligere. Vi skal være sikre på, at vi ikke har tilføjet en ikke-indlysende fejl til anmodningen.

En simpel test var at køre count(*) på både langsomme og hurtige forespørgsler til en lang række forskellige datasæt. Derefter, for en lille delmængde af dataene, bekræftede jeg manuelt, at alle resultater var korrekte.

Alle test gav konsekvent positive resultater. Vi fiksede alt!

Erfaringer

Der er mange erfaringer at lære af denne historie:

Forespørgselsplaner fortæller ikke hele historien, men de kan give ledetråde
De hovedmistænkte er ikke altid de egentlige skyldige
Langsomme forespørgsler kan opdeles for at isolere flaskehalse
Ikke alle optimeringer er af reduktiv karakter
Brug EXIST, hvor det er muligt, kan føre til dramatiske stigninger i produktiviteten

Output

Vi gik fra en forespørgselstid på ~24 minutter til 2 sekunder - en ganske betydelig ydelsesforøgelse! Selvom denne artikel kom ud stort, skete alle de eksperimenter, vi lavede på én dag, og det blev anslået, at de tog mellem 1,5 og 2 timer for optimeringer og test.

SQL er et vidunderligt sprog, hvis du ikke er bange for det, men prøver at lære og bruge det. Ved at have en god forståelse af, hvordan SQL-forespørgsler udføres, hvordan databasen genererer forespørgselsplaner, hvordan indekser fungerer, og blot størrelsen af de data, du har med at gøre, kan du få stor succes med at optimere forespørgsler. Det er dog lige så vigtigt at fortsætte med at prøve forskellige tilgange og langsomt nedbryde problemet og finde flaskehalsene.

Det bedste ved at opnå resultater som disse er den mærkbare, synlige hastighedsforbedring - hvor en rapport, der tidligere ikke engang ville indlæses, nu indlæses næsten øjeblikkeligt.

Særlig tak til mine kammerater på kommando af Aditya Mishra, Aditya Gauru и Varun Malhotra til brainstorming og Dinkar Pandir for at finde en vigtig fejl i vores sidste anmodning, før vi endelig sagde farvel til den!

Kilde: www.habr.com

Historien om én SQL-undersøgelse