🥇Zgodba o enem SQL preiskovanju

Decembra lani sem prejel zanimivo poročilo o napaki od ekipe za podporo VWO. Čas nalaganja enega od analitičnih poročil za velikega korporativnega klienta se je zdel izjemno dolg. Glede na to, da je to področje moje odgovornosti, sem se takoj osredotočil na rešitev težave.

Pozadina

Da bo jasno, o čem govorim, bom na kratko predstavil VWO. To je platforma, s katero lahko izvajate različne ciljne kampanje na svojih spletnih straneh: izvajate A/B eksperimente, spremljate obiskovalce in konverzije, analizirate prodajne lijake, prikazujete toplotne zemljevide in predvajate posnetke obiskov.

Toda najpomembnejša funkcija platforme je priprava poročil. Vse zgoraj omenjene funkcije so med seboj povezane. Za korporativne stranke bi bil velik obseg informacij preprosto brez smisla brez močne platforme, ki jih predstavlja v analitični obliki.

Z uporabo platforme lahko postavite poljuben zahtevek na velikem naboru podatkov. Tukaj je preprost primer:

Prikaži vse klike na strani "abc.com"
OD <datuma d1> DO <datuma d2>
za ljudi, ki
so uporabili Chrome ALI
(sobili v Evropi IN uporabljali iPhone)

Upoštevajte logične operatorje. Ti so na voljo klientom v vmesniku za poizvedbe, da bi omogočili čim bolj kompleksne poizvedbe za pridobivanje vzorcev.

Počasen poizvedba

Klient, o katerem govorimo, je poskušal narediti nekaj, kar intuitivno deluje hitro:

Prikaži vse zapise sej
za uporabnike, ki so obiskali katerokoli stran
s URL-jem, ki vsebuje "\/jobs"

Na tej spletni strani je bilo ogromno prometa, shranili smo več kot milijon edinstvenih URL-jev samo za to. In želeli so najti precej preprost vzorec URL-ja, ki se nanaša na njihov poslovni model.

Predhodno preiskovanje

Poglejmo, kaj se dogaja v bazi podatkov. Spodaj je izvirni počasen SQL poizvedba:

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 ;

Tukaj so časovne oznake:

Načrtovani čas: 1.480 ms
Čas izvedbe: 1431924.650 ms

Poizvedba je obdelala 150 tisoč vrstic. Načrtovalec poizvedb je pokazal nekaj zanimivih podrobnosti, a nobenih očitnih ozkih grl.

Poglejmo še naprej to poizvedbo. Kot je razvidno, dela JOIN treh tabel:

sessions: za prikaz informacij o sejah: brskalnik, uporabniška agenta, država itd.
recording_data: zabeleženi URL-ji, strani, trajanje obiskov
urls: da bi se izognili podvajanju izjemno dolgih URL-jev, jih shranjujemo v ločeno tabelo.

Prav tako bodite pozorni, da so vse naše tabele že razdeljene po account_id. S tem je izključena situacija, ko zaradi enega posebej velikega računa težave nastanejo pri drugih.

V iskanju dokazov

Ob natančnejšem pregledu vidimo, da nekaj pri tej konkretni poizvedbi ni v redu. Osvetljiti je vredno to vrstico:

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

Prva misel je bila, da bi morda zaradi ILIKE na vseh teh dolgih URL-jih (imamo več kot 1,4 milijona edinstvenih URL-jev, zbranih za ta račun) lahko prišlo do zmanjšanja uspešnosti.

Ampak ne — to ni bilo to!

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

Čas: 5231.765 ms

Sama poizvedba za iskanje po vzorcu traja le pet sekund. Iskanje po vzorcu pri milijonu unikatnih URL-jev očitno ni težava.

Naslednji osumljenec na seznamu — nekaj JOIN. Morda je njihova pretirana uporaba povzročila upočasnitev? Običajno JOIN‘ji — so najobičajnejši kandidati za težave z uspešnostjo, a nisem verjel, da je naš primer tipičen.

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 vrstica)

Čas: 147.851 ms

