🥇Priča o jednoj SQL istrazi

Prošlog decembra primio sam zanimljiv izvještaj o grešci od VWO tima za podršku. Vrijeme učitavanja jednog od analitičkih izvještaja za velikog korporativnog klijenta činilo se previsokim. A pošto je to moja oblast odgovornosti, odmah sam se fokusirao na rešavanje problema.

prapovijest

Da bi bilo jasno o čemu govorim, reći ću vam nešto o VWO-u. Ovo je platforma s kojom možete pokrenuti različite ciljane kampanje na svojim web stranicama: provoditi A/B eksperimente, pratiti posjetitelje i konverzije, analizirati tok prodaje, prikazati toplinske mape i puštati snimke posjeta.

Ali najvažnija stvar u vezi sa platformom je izvještavanje. Sve gore navedene funkcije su međusobno povezane. A za korporativne klijente, ogromna količina informacija bila bi jednostavno beskorisna bez moćne platforme koja ih predstavlja u obliku analitike.

Koristeći platformu, možete napraviti nasumični upit na velikom skupu podataka. Evo jednostavnog primjera:

Prikaži sve klikove na stranici "abc.com" OD <datum d1> DO <datum d2> za ljude koji su koristili Chrome ILI (nalazi se u Evropi I koristili iPhone)

Obratite pažnju na Bulove operatore. Oni su dostupni klijentima u interfejsu upita da naprave proizvoljno složene upite za dobijanje uzoraka.

Spor zahtjev

Dotični klijent je pokušavao da uradi nešto što bi intuitivno trebalo da funkcioniše brzo:

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

Ova stranica je imala tonu prometa i pohranili smo preko milion jedinstvenih URL-ova samo za nju. I htjeli su pronaći prilično jednostavan URL predložak koji se odnosi na njihov poslovni model.

Prethodna istraga

Hajde da pogledamo šta se dešava u bazi podataka. Ispod je originalni 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 i tajminga:

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

Upit je prešao 150 hiljada redova. Planer upita pokazao je nekoliko zanimljivih detalja, ali bez očiglednih uskih grla.

Proučimo zahtjev dalje. Kao što vidite, ima JOIN tri stola:

sesije: za prikaz informacija o sesiji: pretraživač, korisnički agent, zemlja i tako dalje.
recording_data: snimljeni URL-ovi, stranice, trajanje posjeta
url: Da bismo izbjegli dupliciranje izuzetno velikih URL-ova, pohranjujemo ih u posebnu tabelu.

Također imajte na umu da su sve naše tablice već podijeljene po account_id. Na ovaj način se isključuje situacija u kojoj jedan posebno veliki račun uzrokuje probleme drugima.

U potrazi za tragovima

Pažljivijim pregledom vidimo da nešto nije u redu sa određenim zahtjevom. Vrijedi pažljivije pogledati ovu liniju:

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

Prva pomisao je bila da možda zato ILIKE na svim ovim dugačkim URL-ovima (imamo preko 1,4 miliona jedinstven URL-ovi prikupljeni za ovaj račun) performanse mogu štetiti.

Ali ne, nije to poenta!

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

Time: 5231.765 ms

Sam zahtjev za pretraživanje šablona traje samo 5 sekundi. Traženje uzorka u milion jedinstvenih URL-ova očigledno nije problem.

Sljedeći osumnjičeni na listi je nekoliko JOIN. Možda je njihova prekomjerna upotreba uzrokovala usporavanje? Obično JOIN's su najočitiji kandidati za probleme sa 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 < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

A to takođe nije bio naš slučaj. JOINIspostavilo se da je prilično brz.

Sužavanje kruga osumnjičenih

Bio sam spreman da počnem da menjam upit kako bih postigao bilo kakva moguća poboljšanja performansi. Moj tim i ja smo razvili 2 glavne ideje:

Koristite EXISTS za URL podupita: Htjeli smo ponovo provjeriti ima li problema sa podupitom za URL-ove. Jedan od načina da se to postigne je jednostavno korištenje EXISTS. EXISTS moći značajno poboljšava performanse jer se završava odmah čim pronađe jedini niz koji odgovara uslovu.

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

Pa da. Podupit kada je umotan EXISTS, čini sve super brzim. Sljedeće logično pitanje je zašto zahtjev sa JOIN-ami i sam podupit su brzi pojedinačno, ali su užasno spori zajedno?

Premještanje podupita na CTE : Ako je upit sam po sebi brz, možemo jednostavno prvo izračunati brzi rezultat, a zatim ga dati 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 < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Ali i dalje je bilo veoma sporo.

Pronalaženje krivca

Sve to vrijeme pred očima mi je bljesnula jedna sitnica koju sam stalno odbacivao. Ali pošto nije preostalo ništa drugo, odlučio sam da pogledam i nju. govorim o tome && operater. ćao EXISTS samo poboljšane performanse && bio je jedini preostali zajednički faktor u svim verzijama sporog upita.

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

