Historien om en SQL-etterforskning

I fjor i desember mottok jeg en interessant feilrapport fra VWO-støtteteamet. Lastetiden for en av analyserapportene for en stor bedriftskunde virket uforholdsmessig lang. Siden dette er mitt ansvar, begynte jeg straks å fokusere på å løse problemet.

Bakgrunn

For å gi litt kontekst, vil jeg forklare litt om VWO. Dette er en plattform som lar deg kjøre ulike målrettede kampanjer på nettsidene dine: gjennomføre A/B-tester, spore besøkende og konverteringer, analysere salgstrakter, vise varmekart og spille av besøkopptak.

Men det viktigste med plattformen er rapporteringen. Alle funksjonene nevnt ovenfor er sammenkoblet. For bedriftskunder ville en enorm mengde informasjon vært helt ubrukelig uten en kraftig plattform som presenterer den for analyse.

Ved å bruke plattformen kan du gjøre forespørsel 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 ELLER
(var i Europa OG brukte iPhone)

Legg merke til de boolske operatorene. De er tilgjengelige for kunder i forespørselgrensesnittet, slik at du kan lage så kompliserte forespørsel som nødvendig for å få utvalg.

Langsom forespørsel

Kunden det er snakk om, prøvde å gjøre noe som intuitivt burde fungere raskt:

Vis alle sesjonsopptak
for brukere som besøkte hvilken som helst side
med en URL som inneholder "/jobs"

Denne nettsiden hadde enorm mengde trafikk, og vi lagret over en million unike URL-adresser kun for den. Og de ønsket å finne et ganske enkelt URL-mønster relatert til forretningsmodellen deres.

Forespørsel om etterforskning

La oss se hva som skjer i databasen. Nedenfor er den originale langsomme SQL-forespørselen:

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

Forventet tid: 1.480 ms
Kjøringstid: 1431924.650 ms

Forespørselen krysset 150 000 rader. Spørringsplanleggeren viste noen interessante detaljer, men ingen åpenbare flaskehalser.

La oss gå videre med forespørselen. Som vi ser, gjør den JOIN tre tabeller:

1. økter: for å vise sesjonsinformasjon: nettleser, brukeragent, land og så videre.
2. recording_data: registrerte URL-er, sider, varighet av besøkene
3. urls: for å unngå duplisering av ekstremt store URL-er, lagrer vi dem i en egen tabell.

Vær også oppmerksom på at alle tabellene våre allerede er delt opp etter account_id. Dermed er situasjonen utelukket hvor problemer med andre oppstår på grunn av én spesielt stor konto.

I søken etter spor

Ved nærmere ettersyn ser vi at noe er galt med den spesifikke forespørselen. Det er verdt å se nærmere på denne raden:

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

Den første tanken var at på grunn av ILIKE på alle disse lange URL-ene (vi har mer enn 1,4 millioner unike URL-er samlet for denne kontoen) kan ytelsen være dårlig.

Men nei — det er ikke saken!

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

Tid: 5231.765 ms

Selve søkeforespørselen etter mønstre tar bare 5 sekunder. Søking etter mønstre på en million unike URL-er er tydeligvis ikke et problem.

Den neste mistenkte på listen — flere JOIN. Kan det ha vært deres overdrevne bruk som førte til forsinkelse? Vanligvis JOIN‘er de de mest åpenbare kandidatene for ytelsesproblemer, men jeg trodde ikke at vårt tilfelle 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 =5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 rad)

Tid: 147.851 ms

Og det var heller ikke vårt tilfelle. JOIN‘ene viste seg å være ganske raske.

Vi snevrer inn listen over mistenkte

Jeg var klar til å begynne å endre forespørselen for å oppnå enhver mulig forbedring i ytelse. Vi i teamet utviklet to hovedideer:

