ProHoster > Log > administrasjon > Historien om én SQL-undersøkelse

Historien om én SQL-undersøkelse

I desember i fjor mottok jeg en interessant feilrapport fra VWO-støtteteamet. Lastetiden for en av analyserapportene for en stor bedriftskunde virket uoverkommelig. Og siden dette er mitt ansvarsområde, fokuserte jeg umiddelbart på å løse problemet.

forhistorie

For å gjøre det klart hva jeg snakker om, skal jeg fortelle litt om VWO. Dette er en plattform der du kan lansere ulike målrettede kampanjer på nettsidene dine: gjennomføre A/B-eksperimenter, spore besøkende og konverteringer, analysere salgstrakten, vise varmekart og spille av besøksopptak.

Men det viktigste med plattformen er rapportering. Alle de ovennevnte funksjonene er sammenkoblet. Og for bedriftskunder ville en enorm mengde informasjon rett og slett vært ubrukelig uten en kraftig plattform som presenterer den i analytisk form.

Ved å bruke plattformen kan du gjøre en tilfeldig spørring på et stort datasett. Her er et enkelt eksempel:

Vis alle klikk på siden "abc.com" FRA <dato d1> TIL <dato d2> for personer som brukte Chrome OR (lokalisert i Europa OG brukte en iPhone)

Vær oppmerksom på boolske operatorer. De er tilgjengelige for klienter i spørringsgrensesnittet for å lage vilkårlig komplekse spørringer for å få prøver.

Sakte forespørsel

Den aktuelle klienten prøvde å gjøre noe som intuitivt skulle fungere raskt:

Vis alle øktposter for brukere som besøkte en side med en URL som inneholder "/jobber"

Dette nettstedet hadde massevis av trafikk, og vi lagret over en million unike nettadresser bare for det. Og de ønsket å finne en ganske enkel URL-mal som var relatert til forretningsmodellen deres.

Foreløpig etterforskning

La oss ta en titt på hva som skjer i databasen. Nedenfor er den opprinnelige sakte SQL-spørringen:

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 tidspunktene:

Planlagt tid: 1.480 ms Gjennomføringstid: 1431924.650 ms

Søket gjennomsøkte 150 tusen rader. Spørringsplanleggeren viste et par interessante detaljer, men ingen åpenbare flaskehalser.

La oss studere forespørselen videre. Som du kan se, gjør han det JOIN tre bord:

  1. sesjoner: for å vise øktinformasjon: nettleser, brukeragent, land og så videre.
  2. recording_data: registrerte nettadresser, sider, varighet av besøk
  3. urls: For å unngå duplisering av ekstremt store nettadresser, lagrer vi dem i en egen tabell.

Vær også oppmerksom på at alle bordene våre allerede er partisjonert av account_id. På denne måten utelukkes en situasjon der én spesielt stor konto skaper problemer for andre.

Leter etter ledetråder

Ved nærmere ettersyn ser vi at noe er galt med en bestemt forespørsel. Det er verdt å se nærmere på denne linjen:

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

Den første tanken var at kanskje pga ILIKE på alle disse lange nettadressene (vi har over 1,4 millioner unikt Nettadresser som samles inn for denne kontoen) kan bli dårligere.

Men nei, det er ikke poenget!

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

Time: 5231.765 ms

Selve malsøkeforespørselen tar bare 5 sekunder. Å søke etter et mønster i en million unike nettadresser er tydeligvis ikke et problem.

Den neste mistenkte på listen er flere JOIN. Kanskje overforbruket deres har forårsaket nedgangen? Som oftest JOIN's er de mest åpenbare kandidatene for ytelsesproblemer, men jeg trodde ikke at saken vår 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 dette var heller ikke vårt tilfelle. JOINDet viste seg å være ganske raskt.

Innsnevring av kretsen av mistenkte

Jeg var klar til å begynne å endre spørringen for å oppnå eventuelle ytelsesforbedringer. Teamet mitt og jeg utviklet 2 hovedideer:

  • Bruk EXISTS for undersøkings-URL: Vi ønsket å sjekke på nytt om det var noen problemer med underspørringen for URL-ene. En måte å oppnå dette på er å ganske enkelt bruke EXISTS. EXISTS kan forbedre ytelsen betraktelig siden den avsluttes umiddelbart så snart den finner den eneste strengen som samsvarer med tilstanden.

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

Vel ja. Undersøk når den er pakket inn EXISTS, gjør alt superraskt. Det neste logiske spørsmålet er hvorfor forespørselen med JOIN-ami og selve underspørringen er raske hver for seg, men er fryktelig trege sammen?

  • Flytter underspørringen til CTE : Hvis spørringen er rask alene, kan vi ganske enkelt beregne det raske resultatet først og deretter gi det til hovedspørringen

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 gikk fortsatt veldig sakte.

Finner den skyldige

Hele denne tiden blinket en liten ting foran øynene mine, som jeg hele tiden børstet til side. Men siden det ikke var noe annet igjen, bestemte jeg meg for å se på henne også. jeg snakker om && operatør. Ha det EXISTS bare forbedret ytelse && var den eneste gjenværende felles faktoren på tvers av alle versjoner av den trege spørringen.

