એક SQL તપાસની વાર્તા

ગયા ડિસેમ્બરમાં મને VWO સપોર્ટ ટીમ તરફથી એક રસપ્રદ બગ રિપોર્ટ મળ્યો હતો. મોટા કોર્પોરેટ ક્લાયન્ટ માટે એનાલિટિક્સ રિપોર્ટ્સમાંથી એક માટે લોડ થવાનો સમય પ્રતિબંધિત લાગતો હતો. અને આ મારી જવાબદારીનું ક્ષેત્ર હોવાથી, મેં તરત જ સમસ્યાના ઉકેલ પર ધ્યાન કેન્દ્રિત કર્યું.

પ્રાગૈતિહાસિક

હું જેના વિશે વાત કરી રહ્યો છું તે સ્પષ્ટ કરવા માટે, હું તમને VWO વિશે થોડું કહીશ. આ એક પ્લેટફોર્મ છે જેની મદદથી તમે તમારી વેબસાઇટ્સ પર વિવિધ લક્ષિત ઝુંબેશ શરૂ કરી શકો છો: A/B પ્રયોગો કરો, મુલાકાતીઓ અને રૂપાંતરણોને ટ્રૅક કરો, વેચાણ ફનલનું વિશ્લેષણ કરો, હીટ નકશા પ્રદર્શિત કરો અને મુલાકાત રેકોર્ડિંગ ચલાવો.

પરંતુ પ્લેટફોર્મ વિશેની સૌથી મહત્વની વસ્તુ રિપોર્ટિંગ છે. ઉપરોક્ત તમામ કાર્યો એકબીજા સાથે જોડાયેલા છે. અને કોર્પોરેટ ક્લાયન્ટ્સ માટે, માહિતીનો વિશાળ જથ્થો એક શક્તિશાળી પ્લેટફોર્મ વિના નકામું હશે જે તેને વિશ્લેષણ સ્વરૂપમાં રજૂ કરે છે.

પ્લેટફોર્મનો ઉપયોગ કરીને, તમે મોટા ડેટા સેટ પર રેન્ડમ ક્વેરી કરી શકો છો. અહીં એક સરળ ઉદાહરણ છે:

પૃષ્ઠ "abc.com" પરની બધી ક્લિક્સ બતાવો
 થી  સુધી
જે લોકો માટે
Chrome અથવા વપરાયેલ
(યુરોપમાં હતા અને iPhone નો ઉપયોગ કરતા હતા)

બુલિયન ઓપરેટરો પર ધ્યાન આપો. નમૂનાઓ મેળવવા માટે તેઓ મનસ્વી રીતે જટિલ પ્રશ્નો કરવા માટે ક્વેરી ઇન્ટરફેસમાં ક્લાયન્ટ માટે ઉપલબ્ધ છે.

ધીમી વિનંતી

પ્રશ્નમાં ક્લાયંટ કંઈક એવું કરવાનો પ્રયાસ કરી રહ્યો હતો કે જે સાહજિક રીતે ઝડપથી કાર્ય કરે:

બધા સત્ર રેકોર્ડિંગ્સ બતાવો
કોઈપણ પૃષ્ઠની મુલાકાત લેનારા વપરાશકર્તાઓ માટે
URL સાથે જ્યાં "/jobs" છે

આ સાઇટ પર એક ટન ટ્રાફિક હતો અને અમે તેના માટે એક મિલિયનથી વધુ અનન્ય URL સ્ટોર કરી રહ્યાં હતાં. અને તેઓ એકદમ સરળ URL ટેમ્પલેટ શોધવા માગતા હતા જે તેમના બિઝનેસ મોડલથી સંબંધિત હોય.

પ્રાથમિક તપાસ

ચાલો ડેટાબેઝમાં શું ચાલી રહ્યું છે તેના પર એક નજર કરીએ. નીચે મૂળ ધીમી 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 ;

અને અહીં સમય છે:

આયોજિત સમય: 1.480 ms
એક્ઝેક્યુશન સમય: 1431924.650 ms

ક્વેરી 150 હજાર પંક્તિઓ ક્રોલ કરી. ક્વેરી પ્લાનરે કેટલીક રસપ્રદ વિગતો દર્શાવી, પરંતુ કોઈ સ્પષ્ટ અડચણો નથી.

ચાલો વિનંતીનો વધુ અભ્યાસ કરીએ. જેમ તમે જોઈ શકો છો, તે કરે છે JOIN ત્રણ કોષ્ટકો:

