🥇Ang istorya sa usa ka imbestigasyon sa SQL

Kaniadtong Disyembre nakadawat ako usa ka makapaikag nga taho sa bug gikan sa VWO support team. Ang oras sa pagkarga alang sa usa sa mga taho sa analytics alang sa usa ka dako nga kliyente sa negosyo ingon og gidili. Ug tungod kay kini ang akong lugar nga responsibilidad, gipunting dayon nako ang pagsulbad sa problema.

sa naunang kasaysayan

Aron maklaro kung unsa ang akong gihisgutan, isulti ko kanimo ang gamay bahin sa VWO. Kini usa ka plataporma diin mahimo nimong ilunsad ang lainlaing gipunting nga mga kampanya sa imong mga website: pagpahigayon sa mga eksperimento sa A/B, pagsubay sa mga bisita ug mga pagkakabig, pag-analisar sa funnel sa pagpamaligya, pagpakita sa mga mapa sa init ug pagdula mga rekording sa pagbisita.

Apan ang labing hinungdanon nga butang bahin sa plataporma mao ang pagreport. Ang tanan nga mga gimbuhaton sa ibabaw magkadugtong. Ug alang sa mga kliyente sa korporasyon, ang daghang kasayuran mahimong wala’y kapuslanan kung wala ang usa ka kusgan nga plataporma nga nagpresentar niini sa porma sa analytics.

Gamit ang plataporma, makahimo ka og random nga pangutana sa usa ka dako nga set sa datos. Ania ang usa ka yano nga pananglitan:

Ipakita ang tanang pag-klik sa panid "abc.com" GIKAN sa <petsa d1> HANGTOD <petsa d2> para sa mga tawo nga migamit sa Chrome O (naa sa Europe UG migamit ug iPhone)

Hatagi'g pagtagad ang mga operator sa Boolean. Anaa sila sa mga kliyente sa interface sa pangutana aron makahimo og arbitraryong komplikado nga mga pangutana aron makakuha mga sample.

Hinay nga hangyo

Ang kliyente nga gipangutana naningkamot sa pagbuhat sa usa ka butang nga intuitively kinahanglan nga molihok dayon:

Ipakita ang tanan nga mga rekord sa sesyon para sa mga tiggamit nga mibisita sa bisan unsang panid nga adunay URL nga adunay "/jobs"

Kini nga site adunay usa ka tonelada nga trapiko ug kami nagtipig sa usa ka milyon nga talagsaon nga mga URL para lang niini. Ug gusto nila nga makapangita usa ka yano nga template sa URL nga adunay kalabotan sa ilang modelo sa negosyo.

Preliminary nga imbestigasyon

Atong tan-awon kung unsa ang nahitabo sa database. Sa ubos mao ang orihinal nga hinay nga pangutana sa SQL:

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 ;

Ug ania ang mga timing:

Giplano nga oras: 1.480 ms Oras sa pagpatuman: 1431924.650 ms

Ang pangutana mikamang sa 150 ka libo nga mga laray. Gipakita sa tagplano sa pangutana ang pipila ka makapaikag nga mga detalye, apan wala’y klaro nga mga bottleneck.

Atong tun-an pa ang hangyo. Sama sa imong makita, iyang gibuhat JOIN tulo ka lamesa:

sesyon: aron ipakita ang impormasyon sa sesyon: browser, user agent, nasud, ug uban pa.
recording_data: natala nga mga URL, mga panid, gidugayon sa mga pagbisita
urls: Aron malikayan ang pagdoble sa hilabihan ka dako nga mga URL, among gitipigan sila sa usa ka bulag nga lamesa.

Timan-i usab nga ang tanan namong mga lamesa gibahin na sa account_id. Niining paagiha, ang usa ka kahimtang diin ang usa ka labi ka dako nga account hinungdan sa mga problema sa uban wala iapil.

Nangita ug mga timailhan

Sa mas duol nga pagsusi, atong makita nga adunay usa ka butang nga sayup sa usa ka partikular nga hangyo. Angayan nga tan-awon pag-ayo kini nga linya:

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

Ang una nga gihunahuna mao nga tingali tungod ILIKE sa tanan niining taas nga mga URL (adunay kami sobra sa 1,4 milyon talagsaon Ang mga URL nga nakolekta alang niini nga account) mahimong mag-antus.

Apan dili, dili kana ang punto!

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

Time: 5231.765 ms

Ang hangyo sa pagpangita sa template mismo mokabat lang ug 5 segundos. Ang pagpangita alang sa usa ka sumbanan sa usa ka milyon nga talagsaon nga mga URL klaro nga dili usa ka problema.

