बिल्डर्ससाठी B2B सेवेचे उदाहरण वापरून डेटाबेस क्वेरी ऑप्टिमाइझ करणे

अधिक उत्पादनक्षम सर्व्हरवर न जाता डेटाबेसमधील प्रश्नांची संख्या 10 पट कशी वाढवायची आणि सिस्टम कार्यक्षमता कशी राखायची? मी तुम्हाला सांगेन की आम्ही आमच्या डेटाबेसच्या कार्यक्षमतेतील घसरणीचा सामना कसा केला, आम्ही शक्य तितक्या वापरकर्त्यांना सेवा देण्यासाठी आणि संगणकीय संसाधनांची किंमत वाढवू नये म्हणून आम्ही SQL क्वेरी कशी ऑप्टिमाइझ केली.

मी बांधकाम कंपन्यांमधील व्यवसाय प्रक्रिया व्यवस्थापित करण्यासाठी सेवा तयार करतो. आमच्यासोबत सुमारे तीन हजार कंपन्या काम करतात. 3 हजारांहून अधिक लोक दररोज 10-4 तास आमच्या सिस्टमसोबत काम करतात. हे नियोजन, सूचना, चेतावणी, प्रमाणीकरणाच्या विविध समस्यांचे निराकरण करते... आम्ही PostgreSQL 10 वापरतो. आमच्याकडे डेटाबेसमध्ये सुमारे 9.6 टेबल्स आहेत आणि दररोज 300 दशलक्ष प्रश्न (200 हजार भिन्न) प्राप्त होतात. आमच्याकडे प्रति सेकंद सरासरी 10-3 हजार विनंत्या आहेत, सर्वात सक्रिय क्षणी प्रति सेकंद 4 हजार पेक्षा जास्त विनंत्या आहेत. बहुतेक प्रश्न OLAP आहेत. त्यात खूप कमी जोडणे, बदल आणि हटवणे आहेत, याचा अर्थ OLTP लोड तुलनेने हलका आहे. मी हे सर्व आकडे दिले आहेत जेणेकरुन तुम्ही आमच्या प्रकल्पाच्या प्रमाणाचे मूल्यांकन करू शकाल आणि आमचा अनुभव तुमच्यासाठी किती उपयुक्त आहे हे समजू शकेल.

चित्र एक. गेय

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

चित्र दोन. सांख्यिकी

त्यामुळे आमच्या डेटाबेसवर दररोज सुमारे 10 हजार वेगवेगळ्या क्वेरी आहेत. या 10 हजारांपैकी, असे राक्षस आहेत जे 2-3 एमएसच्या सरासरी अंमलबजावणी वेळेसह 0.1-0.3 दशलक्ष वेळा कार्यान्वित केले जातात आणि 30 सेकंदांच्या सरासरी अंमलबजावणी वेळेसह क्वेरी आहेत ज्यांना दिवसातून 100 वेळा म्हटले जाते.

सर्व 10 हजार क्वेरी ऑप्टिमाइझ करणे शक्य नव्हते, म्हणून आम्ही डेटाबेसचे कार्यप्रदर्शन योग्यरित्या सुधारण्यासाठी आमचे प्रयत्न कोठे निर्देशित करायचे हे शोधण्याचा निर्णय घेतला. अनेक पुनरावृत्तीनंतर, आम्ही विनंत्या प्रकारांमध्ये विभागण्यास सुरुवात केली.

शीर्ष विनंत्या

या सर्वात जड प्रश्न आहेत ज्यांना सर्वात जास्त वेळ लागतो (एकूण वेळ). या अशा क्वेरी आहेत ज्यांना एकतर वारंवार कॉल केले जाते किंवा क्वेरी ज्यांना अंमलात आणण्यासाठी खूप वेळ लागतो (वेगासाठी लढण्याच्या पहिल्या पुनरावृत्तीमध्ये दीर्घ आणि वारंवार क्वेरी ऑप्टिमाइझ केल्या गेल्या होत्या). परिणामी, सर्व्हर त्यांच्या अंमलबजावणीवर सर्वाधिक वेळ घालवतो. शिवाय, शीर्ष विनंत्या एकूण अंमलबजावणी वेळेनुसार आणि IO वेळेनुसार वेगळे करणे महत्त्वाचे आहे. अशा क्वेरी ऑप्टिमाइझ करण्याच्या पद्धती थोड्या वेगळ्या आहेत.