• Bruke EXISTS for URL-undersøkelsen: Vi ønsket å sjekke igjen om det var problemer med underforespørselen for URL-ene. En måte å oppnå dette på er ganske enkelt å bruke EXISTS. EXISTS finne ut at brukeren har deaktivert animasjons-effektene og dermed også deaktivere ulike animasjonsmuligheter på nettsiden, for eksempel fjerne skjelvingseffekten på knapper som brukes for å tiltrekke seg oppmerksomhet; betyr mykje for prestasjonen, ettersom det stopper med en gang det finner den eine raden som møter 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 =5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 rad)
Tid: 1636.637 ms

Ja. Ein underforespørsel, når den er pakket inn i EXISTS, gjør alt super raskt. Det neste logiske spørsmålet er, hvorfor er forespørselen med JOIN-ene og den underforespørselen raske hver for seg, men fryktelig trege sammen?

• Vi flytter underforespørselen til CTE : hvis forespørselen er rask i seg selv, kan vi bare beregne det raske resultatet først, og deretter gi det til hovedforespørselen.

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 =5 
    AND recording_data.num_of_pages > 0;

Men dette var fortsatt veldig sakte.

Vi finner synderen

Hele tiden hadde en liten ting flommet forbi øynene mine, som jeg stadig avviste. Men siden det ikke var noe annet å se på, bestemte jeg meg for å ta en titt på det også. Jeg snakker om && operatøren. Mens EXISTS bare forbedret ytelsen, && var den eneste gjenværende felles faktoren i alle versjoner av den langsomme forespørselen.

Ser på dokumentasjonen, ser vi at && brukes når du trenger å finne felles elementer mellom to arrayer.

I den originale forespørselen er dette:

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

Det betyr at vi gjør et mønstergitt søk på URL-ene våre, og deretter finner kryssingen med alle URL-ene som har felles poster. Dette er litt forvirrende, siden «urls» her ikke refererer til tabellen som inneholder alle URL-adressene, men til kolonnen «urls» i tabellen recording_data.

Med økende mistanker om &&, prøvde jeg å bekrefte dem i forespørselen som ble generert av EXPLAIN ANALYZE (jeg hadde allerede en lagret plan, men jeg pleier vanligvis å eksperimentere i SQL heller enn å prøve å forstå den uklare planleggingen av spørringer).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time = '5'::double precision) AND (num_of_pages > 0))
                           Rader fjernet av filteret: 52710

Det var flere filterlinjer kun fra &&. Dette betydde at denne operasjonen ikke bare var kostbar, men også ble utført flere ganger.

Jeg sjekket dette ved å isolere betingelsen

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ørselen ble utført sakte. Siden JOIN-ene er raske og underforespørslene er raske, var det bare igjen && operatoren.

Bare dette er den nøkkeloperasjonen. Vi må alltid søke gjennom hovedtabellen URL-adresser for å lete etter mønstre, og vi må alltid finne overlappinger. Vi kan ikke søke direkte på URL-oppføringene fordi de bare er ID-er som refererer til urls.

Veien til løsningen

&& er sakte, fordi begge settene er enorme. Operasjonen vil være relativt rask hvis jeg erstatter urls En funksjon har blitt { "http://google.com/", "http://wingify.com/" }.

Jeg begynte å se etter en måte å gjøre i Postgres mengdeinterseksjoner uten å bruke &&, men uten særlig hell.

Til slutt bestemte vi oss for å bare løse problemet isolert: gi meg alle urls linjer hvor URL-en matcher mønsteret. Uten ytterligere betingelser vil dette 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 JOIN Syntaksen, jeg brukte bare en underforespørsel og rullet ut recording_data.urls array slik at betingelsen kunne anvendes direkte i Den maksimale/minimale hurtig oppfriskningen etter sletting eller blandede DML har ikke samme oppførsel som innsetting-tilfelle. Den sletter og beregner på nytt maks/min verdiene for de berørte gruppene. Du må være oppmerksom på dens ytelseseffekt..