Ang sunod nga suspek sa listahan daghan JOIN. Tingali ang ilang sobrang paggamit maoy hinungdan sa paghinay? Kasagaran JOINAng 's mao ang labing klaro nga mga kandidato alang sa mga problema sa pasundayag, apan wala ako motuo nga ang among kaso kasagaran.

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

Ug dili usab kini ang among kaso. JOIN's nahimo nga medyo paspas.

Pagkunhod sa lingin sa mga suspek

Andam na ko sa pagsugod sa pag-usab sa pangutana aron makab-ot ang bisan unsang posibleng pag-uswag sa performance. Ang akong team ug ako nakaugmad ug 2 ka pangunang ideya:

Gamita ang EXISTS para sa subquery URL: Gusto namong susihon pag-usab kon aduna bay mga problema sa subquery alang sa mga URL. Usa ka paagi aron makab-ot kini mao ang yano nga paggamit EXISTS. EXISTS mahimo pag-ayo pag-ayo sa performance tungod kay kini matapos diha-diha dayon sa diha nga kini makit-an ang bugtong string nga mohaum sa kondisyon.

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

Aw, oo. Subquery kung giputos EXISTS, naghimo sa tanan nga labing paspas. Ang sunod nga lohikal nga pangutana mao ngano nga ang hangyo sa JOIN-ami ug ang subquery mismo paspas nga tagsa-tagsa, apan hinay kaayo nga magkauban?

Pagbalhin sa subquery ngadto sa CTE : Kung ang pangutana paspas sa kaugalingon, mahimo ra naton kalkulahon una ang paspas nga resulta ug dayon ihatag kini sa panguna nga pangutana

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;

Pero hinay gihapon kaayo.

Pagpangita sa sad-an

Niining panahona, usa ka gamay nga butang ang mikidlap atubangan sa akong mga mata, nga kanunay nakong gisalikway. Pero kay wala nay lain, nakahukom ko nga tan-awon sab siya. Naghisgot ko && operator. Bye EXISTS nag improve nga performance lang && mao na lang ang nahabilin nga komon nga hinungdan sa tanang bersyon sa hinay nga pangutana.

Nagtan-aw sa dokumentasyon, makita nato kana && gigamit kung kinahanglan nimo pangitaon ang sagad nga mga elemento taliwala sa duha nga mga arrays.

Sa orihinal nga hangyo kini mao ang:

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

Nga nagpasabut nga naghimo kami usa ka pattern nga pagpangita sa among mga URL, dayon pangitaa ang intersection sa tanan nga mga URL nga adunay sagad nga mga post. Kini usa ka gamay nga makalibog tungod kay ang "mga url" dinhi wala magtumong sa lamesa nga adunay tanan nga mga URL, apan sa "mga url" nga kolum sa lamesa recording_data.

Uban sa nagkadako nga mga pagduda bahin sa &&, gisulayan nako pagpangita ang kumpirmasyon alang kanila sa plano sa pangutana nga nahimo EXPLAIN ANALYZE (Naa na koy plano nga natipig, apan kasagaran mas komportable ako nga mag-eksperimento sa SQL kaysa pagsulay nga masabtan ang opacity sa mga tigplano sa pangutana).

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

Adunay pipila ka linya sa mga filter gikan lamang sa &&. Nga nagpasabut nga kini nga operasyon dili lamang mahal, apan gihimo usab sa daghang mga higayon.

Gisulayan nako kini pinaagi sa pag-isolate sa kondisyon

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

Kini nga pangutana hinay. Tungod kay ang JOIN-s mga paspas ug ang mga subquery paspas, ang nahabilin mao ra && operator.

Kini usa lamang ka yawe nga operasyon. Kinahanglan namon nga pangitaon kanunay ang tibuuk nga talaan sa mga URL aron makapangita usa ka sumbanan, ug kinahanglan namon kanunay nga mangita mga interseksyon. Dili kami direktang makapangita pinaagi sa mga rekord sa URL, tungod kay kini mga ID lang nga gipasabot urls.

Sa dalan sa usa ka solusyon

&& hinay kay ang duha ka set dako. Ang operasyon medyo dali kung ako mag-ilis urls sa { "http://google.com/", "http://wingify.com/" }.

Nagsugod ko sa pagpangita og paagi sa paghimo og set intersection sa Postgres nga walay gamit &&, apan walay daghang kalampusan.

