पिछले दिसंबर में, मुझे VWO समर्थन टीम से एक दिलचस्प बग रिपोर्ट मिली। एक बड़े कॉर्पोरेट ग्राहक के लिए विश्लेषणात्मक रिपोर्ट में से एक के लिए लोडिंग समय निषेधात्मक लग रहा था। और चूंकि यह मेरी जिम्मेदारी का क्षेत्र है, मैंने तुरंत समस्या को हल करने पर ध्यान केंद्रित किया।
प्रागितिहास
यह स्पष्ट करने के लिए कि मैं किस बारे में बात कर रहा हूं, मैं आपको VWO के बारे में थोड़ा बताऊंगा। यह एक ऐसा प्लेटफ़ॉर्म है जिसके साथ आप अपनी साइटों पर विभिन्न लक्षित अभियान चला सकते हैं: ए / बी प्रयोगों का संचालन करें, आगंतुकों और रूपांतरणों को ट्रैक करें, बिक्री फ़नल का विश्लेषण करें, हीटमैप प्रदर्शित करें और यात्राओं की रिकॉर्डिंग चलाएं।
लेकिन प्लेटफॉर्म में सबसे महत्वपूर्ण बात रिपोर्टिंग है। उपरोक्त सभी कार्य आपस में जुड़े हुए हैं। और कॉर्पोरेट ग्राहकों के लिए, जानकारी का एक विशाल सरणी केवल एक शक्तिशाली मंच के बिना बेकार होगा जो उन्हें एनालिटिक्स के रूप में प्रस्तुत करेगा।
प्लेटफॉर्म का उपयोग करके, आप एक बड़े डेटा सेट पर एक मनमाना अनुरोध कर सकते हैं। यहाँ एक सरल उदाहरण है:
Abc.com पर सभी क्लिक दिखाएं
<तारीख d1> से <तारीख d2> तक
उन लोगों के लिए जो
क्रोम OR का उपयोग किया
(यूरोप में थे और 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 एमएस
लीड समय: 1431924.650 एमएस
अनुरोध 150 हजार लाइनों को बायपास किया गया। क्वेरी प्लानर ने कुछ दिलचस्प विवरण दिखाए, लेकिन कोई स्पष्ट अड़चन नहीं।
चलिए क्वेरी का और अध्ययन करते हैं। जैसा कि आप देख सकते हैं, यह तीन टेबल बनाता है JOIN :
- सत्र : सत्र जानकारी प्रदर्शित करने के लिए: ब्राउज़र, उपयोगकर्ता एजेंट, देश, और इसी तरह।
- रिकॉर्डिंग_डेटा : दर्ज किए गए url, पृष्ठ, विज़िट की अवधि
- urls : बहुत बड़े url के दोहराव से बचने के लिए, हम उन्हें एक अलग तालिका में संग्रहीत करते हैं।
यह भी ध्यान दें कि हमारे सभी टेबल पहले से ही account_id से विभाजित हैं। इस प्रकार, एक स्थिति को बाहर रखा जाता है, जब एक विशेष रूप से बड़े खाते के कारण, दूसरों को समस्या होती है।
सबूत की तलाश है
करीब से निरीक्षण करने पर, हम देखते हैं कि किसी विशेष अनुरोध में कुछ सही नहीं है। यह इस लाइन पर एक नज़र है:
urls && array( select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%' )::text[]
पहला विचार यह था कि शायद इन सभी लंबे URL में ILIKE कारण (हमारे पास इस खाते के लिए 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 में लपेटा जाता है, तो सब कुछ सुपर फास्ट हो जाता है। अगला तार्किक सवाल यह है कि जॉइन और सबक्वेरी के साथ क्वेरी व्यक्तिगत रूप से तेज क्यों है, लेकिन एक साथ बहुत धीमी है?
- हम सबक्वेरी को 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 वाले तालिका का संदर्भ नहीं है, लेकिन recording_data तालिका में एक स्तंभ "urls" पर है।
जैसे ही && संदेह && , मैंने 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 तेज़ हैं और JOIN तेज़ हैं, केवल && ऑपरेटर ही रहता है।
यह सिर्फ एक महत्वपूर्ण ऑपरेशन है। हमें हमेशा पैटर्न द्वारा खोज करने के लिए URL की मुख्य तालिका पर खोज करने की आवश्यकता है, और हमें हमेशा चौराहों को खोजने की आवश्यकता है। हम सीधे url प्रविष्टियाँ नहीं खोज सकते, क्योंकि ये सिर्फ पहचानकर्ता हैं जो urls से urls ।
एक समाधान की ओर
&& धीमा क्योंकि दोनों सेट विशाल हैं। यदि मैं urls को { "http://google.com/", "http://wingify.com/" } साथ बदल urls तो ऑपरेशन अपेक्षाकृत जल्दी हो जाएगा।
मैंने Postgres में बिना && का उपयोग किए, लेकिन बहुत अधिक सफलता के बिना सेटों के प्रतिच्छेदन का रास्ता खोजना शुरू कर दिया।
अंत में, हमने केवल अलगाव में समस्या को हल करने का फैसला किया: मुझे स्ट्रिंग के सभी 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(*) करना। फिर, डेटा के एक छोटे सबसेट के लिए, मैंने मैन्युअल रूप से सभी परिणामों की शुद्धता की जांच की।
सभी चेकों ने लगातार सकारात्मक परिणाम दिए। हमने इसे ठीक कर दिया!
सबक सीखा
इस कहानी से कई सबक सीखे जाने हैं:
- क्वेरी योजना पूरी कहानी नहीं बताती है, लेकिन सुराग दे सकती है
- मुख्य संदिग्ध हमेशा वास्तविक अपराधी नहीं होते हैं
- अड़चनों को अलग करने के लिए धीमे प्रश्नों को तोड़ा जा सकता है
- सभी अनुकूलन प्रकृति में कम नहीं हैं
EXIST का उपयोग करना, जहां संभव हो, उत्पादकता में तेज वृद्धि हो सकती है।
निष्कर्ष
हम ~ 24 मिनट 2 सेकंड के अनुरोध समय से चले गए - एक बहुत ही गंभीर प्रदर्शन वृद्धि! यद्यपि यह लेख बड़ा हो गया था, हमारे द्वारा किए गए सभी प्रयोग एक ही दिन में हुए थे, और अनुमान के अनुसार, अनुकूलन और परीक्षण के लिए 1.5 से 2 घंटे का समय लगा।
एसक्यूएल एक अद्भुत भाषा है, अगर यह डर नहीं है, लेकिन सीखने और उपयोग करने का प्रयास करें। SQL क्वेरी को कैसे निष्पादित किया जाता है, इसकी अच्छी समझ होने पर, डेटाबेस क्वेरी प्लान कैसे तैयार करता है, इंडेक्स कैसे काम करते हैं, और बस जिस डेटा से आप काम कर रहे हैं, उसका आकार, आप क्वेरी ऑप्टिमाइज़ेशन में बहुत सफल हो सकते हैं। हालांकि, अलग-अलग तरीकों की कोशिश जारी रखना और धीरे-धीरे समस्या को तोड़ना, अड़चनों का पता लगाना भी उतना ही महत्वपूर्ण है।
इस तरह के परिणामों को प्राप्त करने में सबसे अच्छा हिस्सा गति में ध्यान देने योग्य दृश्यमान सुधार है - जब एक रिपोर्ट जिसे पहले डाउनलोड नहीं किया गया था, अब लगभग तुरंत लोड किया गया है।
मेरे साथी आदित्य मिश्रा , आदित्य गौरू और वरुण मल्होत्रा को मंथन और दिनकर पंडिर के लिए विशेष धन्यवाद हमारे अंतिम अनुरोध में एक महत्वपूर्ण गलती खोजने के लिए, इससे पहले कि हम अंत में उन्हें अलविदा कहें!