In to prav tako ni bil naš primer. JOIN‘ji so se izkazali za zelo hitre.

Omejitev kroga osumljencev

Bil sem pripravljen začeti spreminjati poizvedbo, da bi dosegel kakršne koli možne izboljšave uspešnosti. Skupaj s timom smo razvili 2 glavna načina:

Uporabite EXISTS za podpoizvedbo URL-jev: Želeli smo ponovno preveriti, ali so težave s podpoizvedbo za URL-je. Eden od načinov, kako to doseči, je preprosto uporabiti EXISTS. EXISTS lahko znatno poboljšati performanse jer završava odmah nakon pronalaska jedine linije prema uvjetu.

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

Da, podupit, kada je omotan u EXISTS, čini sve super brzim. Sljedeće logično pitanje je zašto je upit s JOIN-ima i sam podupit brzi pojedinačno, ali strašno usporavaju zajedno?

Premještamo podupit u CTE : ako je upit brz sam po sebi, možemo prvo izračunati brzi rezultat, a zatim ga osigurati glavnom upitu

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;

Ali je sve to još uvijek bilo vrlo sporo.

Pronalazimo krivca

Tijekom cijelog ovog vremena pred očima mi se pojavila mala sitnica koju sam stalno odbacivao. No, budući da više ništa drugo nije ostalo, odlučio sam malo pogledati i nju. Govorim o && operatoru. Dok EXISTS samo poboljšao performanse, && bila je jedini preostali zajednički faktor u svim verzijama sporog upita.

Gledajući dokumentacijo, vidimo da && se koristi kada je potrebno pronaći zajedničke elemente između dva niza.

U originalnom upitu to je:

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

Što znači da tražimo po obrascu po našim URL-ovima, zatim pronalazimo presjek sa svim URL-ovima s zajedničkim zapisima. Ovo je pomalo zbunjujuće, jer "urls" ovdje ne upućuje na tablicu koja sadrži sve URL-ove, već na stupac "urls" u tablici recording_data.

S rastućim sumnjama u vezi sa &&, pokušao sam pronaći njihovo potvrđenje u planu upita, generiranom EXPLAIN ANALYZE (imela sem shranjen načrt, vendar mi je običajno bolj priročno eksperimentirati v SQL kot poskušati razumeti nejasnosti načrtovalnikov poizvedb).

Filter: ((urls && ($0)::text[]) IN (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) IN (r_time = '5'::double precision) IN (num_of_pages > 0))
                           Raziščene vrstice: 52710

Tam je bilo nekaj vrstic filtrov le iz &&. Kar je pomenilo, da ta operacija ni bila le draga, ampak je bila izvedena večkrat.

To sem preveril, tako da sem izoliral pogoje

SELECT 1
FROM 
    acc_{account_id}.urls kot recordings_urls, 
    acc_{account_id}.recording_data_30 kot recording_data_30, 
    acc_{account_id}.sessions_30 kot sessions_30 
WHERE 
	urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]

Ta poizvedba se je izvajala počasi. Ker JOIN-i so hitri in podpoizvedbe so hitre, je ostal le && operator.

Samo to je ključna operacija. Vedno moramo iskati po glavni tabeli URL-jev, da bi iskali po vzorcu, in vedno moramo najti preseke. Ne moremo iskati neposredno po zapisih URL-jev, ker so to le ID-ji, ki se navezujejo na urls.

Na poti do rešitve

&& počasni, ker sta oba niza ogromna. Operacija bo relativno hitra, če zamenjam urls na { "http://google.com/", "http://wingify.com/" }.

Začel sem iskati način, kako v Postgresu uporabiti presek množic brez &&, vendar brez večjega uspeha.

Na koncu smo se odločili, da preprosto rešimo problem izolirano: daj mi vse urls vrstice, za katere se URL ujema z vzorcem. Brez dodatnih pogojev bo to —

SELECT urls.url
FROM 
	acc_{account_id}.urls kot urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id IN
	urls.url ILIKE '%jobs%'

Namesto JOIN sintaksa sem preprosto uporabil podpoizvedbo in razvil recording_data.urls niz, da bi lahko neposredno uporabili pogoj v WHERE.