Ser på dokumentasjon, det ser vi && brukes når du trenger å finne felles elementer mellom to arrays.

I den opprinnelige forespørselen er dette:

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

Dette betyr at vi gjør et mønstersøk på nettadressene våre, og deretter finner vi skjæringspunktet med alle nettadressene med vanlige innlegg. Dette er litt forvirrende fordi "urls" her ikke refererer til tabellen som inneholder alle nettadressene, men til "urls"-kolonnen i tabellen recording_data.

Med økende mistanker vedr &&, prøvde jeg å finne bekreftelse for dem i søkeplanen som ble generert EXPLAIN ANALYZE (Jeg hadde allerede en plan lagret, men jeg er vanligvis mer komfortabel med å eksperimentere i SQL enn å prøve å forstå opasiteten til spørringsplanleggere).

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

Det var flere linjer med filtre bare fra &&. Noe som gjorde at denne operasjonen ikke bare ble dyr, men også utført flere ganger.

Jeg testet dette ved å 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 spørringen var treg. Fordi det JOIN-er er raske og underspørringer er raske, det eneste som var igjen var && operatør.

Dette er bare en nøkkeloperasjon. Vi må alltid søke i hele den underliggende tabellen med URL-er for å søke etter et mønster, og vi må alltid finne kryss. Vi kan ikke søke direkte etter URL-poster, fordi disse bare er ID-er som refererer til urls.

På vei mot en løsning

&& sakte fordi begge settene er enorme. Operasjonen vil gå relativt raskt hvis jeg bytter urls på { "http://google.com/", "http://wingify.com/" }.

Jeg begynte å lete etter en måte å gjøre sett kryss i Postgres uten å bruke &&, men uten særlig suksess.

Til slutt bestemte vi oss for å bare løse problemet isolert: gi meg alt urls linjer som URL-en samsvarer med mønsteret for. Uten ytterligere 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 brukte bare en underspørring og utvidet recording_data.urls array slik at du kan bruke betingelsen direkte i WHERE.

Det viktigste her er det && brukes til å sjekke om en gitt oppføring inneholder en matchende URL. Hvis du myser litt, kan du se at denne operasjonen beveger seg gjennom elementene i en matrise (eller rader i en tabell) og stopper når en betingelse (match) er oppfylt. Minner deg ikke om noe? ja, EXISTS.

Siden recording_data.urls kan refereres fra utenfor underspørringskonteksten, når dette skjer kan vi falle tilbake på vår gamle venn EXISTS og pakk underspørringen med den.

Når vi setter alt sammen, får vi den endelige optimaliserte spørringen:

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 ledetiden Time: 1898.717 ms På tide å feire?!?

Ikke så fort! Først må du sjekke riktigheten. Jeg var ekstremt mistenksom EXISTS optimalisering ettersom den endrer logikken for å avslutte tidligere. Vi må være sikre på at vi ikke har lagt til en ikke-åpenbar feil i forespørselen.

En enkel test var å kjøre count(*) på både langsomme og raske spørringer for et stort antall forskjellige datasett. Deretter, for en liten delmengde av dataene, bekreftet jeg manuelt at alle resultater var korrekte.

Alle tester ga gjennomgående positive resultater. Vi fikset alt!

Lærdom

Det er mye å lære av denne historien:

  1. Søkeplaner forteller ikke hele historien, men de kan gi ledetråder
  2. De hovedmistenkte er ikke alltid de virkelige skyldige
  3. Langsomme søk kan brytes ned for å isolere flaskehalser
  4. Ikke alle optimaliseringer er reduktive
  5. Bruk EXIST, der det er mulig, kan føre til dramatiske økninger i produktiviteten

Utgang

Vi gikk fra en spørretid på ~24 minutter til 2 sekunder – en ganske betydelig ytelsesøkning! Selv om denne artikkelen ble stor, skjedde alle eksperimentene vi gjorde på en dag, og det ble anslått at de tok mellom 1,5 og 2 timer for optimaliseringer og testing.

SQL er et fantastisk språk hvis du ikke er redd for det, men prøver å lære og bruke det. Ved å ha en god forståelse av hvordan SQL-spørringer utføres, hvordan databasen genererer spørringsplaner, hvordan indekser fungerer, og ganske enkelt størrelsen på dataene du har å gjøre med, kan du være svært vellykket med å optimalisere spørringer. Like viktig er det imidlertid å fortsette å prøve ulike tilnærminger og sakte bryte ned problemet, og finne flaskehalsene.

Det beste med å oppnå disse resultatene er den merkbare, synlige hastighetsforbedringen - der en rapport som tidligere ikke engang ville lastes nå, lastes nesten umiddelbart.

En spesiell takk til mine kamerater på kommando av Aditya Mishra, Aditya Gauru и Varun Malhotra for idédugnad og Dinkar Pandir for å ha funnet en viktig feil i vår siste forespørsel før vi endelig sa farvel til den!

Kilde: www.habr.com

Legg til en kommentar