🥇एक एसक्यूएल जांच की कहानी

पिछले दिसंबर में मुझे VWO सहायता टीम से एक दिलचस्प बग रिपोर्ट प्राप्त हुई। एक बड़े कॉर्पोरेट ग्राहक के लिए विश्लेषणात्मक रिपोर्टों में से एक का लोडिंग समय निषेधात्मक लग रहा था। और चूंकि यह मेरी जिम्मेदारी का क्षेत्र है, इसलिए मैंने तुरंत समस्या को हल करने पर ध्यान केंद्रित किया।

पृष्ठभूमि

यह स्पष्ट करने के लिए कि मैं किस बारे में बात कर रहा हूं, मैं आपको VWO के बारे में थोड़ा बताऊंगा। यह एक ऐसा प्लेटफ़ॉर्म है जिसके साथ आप अपनी वेबसाइटों पर विभिन्न लक्षित अभियान लॉन्च कर सकते हैं: ए/बी प्रयोगों का संचालन करें, आगंतुकों और रूपांतरणों को ट्रैक करें, बिक्री फ़नल का विश्लेषण करें, हीट मैप प्रदर्शित करें और विज़िट रिकॉर्डिंग चलाएं।

लेकिन प्लेटफ़ॉर्म के बारे में सबसे महत्वपूर्ण बात रिपोर्टिंग है। उपरोक्त सभी कार्य आपस में जुड़े हुए हैं। और कॉर्पोरेट ग्राहकों के लिए, एक शक्तिशाली मंच के बिना बड़ी मात्रा में जानकारी बेकार होगी जो इसे विश्लेषणात्मक रूप में प्रस्तुत करती है।

प्लेटफ़ॉर्म का उपयोग करके, आप बड़े डेटा सेट पर यादृच्छिक क्वेरी कर सकते हैं। यहाँ एक सरल उदाहरण है:

उन लोगों के लिए पृष्ठ "abc.com" पर <दिनांक d1> से <दिनांक d2> तक के सभी क्लिक दिखाएं, जो Chrome का उपयोग करते हैं या (यूरोप में स्थित हैं और iPhone का उपयोग करते हैं)

बूलियन ऑपरेटरों पर ध्यान दें. वे नमूने प्राप्त करने के लिए मनमाने ढंग से जटिल प्रश्न बनाने के लिए क्वेरी इंटरफ़ेस में ग्राहकों के लिए उपलब्ध हैं।

धीमा अनुरोध

विचाराधीन ग्राहक कुछ ऐसा करने का प्रयास कर रहा था जो सहज रूप से शीघ्रता से कार्य करे:

उन उपयोगकर्ताओं के लिए सभी सत्र रिकॉर्ड दिखाएं जो "/jobs" वाले यूआरएल वाले किसी भी पृष्ठ पर गए थे

इस साइट पर बहुत अधिक ट्रैफ़िक था और हम इसके लिए दस लाख से अधिक अद्वितीय यूआरएल संग्रहीत कर रहे थे। और वे एक काफी सरल यूआरएल टेम्पलेट ढूंढना चाहते थे जो उनके बिजनेस मॉडल से संबंधित हो।

प्राथमिक जांच

आइए देखें कि डेटाबेस में क्या चल रहा है। नीचे मूल धीमी 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 एमएस निष्पादन समय: 1431924.650 एमएस

क्वेरी ने 150 हजार पंक्तियों को क्रॉल किया। क्वेरी प्लानर ने कुछ दिलचस्प विवरण दिखाए, लेकिन कोई स्पष्ट बाधा नहीं दिखाई दी।

आइए अनुरोध का आगे अध्ययन करें। जैसा कि आप देख सकते हैं, वह करता है JOIN तीन टेबल:

सत्र: सत्र जानकारी प्रदर्शित करने के लिए: ब्राउज़र, उपयोगकर्ता एजेंट, देश, इत्यादि।
रिकॉर्डिंग_डेटा: रिकॉर्ड किए गए यूआरएल, पेज, विज़िट की अवधि
यूआरएल: अत्यधिक बड़े URL की नकल से बचने के लिए, हम उन्हें एक अलग तालिका में संग्रहीत करते हैं।

यह भी ध्यान दें कि हमारी सभी तालिकाएँ पहले से ही विभाजित हैं account_id. इस तरह, ऐसी स्थिति को बाहर रखा जाता है जहां एक विशेष रूप से बड़ा खाता दूसरों के लिए समस्याएं पैदा करता है।

सुराग की तलाश की जा रही है

करीब से निरीक्षण करने पर, हम देखते हैं कि किसी विशेष अनुरोध में कुछ गड़बड़ है। इस पंक्ति पर करीब से नज़र डालना उचित है:

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

पहला विचार यह था कि शायद इसलिये ILIKE इन सभी लंबे यूआरएल पर (हमारे पास 1,4 मिलियन से अधिक हैं अद्वितीय इस खाते के लिए एकत्र किए गए यूआरएल) के प्रदर्शन पर असर पड़ सकता है।

लेकिन नहीं, वह बात नहीं है!

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

Time: 5231.765 ms

टेम्प्लेट खोज अनुरोध में केवल 5 सेकंड लगते हैं। लाखों अद्वितीय यूआरएल में एक पैटर्न खोजना स्पष्ट रूप से कोई समस्या नहीं है।

सूची में अगले संदिग्ध कई हैं 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 मुख्य विचार विकसित किए:

