Prošlog prosinca primio sam zanimljivo izvješće o bugu od VWO tima za podršku. Vrijeme učitavanja jednog od analitičkih izvješća za velikog korporativnog klijenta činilo se previsokim. A kako je to moje područje odgovornosti, odmah sam se usredotočio na rješavanje problema.
prapovijest
Da bude jasno o čemu pričam, reći ću vam nešto o VWO. Ovo je platforma s kojom možete pokretati različite ciljane kampanje na svojim web stranicama: provoditi A/B eksperimente, pratiti posjetitelje i konverzije, analizirati tok prodaje, prikazivati toplinske karte i reproducirati snimke posjeta.
Ali najvažnija stvar kod platforme 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 analitičkom obliku.
Pomoću platforme 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 (koji se nalaze u Europi I koristili su iPhone)
Obratite pozornost na Booleove operatore. Dostupni su klijentima u sučelju upita za izradu proizvoljno složenih upita za dobivanje uzoraka.
Spor zahtjev
Klijent o kojem je riječ pokušavao je učiniti nešto što bi intuitivno trebalo raditi brzo:
Prikaži sve zapise sesije za korisnike koji su posjetili bilo koju stranicu s URL-om koji sadrži "/jobs"
Ova je stranica imala tonu prometa i pohranjivali smo više od milijun 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
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 i vremena:
Planirano vrijeme: 1.480 ms Vrijeme izvršenja: 1431924.650 ms
Upit je indeksirao 150 tisuća redaka. Planer upita pokazao je nekoliko zanimljivih detalja, ali bez očitih uskih grla.
Proučimo dalje zahtjev. Kao što vidite, ima JOIN tri stola:
- sjednice: za prikaz informacija o sesiji: preglednik, korisnički agent, država i tako dalje.
- podaci_snimanja: snimljeni URL-ovi, stranice, trajanje posjeta
- urls: Kako bismo izbjegli dupliciranje iznimno velikih URL-ova, pohranjujemo ih u zasebnu tablicu.
Također imajte na umu da su sve naše tablice već particionirane prema account_id. Na taj način je isključena situacija u kojoj jedan posebno velik račun uzrokuje probleme drugima.
Tražeći tragove
Nakon detaljnijeg pregleda, vidimo da nešto nije u redu s određenim zahtjevom. Vrijedi pobliže pogledati 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 zato ILIKE na svim tim dugačkim URL-ovima (imamo ih preko 1,4 milijuna jedinstvena URL-ovi prikupljeni za ovaj račun) mogu biti lošiji.
Ali ne, nije u tome poanta!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 rows)
Time: 5231.765 msSam zahtjev za pretraživanje predloška traje samo 5 sekundi. Pretraživanje uzorka u milijun jedinstvenih URL-ova očito nije problem.
Sljedećih osumnjičenih na listi je nekoliko JOIN. Možda je njihova pretjerana uporaba uzrokovala usporavanje? Obično JOINsu najočitiji kandidati za probleme s izvedbom, 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 msA to također nije bio naš slučaj. JOINIspostavilo se da je prilično brz.
Sužavanje kruga osumnjičenih
Bio sam spreman početi mijenjati upit kako bih postigao sva moguća poboljšanja performansi. Moj tim i ja razvili smo 2 glavne ideje:
- Koristite EXISTS za URL podupita: Htjeli smo ponovno provjeriti je li bilo problema s podupitom za URL-ove. Jedan od načina da to postignete je jednostavno korištenje
EXISTS.EXISTSuvelike poboljšava izvedbu budući da se odmah završava čim pronađe jedini niz koji odgovara 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 < to_timestamp(1549177599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0 ;
count
32519
(1 row)
Time: 1636.637 msPa da. Podupit kada je omotan 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 u CTE : Ako je upit brz sam po sebi, 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 jako sporo.
Pronalaženje krivca
Cijelo to vrijeme pred očima mi je bljeskala jedna sitnica koju sam neprestano odmicala. Ali kako mi ništa drugo nije preostalo, odlučio sam pogledati i nju. govorim o && operater. Pozdrav EXISTS samo poboljšane performanse && bio je jedini preostali zajednički faktor u svim verzijama sporog upita.
Gledati u , vidimo to && koristi se kada trebate pronaći zajedničke elemente između dva niza.
U izvornom 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 pretražujemo uzorke na našim URL-ovima, a zatim pronalazimo sjecište sa svim URL-ovima sa zajedničkim postovima. Ovo je pomalo zbunjujuće jer se "urls" ovdje ne odnosi na tablicu koja sadrži sve URL-ove, već na stupac "urls" u tablici 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 obično mi je 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: 52710Bilo je nekoliko redaka filtara samo iz &&. Što je značilo da je ova operacija bila ne samo skupa, već i višekratna.
To sam testirao izoliranjem stanja
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. Jer JOIN-s su brzi i podupiti su brzi, jedino što je ostalo je && operater.
Ovo je samo ključna operacija. Uvijek moramo pretražiti cijelu temeljnu tablicu URL-ova kako bismo potražili uzorak i uvijek moramo pronaći sjecišta. Ne možemo izravno pretraživati prema URL zapisima jer su to samo ID-ovi koji se odnose na urls.
Na putu do rješenja
&& spor jer su oba skupa ogromna. Operacija će biti relativno brza ako zamijenim urls na { "http://google.com/", "http://wingify.com/" }.
Počeo sam tražiti način da napravim presjek skupova u Postgresu bez korištenja &&, ali bez većeg uspjeha.
Na kraju smo odlučili samo riješiti problem u izolaciji: daj mi sve urls linije za koje URL odgovara uzorku. Bez dodatnih uvjeta bit ć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 sintaksu Upravo sam upotrijebio podupit i proširio recording_data.urls niz tako da možete izravno primijeniti uvjet u WHERE.
Ovdje je najvažnije to && koristi se za provjeru sadrži li određeni unos odgovarajući URL. Ako malo zaškiljite, možete vidjeti kako se ova operacija kreće kroz elemente niza (ili redove tablice) i zaustavlja se kada se ispuni uvjet (podudaranje). Ne podsjeća te ni na što? Da, EXISTS.
Od na recording_data.urls može referencirati izvan konteksta podupita, kada se to dogodi možemo se osloniti na našeg starog prijatelja EXISTS i njime omotajte podupit.
Spajajući sve zajedno, dobivamo konačni optimizirani 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 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 se ranije prekine. Moramo biti sigurni da zahtjevu nismo dodali neočitu pogrešku.
Jednostavan test je bio pokrenuti count(*) na sporim i brzim upitima za veliki broj različitih skupova podataka. Zatim sam za mali podskup podataka ručno provjerio jesu li svi rezultati točni.
Svi testovi dali su dosljedno 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 pružiti tragove
- Glavni osumnjičenici nisu uvijek pravi krivci
- Spori upiti mogu se rastaviti kako bi se izolirala uska grla
- Nisu sve optimizacije reduktivne prirode
- Koristiti
EXIST, gdje je to moguće, može dovesti do dramatičnog povećanja produktivnosti
Izlaz
Prešli smo s vremena upita od ~24 minute na 2 sekunde - prilično značajno povećanje izvedbe! Iako je ovaj članak bio velik, svi eksperimenti koje smo radili dogodili su se u jednom danu, a procjenjuje se da im je trebalo između 1,5 i 2 sata za optimizacije i testiranje.
SQL je prekrasan jezik ako ga se ne bojite, ali ga pokušate naučiti i koristiti. Ako dobro razumijete kako se izvršavaju SQL upiti, kako baza podataka generira planove upita, kako rade indeksi i jednostavno veličinu podataka s kojima radite, možete biti vrlo uspješni u optimiziranju upita. Međutim, jednako je važno nastaviti iskušavati različite pristupe i polako razbijati problem, pronalazeći uska grla.
Najbolji dio postizanja ovakvih rezultata je primjetno, vidljivo poboljšanje brzine - gdje se izvješće koje se prije nije ni učitavalo sada učitava gotovo trenutno.
Posebno zahvaljujemo moji drugovi na zapovijed Aditya Mishra, Aditya Gauru и za mozganje i Dinkar Pandir jer ste pronašli važnu pogrešku u našem posljednjem zahtjevu prije nego što smo se konačno oprostili od njega!
Izvor: www.habr.com