🥇 एका SQL तपासाची कहाणी

गेल्या डिसेंबरमध्ये मला VWO समर्थन कार्यसंघाकडून एक मनोरंजक बग अहवाल प्राप्त झाला. मोठ्या कॉर्पोरेट क्लायंटसाठी विश्लेषण अहवालांपैकी एक लोडिंग वेळ निषेधार्ह वाटला. आणि हे माझ्या जबाबदारीचे क्षेत्र असल्याने मी लगेच समस्या सोडवण्यावर लक्ष केंद्रित केले.

prehistory

मी कशाबद्दल बोलत आहे हे स्पष्ट करण्यासाठी, मी तुम्हाला VWO बद्दल थोडेसे सांगेन. हे एक व्यासपीठ आहे ज्याद्वारे तुम्ही तुमच्या वेबसाइटवर विविध लक्ष्यित मोहिमा सुरू करू शकता: A/B प्रयोग करा, अभ्यागत आणि रूपांतरणांचा मागोवा घ्या, विक्री फनेलचे विश्लेषण करा, उष्णता नकाशे प्रदर्शित करा आणि भेट रेकॉर्डिंग प्ले करा.

पण प्लॅटफॉर्मची सर्वात महत्त्वाची गोष्ट म्हणजे रिपोर्टिंग. वरील सर्व कार्ये एकमेकांशी जोडलेली आहेत. आणि कॉर्पोरेट क्लायंटसाठी, विश्लेषणाच्या स्वरूपात सादर करणार्‍या शक्तिशाली प्लॅटफॉर्मशिवाय मोठ्या प्रमाणात माहिती निरुपयोगी होईल.

प्लॅटफॉर्मचा वापर करून, तुम्ही मोठ्या डेटा सेटवर यादृच्छिक क्वेरी करू शकता. येथे एक साधे उदाहरण आहे:

<date d1> पासून <date d2> या पृष्‍ठावरील सर्व क्लिक्‍स दाखवा जे लोक Chrome वापरतात किंवा (युरोपमध्‍ये असलेल्‍या आणि iPhone वापरतात)

बुलियन ऑपरेटरकडे लक्ष द्या. क्‍वेरी इंटरफेसमध्‍ये क्‍लायंटसाठी नमुने मिळवण्‍यासाठी अनियंत्रितपणे जटिल क्‍वेरी करण्‍यासाठी ते उपलब्‍ध आहेत.

सावकाश विनंती

प्रश्नातील क्लायंट असे काहीतरी करण्याचा प्रयत्न करीत होता जे अंतर्ज्ञानाने त्वरीत कार्य केले पाहिजे:

"/jobs" असलेल्या URL सह कोणत्याही पृष्ठास भेट दिलेल्या वापरकर्त्यांसाठी सर्व सत्र रेकॉर्ड दर्शवा

या साइटवर एक टन रहदारी होती आणि आम्ही फक्त त्यासाठी एक दशलक्षाहून अधिक अद्वितीय 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 तीन टेबल:

सत्र: सत्र माहिती प्रदर्शित करण्यासाठी: ब्राउझर, वापरकर्ता एजंट, देश इ.
रेकॉर्डिंग_डेटा: रेकॉर्ड केलेल्या URL, पृष्ठे, भेटींचा कालावधी
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[]   )

याचा अर्थ आम्ही आमच्या URL वर नमुना शोधतो, नंतर सामान्य पोस्टसह सर्व URL सह छेदनबिंदू शोधा. हे थोडे गोंधळात टाकणारे आहे कारण येथे "urls" सर्व URL असलेल्या सारणीचा संदर्भ देत नाही तर सारणीतील "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 रेकॉर्डद्वारे शोधू शकत नाही, कारण हे फक्त आयडी आहेत 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(*) मोठ्या संख्येने विविध डेटा संचांसाठी धीमे आणि जलद दोन्ही क्वेरींवर. त्यानंतर, डेटाच्या छोट्या उपसंचासाठी, सर्व परिणाम बरोबर असल्याचे मी व्यक्तिचलितपणे सत्यापित केले.

सर्व चाचण्या सातत्याने सकारात्मक परिणाम देतात. आम्ही सर्वकाही निश्चित केले!

शिकलेले धडे

या कथेतून अनेक धडे शिकायला मिळतात:

क्वेरी योजना संपूर्ण कथा सांगत नाहीत, परंतु ते संकेत देऊ शकतात
मुख्य संशयित नेहमीच खरे गुन्हेगार नसतात
अडथळे दूर करण्यासाठी धीमे क्वेरी खंडित केल्या जाऊ शकतात
सर्व ऑप्टिमायझेशन निसर्गात कमी नाहीत
वापरा EXIST, जेथे शक्य असेल, उत्पादकतेमध्ये नाट्यमय वाढ होऊ शकते

निष्कर्ष

आम्ही ~24 मिनिटांच्या क्वेरी वेळेपासून 2 सेकंदांपर्यंत गेलो - कामगिरीत लक्षणीय वाढ! हा लेख मोठा आला असला तरी, आम्ही केलेले सर्व प्रयोग एका दिवसात झाले आणि त्यांना ऑप्टिमायझेशन आणि चाचणीसाठी 1,5 ते 2 तास लागले असा अंदाज आहे.

तुम्ही घाबरत नसाल तर SQL ही एक अप्रतिम भाषा आहे, पण ती शिकण्याचा आणि वापरण्याचा प्रयत्न करा. SQL क्वेरी कशा अंमलात आणल्या जातात, डेटाबेस क्वेरी प्लॅन कसा तयार करतो, इंडेक्स कसे कार्य करतात आणि तुम्ही ज्या डेटाशी व्यवहार करत आहात त्या डेटाचा आकार याविषयी चांगल्या प्रकारे समजून घेतल्यास, तुम्ही क्वेरी ऑप्टिमाइझ करण्यात खूप यशस्वी होऊ शकता. तथापि, हे तितकेच महत्त्वाचे आहे की, वेगवेगळ्या पद्धतींचा प्रयत्न करत राहणे आणि अडथळे शोधून हळूहळू समस्या सोडवणे.

यासारखे परिणाम साध्य करण्याबाबतचा सर्वोत्तम भाग म्हणजे लक्षात येण्याजोगा, दृश्यमान गती सुधारणा - जिथे पूर्वी लोड होत नसलेला अहवाल आता जवळजवळ त्वरित लोड होतो.

यांचे विशेष आभार माझे सोबती आदित्य मिश्रा यांच्या आदेशानुसार, आदित्य गौरू и वरुण मल्होत्रा विचारमंथनासाठी आणि दिनकर पंडीर आम्ही शेवटी निरोप घेण्यापूर्वी आमच्या अंतिम विनंतीमध्ये एक महत्त्वाची त्रुटी शोधल्याबद्दल!

स्त्रोत: www.habr.com

एका एसक्यूएल तपासणीची कथा