सबक्वेरी यूआरएल के लिए EXISTS का उपयोग करें: हम दोबारा जांचना चाहते थे कि यूआरएल की सबक्वेरी में कोई समस्या तो नहीं है। इसे प्राप्त करने का एक तरीका बस उपयोग करना है 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-अमी और सबक्वेरी स्वयं व्यक्तिगत रूप से तेज़ हैं, लेकिन एक साथ बहुत धीमी हैं?

सबक्वेरी को 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[]   )

जिसका अर्थ है कि हम अपने यूआरएल पर एक पैटर्न खोज करते हैं, फिर सामान्य पोस्ट वाले सभी यूआरएल के साथ प्रतिच्छेदन ढूंढते हैं। यह थोड़ा भ्रमित करने वाला है क्योंकि यहां "यूआरएल" सभी यूआरएल वाली तालिका को संदर्भित नहीं करता है, बल्कि तालिका में "यूआरएल" कॉलम को संदर्भित करता है। recording_data.

के बारे में बढ़ते संदेह के साथ &&, मैंने उत्पन्न क्वेरी योजना में उनके लिए पुष्टि खोजने का प्रयास किया EXPLAIN ANALYZE (मेरे पास पहले से ही एक योजना सहेजी हुई थी, लेकिन मैं आमतौर पर क्वेरी प्लानर्स की अस्पष्टता को समझने की तुलना में एसक्यूएल में प्रयोग करने में अधिक सहज महसूस करता हूं)।

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 की संपूर्ण अंतर्निहित तालिका को खोजने की आवश्यकता होती है, और हमें हमेशा चौराहों को खोजने की आवश्यकता होती है। हम सीधे यूआरएल रिकॉर्ड से खोज नहीं कर सकते, क्योंकि ये केवल आईडी का संदर्भ है 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.

यहां सबसे महत्वपूर्ण बात यही है && यह जांचने के लिए उपयोग किया जाता है कि किसी दी गई प्रविष्टि में मेल खाने वाला यूआरएल है या नहीं। यदि आप थोड़ा तिरछा करें, तो आप देख सकते हैं कि यह ऑपरेशन किसी सरणी (या तालिका की पंक्तियों) के तत्वों के माध्यम से चलता है और एक शर्त (मिलान) पूरा होने पर रुक जाता है। क्या आपको कुछ याद नहीं आता? हाँ, 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(*) बड़ी संख्या में विभिन्न डेटा सेटों के लिए धीमी और तेज़ दोनों क्वेरी पर। फिर, डेटा के एक छोटे उपसमूह के लिए, मैंने मैन्युअल रूप से सत्यापित किया कि सभी परिणाम सही थे।

सभी परीक्षणों ने लगातार सकारात्मक परिणाम दिये। हमने सब कुछ ठीक कर दिया!

सीख सीखी

इस कहानी से बहुत कुछ सीखने को मिलता है:

क्वेरी योजनाएँ पूरी कहानी नहीं बतातीं, लेकिन वे सुराग प्रदान कर सकती हैं
मुख्य संदिग्ध हमेशा असली अपराधी नहीं होते
बाधाओं को अलग करने के लिए धीमी क्वेरी को विभाजित किया जा सकता है
सभी अनुकूलन प्रकृति में रिडक्टिव नहीं होते हैं
उपयोग EXIST, जहां संभव हो, उत्पादकता में नाटकीय वृद्धि ला सकता है

उत्पादन

हम ~24 मिनट के क्वेरी समय से 2 सेकंड तक पहुंच गए - प्रदर्शन में काफी महत्वपूर्ण वृद्धि! हालाँकि यह लेख बड़ा निकला, हमारे द्वारा किए गए सभी प्रयोग एक दिन में हुए, और यह अनुमान लगाया गया कि अनुकूलन और परीक्षण के लिए उन्हें 1,5 से 2 घंटे का समय लगा।

यदि आप इससे डरते नहीं हैं, बल्कि इसे सीखने और उपयोग करने का प्रयास करते हैं तो SQL एक अद्भुत भाषा है। SQL क्वेरीज़ को कैसे निष्पादित किया जाता है, डेटाबेस क्वेरी प्लान कैसे बनाता है, इंडेक्स कैसे काम करते हैं, और जिस डेटा के साथ आप काम कर रहे हैं उसके आकार की अच्छी समझ होने से, आप क्वेरीज़ को अनुकूलित करने में बहुत सफल हो सकते हैं। हालाँकि, यह भी उतना ही महत्वपूर्ण है कि अलग-अलग दृष्टिकोण अपनाते रहें और बाधाओं का पता लगाते हुए धीरे-धीरे समस्या का समाधान करें।

इस तरह के परिणाम प्राप्त करने का सबसे अच्छा हिस्सा ध्यान देने योग्य, दृश्यमान गति में सुधार है - जहां एक रिपोर्ट जो पहले लोड भी नहीं होती थी अब लगभग तुरंत लोड हो जाती है।

विशेष धन्यवाद मेरे साथियों आदित्य मिश्रा के आदेश पर, आदित्य गौरु и वरुण मल्होत्रा विचार-मंथन के लिए और दिनकर पंडिर हमारे अंतिम अनुरोध को अंततः अलविदा कहने से पहले उसमें एक महत्वपूर्ण त्रुटि ढूंढने के लिए!

स्रोत: www.habr.com

एक SQL जांच की कहानी