Historia e një hetimi SQL

Në dhjetor të vitit të kaluar mora një raport interesant për një gabim nga ekipi i mbështetjes VWO. Koha e ngarkesës për një nga raportet analitikë për një klient të madh korporativ dukeshin të tepërta. Dhe, dëshmimi për këtë është në fushën time të përgjegjësisë, prandaj menjëherë përqendrova në zgjidhjen e problemit.

Pas historia

Për ta bërë më të qartë për çfarë bëhet fjalë, do të flas pak për VWO. Kjo është një platformë që mundëson të shkaktoni fushata të ndryshme të targetuara në faqet tuaja: të realizoni eksperimente A/B, të ndjekni vizitorët dhe konversat, të analizoni funnel-in e shitjeve, të shfaqni hartat e nxehtësisë dhe të riprodhoni regjistrimet e vizitave.

Por, ajo që është më e rëndësishmja në këtë platformë është përgatitja e raporteve. Të gjitha funksionet e mësipërme janë të lidhura me njëra-tjetrën. Dhe për klientët korporativë, një masiv i madh informacioni do të ishte thjesht i padobishëm pa një platformë të fuqishme që e paraqet atë për analizë.

Duke përdorur platformën, mund të bëni kërkesa të rastësishme në një bazë të madhe të dhënash. Ja një shembull i thjeshtë:

Trego të gjitha klikimet në faqen "abc.com"
Nga  DERI në 
për njerëzit që
përdorën Chrome OSE
(që ishin në Evropë DHE përdorën iPhone)

Kujdesi për operatorët logjikë. Ata janë në dispozicion për klientët në ndërfaqen e kërkimit, për të bërë kërkesa të ndërlikuara për të marrë mostra.

Kërkesë e ngadaltë

Klienti në fjalë po përpiqej të bënte diçka që intuitivisht duhet të funksiononte shpejt:

Trego të gjithë regjistrat e sesioneve
për përdoruesit që vizituan çdo faqe
me url që përmban "/jobs"

Ky sit pati një sasi të madhe trafiku, dhe ne ruajmë më shumë se një milion URL unike vetëm për të. Dhe ata donin të gjenin një model relativisht të thjeshtë URL-je të lidhur me modelin e tyre të biznesit.

Hetimi paraprak

Le të shohim çfarë po ndodh në bazën e të dhënave. Më poshtë është kërkesa e ngadalshme SQL origjinale:

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 =5 
    AND recording_data.num_of_pages > 0 ;

Ja këtu janë koha:

Koha e planifikuar: 1.480 ms
Koha e ekzekutimit: 1431924.650 ms

Kërkesa kaloi 150,000 rreshta. Planifikuesi i kërkesave tregoi disa detaje interesante, por nuk kishte ndonjë pikë evidente ngushtimi.

Le të eksplorojmë kërkesën më tej. Siç duket, ajo bën JOIN tre tabela:

1. sessions: për të treguar informacionin mbi sesionin: shfletuesi, agjenti i përdoruesit, vendi dhe kështu me radhë.
2. recording_data: URL-të e regjistruara, faqet, kohëzgjatja e vizitave
3. urls: për të shmangur dyfishimin e URL-ve jashtëzakonisht të mëdha, ne i ruajmë ato në një tabelë të veçantë.

Gjithashtu vini re se të gjitha tabelat tona janë tashmë të ndara sipas account_id. Kështu, situata kur një llogari jashtëzakonisht e madhe shkakton probleme për të tjerët është përjashtuar.

Në kërkim të provave

Duke shqyrtimit të afërt, ne shohim se diçka në kërkesën specifike nuk është në rregull. Është e nevojshme të shikoni këtë rresht:

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 milion unikale URL adresash të mbledhura për këtë llogari) performanca mund të ishte e dobët.

Por, jo — nuk është kjo!

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

Koha: 5231.765 ms

