Desemba iliyopita nilipokea ripoti ya kuvutia ya hitilafu kutoka kwa timu ya usaidizi ya VWO. Muda wa kupakia kwa mojawapo ya ripoti za uchanganuzi kwa mteja mkubwa wa kampuni ulionekana kuwa mbaya. Na kwa kuwa hili ni eneo langu la uwajibikaji, mara moja nililenga kutatua tatizo.
kabla ya historia
Ili kuweka wazi kile ninachozungumzia, nitakuambia kidogo kuhusu VWO. Hili ni jukwaa ambalo unaweza kutumia kuzindua kampeni mbalimbali zinazolengwa kwenye tovuti zako: kufanya majaribio ya A/B, kufuatilia wageni na walioshawishika, kuchanganua fani ya mauzo, kuonyesha ramani za joto na kucheza rekodi za kutembelea.
Lakini jambo muhimu zaidi kuhusu jukwaa ni kuripoti. Kazi zote hapo juu zimeunganishwa. Na kwa wateja wa kampuni, kiasi kikubwa cha habari hakitakuwa na maana bila jukwaa thabiti ambalo linawasilisha katika fomu ya uchanganuzi.
Kwa kutumia jukwaa, unaweza kuuliza bila mpangilio kwenye seti kubwa ya data. Hapa kuna mfano rahisi:
Onyesha mibofyo yote kwenye ukurasa wa "abc.com" KUANZIA <date d1> HADI <date d2> kwa watu waliotumia Chrome AU (walio Ulaya NA wanaotumia iPhone)
Makini na waendeshaji wa Boolean. Zinapatikana kwa wateja katika kiolesura cha hoja ili kuuliza maswali magumu kiholela ili kupata sampuli.
Ombi la polepole
Mteja anayehusika alikuwa akijaribu kufanya kitu ambacho kinapaswa kufanya kazi haraka:
Onyesha rekodi zote za kipindi kwa watumiaji waliotembelea ukurasa wowote wenye URL iliyo na "/kazi"
Tovuti hii ilikuwa na trafiki nyingi na tulikuwa tukihifadhi URL za kipekee zaidi ya milioni moja kwa ajili yake. Na walitaka kupata kiolezo rahisi cha URL ambacho kinahusiana na mtindo wao wa biashara.
Uchunguzi wa awali
Wacha tuangalie kile kinachoendelea kwenye hifadhidata. Hapo chini kuna hoja ya polepole ya 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 ;Na hapa kuna nyakati:
Wakati uliopangwa: 1.480 ms Muda wa utekelezaji: 1431924.650 ms
Swali lilitambaa safu 150 elfu. Mpangaji wa hoja alionyesha maelezo kadhaa ya kuvutia, lakini hakuna vikwazo dhahiri.
Hebu tujifunze ombi zaidi. Kama unavyoona, anafanya JOIN meza tatu:
- vikao vya: kuonyesha maelezo ya kipindi: kivinjari, wakala wa mtumiaji, nchi, na kadhalika.
- kurekodi_data: URL zilizorekodiwa, kurasa, muda wa kutembelewa
- urls: Ili kuepuka kunakili URL kubwa sana, tunazihifadhi katika jedwali tofauti.
Pia kumbuka kuwa meza zetu zote tayari zimegawanywa na account_id. Kwa njia hii, hali ambapo akaunti moja kubwa hasa husababisha matatizo kwa wengine imetengwa.
Kutafuta dalili
Tunapochunguza kwa makini, tunaona kwamba kuna kitu kibaya na ombi fulani. Inastahili kuangalia kwa karibu mstari huu:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]Wazo la kwanza lilikuwa kwamba labda kwa sababu ILIKE kwenye URL hizi zote ndefu (tuna zaidi ya milioni 1,4 kipekee Utendaji wa URL zilizokusanywa kwa ajili ya akaunti hii) huenda ukaathiriwa.
Lakini hapana, hiyo sio maana!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 rows)
Time: 5231.765 msOmbi la utafutaji wa kiolezo yenyewe huchukua sekunde 5 pekee. Kutafuta mchoro katika URL za kipekee milioni moja sio tatizo.
Mshukiwa anayefuata kwenye orodha ni kadhaa JOIN. Labda matumizi yao kupita kiasi yamesababisha kupungua? Kwa kawaida JOIN's ndio wagombeaji dhahiri zaidi wa shida za utendakazi, lakini sikuamini kuwa kesi yetu ilikuwa ya kawaida.
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 msNa hii pia haikuwa kesi yetu. JOINiligeuka kuwa haraka sana.
Kupunguza mduara wa watuhumiwa
Nilikuwa tayari kuanza kubadilisha hoja ili kufikia uboreshaji wowote wa utendakazi. Timu yangu na mimi tulitengeneza mawazo makuu 2:
- Tumia EXISTS kwa URL ya hoja ndogo: Tulitaka kuangalia tena ikiwa kulikuwa na matatizo yoyote na hoja ndogo ya URLs. Njia moja ya kufikia hili ni kutumia tu
EXISTS.EXISTSkuboresha sana utendakazi kwani inaisha mara tu inapopata kamba pekee inayolingana na hali hiyo.
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 msNaam, ndiyo. Maswali madogo yanapofungwa EXISTS, hufanya kila kitu haraka sana. Swali linalofuata la kimantiki ni kwa nini ombi na JOIN-ami na subquery yenyewe ni haraka mmoja mmoja, lakini ni polepole sana pamoja?
- Kuhamisha hoja ndogo hadi CTE : Ikiwa swali ni la haraka kivyake, tunaweza kuhesabu matokeo ya haraka kwanza kisha tutoe kwa swali kuu.
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;Lakini bado ilikuwa polepole sana.
Kutafuta mhalifu
Wakati huu wote, kitu kidogo kiliangaza mbele ya macho yangu, ambayo mara kwa mara niliiweka kando. Lakini kwa kuwa hakuna kitu kingine kilichosalia, niliamua kumtazama pia. Ninazungumzia && mwendeshaji. Kwaheri EXISTS tu kuboresha utendaji && ilikuwa sababu pekee iliyosalia ya kawaida katika matoleo yote ya hoja ya polepole.
Kuangalia , tunaona hivyo && kutumika wakati unahitaji kupata vipengele vya kawaida kati ya safu mbili.
Katika ombi la asili hii ni:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Inayomaanisha kuwa tunatafuta muundo kwenye URL zetu, kisha kupata makutano na URL zote zilizo na machapisho ya kawaida. Hii inachanganya kidogo kwa sababu "urls" hapa hairejelei jedwali lililo na URL zote, lakini safu wima ya "urls" kwenye jedwali. recording_data.
Pamoja na kuongezeka kwa tuhuma kuhusu &&, nilijaribu kupata uthibitisho kwao katika mpango wa hoja uliotolewa EXPLAIN ANALYZE (Tayari nilikuwa na mpango uliohifadhiwa, lakini kawaida niko vizuri zaidi kujaribu katika SQL kuliko kujaribu kuelewa uwazi wa wapangaji wa hoja).
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: 52710Kulikuwa na mistari kadhaa ya vichungi kutoka tu &&. Ambayo ilimaanisha kuwa operesheni hii haikuwa ghali tu, lakini pia ilifanyika mara kadhaa.
Nilijaribu hii kwa kutenganisha hali hiyo
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[]Swali hili lilikuwa polepole. Kwa sababu ya JOIN-s ni haraka na subqueries ni haraka, kitu pekee kilichosalia kilikuwa && mwendeshaji.
Hii ni operesheni muhimu tu. Daima tunahitaji kutafuta jedwali lote la msingi la URL ili kutafuta mchoro, na tunahitaji kupata makutano kila wakati. Hatuwezi kutafuta kwa rekodi za URL moja kwa moja, kwa sababu hivi ni vitambulisho vinavyorejelea urls.
Njiani kuelekea suluhisho
&& polepole kwa sababu seti zote mbili ni kubwa. Operesheni itakuwa ya haraka ikiwa nitabadilisha urls juu ya { "http://google.com/", "http://wingify.com/" }.
Nilianza kutafuta njia ya kufanya makutano ya kuweka kwenye Postgres bila kutumia &&, lakini bila mafanikio mengi.
Mwishoni, tuliamua tu kutatua tatizo kwa kutengwa: nipe kila kitu urls mistari ambayo URL inalingana na muundo. Bila masharti ya ziada itakuwa -
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%'Badala ya JOIN syntax nimetumia tu subquery na kupanua recording_data.urls safu ili uweze kutumia hali moja kwa moja ndani WHERE.
Jambo kuu hapa ni kwamba && hutumika kuangalia kama ingizo fulani lina URL inayolingana. Ukikodolea macho kidogo, unaweza kuona operesheni hii ikipitia vipengele vya safu (au safu za jedwali) na husimama wakati hali (mechi) inapofikiwa. Je, hukukumbusha chochote? Ndiyo, EXISTS.
Tangu kuendelea recording_data.urls inaweza kurejelewa kutoka nje ya muktadha mdogo, hii inapotokea tunaweza kurudi kwa rafiki yetu wa zamani EXISTS na ufunge subquery nayo.
Kuweka kila kitu pamoja, tunapata swali la mwisho lililoboreshwa:
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%'
);
Na wakati wa mwisho wa kuongoza Time: 1898.717 ms Wakati wa kusherehekea?!
Sio haraka sana! Kwanza unahitaji kuangalia usahihi. Nilikuwa na shaka sana EXISTS optimization inapobadilisha mantiki kukomesha mapema. Tunahitaji kuwa na uhakika kwamba hatujaongeza hitilafu isiyo dhahiri kwenye ombi.
Jaribio rahisi lilikuwa kukimbia count(*) kwa maswali ya polepole na ya haraka kwa idadi kubwa ya seti tofauti za data. Kisha, kwa sehemu ndogo ya data, nilithibitisha mwenyewe kuwa matokeo yote yalikuwa sahihi.
Vipimo vyote vilitoa matokeo chanya mara kwa mara. Tumerekebisha kila kitu!
Mafunzo Yanayopatikana
Kuna mambo mengi ya kujifunza kutoka kwa hadithi hii:
- Mipango ya hoja haisemi hadithi nzima, lakini inaweza kutoa vidokezo
- Washukiwa wakuu sio wahalifu wa kweli kila wakati
- Maswali ya polepole yanaweza kugawanywa ili kutenganisha vikwazo
- Sio uboreshaji wote unapunguza asili
- Matumizi ya
EXIST, inapowezekana, inaweza kusababisha ongezeko kubwa la tija
Pato
Tulitoka kwa muda wa hoja wa ~ dakika 24 hadi sekunde 2 - ongezeko kubwa la utendaji! Ingawa nakala hii ilitoka kwa ukubwa, majaribio yote tuliyofanya yalifanyika kwa siku moja, na ilikadiriwa kuwa yalichukua kati ya saa 1,5 na 2 kwa uboreshaji na majaribio.
SQL ni lugha nzuri sana ikiwa huiogopi, lakini jaribu kujifunza na kuitumia. Kwa kuwa na uelewa mzuri wa jinsi maswali ya SQL yanatekelezwa, jinsi hifadhidata huzalisha mipango ya hoja, jinsi faharisi zinavyofanya kazi, na saizi tu ya data unayoshughulikia, unaweza kufanikiwa sana katika kuboresha maswali. Ni muhimu vile vile, hata hivyo, kuendelea kujaribu mbinu tofauti na polepole kuvunja tatizo, kutafuta vikwazo.
Sehemu bora zaidi kuhusu kupata matokeo kama haya ni uboreshaji unaoonekana, unaoonekana wa kasi - ambapo ripoti ambayo hapo awali haikuweza kupakiwa sasa inapakia karibu papo hapo.
Shukrani za pekee kwa wenzangu kwa amri ya Aditya Mishra, Aditya Gauru ΠΈ kwa bongo na Dinkar Pandir kwa kupata hitilafu muhimu katika ombi letu la mwisho kabla hatujaagana nalo!
Chanzo: mapenzi.com