U originalnom zahtjevu ovo je:

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

Što znači da radimo pretragu po uzorku na našim URL-ovima, a zatim pronađemo raskrsnicu sa svim URL-ovima sa uobičajenim objavama. Ovo je malo zbunjujuće jer se "urls" ovdje ne odnosi na tabelu koja sadrži sve URL-ove, već na stupac "urls" u tabeli recording_data.

Uz rastuće sumnje u vezi &&, pokušao sam pronaći potvrdu za njih u generiranom planu upita EXPLAIN ANALYZE (Već sam imao spremljen plan, ali mi je obično ugodnije eksperimentirati u SQL-u nego pokušavati razumjeti neprozirnost planera upita).

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

Bilo je nekoliko linija filtera samo od &&. Što je značilo da je ova operacija bila ne samo skupa, već i nekoliko puta izvedena.

Testirao sam ovo tako što sam izolovao stanje

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 je bio spor. Zbog JOIN-s su brzi i podupiti su brzi, jedino što je ostalo je && operater.

Ovo je samo ključna operacija. Uvijek moramo pretraživati cijelu tabelu URL-ova u osnovi da bismo tražili uzorak, i uvijek moramo pronaći raskrsnice. Ne možemo direktno pretraživati po URL zapisima, jer su to samo ID-ovi na koje se odnose urls.

Na putu do rješenja

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

Počeo sam da tražim način da napravim raskrsnicu u Postgresu bez upotrebe &&, ali bez većeg uspeha.

Na kraju smo odlučili problem riješiti samo izolovano: daj mi sve urls linije za koje URL odgovara uzorku. Bez dodatnih uslova biće -

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 sintaksa Upravo sam koristio potupit i proširio recording_data.urls niz tako da možete direktno primijeniti uvjet u WHERE.

Ono što je ovdje najvažnije je to && koristi se za provjeru da li dati unos sadrži odgovarajući URL. Ako malo zaškiljite, možete vidjeti kako se ova operacija kreće kroz elemente niza (ili redova tabele) i zaustavlja se kada se ispuni uslov (podudaranje). Ne podsjeća te ni na šta? da, EXISTS.

Od dalje recording_data.urls može se referencirati izvan konteksta potupita, kada se to dogodi, možemo se vratiti na našeg starog prijatelja EXISTS i omotajte potupit s njim.

Stavljajući sve zajedno, dobijamo konačni optimizovani 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  (  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%'
    );

I konačno vrijeme vođenja Time: 1898.717 ms Vrijeme je za slavlje?!?

Ne tako brzo! Prvo morate provjeriti ispravnost. Bio sam krajnje sumnjičav EXISTS optimizacija jer mijenja logiku da bi se završila ranije. Moramo biti sigurni da zahtjevu nismo dodali neočiglednu grešku.

Jednostavan test je bio pokretanje count(*) na sporim i brzim upitima za veliki broj različitih skupova podataka. Zatim, za mali podskup podataka, ručno sam potvrdio da su svi rezultati tačni.

Svi testovi davali su konstantno pozitivne rezultate. Sve smo popravili!

Naučene lekcije

Mnogo je lekcija koje se mogu naučiti iz ove priče:

Planovi upita ne govore cijelu priču, ali mogu dati tragove
Glavni osumnjičeni nisu uvijek pravi krivci
Spori upiti se mogu raščlaniti kako bi se izolirala uska grla
Nisu sve optimizacije reduktivne prirode
Koristite EXIST, gdje je to moguće, može dovesti do dramatičnog povećanja produktivnosti

zaključak

Prešli smo sa vremena upita od ~24 minuta na 2 sekunde - prilično značajno povećanje performansi! Iako je ovaj članak izašao veliki, svi eksperimenti koje smo radili dogodili su se u jednom danu, a procijenjeno je da im je za optimizaciju i testiranje potrebno između 1,5 i 2 sata.

SQL je divan jezik ako ga se ne plašite, ali pokušajte da ga naučite i koristite. Ako dobro razumijete kako se izvršavaju SQL upiti, kako baza podataka generiše planove upita, kako indeksi rade i jednostavno veličinu podataka s kojima imate posla, možete biti vrlo uspješni u optimizaciji upita. Podjednako je važno, međutim, nastaviti s isprobavanjem različitih pristupa i polako razbijati problem, pronalazeći uska grla.

Najbolji dio u postizanju ovakvih rezultata je primjetno, vidljivo poboljšanje brzine - gdje se izvještaj koji se ranije nije ni učitavao sada učitava gotovo trenutno.

Posebno hvala moji drugovi po komandi Aditya Mishra, Aditya Gauru и Varun Malhotra za brainstorming i Dinkar Pandir jer smo pronašli važnu grešku u našem konačnom zahtjevu prije nego što smo se konačno oprostili od nje!

izvor: www.habr.com

Priča o jednoj SQL istrazi