ProHoster > Blog > Administration > Historien om en SQL-undersøgelse

Historien om en SQL-undersøgelse

I december sidste år modtog jeg en interessant fejlrapport fra VWO-supportteamet. Indlæsningstiden for en af de analytiske rapporter til en stor virksomhedskunde syntes urimeligt lang. Da dette er mit ansvarsområde, fokuserede jeg straks på at løse problemet.

Background

For at gøre det klart, hvad der menes, vil jeg fortælle lidt om VWO. Dette er en platform, der gør det muligt at køre forskellige målrettede kampagner på ens hjemmesider: udføre A/B tests, spore besøgende og konverteringer, lave salgstragtanalyser, vise varme kort og afspille besøgsoptagelser.

Men det vigtigste ved platformen er udarbejdelsen af rapporter. Alle de nævnte funktioner hænger sammen. For virksomhedskunder ville et massivt informationssæt være helt ubrugeligt uden en kraftfuld platform, der præsenterer dem til analyse.

Ved at bruge platformen kan man lave en vilkårlig 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 ELLER
(var i Europa OG brugte iPhone)

Bemærk de booleske operatorer. De er tilgængelige for kunderne i forespørgselsgrænsefladen, så man kan lave så komplekse forespørgsler, man ønsker, for at få udtræk.

Langsom forespørgsel

Kunden, som vi taler om, forsøgte at gøre noget, der intuitivt burde fungere hurtigt:

Vis alle sessionsoptagelser
for brugere der besøgte enhver side
med en URL, der indeholder "/jobs"

På denne hjemmeside var der enormt mange besøgende, og vi gemte over en million unikke URL-adresser kun for den. De ønskede at finde en ret simpel URL-skabelon, der var relateret til deres forretningsmodel.

Foreløbig undersøgelse

Lad os se på, hvad der sker i databasen. Her 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 tiderne:

Forventet tid: 1.480 ms
Udførelsestid: 1431924.650 ms

Forespørgslen omfattede 150.000 rækker. Spørgselsplanlæggeren viste et par interessante detaljer, men der var ingen åbenlyse flaskehalse.

Lad os undersøge forespørgslen nærmere. Som det fremgår, gør den JOIN tre tabeller:

  1. sessions: til at vise sessionsinformation: browser, brugeragent, land og så videre.
  2. recording_data: optagede URLs, sider, besøgsvarighed
  3. urls: for at undgå duplikering af ekstremt lange URLs, gemmer vi dem i en separat tabel.

Bemærk også, at alle vores tabeller allerede er opdelt efter account_id. På den måde undgås situationen, hvor problemer opstår for de andre på grund af en særlig stor konto.

I jagten på spor

Ved nærmere eftersyn ser vi, at der er noget galt med denne specifikke forespørgsel. 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 på grund af ILIKE på alle disse lange URLs (vi har over 1,4 millioner unikke URL-adresser indsamlet til denne konto) kunne ydeevnen være påvirket.

Men nej — det var ikke tilfældet!

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

Tid: 5231.765 ms

Selve forespørgslen til at søge efter mønsteret tager kun 5 sekunder. Søgning efter mønsteret i en million unikke URLs er klart ikke et problem.

Næste mistænkte på listen — flere JOIN. Måske deres overdrevne brug førte til nedsættelse? Normalt JOIN'e er de mest åbenlyse kandidater til ydeevneproblemer, men jeg troede ikke, at vores tilfælde 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 række)

Tid: 147.851 ms

Og det var heller ikke vores tilfælde. JOIN'e viste sig at være ganske hurtige.

Snævrer mistænkte ind

Jeg var klar til at begynde at ændre forespørgslen for at opnå forbedringer i ydeevnen. Vi i teamet udviklede 2 hovedideer:

  • Brug af EXISTS til URL-underforespørgsel: Vi ønskede at dobbelttjekke, om der var problemer med underforespørgslen for URLs. En måde at gøre dette på er simpelthen at bruge EXISTS. EXISTS kan kraftigt forbedre ydeevnen da det stopper med det samme, så snart det finder den eneste række i henhold til 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 row)
Time: 1636.637 ms

Ja, selve underforespørgslen, når den er indkapslet i EXISTS, gør alt super hurtigt. Det næste logiske spørgsmål er, hvorfor forespørgslen med JOIN-er og selve underforespørgslen er hurtige hver for sig, men i fællesskab bremser de forfærdeligt?

  • Vi flytter underforespørgslen til CTE : hvis forespørgslen er hurtig i sig selv, 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 =5 
    AND recording_data.num_of_pages > 0;

Men selv det var stadig meget langsomt.

Vi finder synderen

I hele denne tid har en lille detalje svømmet forbi mine øjne, som jeg konstant har forsøgt at kaste væk. Men da der ikke var meget tilbage, besluttede jeg at kigge nærmere på den. Jeg taler om && operatoren. Indtil EXISTS kunne jeg blot forbedre ydeevnen, && var den eneste tilbageværende fælles faktor i alle versioner af den langsomme forespørgsel.

Ser vi på dokumentationen, ser vi, at && benyttes, når der skal findes fælles elementer mellem to arrays.

I den originale forespørgsel er det:

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

Hvilket betyder, at vi laver et mønstersøgning på vores url'er, og derefter finder vi skæringspunktet med alle url'er med fælles poster. Dette er en smule forvirrende, da 'urls' her ikke henviser til tabellen, der indeholder alle URL'er, men til kolonnen 'urls' i tabellen recording_data.