Najpomembnejše je, && da se uporablja za preverjanje, ali ta zapis vsebuje ustrezen URL. Če se malo skoncentriramo, lahko opazimo, da pri tej operaciji potujemo po elementih niza (ali vrsticah tabele) in se ustavimo, ko se izpolni pogoj (ustreznost). Nič ne spominja? Aha, EXISTS.

Ker recording_data.urls lahko sklicujemo izven konteksta podpoizvedbe, ko se to zgodi, se lahko vrnemo k našemu staremu prijatelju EXISTS in ga ovijemo s podpoizvedbo.

Kdaj vse skupaj združimo, dobimo končno optimizirano poizvedbo:

IZBERI 
    št. 
IZ 
    acc_{account_id}.urls kot recordings_urls, 
    acc_{account_id}.recording_data kot recording_data, 
    acc_{account_id}.sessions kot sessions 
KJER 
    recording_data.usp_id = sessions.usp_id 
    IN  (  1 = 1  )  
    IN sessions.referrer_id = recordings_urls.id 
    IN r_time > to_timestamp(1542585600) 
    IN r_time =5 
    IN recording_data.num_of_pages > 0
    IN OBSTOJI(
        IZBERI urls.url
        IZ 
            acc_{account_id}.urls kot urls,
            (IZBERI unnest(urls) AS rec_url_id IZ acc_{account_id}.recording_data) 
            AS unrolled_urls
        KJER
            urls.id = unrolled_urls.rec_url_id IN
            urls.url ILIKE '%enterprise_customer.com/jobs%'
    );

In končni čas izvajanja Čas: 1898.717 ms Čas za praznovanje?!?

Ne tako hitro! Najprej moramo preveriti pravilnost. Bil sem izjemno sumljiv glede EXISTS optimizacije, ker spreminja logiko na hitrejše dokončanje. Prepričati se moramo, da nismo dodali skrite napake v poizvedbo.

Preprost pregled je bil izvajanje count(*) in pri počasnih in hitrih poizvedbah za veliko različnih sklopov podatkov. Nato sem za manjši podsklop podatkov ročno preveril pravilnost vseh rezultatov.

Vse preglede so dale dosledno pozitivne rezultate. Vse smo popravili!

Izkušene Lekcije

Iz te zgodbe lahko pridobimo veliko lekcij:

Načrti poizvedb ne povedo vse zgodbe, lahko pa dajo namige
Glavni osumljenci niso vedno pravi storilci
Počasne poizvedbe je mogoče razdeliti, da izoliramo ozka grla
Ne vse optimizacije so po naravi reduktivne
Uporaba EXIST, kjer je to mogoče, lahko privede do drastičnega povečanja zmogljivosti

Izlaz

Prešli smo od časa poizvedbe približno 24 minut do 2 sekund — precej resno povečanje zmogljivosti! Čeprav je ta članek dolg, so se vsi eksperimenti, ki smo jih izvedli, zgodili v enem dnevu in po ocenah trajali med 1,5 in 2 uri za optimizacijo in testiranje.

SQL je čudovit jezik, če se ga ne bojite, temveč poskušate razumeti in uporabljati. Z dobrim razumevanjem, kako se izvajajo SQL poizvedbe, kako DB generira načrte poizvedb, kako delujejo indeksi in preprosto velikost podatkov, ki jih imate, lahko zelo napredujete pri optimizaciji poizvedb. Enako pomembno pa je, da še naprej preizkušate različne pristope in počasi razdelite težave, pri tem pa raziskujete ozka grla.

Najboljša stvar pri doseganju takih rezultatov je opazno izboljšanje hitrosti delovanja — poročilo, ki se prej sploh ni naložilo, se zdaj naloži skoraj takoj.

Posebna zahvala mojim kolegom v ekipi Aditya Mishra, Aditya Gaur in Varun Malhotra za brainstorm in Dinkar Pandir za to, da je našel pomembno napako v naši končni zahtevi, preden smo se dokončno poslovili!

Izvor: habr.com

Zgodba enega SQL pregleda