ProHoster > Blog > Administracija > Povijest jedne SQL istrage

Povijest jedne SQL istrage

U prosincu prošle godine primio sam zanimljivo izvješće o grešci od VWO tima za podršku. Vrijeme učitavanja jednog od analitičkih izvještaja za velikog korporativnog klijenta činilo se nevjerojatno dugim. Kako je to bila moja odgovornost, odmah sam se fokusirao na rješavanje problema.

Povijest

Da bih pojasnio o čemu se radi, malo ću reći o VWO-u. To je platforma koja omogućava pokretanje različitih ciljanih kampanja na vašim web stranicama: provođenje A/B eksperimenata, praćenje posjetitelja i konverzija, analizu prodajnog lijevka, prikazivanje toplinskih karata i reprodukciju snimaka posjeta.

No najvažnija funkcija platforme su izvještaji. Sve prethodno navedene funkcije su međusobno povezane. Za korporativne klijente, ogroman skup informacija bio bi potpuno beskoristan bez moćne platforme koja ih prikazuje na način prikladan za analizu.

Korištenjem platforme možete postaviti bilo kakav upit na velikom skupu podataka. Evo jednostavnog primjera:

Prikaži sve klikove na stranici "abc.com"
OD <datuma d1> DO <datuma d2>
za osobe koje
koriste Chrome ILI
(žive u Europi I koriste iPhone)

Obratite pažnju na booleanske operatore. Oni su dostupni klijentima u sučelju za upite, omogućujući izradu neograničeno složenih upita za dobivanje uzoraka.

Spori upit

Klijent o kojem se radi pokušavao je učiniti nešto što intuitivno treba brzo raditi:

Prikaži sve snimke sesija
za korisnike koji su posjetili bilo koju stranicu
s URL-om koji sadrži "\/jobs"

Na ovoj web stranici bilo je ogromno količine prometa, a pohranjivali smo više od milijun jedinstvenih URL adresa samo za nju. A željeli su pronaći prilično jednostavan uzorak URL-a, koji se odnosio na njihov poslovni model.

Preliminarna istraga

Pogledajmo što se događa u bazi podataka. Ispod je izvorni spori SQL upit:

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 ;

A evo vremenskih oznaka:

Planirano vrijeme: 1.480 ms
Vrijeme izvršenja: 1431924.650 ms

Upit je obuhvatio 150 tisuća redaka. Planer upita pokazao je nekoliko zanimljivih detalja, ali nikakvih očiglednih uskih grla.

Pogledajmo upit dublje. Kao što se može vidjeti, on radi JOIN tri tablice:

  1. sessions: za prikaz informacija o sesiji: preglednik, korisnički agent, država i tako dalje.
  2. recording_data: snimljeni URL-ovi, stranice, trajanje posjeta
  3. urls: kako bismo izbjegli dupliciranje iznimno dugih URL-ova, pohranjujemo ih u zasebnu tablicu.

Također, obratite pozornost da su sve naše tablice već podijeljene prema account_id. Na taj način isključena je situacija u kojoj bi zbog jednog izuzetno velikog računa problemi nastali za ostale.

U potrazi za tragovima

Pri bližem pregledu vidimo da nešto nije u redu s konkretnim upitom. Treba obratiti pažnju na ovu liniju:

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

Prva pomisao bila je da možda, zbog ILIKE na svim tim dugim URL-ovima (imamo više od 1,4 milijuna jedinstvenih URL-ova, prikupljenih za ovaj račun) performanse mogu opadati.

Ali, ne — nije do toga!

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

Vrijeme: 5231.765 ms

Sam upit za pretraživanje po obrascu traje samo 5 sekundi. Pretraživanje po obrascu na milijunu jedinstvenih URL-ova očito nije problem.

Sljedeći osumnjičenik na popisu — nekoliko JOIN. Možda je njihovo pretjerano korištenje dovelo do usporavanja? Obično JOIN‘e — najočigledniji su kandidati za probleme s performansama, ali nisam vjerovao da je naš slučaj tipičan.

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

Vrijeme: 147.851 ms

I to također nije bio naš slučaj. JOIN‘i su se pokazali vrlo brzim.

Sužavanje kruga osumnjičenika

Bio sam spreman početi mijenjati upit kako bismo postigli bilo kakva moguća poboljšanja performansi. Tim smo razvili 2 glavne ideje:

  • Koristiti EXISTS za podupit URL-ova: Željeli smo još jednom provjeriti ima li problema s podupitom za URL-ove. Jedan od načina da se to postigne — jednostavno koristiti EXISTS. EXISTS može značajno poboljšanje performansi dolazi odmah nakon što se pronađe jedini redak 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 jako usporavaju zajedno?

  • Premještamo podupit u CTE : ako je upit brz sam po sebi, možemo prvo izračunati brz rezultat, a zatim ga donijeti 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 još uvijek bilo vrlo sporo.

Pronalazimo krivca

Cijelo to vrijeme pred očima mi je bila jedna sitnica od koje sam se stalno odmičući. No s obzirom na to da više ništa nije preostalo, odlučio sam pogledati i to. Govorim o && operatoru. Dok EXISTS samo poboljšava performanse, && je bio jedini preostali zajednički faktor u svim verzijama sporog upita.

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

U izvornom 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 radimo pretraživanje prema obrascu na našim URL-ovima, a zatim nalazimo presjek sa svim URL-ovima s zajedničkim zapisima. To je malo zbunjujuće, jer "urls" ovdje ne referira na tablicu koja sadrži sve URL-ove, već na stupac "urls" unutar tablice recording_data.

