ट्यूनिंग डेटाबेस प्रदर्शन - डेवलपर्स आमतौर पर या तो इसे प्यार करते हैं या नफरत करते हैं। मैं इसका आनंद लेता हूं और कुछ ऐसे तरीकों को साझा करना चाहता हूं, जिनका मैंने हाल ही में पोस्टग्रेक्यूएल में खराब निष्पादित प्रश्नों को ट्यून करने के लिए उपयोग किया है। मेरे तरीके थकाऊ नहीं हैं, बल्कि उन लोगों के लिए एक पाठ्यपुस्तक है जो ट्यूनिंग के बारे में बताते हैं।
धीमे प्रश्नों के लिए खोजें
ट्यूनिंग शुरू करने का पहला स्पष्ट तरीका विशिष्ट ऑपरेटरों को ढूंढना है जो खराब काम करते हैं।
pg_stats_statements
Pg_stats_statements मॉड्यूल शुरू करने के लिए एक शानदार जगह है। यह सिर्फ एसक्यूएल स्टेटमेंट्स के निष्पादन के आंकड़ों पर नज़र रखता है और अक्षम प्रश्नों को खोजने का एक आसान तरीका हो सकता है।
एक बार जब आप इस मॉड्यूल को स्थापित कर लेते हैं, तो
pg_stat_statements नामक एक सिस्टम दृश्य इसके सभी गुणों के साथ उपलब्ध होगा। एक बार जब उसके पास पर्याप्त डेटा एकत्र करने का अवसर होता है, तो उन प्रश्नों की तलाश करें जिनके पास अपेक्षाकृत उच्च
total_time मूल्य है
। पहले इन ऑपरेटरों पर ध्यान दें।
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
auto_explain
Auto_explain मॉड्यूल धीमे प्रश्नों को खोजने के लिए भी उपयोगी है, लेकिन इसके 2 स्पष्ट फायदे हैं: यह वास्तविक निष्पादन योजना को पंजीकृत करता है और
log_nested_statements विकल्प का उपयोग करके नेस्टेड स्टेटमेंट रिकॉर्ड करने का समर्थन करता है। नेस्टेड स्टेटमेंट एक स्टेटमेंट होते हैं जो किसी फंक्शन के अंदर निष्पादित होते हैं। यदि आपका एप्लिकेशन कई सुविधाओं का उपयोग करता है, तो विस्तृत निष्पादन योजना प्राप्त करने के लिए auto_explain अमूल्य है।
Log_min_duration विकल्प नियंत्रित करता है कि कौन सी क्वेरी निष्पादन योजनाएँ कितनी देर तक चलती हैं, उसके आधार पर लॉग की जाती हैं। उदाहरण के लिए, यदि आप मूल्य को 1000 पर सेट करते हैं, तो 1 सेकंड से अधिक समय लेने वाले सभी रिकॉर्ड पंजीकृत होंगे।
सूचकांक ट्यूनिंग
एक और महत्वपूर्ण ट्यूनिंग रणनीति यह सुनिश्चित करना है कि इंडेक्स का सही तरीके से उपयोग किया जाए। एक शर्त के रूप में, हमें सांख्यिकी कलेक्टर को शामिल करना होगा।
पोस्टग्रैट्स स्टैटिस्टिक्स कलेक्टर एक प्रथम श्रेणी का सबसिस्टम है जो सभी प्रकार के उपयोगी प्रदर्शन आँकड़े एकत्र करता है।
इस संग्राहक को सक्षम करने से, आपको
pg_stat _... के विचार मिलते हैं जिसमें सभी गुण सम्मिलित हैं। विशेष रूप से, मैंने इसे लापता और अप्रयुक्त इंडेक्स खोजने के लिए विशेष रूप से उपयोगी पाया।
अनुक्रमणिका गुम है
क्वेरी इंडेक्स को बेहतर बनाने के लिए मिसिंग इंडेक्स सबसे आसान समाधानों में से एक हो सकता है। हालांकि, वे एक चांदी की गोली नहीं हैं और इसे सही तरीके से इस्तेमाल किया जाना चाहिए (बाद में इस पर अधिक)। यदि आपके पास सांख्यिकी संग्राहक सक्षम है, तो आप निम्नलिखित क्वेरी (
स्रोत ) चला सकते हैं।
SELECT relname, seq_scan - idx_scan AS too_much_seq, CASE WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC;
क्वेरी में ऐसे टेबल मिलते हैं जिनमें इंडेक्स स्कैन की तुलना में अधिक अनुक्रमिक स्कैन (सूचकांक स्कैन) होते हैं - एक स्पष्ट संकेत है कि सूचकांक मदद करेगा। यह आपको यह नहीं बताएगा कि इंडेक्स बनाने के लिए कौन सा कॉलम है, इसलिए यह थोड़ा और काम करेगा। हालांकि, यह जानना कि उन्हें किन तालिकाओं की जरूरत है, यह एक अच्छा पहला कदम है।
अप्रयुक्त सूचकांक
सूचकांक सभी संस्थाओं, सही? क्या आप जानते हैं कि अप्रयुक्त इंडेक्स लेखन प्रदर्शन पर प्रतिकूल प्रभाव डाल सकते हैं? कारण यह है कि पोस्टग्रैज इंडेक्स बनाते समय, यह राइट्स (INSERT / UPDATE / DELETE) लिखने के बाद इस इंडेक्स को अपडेट करने के कार्य पर बोझ होता है। इस प्रकार, एक इंडेक्स जोड़ना एक संतुलन अधिनियम है, क्योंकि यह डेटा की रीडिंग को तेज कर सकता है (यदि इसे सही तरीके से बनाया गया था), लेकिन यह लिखने के संचालन को धीमा कर देगा। अप्रयुक्त इंडेक्स खोजने के लिए, आप निम्नलिखित क्वेरी चला सकते हैं।
SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false;
विकास के पर्यावरण संबंधी आंकड़ों पर ध्यान दें
एक स्थानीय विकास डेटाबेस से आंकड़ों पर भरोसा समस्याग्रस्त हो सकता है। आदर्श रूप से, आप अपने काम करने की मशीन से उपरोक्त आँकड़े प्राप्त कर सकते हैं या इसे एक काम कर रहे बैकअप से उत्पन्न कर सकते हैं। क्यों? पर्यावरणीय कारक Postgres क्वेरी ऑप्टिमाइज़र के व्यवहार को बदल सकते हैं। दो उदाहरण:
- जब मशीन में मेमोरी कम होती है, तो हो सकता है कि PostgreSQL एक Hash Join नहीं कर पाए, अन्यथा वह इसे तेजी से कर सकता है।
- यदि तालिका में (विकास डेटाबेस में) कई पंक्तियाँ नहीं हैं, तो PostgresSQL उपलब्ध सूचकांक का उपयोग करने के बजाय तालिका के अनुक्रमिक स्कैन को करना पसंद कर सकता है। जब टेबल का आकार छोटा होता है, तो Seq Scan तेज हो सकता है। (नोट: आप चला सकते हैं
SET enable_seqscan = OFF
एक सत्र में ताकि अनुकूलक अनुक्रमणिका का उपयोग करना चुनता है, भले ही अनुक्रमिक स्कैन तेजी से हो सकते हैं। विकास डेटाबेस के साथ काम करते समय यह उपयोगी होता है जिसमें बहुत अधिक डेटा नहीं होता है)
निष्पादन योजनाओं को समझना
अब जब आपको कुछ धीमी क्वेरी मिल गई हैं, तो मजा शुरू करने का समय आ गया है।
व्याख्या
प्रश्न सेट करते समय
EXPLAIN कमांड आवश्यक है। वह बताता है कि वास्तव में क्या हो रहा है। इसका उपयोग करने के लिए, केवल क्वेरी में
EXPLAIN जोड़ें और इसे चलाएं। PostgreSQL आपको निष्पादन योजना दिखाएगा जो इसका उपयोग करता था।
ट्यूनिंग के लिए EXPLAIN का उपयोग करते समय, मैं हमेशा
ANALYZE विकल्प (
EXPLAIN ANALYZE ) का उपयोग करने की सलाह देता हूं, क्योंकि यह आपको अधिक सटीक परिणाम देता है। ANALYZE विकल्प वास्तव में कथन को निष्पादित करता है (केवल इसका मूल्यांकन करने के बजाय), और फिर उसे समझाता है।
चलो एक डुबकी लेते हैं और
EXPLAIN के आउटपुट को समझना शुरू करते हैं। यहाँ एक उदाहरण है:
समुद्री मील
समझने वाली पहली बात यह है कि पिछले "->" (शीर्ष पंक्ति के साथ) के साथ प्रत्येक इंडेंट ब्लॉक को नोड कहा जाता है। एक नोड काम की एक तार्किक इकाई है (एक "कदम", यदि आप चाहें) संबद्ध लागत और लीड समय के साथ। प्रत्येक नोड पर प्रस्तुत लागत और समय संचयी हैं और सभी बच्चे नोड्स को एक साथ लाते हैं। इसका मतलब है कि सबसे ऊपरी पंक्ति (नोड) कुल लागत और पूरे ऑपरेटर के लिए वास्तविक समय दिखाती है। यह महत्वपूर्ण है क्योंकि आप आसानी से यह निर्धारित करने के लिए नीचे ड्रिल कर सकते हैं कि कौन से नोडल अड़चन हैं।
की लागत
cost=146.63..148.65
पहली संख्या प्रारंभिक लागत (पहला रिकॉर्ड प्राप्त करने की लागत) है, और दूसरी संख्या संपूर्ण नोड को संसाधित करने की लागत है (कुल लागत शुरू से अंत तक)।
वास्तव में, यह लागत है कि पोस्टग्रेक्यूएल के अनुमानों को कथन को निष्पादित करने के लिए पूरा करना होगा। इस संख्या का मतलब यह नहीं है कि अनुरोध को पूरा करने में कितना समय लगेगा, हालांकि आमतौर पर पूरा करने के लिए प्रत्यक्ष संबंध की आवश्यकता होती है। लागत आवश्यक कार्य का मूल्यांकन करने के लिए उपयोग किए जाने वाले 5 कामकाजी घटकों का एक संयोजन है: अनुक्रमिक नमूनाकरण, असंगत (यादृच्छिक) नमूनाकरण, पंक्ति प्रसंस्करण, प्रसंस्करण ऑपरेटर (फ़ंक्शन) और प्रसंस्करण सूचकांक की रिकॉर्डिंग। लागत इनपुट / आउटपुट और प्रोसेसर लोड है, और यह जानना महत्वपूर्ण है कि अपेक्षाकृत उच्च लागत का मतलब है कि PostgresSQL का मानना है कि इसे और अधिक काम करना होगा। आशावादी निर्णय लेता है कि लागत के आधार पर किस निष्पादन योजना का उपयोग करना है। आशावादी कम लागत पसंद करता है।
वास्तविक समय
actual time=55.009..55.012
मिलीसेकंड में, पहली संख्या प्रारंभ समय (पहला रिकॉर्ड प्राप्त करने का समय) है, और दूसरी संख्या पूरे नोड को संसाधित करने के लिए आवश्यक समय है (कुल समय शुरू से अंत तक)। समझने में आसान है, है ना?
उपरोक्त उदाहरण में, पहला नोड प्राप्त करने के लिए 55.009 एमएस लिया और पूरे नोड को पूरा करने के लिए 55.012 एमएस।
निष्पादन योजनाओं के बारे में अधिक जानें।
परिणाम समझने के लिए कुछ बहुत अच्छे लेख हैं। मैं उन्हें यहाँ से हटाने की कोशिश करने के बजाय, इन 2 अद्भुत संसाधनों पर जाकर वास्तव में उन्हें समझने के लिए समय निकालने की सलाह देता हूँ:
ट्यूनिंग का अनुरोध करें
अब जब आप जानते हैं कि कौन से ऑपरेटर खराब तरीके से काम कर रहे हैं और आपकी निष्पादन योजनाओं को देख सकते हैं, तो प्रदर्शन में सुधार के लिए अपनी क्वेरी को शुरू करने का समय आ गया है। यहां आप एक बेहतर निष्पादन योजना प्राप्त करने के लिए अपने प्रश्नों और / या अनुक्रमणिका में परिवर्तन करते हैं। अड़चनों से शुरू करें और देखें कि क्या कोई बदलाव है जिससे आप लागत और / या लीड समय को कम कर सकते हैं।
डेटा कैश और लागत नोट
कार्यान्वयन योजनाओं में परिवर्तन और मूल्यांकन करते समय, यह देखने के लिए कि क्या सुधार होगा, यह जानना महत्वपूर्ण है कि भविष्य के कार्यान्वयन कैशिंग डेटा पर निर्भर हो सकते हैं जो सर्वोत्तम परिणामों का विचार देता है। यदि आप एक बार अनुरोध चलाते हैं, तो सुधार करें और इसे दूसरी बार चलाएं, सबसे अधिक संभावना है कि यह बहुत तेज़ी से चलेगा, भले ही निष्पादन योजना अधिक अनुकूल न हो। ऐसा इसलिए है क्योंकि PostgreSQL पहली शुरुआत में उपयोग किए गए डेटा को कैश कर सकता है, और दूसरी शुरुआत में इसका उपयोग कर सकता है। इसलिए, आपको कम से कम 3 बार प्रश्नों को पूरा करना होगा और लागतों की तुलना करने के लिए परिणामों को औसत करना होगा।
मेरे द्वारा सीखी गई चीजें निष्पादन योजनाओं को बेहतर बनाने में मदद कर सकती हैं:
- सूचकांक
- अनुक्रमित जोड़कर अनुक्रमिक स्कैनिंग (सीक स्कैन) को बाहर करें (यदि तालिका का आकार छोटा नहीं है)
- मल्टी-कॉलम इंडेक्स का उपयोग करते समय, सुनिश्चित करें कि आप उस क्रम पर ध्यान देते हैं जिसमें आप शामिल कॉलम को परिभाषित करते हैं - अधिक जानकारी
- उन अनुक्रमित को आज़माएं जो अक्सर उपयोग किए जाने वाले डेटा के लिए बहुत चयनात्मक होते हैं। इससे उनका उपयोग अधिक कुशल हो जाएगा।
- हालत कहां
- LIKE से बचें
- WHERE क्लॉज में फ़ंक्शन कॉल से बचें
- () में बड़ी स्थितियों से बचें
- JOINy
- तालिकाओं में शामिल होने पर, ऑन क्लॉज (यानी a.id = b.person_id) में एक साधारण समानता अभिव्यक्ति का उपयोग करने का प्रयास करें। यह आपको अधिक कुशल जुड़ने के तरीकों का उपयोग करने की अनुमति देता है (यानी, हैश जॉइन, नॉटेड लूप जॉइन)
- जब संभव हो तो जिन्न के बयानों में उपश्रेणियों को रूपांतरित करें, क्योंकि यह आमतौर पर अनुकूलक को लक्ष्य को समझने और संभवतः सबसे अच्छी योजना चुनने की अनुमति देता है।
- COMPOUNDS का उपयोग सही तरीके से करें: क्या आप डुप्लिकेट परिणाम प्राप्त करने के लिए केवल ग्रुप BY या DISTINCT का उपयोग करते हैं? यह आमतौर पर JOINs के अनुचित उपयोग को इंगित करता है और इसके परिणामस्वरूप उच्च लागत हो सकती है।
- यदि निष्पादन योजना हाश जॉइन का उपयोग करती है, तो यह बहुत धीमी हो सकती है यदि तालिका आकार का अनुमान गलत है। इसलिए, सुनिश्चित करें कि वैक्यूमिंग रणनीति की समीक्षा करके आपकी तालिका के आंकड़े सटीक हैं ।
- जब भी संभव हो सहसंबंधित उप-क्षेत्रों से बचें; वे एक अनुरोध की लागत में काफी वृद्धि कर सकते हैं
- एक कसौटी पर आधारित तारों के अस्तित्व की जाँच करते समय EXISTS का उपयोग करें, क्योंकि यह एक शॉर्ट सर्किट के समान है (यह कम से कम एक मैच मिलने पर प्रोसेसिंग रोक देता है)
- सामान्य सिफारिशें