1. સત્રો: સત્ર માહિતી પ્રદર્શિત કરવા માટે: બ્રાઉઝર, વપરાશકર્તા એજન્ટ, દેશ, વગેરે.
2. રેકોર્ડિંગ_ડેટા: રેકોર્ડ કરેલ URL, પૃષ્ઠો, મુલાકાતોનો સમયગાળો
3. url: અત્યંત મોટા URL ને ડુપ્લિકેટ ન કરવા માટે, અમે તેમને એક અલગ કોષ્ટકમાં સંગ્રહિત કરીએ છીએ.

એ પણ નોંધ લો કે અમારા બધા કોષ્ટકો પહેલેથી જ દ્વારા વિભાજિત છે account_id. આ રીતે, એવી પરિસ્થિતિ જ્યાં એક ખાસ કરીને મોટું એકાઉન્ટ અન્ય લોકો માટે સમસ્યાઓનું કારણ બને છે તેને બાકાત રાખવામાં આવે છે.

કડીઓ શોધી રહ્યાં છીએ

નજીકના નિરીક્ષણ પર, અમે જોઈ શકીએ છીએ કે ચોક્કસ વિનંતીમાં કંઈક ખોટું છે. આ લાઇનને નજીકથી જોવાનું મૂલ્યવાન છે:

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

પ્રથમ વિચાર હતો કે કદાચ કારણ કે ILIKE આ બધા લાંબા URL પર (અમારી પાસે 1,4 મિલિયનથી વધુ છે અનન્ય આ એકાઉન્ટ માટે એકત્રિત કરાયેલ URL) કામગીરીને નુકસાન થઈ શકે છે.

પરંતુ ના, તે મુદ્દો નથી!

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

Time: 5231.765 ms

ટેમ્પલેટ શોધ વિનંતી પોતે માત્ર 5 સેકન્ડ લે છે. એક મિલિયન અનન્ય URL માં પેટર્ન શોધવામાં સ્પષ્ટપણે કોઈ સમસ્યા નથી.

સૂચિમાં આગામી શંકાસ્પદ ઘણા છે JOIN. કદાચ તેમના વધુ પડતા ઉપયોગથી મંદી આવી છે? સામાન્ય રીતે JOINપ્રદર્શન સમસ્યાઓ માટેના સૌથી સ્પષ્ટ ઉમેદવારો છે, પરંતુ હું માનતો ન હતો કે અમારો કેસ લાક્ષણિક છે.

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

અને આ પણ અમારો કેસ નહોતો. JOINતદ્દન ઝડપી હોવાનું બહાર આવ્યું છે.

શંકાસ્પદ વર્તુળને સંકુચિત કરવું

હું કોઈપણ સંભવિત પ્રદર્શન સુધારણાઓ હાંસલ કરવા માટે ક્વેરી બદલવાનું શરૂ કરવા માટે તૈયાર હતો. મારી ટીમ અને મેં 2 મુખ્ય વિચારો વિકસાવ્યા:

• સબક્વેરી URL માટે EXISTS નો ઉપયોગ કરો: URL માટે સબક્વેરી સાથે કોઈ સમસ્યા છે કે કેમ તે અમે ફરીથી તપાસવા માગીએ છીએ. આ હાંસલ કરવાની એક રીત છે ફક્ત ઉપયોગ કરવો EXISTS. EXISTS કરી શકો છો કાર્યક્ષમતામાં ઘણો સુધારો થાય છે કારણ કે તે શરત સાથે મેળ ખાતી એકમાત્ર સ્ટ્રિંગ શોધે તે તરત જ સમાપ્ત થાય છે.

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

ભલે હા. સબક્વેરી જ્યારે આવરિત હોય EXISTS, બધું સુપર ફાસ્ટ બનાવે છે. આગામી તાર્કિક પ્રશ્ન શા માટે સાથે વિનંતી છે JOIN-ami અને સબક્વેરી પોતે વ્યક્તિગત રીતે ઝડપી છે, પરંતુ એકસાથે ભયંકર રીતે ધીમું છે?

• સબક્વેરી CTE પર ખસેડી રહ્યા છીએ : જો ક્વેરી તેના પોતાના પર ઝડપી હોય, તો અમે પહેલા ઝડપી પરિણામની ગણતરી કરી શકીએ છીએ અને પછી તેને મુખ્ય ક્વેરી માટે પ્રદાન કરી શકીએ છીએ.

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;

પરંતુ તે હજુ પણ ખૂબ ધીમું હતું.

ગુનેગારને શોધી રહ્યા છે