Med stigende mistanker om &&, forsøgte jeg at finde bekræftelse på dem i forespørgselsplanen, der blev genereret af EXPLAIN ANALYZE (jeg har allerede haft en gemt plan, men jeg plejer at finde det lettere at eksperimentere i SQL, end at prøve at forstå de uklare anmodningsplanlæggere).

Filter: ((urls && ($0)::text[]) OG (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) OG (r_time = '5'::double precision) OG (num_of_pages > 0))
                           Rækker fjernet af filter: 52710

Der var flere filterlinjer kun fra &&. Hvilket betød, at denne operation ikke kun var kostbar, men også blev udført flere gange.

Jeg kontrollerede dette ved at 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ørgsel kørte langsomt. Da JOIN-er er hurtige og underforespørgsler er hurtige, var der kun && operatøren tilbage.

Her er kun den nøgleoperation. Vi skal altid søge i hele den primære tabel af URL'er for at søge efter mønstre, og vi skal altid finde skæringspunkter. Vi kan ikke søge direkte i url-posterne, fordi de bare er ID'er, der refererer til urls.

På vej mod løsningen

&& langsom, fordi begge sæt er enorme. Operationen vil være relativt hurtig, hvis jeg erstatter urls og få en fungerende kommando. { "http://google.com/", "http://wingify.com/" }.

Jeg begyndte at lede efter en måde at lave mængdeintersektioner i Postgres uden at bruge &&, men uden særlig succes.

I sidste ende besluttede vi at løse problemet isoleret: giv mig alle urls rækker, hvor 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 syntaksen brugte jeg blot en underforespørgsel og rullede ud recording_data.urls arrayet, så betingelsen kunne anvendes direkte i HVOR.

Det vigtigste her er, at && bruges til at kontrollere, om den pågældende post indeholder den relevante URL. Hvis man ser lidt nærmere på denne operation, kan man se bevægelsen gennem elementerne i arrayet (eller tabellens rækker) og stoppet, når betingelsen (matchen) er opfyldt. Minder det dig om noget? Aha, EXISTS.

Da recording_data.urls kan refereres til udefra konteksten af underforespørgslen, når det sker, kan vi vende tilbage til vores gamle ven EXISTS og indkapsle det i underforespørgslen.

Når vi samler alt, får vi den endelige optimerede forespørgsel:

VÆLG 
    COUNT(*) 
FRA 
    acc_{account_id}.urls som recordings_urls, 
    acc_{account_id}.recording_data som recording_data, 
    acc_{account_id}.sessions som sessions 
HVOR 
    recording_data.usp_id = sessions.usp_id 
    OG  (  1 = 1  )  
    OG sessions.referrer_id = recordings_urls.id 
    OG r_time > to_timestamp(1542585600) 
    OG r_time = 5 
    OG recording_data.num_of_pages > 0
    OG EXISTS(
        VÆLG urls.url
        FRA 
            acc_{account_id}.urls som urls,
            (VÆLG unnest(urls) SOM rec_url_id FRA acc_{account_id}.recording_data) 
            SOM unrolled_urls
        HVOR
            urls.id = unrolled_urls.rec_url_id OG
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

Og den endelige udførelsestid Tid: 1898.717 ms Er det tid til at fejre?!?

Ikke så hurtigt! Først skal vi tjekke korrektheden. Jeg var meget skeptisk over for EXISTS optimering, da det ændrer logikken til en tidligere afslutning. Vi skal sikre os, at vi ikke har tilføjet en skjult fejl i forespørgslen.

En simpel kontrol indebar udførelsen af count(*) både på langsomme og hurtige forespørgsler for et stort antal forskellige datasæt. Derefter, for en lille undergruppe af data, kontrollerede jeg alle resultater manuelt.

Alle kontroller gav stabilt positive resultater. Vi har rettet alt!

Udtrukne Lærerige Punkter

Fra denne historie kan der udtrækkes mange lektioner:

  1. Planer for forespørgsler fortæller ikke hele historien, men kan give indikationer
  2. De vigtigste mistænkte er ikke altid de virkelige syndere
  3. Langsomme forespørgsler kan opdeles for at isolere flaskehalse
  4. Ikke alle optimeringer er i natur reduktive
  5. Brugen af EXIST, hvor det er muligt, kan føre til en dramatisk stigning i ydeevne

Output

Vi gik fra en forespørgselstid på ~24 minutter til 2 sekunder - en meget betydelig stigning i ydeevne! Selvom denne artikel blev lang, skete alle de eksperimenter, vi foretog, på én dag og anslås at have taget fra 1,5 til 2 timer for optimeringer og testning.

SQL er et fantastisk sprog, hvis man ikke er bange for det, men forsøger at forstå og bruge det. Med 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, man har med at gøre, vil man kunne gøre store fremskridt i forespørgselsoptimering. Det er lige så vigtigt dog at fortsætte med at afprøve forskellige tilgange og langsomt nedbryde problemet ved at finde flaskehalse.

Den bedste del ved at opnå sådanne resultater er den mærkbare forbedring i hastigheden — når en rapport, der før ikke engang kunne indlæses, nu næsten indlæses øjeblikkeligt.

Særlig tak til mine kolleger i teamet Aditya Mishra, Aditya Gaur og Varun Malhotra for idéudvekslingen og Dinkar Pandir for at finde den vigtige fejl i vores endelige forespørgsel, før vi endelig sagde farvel til den!

Kilde: habr.com

Køb pålidelig webhosting med DDoS-beskyttelse, VPS VDS-servere 🔥 Køb pålidelig webhosting med DDoS-beskyttelse, VPS VDS-servere | ProHoster