Sa katapusan, nakahukom kami nga sulbaron lang ang problema nga nag-inusara: ihatag kanako ang tanan urls mga linya diin ang URL mohaum sa sumbanan. Kung wala’y dugang nga mga kondisyon kini mahimong -

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

Hinuon JOIN syntax Gigamit ra nako ang usa ka subquery ug gipalapdan recording_data.urls array aron direkta nimo nga magamit ang kondisyon sa WHERE.

Ang labing importante nga butang dinhi mao kana && gigamit aron susihon kung ang gihatag nga entry adunay sulud nga katumbas nga URL. Kung magsige ka ug gamay, imong makita nga kini nga operasyon naglihok sa mga elemento sa usa ka laray (o mga laray sa usa ka lamesa) ug mohunong kung ang usa ka kondisyon (match) nahimamat. Wala ka magpahinumdom sa bisan unsa? oo, EXISTS.

Sukad sa recording_data.urls mahimong reperensiya gikan sa gawas sa subquery konteksto, sa diha nga kini mahitabo kita mahibalik sa atong daan nga higala EXISTS ug iputos ang subquery niini.

Paghiusa sa tanan, makuha namon ang katapusang na-optimize nga pangutana:

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

Ug ang katapusan nga lead time Time: 1898.717 ms Oras na para magcelebrate?!?

Dili kaayo paspas! Una kinahanglan nimo nga susihon ang pagkahusto. Nagduda kaayo ko EXISTS pag-optimize tungod kay giusab niini ang lohika aron makompleto sa sayo pa. Kinahanglan namon nga masiguro nga wala kami nakadugang usa ka dili klaro nga sayup sa hangyo.

Usa ka yano nga pagsulay mao ang pagdagan count(*) sa hinay ug paspas nga mga pangutana alang sa daghang lainlaing mga set sa datos. Dayon, alang sa gamay nga subset sa datos, akong gimatuod sa kamut nga ang tanang resulta husto.

Ang tanan nga mga pagsulay naghatag kanunay nga positibo nga mga resulta. Giayo namo ang tanan!

Mga Leksyon nga Nakat-onan

Adunay daghang mga leksyon nga makat-unan gikan niini nga istorya:

Ang mga plano sa pangutana wala magsulti sa tibuok nga istorya, apan makahatag kini og mga timailhan
Ang mga nag-unang suspek dili kanunay ang tinuod nga mga sad-an
Ang hinay nga mga pangutana mahimong mabungkag aron mahimulag ang mga bottleneck
Dili tanan nga mga pag-optimize kay reductive sa kinaiyahan
Paggamit EXIST, kon mahimo, mahimong mosangpot sa talagsaong pag-uswag sa produktibidad

konklusyon

Miadto kami gikan sa oras sa pagpangutana nga ~ 24 minuto hangtod sa 2 segundos - usa ka hinungdanon nga pagtaas sa pasundayag! Bisan tuod kini nga artikulo migawas nga dako, ang tanan nga mga eksperimento nga among gihimo nahitabo sa usa ka adlaw, ug kini gibanabana nga sila mikuha sa taliwala sa 1,5 ug 2 ka oras alang sa pag-optimize ug pagsulay.

Ang SQL usa ka talagsaon nga pinulongan kung dili ka mahadlok niini, apan sulayi nga makat-on ug gamiton kini. Pinaagi sa pagbaton ug maayong pagsabot kon giunsa pagpatuman ang mga pangutana sa SQL, giunsa paghimo sa database ang mga plano sa pangutana, giunsa pagtrabaho ang mga indeks, ug sa yanong gidak-on sa datos nga imong giatubang, mahimo kang magmalampuson kaayo sa pag-optimize sa mga pangutana. Parehas nga hinungdanon, bisan pa, nga magpadayon sa pagsulay sa lainlaing mga pamaagi ug hinayhinay nga bungkagon ang problema, pagpangita sa mga bottleneck.

Ang labing kaayo nga bahin bahin sa pagkab-ot sa mga resulta nga sama niini mao ang mamatikdan, makita nga pag-uswag sa katulin - diin ang usa ka taho nga kaniadto dili gani makarga karon hapit dayon.

Espesyal nga salamat sa akong mga kauban sa mando ni Aditya Mishra, Aditya Gauru и Varun Malhotra para sa brainstorming ug Dinkar Pandir alang sa pagpangita sa usa ka importante nga sayop sa atong katapusan nga hangyo sa wala pa kita sa katapusan nanamilit niini!

Source: www.habr.com

Ang istorya sa usa ka imbestigasyon sa SQL