सर्व कंपन्यांचा नेहमीचा सराव टॉप विनंत्यांसह कार्य करणे आहे. त्यापैकी काही आहेत; अगदी एक क्वेरी ऑप्टिमाइझ केल्याने 5-10% संसाधने मुक्त होऊ शकतात. तथापि, प्रकल्प जसजसा परिपक्व होत जातो, तसतसे टॉप क्वेरी ऑप्टिमाइझ करणे हे एक क्षुल्लक काम बनते. सर्व सोप्या पद्धती आधीच तयार केल्या गेल्या आहेत आणि सर्वात "जड" विनंतीसाठी "केवळ" 3-5% संसाधने लागतात. जर एकूण टॉप प्रश्नांना 30-40% पेक्षा कमी वेळ लागतो, तर बहुधा तुम्ही त्या लवकर कार्यान्वित करण्यासाठी आधीच प्रयत्न केले असतील आणि पुढील गटातील क्वेरी ऑप्टिमाइझ करण्यासाठी पुढे जाण्याची वेळ आली आहे.
या गटात किती शीर्ष क्वेरी समाविष्ट केल्या पाहिजेत या प्रश्नाचे उत्तर देणे बाकी आहे. मी सहसा कमीतकमी 10 घेतो, परंतु 20 पेक्षा जास्त नाही. मी हे सुनिश्चित करण्याचा प्रयत्न करतो की TOP गटातील पहिल्या आणि शेवटच्या वेळेत 10 पेक्षा जास्त वेळा फरक नसावा. म्हणजेच, जर क्वेरी अंमलात आणण्याची वेळ 1ल्या स्थानावरून 10व्या स्थानावर वेगाने घसरली, तर मी TOP-10 घेतो, जर ड्रॉप अधिक हळूहळू असेल, तर मी गट आकार 15 किंवा 20 पर्यंत वाढवतो.

मध्यम शेतकरी

या सर्व विनंत्या आहेत ज्या TOP नंतर लगेच येतात, शेवटचा 5-10% अपवाद वगळता. सहसा, या क्वेरी ऑप्टिमाइझ करताना सर्व्हरची कार्यक्षमता मोठ्या प्रमाणात वाढवण्याची संधी असते. या विनंत्या 80% पर्यंत वजन करू शकतात. परंतु जरी त्यांचा वाटा 50% पेक्षा जास्त झाला असेल, तर त्यांच्याकडे अधिक काळजीपूर्वक पाहण्याची वेळ आली आहे.

शेपूट

नमूद केल्याप्रमाणे, या क्वेरी शेवटी येतात आणि 5-10% वेळ घेतात. जर तुम्ही स्वयंचलित क्वेरी विश्लेषण साधने वापरत नसाल तरच तुम्ही त्यांच्याबद्दल विसरू शकता, नंतर त्यांना ऑप्टिमाइझ करणे देखील स्वस्त असू शकते.

प्रत्येक गटाचे मूल्यमापन कसे करावे?

मी एक SQL क्वेरी वापरतो जी PostgreSQL साठी असे मूल्यांकन करण्यात मदत करते (मला खात्री आहे की इतर अनेक DBMS साठी अशीच क्वेरी लिहिली जाऊ शकते)

TOP-MEDIUM-TAIL गटांच्या आकाराचा अंदाज लावण्यासाठी SQL क्वेरी

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

क्वेरीचा परिणाम तीन स्तंभ आहेत, ज्यापैकी प्रत्येकामध्ये या गटातील क्वेरींवर प्रक्रिया करण्यासाठी लागणारा वेळ आहे. विनंतीमध्ये दोन संख्या आहेत (माझ्या बाबतीत ते 20 आणि 800 आहेत) जे एका गटाकडून दुसऱ्या गटातील विनंत्या वेगळे करतात.

ऑप्टिमायझेशनचे काम सुरू झाले तेव्हाच्या आणि आत्ताच्या वेळी विनंत्यांचे शेअर्स अंदाजे तुलना करतात.

आकृती दर्शवते की शीर्ष विनंत्यांचा वाटा झपाट्याने कमी झाला आहे, परंतु "मध्यम शेतकरी" वाढले आहेत.
सुरुवातीला, शीर्ष विनंत्यांमध्ये निंदनीय चुका समाविष्ट होत्या. कालांतराने, बालपणातील आजार नाहीसे झाले, टॉप विनंत्यांची संख्या कमी झाली आणि कठीण विनंत्यांना गती देण्यासाठी अधिकाधिक प्रयत्न करावे लागले.