Det viktigste her er at && brukes til å sjekke om den aktuelle oppføringen inneholder den tilsvarende URL-en. Litt med øynene lukket kan man se i denne operasjonen at den beveger seg gjennom elementene i arrayet (eller radene i tabellen) og stopper når betingelsen (overensstemmelsen) er oppfylt. Ligner det på noe? Ja, EXISTS.

Siden recording_data.urls kan refereres utenfor konteksten til underforespørselen, når dette skjer, kan vi gå tilbake til vår gamle venn EXISTS og innkapsle dem med underforespørselen.

Når vi setter alt sammen, får vi den endelige optimaliserte forespørselen:

VELG 
    telj (*) 
FROM 
    acc_{account_id}.urls som opptak_urls, 
    acc_{account_id}.recording_data som opptaksdata, 
    acc_{account_id}.sessions som sesjoner 
HVOR 
    opptaksdata.usp_id = sesjoner.usp_id 
    OG  (  1 = 1  )  
    OG sesjoner.referrer_id = opptak_urls.id 
    OG r_time > to_timestamp(1542585600) 
    OG r_time = 5 
    OG opptaksdata.antall_sider > 0
    OG EXIST(
        VELG urls.url
        FRA 
            acc_{account_id}.urls som urls,
            (VELG unnest(urls) SOM rec_url_id FRA acc_{account_id}.recording_data) 
            SOM rullet_ut_urls
        HVOR
            urls.id = rullet_ut_urls.rec_url_id OG
            urls.url ILIKE '%enterprise_customer.com/jobs%'
    );

Og den endelige kjøretiden Tid: 1898.717 ms Er det på tide å feire?!?

Ikke så raskt! Først må vi sjekke korrektheten. Jeg var veldig skeptisk til EXISTS optimaliseringen, fordi den endrer logikken til en tidligere avslutning. Vi må være sikre på at vi ikke har lagt til en skjult feil i forespørselen.

En enkel sjekk var å utføre telj(*) og på både sakte og raske forespørsel for mange forskjellige datasett. Deretter, for et lite undersett av data, sjekket jeg riktigheten av alle resultater manuelt.

Alle sjekker ga konsekvent positive resultater. Vi har fikset alt!

Lærte Leksjoner

Fra denne historien kan det trekkes mange leksjoner:

1. Spørsmålsskjemaer forteller ikke hele historien, men kan gi ledetråder
2. Hovedmistenkte er ikke alltid de virkelige gjerningspersonene
3. Sakte forespørsel kan brytes ned for å isolere flaskehalser
4. Ikke alle optimaliseringer er der for å redusere
5. Bruken EXIST, når det er mulig, kan føre til dramatisk ytelsesvekst

Utdata

Vi gikk fra en forespørselstid på ~24 minutter til 2 sekunder — en betydelig ytelsesøkning! Selv om denne artikkelen ble lang, skjedde alle eksperimentene vi utførte på en dag, og etter estimater, tok de omtrent 1,5 til 2 timer å optimalisere og teste.

SQL er et fantastisk språk, så lenge man ikke er redd for det, men prøver å forstå og bruke det. Med en god forståelse av hvordan SQL-forespørsler kjøres, hvordan databasen genererer spørsmålsskjemaer, hvordan indekser fungerer og bare størrelsen på dataene du har med å gjøre, kan du virkelig utmerke deg i forespørseloptimering. Det er like viktig, imidlertid, å fortsatt prøve forskjellige tilnærminger og sakte bryte ned problemet for å finne flaskehalser.

Den beste delen med å oppnå slike resultater er det merkbare forbedringen i hastigheten – når en rapport som tidligere ikke engang lastet inn, nå lastes inn nesten umiddelbart.

Spesiell takk til mine kolleger i teamet Aditya Mishra, Aditya Gaur og Varun Malhotra for brainstorming og Dinkar Pandir for å ha oppdaget en viktig feil i vår endelige forespørsel før vi forlot den helt!

Kilde: habr.com