આ બધા સમયે, મારી આંખો સામે એક નાની વસ્તુ ચમકતી હતી, જેને હું સતત બાજુ પર બ્રશ કરતો હતો. પણ બીજું કંઈ બાકી ન હોવાથી મેં તેને પણ જોવાનું નક્કી કર્યું. હું વિશે વાત કરું છું && ઓપરેટર બાય EXISTS માત્ર સુધારેલ પ્રદર્શન && ધીમી ક્વેરીનાં તમામ સંસ્કરણોમાં એકમાત્ર બાકી રહેલું સામાન્ય પરિબળ હતું.

ની સામે જોઈને દસ્તાવેજીકરણ, આપણે તે જોઈએ છીએ && જ્યારે તમારે બે એરે વચ્ચે સામાન્ય તત્વો શોધવાની જરૂર હોય ત્યારે વપરાય છે.

મૂળ વિનંતીમાં આ છે:

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

જેનો અર્થ છે કે અમે અમારા URLs પર પેટર્ન શોધ કરીએ છીએ, પછી સામાન્ય પોસ્ટ્સ સાથેના તમામ URL સાથે આંતરછેદ શોધીએ છીએ. આ થોડું ગૂંચવણભર્યું છે કારણ કે અહીં "urls" એ બધા URLs ધરાવતા કોષ્ટકનો સંદર્ભ નથી, પરંતુ કોષ્ટકમાં "urls" કૉલમનો સંદર્ભ આપે છે. recording_data.

અંગે વધતી જતી શંકાઓ સાથે &&, મેં જનરેટ કરેલ ક્વેરી પ્લાનમાં તેમના માટે પુષ્ટિ શોધવાનો પ્રયાસ કર્યો EXPLAIN ANALYZE (મારી પાસે પહેલેથી જ એક પ્લાન સાચવવામાં આવ્યો હતો, પરંતુ હું સામાન્ય રીતે ક્વેરી પ્લાનર્સની અસ્પષ્ટતાને સમજવાનો પ્રયાસ કરતાં SQL માં પ્રયોગ કરવામાં વધુ આરામદાયક છું).

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

માત્ર થી ફિલ્ટર્સની ઘણી રેખાઓ હતી &&. જેનો અર્થ એ થયો કે આ ઓપરેશન માત્ર ખર્ચાળ જ નથી, પરંતુ ઘણી વખત કરવામાં આવ્યું હતું.

મેં સ્થિતિને અલગ કરીને આનું પરીક્ષણ કર્યું

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

આ પ્રશ્ન ધીમો હતો. કારણ કે JOIN-s ઝડપી છે અને સબક્વેરીઝ ઝડપી છે, માત્ર એક જ વસ્તુ બાકી હતી && ઓપરેટર

આ માત્ર એક કી ઓપરેશન છે. પેટર્ન શોધવા માટે અમારે હંમેશા URL ના સમગ્ર અંતર્ગત કોષ્ટકને શોધવાની જરૂર છે, અને અમારે હંમેશા આંતરછેદ શોધવાની જરૂર છે. અમે સીધા URL રેકોર્ડ્સ દ્વારા શોધી શકતા નથી, કારણ કે આ ફક્ત ID નો ઉલ્લેખ કરે છે urls.

ઉકેલના માર્ગ પર

&& ધીમું કારણ કે બંને સેટ વિશાળ છે. જો હું બદલીશ તો ઓપરેશન પ્રમાણમાં ઝડપી થશે urls પર { "http://google.com/", "http://wingify.com/" }.

મેં ઉપયોગ કર્યા વિના પોસ્ટગ્રેસમાં સેટ ઇન્ટરસેક્શન કરવાની રીત શોધવાનું શરૂ કર્યું &&, પરંતુ ઘણી સફળતા વિના.

અંતે, અમે ફક્ત એકલતામાં સમસ્યા હલ કરવાનું નક્કી કર્યું: મને બધું આપો urls લીટીઓ કે જેના માટે URL પેટર્ન સાથે મેળ ખાય છે. વધારાની શરતો વિના તે હશે - 

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

તેના બદલે JOIN સિન્ટેક્સ મેં હમણાં જ સબક્વેરીનો ઉપયોગ કર્યો અને વિસ્તૃત કર્યું recording_data.urls એરે કે જેથી તમે સીધી રીતે શરત લાગુ કરી શકો WHERE.

અહીં સૌથી મહત્વની બાબત એ છે કે && આપેલ એન્ટ્રીમાં મેળ ખાતું URL છે કે કેમ તે ચકાસવા માટે વપરાય છે. જો તમે થોડી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી ઝીણી. શું તમને કંઈપણ યાદ નથી આવતું? હા, EXISTS.

ત્યારથી recording_data.urls સબક્વેરી સંદર્ભની બહારથી સંદર્ભિત કરી શકાય છે, જ્યારે આવું થાય ત્યારે અમે અમારા જૂના મિત્ર પર પાછા પડી શકીએ છીએ EXISTS અને તેની સાથે સબક્વેરી લપેટી.