विनंत्यांचा मजकूर मिळविण्यासाठी आम्ही खालील विनंती वापरतो

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

येथे सर्वात सामान्यपणे वापरल्या जाणार्‍या तंत्रांची सूची आहे ज्याने आम्हाला शीर्ष क्वेरींचा वेग वाढविण्यात मदत केली:

• सिस्टीमचे रीडिझाइन, उदाहरणार्थ, डेटाबेसमध्ये नियतकालिक क्वेरींऐवजी मेसेज ब्रोकरचा वापर करून नोटिफिकेशन लॉजिकची पुनर्रचना करणे
• निर्देशांक जोडणे किंवा बदलणे
• शुद्ध SQL वर ORM क्वेरी पुन्हा लिहित आहे
• आळशी डेटा लोडिंग तर्क पुन्हा लिहित आहे
• डेटा डिनोर्मलायझेशनद्वारे कॅशिंग. उदाहरणार्थ, आमच्याकडे टेबल कनेक्शन डिलिव्हरी -> इनव्हॉइस -> विनंती -> अॅप्लिकेशन आहे. म्हणजेच, प्रत्येक वितरण इतर सारण्यांद्वारे अनुप्रयोगाशी संबंधित आहे. प्रत्येक विनंतीमध्ये सर्व सारण्यांचा दुवा जोडू नये म्हणून, आम्ही डिलिव्हरी टेबलमधील विनंतीची लिंक डुप्लिकेट केली.
• संदर्भ पुस्तकांसह स्थिर सारण्या कॅश करणे आणि प्रोग्राम मेमरीमध्ये क्वचितच बदलणारे टेबल.

काहीवेळा बदल प्रभावी रीडिझाइनचे होते, परंतु त्यांनी सिस्टम लोडच्या 5-10% प्रदान केले आणि ते न्याय्य होते. कालांतराने, एक्झॉस्ट लहान आणि लहान होत गेला आणि अधिकाधिक गंभीर पुनर्रचना आवश्यक होती.

मग आम्ही आमचे लक्ष विनंत्यांच्या दुसऱ्या गटाकडे वळवले - मध्यम शेतकऱ्यांच्या गटाकडे. त्यात अजून बरेच प्रश्न आहेत आणि संपूर्ण गटाचे विश्लेषण करायला खूप वेळ लागेल असे वाटले. तथापि, बर्‍याच क्वेरी ऑप्टिमाइझ करण्यासाठी अगदी सोप्या होत्या आणि बर्‍याच समस्या वेगवेगळ्या भिन्नतेमध्ये डझनभर वेळा पुनरावृत्ती झाल्या. येथे काही ठराविक ऑप्टिमायझेशनची उदाहरणे आहेत जी आम्ही डझनभर समान क्वेरींवर लागू केली आणि ऑप्टिमाइझ केलेल्या क्वेरींच्या प्रत्येक गटाने डेटाबेस 3-5% ने अनलोड केला.

• COUNT आणि संपूर्ण टेबल स्कॅन वापरून रेकॉर्डची उपस्थिती तपासण्याऐवजी, EXISTS वापरण्यास सुरुवात केली
• DISTINCT पासून सुटका मिळाली (कोणतीही सामान्य कृती नाही, परंतु काहीवेळा आपण विनंतीचा वेग 10-100 वेळा वाढवून सहजपणे यापासून मुक्त होऊ शकता).

  उदाहरणार्थ, डिलिव्हरीच्या मोठ्या टेबलमधून सर्व ड्रायव्हर्स निवडण्यासाठी क्वेरीऐवजी (वितरण)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  तुलनेने लहान टेबल PERSON वर क्वेरी केली

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  असे दिसते की आम्ही सहसंबंधित सबक्वेरी वापरली आहे, परंतु ती 10 पेक्षा जास्त वेळा स्पीडअप देते.

• बर्‍याच प्रकरणांमध्ये, COUNT पूर्णपणे सोडून देण्यात आले होते आणि
  अंदाजे मूल्याच्या गणनेने बदलले
• त्याऐवजी

  UPPER(s) LIKE JOHN%’ 
  

  वापरा

  s ILIKE “John%”
  

