Në dhjetor të vitit të kaluar, mora një raport interesante mbi një problem nga ekipi i mbështetjes VWO. Koha e ngarkesës për një nga raportet analitike për një klient të madh korporativ dukej jashtëzakonisht e gjatë. Dhe pasi kjo është në sferën e përgjegjësisë time, menjëherë u fokusova në zgjidhjen e problemit.
Historia e mëparshme
Për të bërë të qartë për çfarë bëhet fjalë, do të tregoj pak për VWO. Kjo është një platformë që lejon të lancohen fushata të ndryshme të targetuara në faqet e internetit: të kryhen eksperimente A/B, të ndjekin vizitorët dhe konvertimet, të bëjnë analizën e funnel-it të shitjeve, të tregojnë hartat termike dhe të shfaqin regjistrime të vizitave.
Por më e rëndësishmja në këtë platformë është përgatitja e raporteve. Të gjitha funksionet e përmendura janë të lidhuara me njëra-tjetrën. Dhe për klientët korporativë, një masë e madhe informacioni do të ishte thjesht e pavlera pa një platformë të fuqishme që e paraqet atë për analiza.
Duke përdorur platformën, mund të bëni një kërkesë të rastësishme mbi një set të madh të dhënash. Ja një shembull i thjeshtë:
Trego të gjitha klikimet në faqen "abc.com" NGA <data d1> DERi <data d2> për ata që përdorën Chrome OSE (ishin në Evropë DHE përdorën iPhone)
Vini re operatorët boolean. Ata janë të disponueshëm për klientët në ndërfaqen e kërkesës, për të bërë kërkesa sa më të komplikuara që të jenë të nevojshme për të marrë mostrën.
Kërkesë e ngadaltë
Klienti që po flasim për të përpiqej të bënte diçka që intuitivisht duhet të punonte shpejt:
Trego të gjitha regjistrimet e seancave për përdoruesit që vizituan çdo faqe me url që përmban "\/jobs"
Në këtë faqe kishte një sasi të madhe trafiku, dhe ne ruanim më shumë se një milion URL unike vetëm për të. Dhe ata donin të gjenin një model mjaft të thjeshtë URL-je që lidhej me modelin e tyre të biznesit.
Hetimi paraprak
Le të shohim se çfarë po ndodh në bazën e të dhënave. Më poshtë është SQL-i origjinal i ngadalshëm:
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 ;Ja koha e ngarkesës:
Koha e planifikuar: 1.480 ms Koha e ekzekutimit: 1431924.650 ms
Kërkesa kaloi 150 mijë rreshta. Planifikuesi i kërkesave tregoi disa detaje interesante, por asnjë ngushticë të dukshme.
Le të shqyrtojmë kërkesën më tej. Siç duket, ajo bën JOIN tre tabela:
- sessions: për të shfaqur informacionin sesional: shfletuesi, agjenti i përdoruesit, vendi dhe kështu me radhë.
- recording_data: URL-të e regjistruara, faqet, kohëzgjatja e vizitave
- urls: për të shmangur duplikimin e URL-ve jashtëzakonisht të gjata, ne i ruajmë ato në një tabelë të veçantë.
Gjithashtu, kushtojini vëmendje faktit që të gjitha tabelat tona janë ndarë tashmë account_id. Kështu, përjashtohet rasti kur për shkak të një llogarie të veçante të madhe, problemet ndodhin për të tjerët.
Në kërkim të provave
Kur e shqyrtojmë më nga afër, ne shohim se diçka në këtë kërkesë specifike nuk është në rregull. Duhet t'i kushtojmë vëmendje kësaj rreshti:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]Mendimi i parë ishte se ndoshta për shkak të ILIKE në të gjitha këto URL të gjata (ne kemi më shumë se 1.4 million URL të veçantë, të grumbulluara për këtë llogari) performanca mund të ndikohet negativisht. Por, jo — problemi nuk është aty!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 rreshta)Koha: 5231.765 ms
Kërkesa për kërkim me model merr vetëm 5 sekonda. Kërkimi me model mbi një milion URL të veçantë sigurisht që nuk është një problem.Tjetri në listën e dyshuar është disa
. Ndoshta përdorimi i tyre i tepruar ka shkaktuar ngadalësim? Zakonisht JOIN‘t janë kandidatët më të dukshëm për probleme me performancën, por unë nuk besova se rasti ynë ishte tipik. JOINanalytics_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 rresht)Koha: 147.851 ms
Dhe ky gjithashtu nuk ishte rasti ynë.‘t rezultuan të ishin mjaft të shpejtë. JOINDuke ngushtuar rrethin e dyshuar
Isha gati të filloja të ndryshoja kërkesën për të arritur çdo përmirësim të mundshëm të performancës. Ne me ekipin zhvilluam 2 ide kryesore:
Përdorimi i EXISTS për nënkërkesë të URL-ve
- : Donim të kontrollonim përsëri nëse ka ndonjë problem me nënkërkesën për URL-të. Një nga mënyrat për ta arritur këtë është thjesht ta përdorim: Ne do dëshironim të verifikonim përsëri nëse ka ndonjë problem me nënkërkesat për URL-të. Një nga mënyrat për ta arritur këtë është thjesht të përdorni
EKZISTON.EKZISTONpërmirëson ndjeshëm performancën, pasi ndalon menjëherë, sapo gjen rreshtin e vetëm sipas kushteve.
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 rresht)
Koha: 1636.637 msPo, po. Nënpyetje, kur është e mbështjellë në EKZISTON, bën gjithçka super të shpejtë. Pyetja tjetër logjike është, pse kërkesa me JOIN-at dhe nënpyetja vetë janë të shpejtë veç e veç, por ngadalësojnë tmerrësisht së bashku?
- Transferojmë nënpyetjen në CTE : nëse kërkesa është e shpejtë vetë, ne mund të llogarisim thjesht rezultatin e shpejtë fillimisht, dhe pastaj t'ia ofrojmë kërkesës kryesore
ME url të përputhura SI (
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;Por edhe kjo ishte akoma shumë e ngadaltë.
Gjejmë fajtorin
Gjithë këtë kohë një detaj i vogël më kishte rënë në sy, nga i cili unë vazhdimisht shihja për të ikur. Por, pasi nuk kishte më asgjë tjetër, vendosa ta shikoj edhe atë. Po flas për && operatorin. Ndërkohë EKZISTON thjesht përmirësoi performancën, && ishte faktori i vetëm i mbetur i përbashkët në të gjitha versionet e kërkesës së ngadalshme.
Duke parë , ne shohim se && përdoret, kur nevojitet të gjenden elementet e përbashkëta midis dy array.
Në kërkesën origjinale kjo është:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Çfarë do të thotë se ne bëjmë kërkimin me model mbi URL-të tona, pastaj gjejmë ndërprerjen me të gjitha URL-të me regjistrime të përbashkëta. Kjo është pak e ngatërruar, pasi 'urls' këtu nuk i referohet tabeles që përmban të gjitha URL-të, por kolonës 'urls' në tabelën recording_data.
Me rritjen e dyshimeve mbi &&, përpiqem t'i gjej ata një dëshmi në planin e kërkesës, të gjeneruar EXPLAIN ANALYZE (Unë kam pasur një plan të ruajtur, por zakonisht është më e lehtë për mua të eksperimentohem në SQL sesa të kuptoj paqartësitë e planifikuesve të pyetjeve).
Filtri: ((urls && ($0)::text[]) DHE (r_time > '2018-12-17 12:17:23+00'::timestamp me zonë kohore) DHE (r_time = '5'::double precision) DHE (num_of_pages > 0))
Rreshtat e hequr nga Filtri: 52710Ishte disa rreshta filtrash vetëm nga &&. Kjo do të thoshte se kjo operacion jo vetëm që ishte e shtrenjtë, por gjithashtu u ekzekutua disa herë.
E kontrollova këtë, duke izoluar kushtin
SELECT 1
FROM
acc_{account_id}.urls si recordings_urls,
acc_{account_id}.recording_data_30 si recording_data_30,
acc_{account_id}.sessions_30 si sessions_30
KU
urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]Kjo pyetje ishte duke u ekzekutuar ngadalë. Pavarësisht se JOIN-t janë të shpejtë dhe nënpyetjet janë të shpejta, mbeti vetëm && operatori.
Kjo është një operacion kyç. Ne gjithmonë duhet të kërkojmë në të gjithë tabelën kryesore të URL-ve për të kërkuar sipas modelit, dhe ne gjithmonë duhet të gjejmë ndërthurje. Ne nuk mund të kërkojmë drejtpërdrejt në regjistrimet e URLs, sepse këto janë thjesht identifikues që referohen në urls.
Në rrugën drejt zgjidhjes
&& e ngadaltë, sepse të dy grupe janë të mëdha. Operacioni do të jetë relativisht i shpejtë nëse zëvendësoj urls në { "http://google.com/", "http://wingify.com/" }.
Fillova të kërkoj një mënyrë për të bërë në Postgres ndërthurje setesh pa përdorimin &&, por pa sukses të veçantë.
Në fund, ne vendosëm thjesht ta zgjidhnim problemin në mënyrë të izoluar: më jep të gjitha urls rreshtat për të cilat url përputhet me modelin. Pa kushte të mëtejshme, kjo do të jetë -
SELECT urls.url
FROM
acc_{account_id}.urls si urls,
(SELECT unnest(recording_data.urls) SI id) SI unrolled_urls
KU
urls.id = unrolled_urls.id DHE
urls.url ILIKE '%jobs%'Në vend të JOIN me sintaksë unë thjesht përdor pyesjen nën dhe shpalosa recording_data.urls në një array, në mënyrë që të mund të aplikojmë drejtpërdrejt kushtin në KU.
E rëndësishme këtu është se && përdoret për të kontrolluar nëse ky regjistrim përmban URL-në përkatëse. Duke mbyllur pak sytë, mund të shihni në këtë operacion lëvizjen përmes elementëve të array-t (ose rreshtave të tabelës) dhe ndalimin në përfundimin e kushtit (përputhjes). A ka ndonjë gjë që ju kujton? Po, EKZISTON.
Pasi në recording_data.urls mund të referohet jashtë kontekstit të nënpyetjes, kur ndodh, ne mund të kthehemi te shoku ynë i vjetër EKZISTON dhe ta mb wrapped nënpyetjen.
Duke bashkuar gjithçka së bashku, ne marrim pyetjen e optimizuar përfundimtare:
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 =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%'
);
Dhe koha e fundit e ekzekutimit Koha: 1898.717 ms Është koha për të festuar?!?
Jo kaq shpejt! Së pari, duhet të kontrollojmë saktësinë. Kam qenë jashtëzakonisht i dyshimtë për EKZISTON optimizimin, pasi ai ndryshon logjikën në një përfundim më të hershëm. Duhet të jemi të sigurt se nuk kemi shtuar ndonjë gabim të paqartë në kërkesë.
Kontrolli i thjeshtë përfshinte ekzekutimin count(*) dhe në kërkesat e ngadalta dhe të shpejta për shumë grupe të ndryshme të të dhënave. Më pas, për një nëngrup të vogël të të dhënave, kontrollova saktësinë e të gjitha rezultateve me dorë.
Të gjitha kontrollimet dhanë rezultate pozitivisht të qëndrueshme. Ne e rregulluam gjithçka!
Mësimet e nxjerra
Nga kjo histori mund të nxjerrim shumë mësime:
- Planet e kërkesave nuk tregojnë gjithçka, por mund të japin sugjerime
- Kryesitë e dyshuara nuk janë gjithmonë fajtorët e vërtetë
- Kërkesat e ngadalta mund të ndahen për të izoluar ngushticat
- Nuk janë të gjitha optimizimet me natyrë reduktive
- Përdorimi
EXIST, ku është e mundur, mund të çojë në një rritje të madhe të performancës
Përfundimi
Kemi kaluar nga koha e kërkesës në ~24 minuta në 2 sekonda — një rritje shumë të konsiderueshme të performancës! Megjithëse ky artikull është i gjatë, të gjitha eksperimetet që kemi bërë ndodhi në një ditë, dhe sipas përllogaritjeve, morën rreth 1.5 deri në 2 orë për optimizim dhe testim.
SQL është një gjuhë e mrekullueshme, nëse nuk e frikësoni, por përpiqeni ta kuptoni dhe ta përdorni. Duke pasur një kuptim të mirë të mënyrës se si ekzekutohen kërkesat SQL, si gjeneron DB planet e kërkesave, si funksionojnë indekset dhe thjesht madhësia e të dhënave me të cilat po merremi, do të jeni në gjendje të përparoni shumë në optimizimin e kërkesave. Po aq e rëndësishme, megjithatë, është të vazhdoni të provoni qasje të ndryshme dhe ngadalë të ndani problemin, duke gjetur ngushticat.
Pjesa më e mirë e arritjes së rezultateve të tilla është përmirësimi i dukshëm i shpejtësisë — kur një raport që më parë nuk ngarkohej, tani ngarkohet pothuajse menjëherë.
Faleminderit të veçantë shokëve të mi në ekipin e Aditya Mishra, Aditya Gaur dhe për brainstormin dhe Dinkar Pandir për gjetjen e një gabimi të rëndësishëm në kërkesën tonë përfundimtare, para se të ndahemi me të përfundimisht!
Burimi: habr.com