બધું એકસાથે મૂકીને, અમને અંતિમ ઑપ્ટિમાઇઝ ક્વેરી મળે છે:

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

અને અંતિમ લીડ સમય Time: 1898.717 ms ઉજવણી કરવાનો સમય?!?

એટલું ઝડપી નથી! પ્રથમ તમારે શુદ્ધતા તપાસવાની જરૂર છે. હું અત્યંત શંકાસ્પદ હતો EXISTS ઓપ્ટિમાઇઝેશન કારણ કે તે તર્કને પહેલા પૂર્ણ કરવા માટે બદલે છે. અમારે ખાતરી કરવાની જરૂર છે કે અમે વિનંતીમાં કોઈ અસ્પષ્ટ ભૂલ ઉમેરી નથી.

એક સરળ ટેસ્ટ ચલાવવાની હતી count(*) મોટી સંખ્યામાં વિવિધ ડેટા સેટ માટે ધીમી અને ઝડપી બંને ક્વેરીઝ પર. પછી, ડેટાના નાના સબસેટ માટે, મેં મેન્યુઅલી ચકાસ્યું કે બધા પરિણામો સાચા હતા.

બધા પરીક્ષણો સતત હકારાત્મક પરિણામો આપે છે. અમે બધું ઠીક કર્યું!

પાઠ શીખ્યા

આ વાર્તામાંથી ઘણા પાઠ શીખવા જેવા છે:

1. ક્વેરી યોજનાઓ આખી વાર્તા કહેતી નથી, પરંતુ તેઓ સંકેતો આપી શકે છે
2. મુખ્ય શંકાસ્પદો હંમેશા વાસ્તવિક ગુનેગારો હોતા નથી
3. અવરોધોને અલગ કરવા માટે ધીમી ક્વેરીઝને તોડી શકાય છે
4. બધા ઑપ્ટિમાઇઝેશન પ્રકૃતિમાં ઘટાડાવાળા નથી
5. ઉપયોગ કરો EXIST, જ્યાં શક્ય હોય, ઉત્પાદકતામાં નાટ્યાત્મક વધારો થઈ શકે છે

નિષ્કર્ષ

અમે ~24 મિનિટના ક્વેરી સમયથી 2 સેકન્ડ સુધી ગયા - નોંધપાત્ર કામગીરીમાં નોંધપાત્ર વધારો! જો કે આ લેખ મોટો બહાર આવ્યો છે, અમે કરેલા તમામ પ્રયોગો એક જ દિવસમાં થયા છે અને એવો અંદાજ છે કે તેમને ઑપ્ટિમાઇઝેશન અને પરીક્ષણ માટે 1,5 થી 2 કલાક જેટલો સમય લાગ્યો છે.

SQL એ એક અદ્ભુત ભાષા છે જો તમે તેનાથી ડરતા નથી, પરંતુ તેને શીખવાનો અને તેનો ઉપયોગ કરવાનો પ્રયાસ કરો. એસક્યુએલ ક્વેરીઝ કેવી રીતે એક્ઝિક્યુટ થાય છે, ડેટાબેઝ કેવી રીતે ક્વેરી પ્લાન જનરેટ કરે છે, ઈન્ડેક્સ કેવી રીતે કામ કરે છે અને તમે જે ડેટા સાથે કામ કરી રહ્યાં છો તેના કદની સારી સમજ મેળવીને, તમે ક્વેરીઝને ઑપ્ટિમાઇઝ કરવામાં ખૂબ જ સફળ થઈ શકો છો. જો કે, વિવિધ અભિગમો અજમાવવાનું ચાલુ રાખવું અને અવરોધોને શોધીને ધીમે ધીમે સમસ્યાને તોડી પાડવી તે એટલું જ મહત્વપૂર્ણ છે.

આના જેવા પરિણામો હાંસલ કરવા વિશેનો શ્રેષ્ઠ ભાગ એ નોંધનીય, દૃશ્યમાન ગતિ સુધારણા છે - જ્યાં એક અહેવાલ જે અગાઉ લોડ થતો ન હતો તે હવે લગભગ તરત જ લોડ થાય છે.

નો ખાસ આભાર મારા સાથીઓ આદિત્ય મિશ્રાના આદેશ પર, આદિત્ય ગૌરુ и વરુણ મલ્હોત્રા મંથન માટે અને દિનકર પંડિર અમે આખરે તેને વિદાય આપીએ તે પહેલાં અમારી અંતિમ વિનંતીમાં મહત્વપૂર્ણ ભૂલ શોધવા માટે!

સોર્સ: www.habr.com