E gjithë kërkesa për kërkimin me model merr vetëm 5 sekonda. Kërkimi me model në një milion URL unike nuk është qartë një problem.

Të dyshuarit e radhës në listë — disa JOIN. Ndoshta përdorimi i tyre të tepërt ka çuar në ngadalësim? Zakonisht JOIN‘t janë kandidatët më të obvious për problemet me performancën, por nuk e besoja se rasti ynë ishte tipik.

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

Koha: 147.851 ms

Dhe kjo gjithashtu nuk ishte rasti ynë. JOINIshim mjaft të shpejtë.

Ngushtojmë rrethin e të dyshuarve

Isha gati të filloja të ndryshoja kërkesën për të arritur çdo përmirësim të mundshëm të performancës. Ne si ekip zhvilluam dy ide kryesore:

• Të përdorim EXISTS për nënkërkesën e URL-ve: Donim ta kontrollonim përsëri nëse kishte ndonjë problem me nënkërkesën për URL-të. Një nga mënyrat për ta arritur këtë është thjesht të përdorim EXISTS. EXISTS mund të përmirësojmë ndjeshëm performancën pasi përfundon menjëherë sapo gjen një rresht të 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 row)
Time: 1636.637 ms

Po, po. Nënkërkesa, kur është e mbështjellë në EXISTS, e bën gjithçka super të shpejtë. Pyetja logjike tjetër është pse kërkesa me JOIN-at dhe vetë nënkërkesa janë të shpejta veç e veç, por ngadalësohen shumë së bashku?

• Po e transferojmë nënkërkesën në CTE : nëse kërkesa është e shpejtë vetë, ne mund thjesht fillimisht të kalkulojmë një rezultat të shpejtë dhe pastaj t'ia ofrojmë kërkesës kryesore

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;

Por kjo ende ishte shumë e ngadalshme.

Gjejmë fajtorin

Gjatë gjithë kësaj kohe, një detaj i vogël që unë vazhdimisht e injoroja shfaqej para syve. Por meqenëse nuk kishte mbetur asgjë tjetër, vendosa ta shikoj atë. Po flas për && operatorin. Deri tani EXISTS thjesht përmirësova performancën, && ishte faktori i vetëm i mbetur i përbashkët në të gjitha versionet e kërkesës së ngadaltë.

Duke parë në dokumentacioni, ne shohim se && përdoret kur nevojitet të gjejmë elementet e përbashkëta midis dy vendeve.

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

Kjo do të thotë që ne bëjmë një kërkim sipas modelit në URL-të tona dhe pastaj gjejmë përputhjet me të gjitha URL-të me regjistrime të ngjashme. Kjo është paksa e komplikuar, sepse "urls" këtu nuk i referohet një tabelë që përmban të gjitha URL-të, por një kolone "urls" në tabelë. recording_data.

Me rritjen e dyshimeve në lidhje me &&, përpiqesha të gjeja një konfirmim në planin e pyetjes, të krijuar EXPLAIN ANALYZE (unë tashmë kisha një plan të ruajtur, por zakonisht më pëlqen të eksperimentoj në SQL sesa të përpiqem të kuptoj paqartësitë e planifikuesve të pyetjes).

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 hequra nga Filtro: 52710

Ishin disa rreshta filtrash vetëm nga &&. Kjo do të thoshte që 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 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

Kjo pyetje u ekzekutua ngadalë. Pasi JOIN-at 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 gjithmonë duhet të gjejmë ndërthurje. Nuk mund të kërkojmë direkt në regjistrimet e URL-ve, sepse ato janë vetëm identifikues që referohen në urls.

Në rrugën drejt zgjidhjes

&& i ngadaltë, sepse të dyja grumbujt janë të mëdhenj. Operacioni do të jetë relativisht i shpejtë nëse unë 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 të grumbujve pa përdorur &&, por pa shumë sukses.

Në fund, ne vendosëm thjesht ta zgjidhim problemin në izolim: më jep të gjitha urls rreshtat për të cilat URL-ja i përputhet modelit. Pa kushte shtesë, do të jetë - 

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