प्रत्येक विशिष्ट विनंतीला कधीकधी 3-1000 वेळा गती दिली जाते. प्रभावी कामगिरी असूनही, सुरुवातीला आम्हाला असे वाटले की पूर्ण होण्यासाठी 10 ms लागणाऱ्या क्वेरीला अनुकूल करण्यात काही अर्थ नाही, ती 3र्‍या शंभर सर्वात भारी क्वेरींपैकी एक आहे आणि एकूण डेटाबेस लोड वेळेच्या शंभरावा भाग घेते. पण त्याच प्रकारातील प्रश्नांच्या गटाला तीच रेसिपी लागू करून आम्ही काही टक्के परत मिळवले. सर्व शेकडो प्रश्नांचे मॅन्युअली पुनरावलोकन करण्यात वेळ वाया घालवू नये म्हणून, आम्ही अनेक सोप्या स्क्रिप्ट लिहिल्या ज्या समान प्रकारच्या क्वेरी शोधण्यासाठी नियमित अभिव्यक्ती वापरतात. परिणामस्वरुप, आपोआप क्वेरीचे गट शोधणे आम्हाला माफक प्रयत्नाने आमचे कार्यप्रदर्शन आणखी सुधारण्यास अनुमती देते.

परिणामी, आम्ही तीन वर्षांपासून एकाच हार्डवेअरवर काम करत आहोत. सरासरी दैनिक भार सुमारे 30% आहे, शिखरांमध्ये ते 70% पर्यंत पोहोचते. विनंत्यांची संख्या, तसेच वापरकर्त्यांची संख्या, अंदाजे 10 पट वाढली आहे. आणि हे सर्व TOP-MEDIUM विनंत्यांच्या समान गटांच्या सतत देखरेखीसाठी धन्यवाद. TOP गटामध्ये नवीन विनंती दिसताच, आम्ही त्वरित त्याचे विश्लेषण करतो आणि त्याचा वेग वाढवण्याचा प्रयत्न करतो. आम्ही क्वेरी विश्लेषण स्क्रिप्ट वापरून आठवड्यातून एकदा MEDIUM गटाचे पुनरावलोकन करतो. ऑप्टिमाइझ कसे करायचे हे आम्हाला आधीच माहित असलेल्या नवीन क्वेरी आढळल्यास, आम्ही त्यांना त्वरीत बदलतो. काहीवेळा आम्हाला नवीन ऑप्टिमायझेशन पद्धती सापडतात ज्या एकाच वेळी अनेक प्रश्नांवर लागू केल्या जाऊ शकतात.

आमच्या अंदाजानुसार, वर्तमान सर्व्हर वापरकर्त्यांच्या संख्येत आणखी 3-5 पट वाढ सहन करेल. खरे आहे, आमच्याकडे आमच्या स्लीव्हवर आणखी एक एक्का आहे - आम्ही अद्याप SELECT क्वेरी मिररमध्ये हस्तांतरित केलेल्या नाहीत, जसे की शिफारस केली जाते. परंतु आम्ही हे जाणीवपूर्वक करत नाही, कारण आम्हाला "हेवी तोफखाना" चालू करण्यापूर्वी "स्मार्ट" ऑप्टिमायझेशनची शक्यता पूर्णपणे संपवायची आहे.
केलेल्या कामावर एक गंभीर दृष्टीक्षेप उभ्या स्केलिंगचा वापर सुचवू शकतो. तज्ञांचा वेळ वाया घालवण्याऐवजी अधिक शक्तिशाली सर्व्हर खरेदी करा. सर्व्हरची किंमत कदाचित जास्त नसेल, विशेषत: आम्ही अद्याप उभ्या स्केलिंगची मर्यादा संपलेली नाही. तथापि, केवळ विनंत्यांची संख्या 10 पट वाढली. अनेक वर्षांच्या कालावधीत, सिस्टमची कार्यक्षमता वाढली आहे आणि आता अधिक प्रकारच्या विनंत्या आहेत. कॅशिंगबद्दल धन्यवाद, अस्तित्वात असलेली कार्यक्षमता कमी विनंत्या आणि अधिक कार्यक्षम विनंत्यांमध्ये केली जाते. याचा अर्थ वास्तविक प्रवेग गुणांक मिळविण्यासाठी तुम्ही सुरक्षितपणे आणखी 5 ने गुणाकार करू शकता. तर, सर्वात पुराणमतवादी अंदाजानुसार, आम्ही असे म्हणू शकतो की प्रवेग 50 पट किंवा त्याहून अधिक होता. सर्व्हरला अनुलंब स्विंग करण्यासाठी 50 पट जास्त खर्च येईल. विशेषत: एकदा ऑप्टिमायझेशन केले की ते सर्व वेळ काम करते आणि भाड्याने घेतलेल्या सर्व्हरचे बिल दरमहा येते.

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