Praėjusį gruodį gavau įdomų pranešimą apie klaidą iš VWO palaikymo komandos. Vienos iš stambaus verslo kliento analitinių ataskaitų įkėlimo laikas atrodė pernelyg ilgas. Ir kadangi tai yra mano atsakomybės sritis, iškart sutelkiau dėmesį į problemos sprendimą.
priešistorė
Kad būtų aišku, apie ką aš kalbu, šiek tiek papasakosiu apie VWO. Tai platforma, su kuria galite pradėti įvairias tikslines kampanijas savo svetainėse: atlikti A/B eksperimentus, sekti lankytojus ir konversijas, analizuoti pardavimo kanalą, rodyti karščio žemėlapius ir leisti apsilankymų įrašus.
Tačiau svarbiausias dalykas platformoje yra ataskaitų teikimas. Visos aukščiau nurodytos funkcijos yra tarpusavyje susijusios. O verslo klientams didžiulis kiekis informacijos būtų tiesiog nenaudingas be galingos platformos, kuri ją pateikia analitine forma.
Naudodami platformą galite atlikti atsitiktinę užklausą dideliame duomenų rinkinyje. Štai paprastas pavyzdys:
Rodyti visus paspaudimus puslapyje „abc.com“ NUO <data d1> IKI <data d2> žmonėms, kurie naudojo „Chrome“ ARBA (esančius Europoje IR naudojo „iPhone“)
Atkreipkite dėmesį į Būlio operatorius. Jie yra prieinami klientams užklausos sąsajoje, kad galėtų atlikti savavališkai sudėtingas užklausas pavyzdžiams gauti.
Lėtas prašymas
Aptariamas klientas bandė padaryti tai, kas intuityviai turėtų veikti greitai:
Rodyti visus seanso įrašus naudotojams, kurie apsilankė bet kuriame puslapyje, kurio URL yra „/jobs“
Ši svetainė turėjo daug srauto ir mes saugojome daugiau nei milijoną unikalių URL adresų tik jai. Ir jie norėjo rasti gana paprastą URL šabloną, susijusį su jų verslo modeliu.
Preliminarus tyrimas
Pažiūrėkime, kas vyksta duomenų bazėje. Žemiau yra originali lėta SQL užklausa:
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 ;Ir čia yra laikai:
Planuojamas laikas: 1.480 ms Vykdymo laikas: 1431924.650 ms
Užklausa nuskaityta 150 tūkstančių eilučių. Užklausų planuotojas parodė keletą įdomių detalių, bet jokių akivaizdžių kliūčių.
Išnagrinėkime prašymą toliau. Kaip matote, jis tai daro JOIN trys lentelės:
- sesijos: rodyti seanso informaciją: naršyklę, vartotojo agentą, šalį ir pan.
- įrašymo_duomenys: įrašyti URL adresai, puslapiai, apsilankymų trukmė
- URL: Kad nesidubliuotų itin dideli URL, saugome juos atskiroje lentelėje.
Taip pat atkreipkite dėmesį, kad visos mūsų lentelės jau yra suskirstytos account_id. Tokiu būdu atmetama situacija, kai viena ypač didelė sąskaita sukelia problemų kitiems.
Ieško įkalčių
Atidžiau pažiūrėję matome, kad konkrečiame prašyme kažkas negerai. Verta atidžiau pažvelgti į šią eilutę:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]Pirma mintis buvo, kad galbūt todėl ILIKE visuose šiuose ilguose URL (turime daugiau nei 1,4 mln unikalus šiai paskyrai surinkti URL) našumas gali nukentėti.
Bet ne, tai ne esmė!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 rows)
Time: 5231.765 msPati šablono paieškos užklausa trunka tik 5 sekundes. Ieškoti šablono milijonuose unikalių URL adresų tikrai nėra problema.
Kitas įtariamasis sąraše yra keli JOIN. Galbūt per didelis jų naudojimas sukėlė sulėtėjimą? Paprastai JOIN's yra akivaizdžiausias našumo problemų kandidatas, bet netikėjau, kad mūsų atvejis yra tipiškas.
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 msIr tai taip pat nebuvo mūsų atvejis. JOINpasirodė gana greitas.
Įtariamųjų rato siaurinimas
Buvau pasirengęs pradėti keisti užklausą, kad patobulinčiau našumą. Su komanda sukūrėme 2 pagrindines idėjas:
- Papildomos užklausos URL naudokite EXISTS: Norėjome dar kartą patikrinti, ar kilo problemų dėl URL antrinės užklausos. Vienas iš būdų tai pasiekti yra tiesiog naudoti
EXISTS.EXISTSžymiai pagerinti našumą, nes baigiasi iškart, kai tik randa vienintelę sąlygą atitinkančią eilutę.
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 msNa taip. Papildoma užklausa, kai suvyniota EXISTS, padaro viską itin greitai. Kitas logiškas klausimas – kodėl prašymas su JOIN-ami ir pati antrinė užklausa yra greiti atskirai, bet kartu baisiai lėti?
- Antrinės užklausos perkėlimas į CTE : Jei užklausa pati savaime yra greita, galime tiesiog pirmiausia apskaičiuoti greitą rezultatą ir tada pateikti jį pagrindinei užklausai
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;Bet vis tiek buvo labai lėtas.
Kaltininko paieška
Visą tą laiką prieš akis blykstelėjo viena smulkmena, kurią nuolatos nubraukiau šalin. Bet kadangi daugiau nieko nebeliko, nusprendžiau pažiūrėti ir į ją. Aš kalbu apie && operatorius. Ate EXISTS tik pagerino našumą && buvo vienintelis bendras veiksnys visose lėtos užklausos versijose.
Žiūrėti į , mes tai matome && naudojamas, kai reikia rasti bendrus elementus tarp dviejų masyvų.
Pradiniame prašyme tai yra:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Tai reiškia, kad atliekame šablonų paiešką savo URL adresuose, tada randame sankirtą su visais URL, kuriuose yra bendrų įrašų. Tai šiek tiek painu, nes „urls“ čia reiškia ne lentelę, kurioje yra visi URL, o lentelės stulpelį „urls“. recording_data.
Didėjant įtarimams dėl &&, bandžiau rasti jiems patvirtinimą sugeneruotame užklausų plane EXPLAIN ANALYZE (Aš jau turėjau išsaugotą planą, bet paprastai man patogiau eksperimentuoti su SQL, nei bandyti suprasti užklausų planavimo priemonių neskaidrumą).
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: 52710Buvo keletas filtrų eilučių tik iš &&. O tai reiškė, kad ši operacija buvo ne tik brangi, bet ir atlikta kelis kartus.
Išbandžiau tai išskirdamas būklę
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[]Ši užklausa buvo lėta. Nes JOIN-s yra greitos, o antrinės užklausos yra greitos, liko tik && operatorius.
Tai tik pagrindinė operacija. Visada turime ieškoti visoje pagrindinėje URL lentelėje, kad galėtume ieškoti šablono, ir visada turime rasti sankryžas. Negalime ieškoti tiesiogiai pagal URL įrašus, nes tai tik ID, nukreipiantys į urls.
Pakeliui į sprendimą
&& lėtas, nes abu rinkiniai didžiuliai. Jei pakeisiu, operacija bus gana greita urls apie { "http://google.com/", "http://wingify.com/" }.
Pradėjau ieškoti būdo, kaip nustatyti sankryžą Postgrese nenaudojant &&, bet be didelio pasisekimo.
Galų gale nusprendėme tiesiog išspręsti problemą atskirai: duok man viską urls eilutės, kurių URL atitinka šabloną. Be papildomų sąlygų bus -
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%'Vietoj to JOIN sintaksė Aš ką tik panaudojau antrinę užklausą ir išplėčiau recording_data.urls masyvą, kad galėtumėte tiesiogiai pritaikyti sąlygą WHERE.
Svarbiausia čia yra tai && naudojamas patikrinti, ar nurodytame įraše yra atitinkantis URL. Jei šiek tiek prisimerksite, pamatysite, kad ši operacija juda per masyvo elementus (arba lentelės eilutes) ir sustoja, kai įvykdoma sąlyga (atitikimas). Ar tau nieko neprimena? taip, EXISTS.
Nuo tada recording_data.urls gali būti remiamasi ne antrinės užklausos kontekste, kai tai atsitiks, galime susigrąžinti savo seną draugą EXISTS ir apvyniokite ja antrinę užklausą.
Sudėjus viską, gauname galutinę optimizuotą užklausą:
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%'
);
Ir galutinis pristatymo laikas Time: 1898.717 ms Laikas švęsti?!?
Ne taip greitai! Pirmiausia turite patikrinti teisingumą. Buvau labai įtarus EXISTS optimizavimas, nes tai pakeičia logiką, kad būtų nutraukta anksčiau. Turime būti tikri, kad prie užklausos nepridėjome neakivaizdžios klaidos.
Paprastas testas buvo paleisti count(*) tiek lėtoms, tiek greitoms užklausoms, skirtoms daugybei skirtingų duomenų rinkinių. Tada nedideliame duomenų pogrupyje rankiniu būdu patikrinau, ar visi rezultatai teisingi.
Visi testai davė nuolat teigiamus rezultatus. Viską sutvarkėme!
Išmoktos pamokos
Iš šios istorijos galima pasimokyti daug pamokų:
- Užklausų planai nepasako visos istorijos, bet gali suteikti užuominų
- Pagrindiniai įtariamieji ne visada yra tikrieji kaltininkai
- Lėtas užklausas galima suskaidyti, kad būtų atskirtos kliūtys
- Ne visi optimizavimai yra redukcinio pobūdžio
- Naudoti
EXIST, jei įmanoma, gali smarkiai padidinti produktyvumą
Produkcija
Nuo ~24 minučių užklausos trukmės perėjome iki 2 sekundžių – gana reikšmingas našumo padidėjimas! Nors šis straipsnis pasirodė didelis, visi eksperimentai, kuriuos atlikome, įvyko per vieną dieną ir buvo apskaičiuota, kad optimizavimas ir testavimas užtruko nuo 1,5 iki 2 valandų.
SQL yra nuostabi kalba, jei jos nebijote, bet stengiatės ją išmokti ir naudoti. Gerai išmanydami, kaip vykdomos SQL užklausos, kaip duomenų bazė generuoja užklausų planus, kaip veikia indeksai, ir paprasčiausiai žinodami duomenų, su kuriais susiduriate, dydį, galite labai sėkmingai optimizuoti užklausas. Tačiau taip pat svarbu ir toliau bandyti įvairius būdus ir lėtai sugriauti problemą, ieškant kliūčių.
Geriausia dalis siekiant tokių rezultatų yra pastebimas, matomas greičio pagerėjimas – kai ataskaita, kuri anksčiau net nebuvo įkeliama, dabar įkeliama beveik akimirksniu.
Ypatingas ačiū mano bendražygiai vadovaujant Aditya Mishra, Aditya Gauru и už protų šturmą ir Dinkaras Pandiras už tai, kad paskutiniame prašyme radome svarbią klaidą, kol galiausiai su ja atsisveikinome!
Šaltinis: www.habr.com