Në vend të JOIN sintaksën e përdora thjesht një nënkat disa dhe shpalosa recording_data.urls në një array, për të aplikuar kushte direkt në WHERE.

E rëndësishme këtu është që && përdoret për të verifikuar nëse ky rekord përmban një URL përkatëse. Duke e shtyrë pak, mund ta shihni që kjo operacion paraqet lëvizjen përmes elementeve të array (ose rreshtave të tabelës) dhe ndalet kur plotësohet kushti (përputhja). A ju duket e njohur? Po, EXISTS.

Duke qenë se në recording_data.urls mund të referohet jashtë kontekstit të nënshtresës, kur ndodh kjo, mund të kthehemi te shoku ynë i vjetër EXISTS dhe ta mbështjellim me të nënshtresën.

Duke e bashkuar gjithçka, marrim kërkesën përfundimtare të optimizuar:

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 përfundimtare e ekzekutimit Koha: 1898.717 ms Është koha për të festuar?!?

Jo kaq shpejt! Së pari, duhet të kontrollojmë saktësinë. Kam qenë shumë dyshimtar për EXISTS optimizimi, pasi ajo ndryshon logjikën për një përfundim më të hershëm. Duhet të jemi të sigurt se nuk kemi shtuar një gabim të padukshëm në kërkesë.

Kontrolli i thjeshtë përfshinte ekzekutimin e count(*) në kërkesat e ngadalta dhe të shpejta për një sasi të madhe të grupeve të ndryshme të të dhënave. Pastaj, për një nënset të vogël të të dhënave, kontrollova saktësinë e të gjitha rezultateve manualisht.

Të gjitha kontrollimet dhanë rezultate pozitivisht konstante. Kemi rregulluar gjithçka!

Mësimet e Nxjerra

Nga kjo histori mund të nxirren mjaft mësime:

1. Planet e kërkesave nuk tregojnë tërë historinë, por mund të japin pista.
2. Të dyshuarit kryesorë nuk janë gjithmonë fajtorët e vërtetë.
3. Kërkesat e ngadalta mund të ndahen për të izoluar pengesat.
4. Jo të gjitha optimizimet janë natyrshëm reduktive.
5. Përdorimi EXIST, kur është e mundur, mund të çojë në një rritje të konsiderueshme të performancës.

Përfundim

Kemi kaluar nga koha e kërkesës në ~24 minuta në 2 sekonda — një rritje mjaft serioze e performancës! Edhe pse ky artikull ishte i gjatë, të gjitha eksperimentet që kemi kryer ndodhën në një ditë dhe përllogaritjet tregojnë se zgjatën nga 1.5 deri në 2 orë për optimizimet dhe testimin.

SQL — një gjuhë e mrekullueshme nëse nuk e frikësosh, por përpiqesh ta kuptosh dhe ta përdorësh. Duke pasur një kuptim të mirë se si ekzekutohen kërkesat SQL, si gjenerojnë DB planet e kërkesave, si funksionojnë indeksat dhe thjesht duke njohur madhësinë e të dhënave me të cilat po merresh, do të jesh shumë i suksesshëm në optimizimin e kërkesave. E rëndësishme është gjithashtu të vazhdosh të provosh qasje të ndryshme dhe ngadalë të shkëputësh problemin, duke gjetur piketat e ngushta.

Pjesa më e mirë e arritjes së rezultateve të tilla është përmirësimi i dukshëm i shpejtësisë së funksionimit — kur një raport që më parë nuk ngarkohej, tani ngarkohet pothuajse menjëherë.

Falënderim të veçantë miqve të mi në ekipin Aditya Mishra, Aditya Gaur dhe Varun Malhotra për mendimet dhe Dinkar Pandir për gjetjen e një gabimi të rëndësishëm në kërkesën tonë përfundimtare, para se ne të ndaheshim me të!

Burimi: habr.com