S rastom sumnje u vezi &&, pokušao sam pronaći potvrdu u planu upita generiranom EXPLAIN ANALYZE (već sam imao spremljeni plan, ali obično mi je lakše eksperimentirati u SQL-u nego pokušavati shvatiti neprozirnosti planera upita).

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))
                           Redovi uklonjeni filtriranjem: 52710

Bilo je nekoliko redaka filtara samo iz &&. Što je značilo da je ova operacija bila ne samo skupa, već je i izvršavana nekoliko puta.

Provjerio sam to izolirajući uvjet

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

Ovaj upit se izvršavao sporo. Budući da JOIN-i su brzi i podupiti su brzi, ostao je samo && operator.

Samo ovo je ključna operacija. Uvijek moramo tražiti u cijeloj glavnoj tablici URL-ova kako bismo tražili prema obrascu, i uvijek trebamo pronaći presjeke. Ne možemo direktno pretraživati zapise URL-ova, jer su to samo ID-evi koji se odnose na urls.

Na putu do rješenja

&& spor, jer su oba seta ogromna. Operacija će biti relativno brza ako zamijenim urls na { "http://google.com/", "http://wingify.com/" }.

Počeo sam tražiti način da u Postgresu napravim presjek skupova bez korištenja &&, ali bez posebnog uspjeha.

Na kraju smo odlučili jednostavno riješiti problem izolirano: daj mi sve urls redove za koje URL odgovara obrascu. Bez dodatnih uvjeta to će biti — 

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

Umjesto JOIN sintaksom samo sam koristio podupit i razvio recording_data.urls niz, kako bi se moglo direktno primijeniti uvjet u GDJE.

Najvažnija stvar ovdje je da && se koristi za provjeru sadrži li određeni zapis odgovarajući URL. Malo sužavajući oči, može se vidjeti kako se u ovoj operaciji premješta kroz elemente niza (ili redove tablice) i zaustavi se kada se ispuni uvjet (odgovaranje). Ništa ne podsjeća? Aha, EXISTS.

Budući da recording_data.urls može se pozivati izvan konteksta podupita, kada se to dogodi, možemo se vratiti našem starom prijatelju EXISTS i obaviti mu podupit.

Kombinirajući sve zajedno, dobivamo konačni optimizirani upit:

ODABERITE 
    count(*) 
IZ 
    acc_{account_id}.urls kao recordings_urls, 
    acc_{account_id}.recording_data kao recording_data, 
    acc_{account_id}.sessions kao sessions 
GDE 
    recording_data.usp_id = sessions.usp_id 
    I 
    (  1 = 1  )  
    I sessions.referrer_id = recordings_urls.id 
    I r_time > to_timestamp(1542585600) 
    I r_time = 5 
    I recording_data.num_of_pages > 0
    I POSTOJI(
        ODABERITE urls.url
        IZ 
            acc_{account_id}.urls kao urls,
            (ODABERITE unnest(urls) KAO rec_url_id IZ acc_{account_id}.recording_data) 
            KAO unrolled_urls
        GDE
            urls.id = unrolled_urls.rec_url_id I
            urls.url ILIKE '%enterprise_customer.com/jobs%'
    );

I konačno vrijeme izvršavanja Vrijeme: 1898.717 ms Vrijeme je za proslavu?!?

Ne tako brzo! Prvo treba provjeriti ispravnost. Bio sam vrlo sumnjičav prema EXISTS optimizaciji, jer ona mijenja logiku za ranije završavanje. Moramo biti sigurni da nismo dodali nejasnu grešku u upit.

Jednostavna provjera bila je izvršavanje count(*) i na sporim i na brzim upitima za mnoge različite skupove podataka. Zatim sam, za malo podskupova podataka, ručno provjerio ispravnost svih rezultata.

Sve provjere su dale stabilno pozitivne rezultate. Sve smo popravili!

Izvučene Lekcije

Mnoge lekcije se mogu izvući iz ove priče:

  1. Planovi upita ne govore cijelu priču, ali mogu dati naznake
  2. Glavni osumnjičeni nisu uvijek pravi zločinci
  3. Spore upite se mogu podijeliti kako bi se izolirali uska mjesta
  4. Nisu sve optimizacije po prirodi reduktivne
  5. Korištenje EXIST, gdje je to moguće, može dovesti do dramatičnog rasta performansi

Izvještaj

Prešli smo s vremena upita od ~24 minute na 2 sekunde — značajan rast performansi! Iako je ovaj članak dug, svi eksperimenti koje smo radili dogodili su se u jednom danu i procijenjeno je da su trajali od 1,5 do 2 sata za optimizacije i testiranje.

SQL je čudesan jezik, ako se ne bojite njega, već pokušavate razumjeti i koristiti ga. Imajući dobro razumijevanje kako se SQL upiti izvršavaju, kako Baze podataka generiraju planove upita, kako rade indeksi i samo veličina podataka s kojima imate posla, mogli biste vrlo uspjeti u optimizaciji upita. No, jednako je važno nastaviti isprobavati različite pristupe i polako raspraviti problem, nalazeći uska mjesta.

Najbolji dio postizanja sličnih rezultata je vidljivo poboljšanje brzine rada – kada se izvještaj koji prije nije mogao učitati sada učitava gotovo trenutno.

Posebna zahvalnost mojim kolegama u timu Aditya Mishre, Aditya Gaur i Varun Malhotra za brainstorming i Dinkaru Pandiru što je pronašao važnu grešku u našem konačnom zahtjevu prije nego što smo se s njim konačno oprostili!

Izvor: habr.com

Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere 🔥 Kupite pouzdan hosting za web stranice s DDoS zaštitom, VPS VDS servere | ProHoster