Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Code
  2. MySQL
Code

MySQL की बिजली सी तेज query लिखना

by
Difficulty:IntermediateLength:LongLanguages:

Hindi (हिंदी) translation by Ashish Rampal (you can also view the original English article)

अच्छी तरह से लिखे SQL और नहीं लिखे SQL में बहुत बड़ा अंतर है, और एक ऊँची मांग वाली साइट के प्रोडक्शन में यह उसकी कार्यक्षमता और विश्वसनीयता पर गंभीर असर डालता है। इस गाइड में मैं तेज query कैसे लिखें और क्या कारक उन्हें धीमी गति से रन बनाने में योगदान देते हैं पर चर्चा करेंगे।

MySQL ही क्यों?

आज एक बहुत बड़ा डेटा और नई टेक्नोलॉजी के बारे में बात जा रही है। NoSQL और क्लाउड-आधारित समाधान बहुत अच्छे हैं, लेकिन बहुत लोकप्रिय वेब सॉफ्टवेयर (जैसे कि WordPress, phpBB, Drupal, VBulletin फोरम सॉफ्टवेयर, आदि) पर MySQL अभी भी चलता है। इन नए समाधानों का माइग्रेशन इतना सरल नहीं है जैसे की आपके उत्पादन में ऑप्टिमिज़िंग (optimizing) और कॉन्फ़िगरेशन (configuration) आपके पास पहले ही है.। इसके अलावा, MySQL का प्रदर्शन बहुत अच्छा है, विशेष रूप से Percona का संस्करण

धीमी क्वेरी और उच्च सर्वर लोड की समस्या से निपटने के लिए अधिक से अधिक कंप्यूटिंग शक्ति लगाने की common गलती मत करना बजाय इसके वास्तव समस्याओं के मूल अंतर्निहित मुद्दों (root underlying) को पता लगाएं। CPU शक्ति जोड़ने, SSDs या RAM ऑप्टिमाइजेशन का एक रूप है अगर आप चाहे, लेकिन यह वो नहीं है जिसके बारे में मैं यहाँ बात कर रहा हूँ। साथ ही, एक अनुकूलित साइट के बिना, जैसे जैसे आप हार्डवेयर के साथ बढ़ते हैं तो समस्याएं भी उसे तेजी से बढ़ेगी। तो यह एक ठोस लंबी अवधि का समाधान नहीं है।

SQL में अच्छा होना एक वेब डेवलपर के लिए हमेशा एक महत्वपूर्ण उपकरण है, और इसे ठीक करना अक्सर वैसा ही है जैसे इंडेक्स को जोड़ना या थोड़ा सा मॉडिफाई करना की कैसे टेबल का प्रयोग किया जा रहा है, यह सच में अच्छी तरह से अपने RDBMS का उपयोग करने के तरीको का पता करने में मदद करता है। इस केस में हम एक मशहूर ओपन-सोर्स डेटाबेस पर फोकस करेंगे जो की अक्सर PHP के साथ प्रयोग किया जाता है, और वह है MySQL।

यह गाइड किसके लिए है?

वेब डेवलपर्स, डेटाबेस आर्किटेक्ट / DBAs और सिस्टम व्यवस्थापक (Administrators) जो की MySQL के साथ परिचित हैं। यदि आप एक नौसिखिया के रूप में MySQL के साथ परिचित नहीं हैं, तो यह गाइड संभवतः अधिक समझ बनाने के लिए नहीं है, लेकिन मैं यह MySQL के नए उपयोगकर्ताओं के लिए संभवतः पूरी जानकारीपूर्ण रखने की कोशिश करूँगा।

सबसे पहले Back Up

मैं रेकमेंड करता हूँ की दिए गए स्टेप्स को अपने खुद के MySQL डेटाबेस में चला कर देखें (निश्चित रूप से पहले सब कुछ का बैकअप ले लें!)। यदि आपके पास काम करने के लिए कोई डाटाबेस नहीं है, डेटाबेस schemas को क्रिएट करने के उदाहरण दिए गए हैं जहां लागू हो।

MYSQL का बैकअप करना mysqldump कमांड लाइन यूटिलिटी के साथ आसान है:

आप mysqldump के बारे में और भी जान सकते हैं.

Query को क्या धीमा बनाता है?

संक्षेप में और महत्व के किसी भी क्रम में, निम्नलिखित सभी क्वेरी और सर्वर प्रदर्शन में महत्वपूर्ण कारक को प्ले करते हैं:

  • टेबल के indexes
  • Where क्लॉज़ (और आंतरिक MySQL फ़ंक्शंस का उपयोग जैसे IF और DATE उदाहरण के लिए)
  • Order By के साथ श्रेणीबद्ध (sorting) करना
  • समवर्ती (concurrent) request के आवृति (frequency)
  • storage engine type (InnoDB, MyISAM, Memory, Blackhole)
  • Percona संस्करण (edition) का उपयोग नहीं
  • सर्वर कॉन्फ़िगरेशन वेरिएबल्स (my.cnf / my.ini की ट्यूनिंग)
  • बड़े रिजल्ट सेट्स (>1,000 rows)
  • non-persistent कनेक्शन
  • sharding / cluster की configuration
  • घटिया टेबल के डिज़ाइन

हम इस गाइड के भीतर इन सभी क्षेत्रों को संबोधित करेंगे। इसके अलावा, यदि आप पहले से ही इसका उपयोग नहीं कर रहे हैं, तो कृपया Percona इनस्टॉल करें, जो कि एक MySQL के लिए ड्रॉप-इन प्रतिस्थापन है जो एक गंभीर प्रदर्शन वृद्धि को लाएगा। Percona बनाम MySQL का बेंचमार्क देखने के लिए, इस तुलना को देखें।

Indexes क्या हैं?

इन्डेक्सेस MySQL द्वारा विशिष्ट कॉलम वैल्यूज के साथ rows को जल्दी से खोजने के लिए उपयोग किया जाता है, उदाहरण के लिए एक WHERE के अंदर। एक इंडेक्स के बिना, MySQL को पहली row से शुरू करता है और फिर प्रासंगिक rows को खोजने के लिए संपूर्ण तालिका के माध्यम से पड़ता है। जितनी बड़ी टेबल होगी, उतनी ही कीमत चुकानी होगी.

अगर टेबल में question में कॉलम के लिए एक इंडेक्स है, तो MySQL डेटा के मध्य में सभी आंकड़ों को देखने के बिना ढूंढने की स्थिति को जल्दी से निर्धारित (quickly determine) कर सकता है। यह सभी rows को एक एक करके पड़ने के मुकाबले काफी तेज है।

Non-Persistent Connections?

जब आपकी स्क्रिप्टिंग भाषा डेटाबेस से जुड़ती है, यदि आपने पर्सिस्ट कनेक्शन कॉन्फ़िगर किया है तो यह एक नया कनेक्शन बनाने के बिना मौजूदा कनेक्शन का पुन: उपयोग करने में सक्षम होगा। यह उत्पादन उपयोग (production usage) के लिए अनुकूल है और इनेबल होना चाहिए।

PHP यूज़र्स PHP मैनुअल में अधिक पढ़ सकते हैं।

कंकररेंट रिक्वेस्ट्स (Concurrent Requests) की आवृत्ति (Frequency) को कम करना

मुझे इसे ठीक करने के लिए सबसे तेज़, सबसे प्रभावशाली तरीका जो मुझे मिला है वो है की-वैल्यू पेअर स्टोर को यूटीलाइज़ करना जैसे की Memcached या Redis

Memcache के साथ आप बस अपने क्वेरी कंटेंट (query content) को निम्नलिखित के साथ कैश (cache) कर सकते हैं, उदाहरण के लिए:

अब उदाहरण LEFT JOIN क्वेरी केवल प्रत्येक 86,400 सेकेंड (24 घंटों) में चलायी जायेगी, इस प्रकार MySQL सर्वर से बहुत अधिक लोड दूर ले जाएगा और कंकररेंट कनेक्शन को कम किया जाएगा।

नोट: परसिस्टेंट कनेक्शन के लिए MySQLi में अपने होस्ट आर्गुमेंट में p: प्रेपेंड करें।

Sharding / Clustering

जब आपके डेटा बड़ा हो जाता है या आपकी सर्विस रैंप के लिए मांग होती है, तो पैनिक में सेट हो सकता है। आपकी सेवा को ऑनलाइन रहना सुनिश्चित करने के लिए एक क्विक फिक्स sharding हो सकता है। लेकिन मैं यह रेकमेंड नहीं करता, क्योंकि आंतरिक रूप से ढंका डेटा स्ट्रक्चर को अत्यधिक जटिल बनाने लगता है। और जैसा कि Percona ब्लॉग से इस आर्टिकल में बहुत स्पष्ट रूप से समझाया गया है, डरो मत

घटिया टेबल का डिज़ाइन

जब आप कुछ गोल्डन नियमों को स्वीकार करते हैं, जैसे कि सीमाओं के साथ काम करना और कुशल बनाने के लिए जागृत होना, तो डेटाबेस स्कीमा बनाना बहुत मुश्किल नहीं है। blob डेटा टाइप के रूप में डेटाबेस में इमेजेज को स्टोर करना, उदाहरण के लिए, अत्यधिक हतोत्साहित करने लायक है; varchar डेटाटाइप कॉलम में एक फ़ाइल नाम स्टोर करना कहीं बेहतर है।

यह सुनिश्चित करना कि आवश्यक उपयोग के लिए डिजाइन सही है आपका ऐप बनाने में सर्वोपरि है। विशिष्ट डेटा को अलग रखें (उदाहरण केटेगरी और पोस्ट) और पक्का करें की वन-टू-मेनी या मैनी-टू-वन रिलेशनशिप को आसानी से ID से जोड़ा जा सकता है। MySQL की FOREIGN KEY सुविधा का उपयोग टेबल्स के बीच व्यापक (cascading) डेटा आकस्मिकता (contingency) के लिए आदर्श है।

अपने टेबल को बनाते समय, निम्नलिखित बातों को याद रखने की कोशिश करें:

  • काम पूरा करने के लिए अपनी आवश्यकता के अनुसार कम से कम का प्रयोग करें; विरल बनें और लक्ष्य पर रहे।
  • अपने बिज़नेस लॉजिक को करने या प्रोग्रामैटिक होने के लिए MySQL से अपेक्षा न करें- वह आपकी स्क्रिप्टिंग भाषा के शामिल होने से पहले वास्तव में किया जाना चाहिए। उदाहरण के लिए, यदि आपको एक सूची को यादृच्छिक बनाने की आवश्यकता है, तो PHP में एक सरणी के रैंडिसाइज़ेशन को, MySQL द्वारा ORDER BY में नहीं।
  • यूनिक डेटासेट के लिए एक UNIQUE इंडेक्स टाइप का उपयोग करें और एक datetime या unix timestamp को अपडेट करने के लिए ON DUPLICATE KEY UPDATE का उपयोग जैसे पिछली बार row की जांच के दौरान किया गया था।
  • Integer न्यूमेरिकल के लिए INT का प्रयोग करें. यदि आप लेंथ निर्दिष्ट नहीं करते हैं, तो MySQL गणना करेगा कि इसके स्वयं की क्या आवश्यक है।

ऑप्टिमाइजेशन का फंडामेंटल

प्रभावी रूप से ऑप्टिमाइज़ करने के लिए, हमें आपके एप्लीकेशन के बारे में तीन मूलभूत डेटा सेटों को देखना होगा:

  1. विश्लेषण (स्लो क्वेरी लॉगिंग, ऑडिटिंग, क्वेरी और टेबल डिजाइन विश्लेषण)
  2. परफॉरमेंस की आवश्यकताएं (कितने यूज़र्स हैं, मांग क्या है)
  3. टेक्नोलॉजी का कॉन्सट्रेंट (हार्डवेयर की गति, बहुत अधिक MySQL से पूछना)

विश्लेषण को कईं तरह से किया जा सकता है। सबसे पहले हम MySQL क्वेरीज़ के बोनट के नीचे देखने के लिए सबसे डायरेक्ट रूट लेंगे। ऑप्टिमाइजेशन टूलबॉक्स में सबसे पहला टूल है EXPLAINSELECT से पहले आपकी क्वेरी में इसका उपयोग करना आपको निम्न आउटपुट देगा:

कॉलम को प्रत्येक क्वेरी को एक्सेक्यूट होने के बारे में उपयोगी जानकारी मिलती है। वो कॉलम जिसपर आपको बहुत ध्यान देने की जरूरत है वह है possible_keys और EXTRA

possible_keys इंडेक्स को प्रदर्शित करेगा कि MySQL इंजन क्वेरी के लिए उपयोग करने के लिए उपलब्ध है। कभी-कभी आपको यह सुनिश्चित करने के लिए इंडेक्स को बल देना होगा कि क्वेरी को सबसे तेज तरीके से एक्सेक्यूट किया गया है।

EXTRA कॉलम दिखाएगा कि क्या एक कंडीशनल WHERE या ORDER BY इस्तेमाल किया गया था। नोट करने के लिए सबसे महत्वपूर्ण यह है कि Using Filesort प्रकट होता है। निम्नलिखित उदाहरण पर विचार करें:

इस प्रकार की क्वेरी कंडीशनल की वजह से डिस्क पर पहुंच सकती है, जो कि हो रही है अगर हम EXPLAIN को देखें:

तो इस क्वेरी में दो इंडेक्सेस का उपयोग करने की संभावना है और वर्तमान में यह Extra में Using filesort के कारण डिस्क को हिट कर रहा है।

Using Filesort का क्या उपयोग MySQL मैनुअल से किया गया है:

"सॉर्टेड आर्डर में rows को कैसे प्राप्त करना है, यह जानने के लिए, MySQL को एक अतिरिक्त पास करना होगा। सॉर्ट पूरा हो गया जॉइन टाइप के अनुसार सभी rows पर जाकर और सॉर्ट key और row के pointer को सभी प्रकार की rows के लिए संग्रहित करके किया जाता है जो कि WHERE क्लॉज से मेल खाते हैं। keys तब सॉर्ट कर दी जाती है और rows को सॉर्टेड आर्डर में प्राप्त किया जाता है."

यह अतिरिक्त पास आपके ऐप को धीमा कर देगा और इसे हर कीमत पर बचा जाना चाहिए। बचने के लिए एक और महत्वपूर्ण अतिरिक्त Extra रिजल्ट Using temporary का उपयोग करना है, जिसका अर्थ है कि MySQL को क्वेरी के लिए एक अस्थायी टेबल बनाना था। जाहिर है यह MySQL का एक छुपा हुआ उपयोग है और इससे किसी भी हालत में बचा जाना चाहिए, जब तक कि आप डेटा आवश्यकताओं के कारण किसी और को ऑप्टिमाइज़ नहीं कर सकते। इस मामले में क्वेरी को Redis या Memcache में कैश किया जाना चाहिए और यूज़र्स द्वारा नहीं चलाया जाना चाहिए।

Using Filesort में समस्या को ठीक करने के लिए हमें यह सुनिश्चित करना होगा कि MySQL एक INDEX का उपयोग करता है। इसमें से चुनने के लिए कई possible_keys हैं, लेकिन MySQL अंतिम क्वेरी में केवल एक इंडेक्स का उपयोग कर सकता है। हालांकि इंडेक्स कई कॉलम के कंपोजिट हो सकते हैं, इनवर्स true नहीं है, यद्यपि आप MySQL ओप्टिमिज़र को संकेत प्रदान कर सकते हैं कि आपने कौन सा इंडेक्स बनाए हैं।

इंडेक्स के हिंट्स

MySQL ओप्टिमिज़र क्वेरी के दायरे के लिए सबसे अच्छा इंडेक्स चुनने के लिए क्वेरीज़ 'टेबल्स के आधार पर आंकड़े का उपयोग करेगा। यह अपने अंतर्निर्मित ओप्टिमिज़र के सांख्यिकीय (statistical) लॉजिक पर आधारित है, हालांकि कई ऑप्शंस के साथ यह हमेशा हिंटिंग के बिना सही नहीं हो सकता। यह सुनिश्चित करने के लिए कि सही key का प्रयोग (या न प्रयोग किया जाता है), आपकी क्वेरी में FORCE INDEX, USE INDEX और IGNORE INDEX कीवर्ड का उपयोग करें। आप इंडेक्स हिंटिंग के बारे में MySQL मैन्युअल में और भी पढ़ सकते हैं।

टेबल की keys को देखने के लिए, इस कमांड का प्रयोग करें SHOW INDEX

आप ओप्टिमिज़र का प्रयोग करने के लिए एक से अधिक हिंट्स को उल्लेखित (specify) कर सकते हैं, उदाहरण के लिए:

अब जब MySQL का उपयोग करने के लिए तालिका से index_status है, तो क्वेरी तय हो गई है।

EXPLAIN के साथ-साथ DESCRIBE कीवर्ड है। DESCRIBE के साथ आप निम्न तालिका की जानकारी देख सकते हैं:

इंडेक्स जोड़ना

आप CREATE INDEX सिंटेक्स के साथ MySQL में इंडेक्स बनाते हैं। इंडेक्स के कुछ फ्लेवर हैं। FULLTEXT का उपयोग full-text सर्च प्रयोजनों के लिए किया जाता है, और फिर यह सुनिश्चित करने के लिए UNIQUE टाइप है कि डेटा को यूनिक रखा जाता है।

अपनी table में कोई index जोड़ने के लिए, उदाहरण के लिए निम्न सिंटैक्स का उपयोग करें:

यह टेबल users पर एक इंडेक्स बनाएगा, जो username कॉलम के पहले 10 अक्षरों का उपयोग करेगा, जो एक varchar डेटा प्रकार है।

इस मामले में, पहले 10 अक्षरों में होने वाले मैच के साथ username पर WHERE सॉर्ट की आवश्यकता वाले किसी भी लुकअप को संपूर्ण टेबल के लुकअप के समान ही होगा।

कम्पोजिट इंडेक्स

इंडेक्स होने पर क्वेरी डेटा को वापस करने के लिए गति पर बहुत बड़ा प्रभाव होता है। बस एक प्राथमिक key और अनूठे इंडेक्स सेट करना आम तौर पर पर्याप्त नहीं है- कम्पोजिट key हैं, जहां असली ट्यूनिंग niche MySQL में है, और प्रायः यह EXPLAIN के साथ कुछ A/B चेक की आवश्यकता है।

उदाहरण के लिए, अगर हमें दो कॉलम को WHERE कंडीशन में संदर्भित करने की आवश्यकता है, तो कम्पोजिट key आदर्श होगी।

यहां ये key username पर पूर्व उदाहरण और कॉलम active पर बनाई जा रही है, एक ENUM डेटा प्रकार जो कि यूजर अकाउंट एक्टिव है या नहीं। तो अब जब WHERE के लिए डाटा को क्वेरी किया जाता है की username कहाँ valid है और account active = 1 है, डेटासेट अब इसे बेहतर ढंग से संभालने के लिए ऑप्टिमाइज़ किया गया है।

आपकी MySQL कितनी फास्ट है?

अपनी MySQL क्वेरीज़ पर एक करीब से देखने के लिए प्रोफाइलिंग इनेबल करें। यह set profiling = 1 के द्वारा रन टाइम पर किया जा सकता है, और फिर आपकी क्वेरी एक्सेक्यूट कर रहा है और show profiles के परिणाम को देख रहा है।

PDO के साथ ये कोड का स्निपेट है जो कि ऐसा करता है:

यदि आप PDO का उपयोग नहीं कर रहे हैं, तो उसी के रूप में mysqli के साथ किया जा सकता है:

यह आपके लिए प्रोफाइलिंग डेटा लौटाएगा, जिसमें असोसिएटिव एरे के दूसरी वैल्यू में एक्सेक्यूशन समय शामिल होगा:

क्वेरी पूरा होने के लिए 0.00024300 सेकंड्स लेगी। उस बारे में चिंतित नहीं होने के लिए काफी तेज़ है। लेकिन जब संख्याएं ऊपर जाती हैं, तो हमें गहरी नज़र रखना चाहिए।

काम करते उदाहरण के रूप में, अपने ऐप को जानना। आपके एप्लिकेशन के डेटाबेस कांस्टेंट लेयर/फ्रेमवर्क डेटाबेस ड्राइवर में DEBUG निरंतर के लिए एक चेक रखें, और फिर आप एक प्रोफ़ाइल केस को सक्षम करके और var_dump / print_r के साथ परिणाम को आउटपुट करके ऑडिटिंग शुरू कर सकते हैं। अब आप आसानी से अपने वेबसाइट के पेजेज को ब्राउज़ और प्रोफाइल करने में सक्षम होंगे!

पूरी तरह से आपके ऐप को ऑडिट करना

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

MySQL संस्करण 5.1.6 में लॉगिंग को इनेबल करने के लिए, आप ग्लोबल log_slow_queries का उपयोग कर सकते हैं और slow_query_log_file ग्लोबल फ़ाइल के साथ निर्दिष्ट कर सकते हैं। इसे रनटाइम प्रॉम्प्ट में ऐसा किया जा सकता है:

आप इसे अपने सर्वर के लिए /etc/my.cnf या my.ini कॉन्फ़िगरेशन फाइल में लगातार सेट कर सकते हैं।

यह परिवर्तन करने के बाद, आपको MySQL सर्वर को पुनरारंभ करना होगा, उदा. लिनक्स सिस्टम पर service mysql restart

नए MySQL 5.6.1 में, log_slow_queries डेप्रिकॉटेड और slow_query_log का उपयोग इसके बजाय किया जाता है। TABLE को इनेबल करने के रूप में आउटपुट प्रकार बहुत अच्छे डिबगिंग अनुभव की अनुमति देता है और MySQL 5.6.1 और बाद में निम्नानुसार किया जा सकता है:

long_query_time धीमी क्वेरी के रूप में वर्गीकृत सेकंडों की संख्या निर्दिष्ट करता है। डिफ़ॉल्ट 10 और न्यूनतम 0 है। यह एक फ्लोट निर्दिष्ट करके मिलीसेकेंड वैल्यू ले सकता है; यहां मैंने इसे 1 सेकेंड में सेट किया है। इसलिए 1 सेकंड से अधिक समय लगने वाला कोई भी क्वेरी TABLE आउटपुट फॉर्मेट में लॉग इन होने जा रहा है।

यह MySQL के भीतर mysql.slow_log और mysql.general_log टेबल्स में प्रवेश करेगा।

लॉगिंग डिसेबल करने के लिए, log_output को NONE पर सेट करें।

log_queries_not_using_indexes एक उपयोगी बूलीयन है, जो धीमा क्वेरी लॉग के साथ संयोजन के साथ सक्षम है, इसका मतलब है कि सभी rows को पुनः प्राप्त करने के लिए अपेक्षित क्वेरी केवल लॉग इन हैं।

यह विकल्प हमेशा इसका मतलब नहीं होता है कि कोई भी इंडेक्स उपयोग नहीं किया जाता है। उदाहरण के लिए, जब कोई क्वेरी पूर्ण इंडेक्स स्कैन का उपयोग करता है, तो यह लॉग होगा क्योंकि इंडेक्स rows की संख्या को सीमित नहीं करेगा।

प्रोडक्शन में लॉगिंग?

ट्रैफिक के साथ प्रोडक्शन साइट पर लॉगिंग को इनेबल करने के लिए थोड़े समय के लिए बहुत कुछ किया जाना चाहिए, यह सुनिश्चित करने के लिए लोड की निगरानी करते समय यह सर्विस को प्रभावित नहीं करता है। यदि आप भारी भार से गुजर रहे हैं और एक जरूरी सुधार की आवश्यकता है, तो प्रॉब्लम SHOW PROCESSLIST या information_schema.PROCESSLIST टेबल के माध्यम से प्रॉम्प्ट पर समस्या को संबोधित करें, उदा. select * from Information_schema.PROCESSLIST;

प्रोडक्शन में सभी क्वेरीज को लॉगिंग करने से आपको बहुत कुछ पता चल सकता है और जब आप किसी परियोजना का ऑडिटिंग कर रहे हैं, तो शोध उद्देश्यों के लिए एक अच्छा अभ्यास है, लेकिन यह अंत के दिनों के लिए चल रहा है, अधिक से अधिक 48 घंटों से आपको अधिक उपयोगी डेटा नहीं देगा ( औसत पर, कम से कम प्रश्नों पर अच्छी नज़र रखने के लिए उपयोग की चोटी के समय पर कब्जा कर लेते हैं और आवृत्ति के कुछ विचार प्राप्त करते हैं)।

नोट: यदि आप ऐसी साइट चलाते हैं जो पीक-ट्रैफिक के बढ़ते अनुभवों का अनुभव करता है और फिर बहुत अधिक अवधि (जैसे कि ऑन और ऑफ सीजन के दौरान एक स्पोर्ट्स वेबसाइट) का अनुभव करता है, तो लॉगिंग को देखने के तरीके के साथ लॉजिकल रहें। यह न मानें कि साइट तेजी से काम कर रही है। ऑडिट करें और सबसे महत्वपूर्ण रूप से कुछ ग्राफिंग सेट अप करें।

लॉगिंग और पेर्कोना की pt-query-digest

पेर्कोना के पास इसके कुछ बड़े टूल हैं, और pt-query-digest क्वेरी लॉग्स, processlist या tcpdumps के विश्लेषण के लिए एक कमांड लाइन टूल है।

आप pt-query-digest का उपयोग निम्न तरीकों से कर सकते हैं:

*.log फ़ाइल का विश्लेषण करें (उदाहरण के लिए आपकी धीमी क्वेरी लॉगिंग से आउटपुट):

वास्तविक समय में host1 से धीमी क्वेरी की रिपोर्ट करें (बहुत उपयोगी!):

MySQL प्रोटोकॉल डेटा से धीमी क्वेरी की रिपोर्ट करने के लिए tcpdump का उपयोग करें:

अंत में हम बाद में की समीक्षा के लिए एक होस्ट से धीमा क्वेरी डाटा को दूसरे में सेव सकते हैं। यहां हम क्वेरी डाइजेस्ट को host2 के slog.log में सहेजते हैं:

पेरकोना के pt-query-digest टूल का पूरी तरह से उपयोग करने के तरीके जानने के लिए मैन्युअल पेज पढ़ें।

MySQL और सर्वर परफॉरमेंस ग्राफिंग

InnoDB Row Operations

InnoDB Row ऑपरेशन्स के इस आलेख से पता चलता है कि InnoDB द्वारा किए गए रौ ऑपरेशन्स ने परफॉर्म किया है: updates, reads, deletes और inserts।

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

इसे प्राप्त करने के लिए मैं एक RRDTool- पर आधारित समाधान स्थापित करने की सलाह देता हूं जैसे कि Cacti एक MySQL कॉन्फ़िगरेशन के साथ। पेर्कोना में लोगों से Cacti के लिए एक टेम्पलेट प्राप्त करें।

एक बार जब Cacti स्थापित हो जाता हैं तो आप ऐप का विश्लेषण करना शुरू कर सकते हैं, तो कुछ समय पार करने के लिए अनुमति दें ताकि ग्राफ़ का निर्माण हो सके। कुछ दिनों के बाद आप अपने ट्रैफिक के दिन और रात को देखने लगेंगे और देखें कि सर्वर वास्तव में कितना व्यस्त है।

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

स्लो क्वेरी लॉग

सभी स्लो क्वेरी को लॉग करते हुए जो कि पूरा करने के लिए एक से अधिक सेकंड लेते हैं, हमें कुछ बता सकते हैं, लेकिन यह भी जानकर कि कौन सी क्वेरी सैकड़ों बार क्रियान्वित कर रहे हैं उतना ही महत्वपूर्ण है। यहां तक कि अगर उन क्वेरी को एक्सेक्यूट करने के लिए कम हैं, तो उच्च अनुरोधों का ओवरहेड अभी भी सर्वर पर इसके टोल लेता है।

यही कारण है कि जब आप कुछ अपडेट करते हैं और इसे लाइव रखते हैं तो आस-पास रहना किसी नए डेटाबेस के काम और परिवर्तनों के लिए सबसे महत्वपूर्ण समय है। किसी लाइव प्रोजेक्ट पर बुधवार के बाद नए फीचर डेटाबेस परिवर्तन को कभी भी समन्वयित (sync) करने के लिए हम हमेशा मेरी टीमों पर एक नीति रखते हैं। यह हाल के मंगलवार को, सप्ताह की शुरुआत में किया जाना चाहिए, ताकि सभी टीमों की निगरानी और उस पर सहायता प्रदान कर सकें।

नए प्रश्नों के साथ लाइव होने से पहले, आपको एक लोड-परीक्षण टूल जैसे कि ab के साथ बेंचमार्क चाहिए। जब आप बेंचमार्क चलाते हैं तो आपको SHOW PROCESSLIST को देखना चाहिए, और top, free और iostat जैसे सिस्टम टूल के साथ लॉगिंग और मॉनिटरिंग को इनेबल करना होगा। यह एक लाइव प्रोडक्शन में कोई नई क्वेरी डालने से पहले एक महत्वपूर्ण कदम है। लेकिन यह एक 100% एसिड टेस्ट नहीं है क्योंकि लाइव ट्रैफ़िक एक गणना बेंचमार्क के लिए अलग तरह से व्यवहार कर सकते हैं।

ab के साथ बेंचमार्क करने के लिए, सुनिश्चित करें कि आपने पैकेज इंस्टॉल किया है, उदा .:

अब आप अपने ऐप का परीक्षण करके शुरू कर सकते हैं, उदाहरण के लिए:

-k का मतलब कनेक्शन को keep-alive रखना है, और -c 350 समवर्ती (concurrent) कनेक्शन की संख्या है, यानी उन people/clients की संख्या जो साइट को एक बार में हिट करेंगे। अंत में -n 20000 my-domain.com पर किए जाने वाले अनुरोधों की संख्या है।

तो ऊपर दिए गए कमांड को चलाकर, आप http://my-domain.com/ हिट करेंगे जब तक 20,000 अनुरोध पूरा नहीं होते हैं, तब तक 350 समानांतर कनेक्शन के साथ, और यह जीवित हेडर को रखने का उपयोग करके किया जाएगा।

इस प्रक्रिया के 20,000 अनुरोध समाप्त होने के बाद, आपको आंकड़ों पर प्रतिक्रिया प्राप्त होगी। यह आपको बताएगा कि ऊपर दिए गए मापदंडों का उपयोग करते समय साइट को आपके द्वारा लगाए गए तनाव के तहत कितनी अच्छी तरह प्रदर्शन किया गया था। यदि आपकी क्वेरी कुछ भी बदल गई है तो यह स्वचालित अर्थ में जानने का एक अच्छा तरीका है।

बेंचमार्किंग हॉट बनाम कोल्ड

रिक्वेस्ट अमाउंट और सर्वर लोड का प्रदर्शन पर भारी प्रभाव पड़ता है, और इसके कारण क्वेरी समय प्रभावित हो सकता है। सभी में आपको प्रोडक्शन में इसे पकड़ने के लिए धीमी क्वेरी लॉग को इनेबल करना चाहिए, और प्रोडक्शन के लिए एक नियम के रूप में आपको यह सुनिश्चित करना होगा कि एक idle सर्वर पर मिलीसेकेंड (0.0xx या तेज) के अंशों में एक्सेक्यूट सभी क्वेरीज़ हैं।

आपके लोड की आवश्यकताओं पर Memcache को इम्प्लीमेंट करना नाटकीय प्रभाव पड़ेगा और प्रोसेसिंग क्वेरीज को इस्तेमाल करने वाले संसाधनों को गंभीरता से बोलेगा। सुनिश्चित करें कि आप Memcached प्रभावी ढंग से उपयोग कर रहे हैं और अपने ऐप को एक हॉट cache (प्रीलोडेड वैल्यूज) बनाम एक कोल्ड बना सकते हैं।

एम्प्टी cache के साथ प्रोडक्शन में आगे बढ़ने से बचने के लिए, प्री-लोडर स्क्रिप्ट cache को सुनिश्चित करने का एक अच्छा तरीका है, cache को पढ़ा जाएगा और आपको बहुत सारे अनुरोध नहीं मिलेंगे, जो एक डाउनटाइम से लौटते समय एक ही बार में आते हैं अति-क्षमता फेलियर के कारण।

स्लो क्वेरीज को ठीक करना

तो लॉगिंग इनेबल करने के बाद, आपको अब अपने ऐप में कुछ स्लो क्वेरी मिल गई हैं। चलो उन्हें फिक्सिंग करने के लिए जाओ! उदाहरण के पर्पस के लिए, मैं उन विभिन्न सामान्य समस्याओं का सामना करूँगा जिनसे आप सामना करेंगे और लॉजिक को ठीक करने के लिए।

अगर आपको अभी तक कोई स्लो क्वेरी नहीं मिल रही है, तो शायद जांच लें कि आपकी सेटिंग कहां से long_query_time के लिए हैं यदि आप क्वेरी लॉगिंग मेथड का उपयोग कर रहे हैं। अन्यथा, प्रोफाइलिंग के साथ आपके सभी प्रश्नों को जांचते समय (set profiling = 1), उन क्वेरीज़ की एक सूची बनाएं जो पूरा करने के लिए मिलीसेकेंड के अंश (0.000x सेकेंड) से अधिक समय लेते हैं और उन पर शुरू करते हैं।

सामान्य समस्या

MySQL क्वेरी को ऑप्टिमाइज़ करते समय मैं यहां छह सामान्य समस्याओं का सामना कर रहा हूं:

1. filesort का प्रयोग करके ORDER BY

ORDER BY name की वजह से filesort से बचना असंभव है। कोई फर्क नहीं पड़ता कि आप किस इंडेक्स क्रमबद्धता का उपयोग करते हैं, सबसे अच्छा आपको मिलेगा Using where; Using Filesort का प्रयोग अपने EXTRA में करना। इसे ऑप्टिमाइज़ करने के लिए, परिणाम को Memcache में सेव करें, या अपने एप्लिकेशन के लॉजिक लेयर में ऑर्डरिंग करें।

2. WHERE और LEFT JOIN पर ORDER BY का उपयोग करना

ORDER BY क्वेरी पर एक महत्वपूर्ण टोल हैI उदाहरण के लिए, निम्नलिखित में एक इन्टिजर ID के माध्यम से एक products टेबल और categories टेबल का एक बुनियादी LEFT JOIN है। ऑर्डरिंग को हटा दिया गया है, और साथ ही filesorting को भी।

जब इसे टाला जा सकता है, तो ORDER BY का उपयोग न करें। यदि यह पूरी तरह से इस्तेमाल किया जाना चाहिए, केवल इंडेक्स key पर ऑर्डर करना।

3. एक टेम्प कॉलम पर Order By

बस ऐसा मत करो। यदि आपको अपने परिणामों को एकत्रित करने की ज़रूरत है, तो अपने एप्लीकेशन लॉजिक में करो; MySQL के अंदर किसी टेम्प टेबल पर फ़िल्टरिंग या ऑर्डरिंग नहीं करते हैं। यह बहुत रिसोर्स इंटेंसिव होने जा रहा है।

4. एक FULLTEXT इंडेक्स का उपयोग नहीं कर रहा है

LIKE क्वेरी का उपयोग करना आपके डेटा पर फुल-टेक्स्ट मिलान करने का सबसे धीमा तरीका है। फुल-टेक्स्ट सर्च को लागू करें और MySQL की इस शानदार फीचर का लाभ उठाएं:

5. बड़ी संख्या में rows का चयन बेकार है

क्वेरी पर एक LIMIT को भूल जाने से बड़ी डेटासेट (लाखों से अधिक rows) पर लुकअप समय में महत्वपूर्ण रूप से परिवर्तन हो सकता है।

6. एक कम्पोजिट टेबल या व्यू बनाने के बजाय ओवर-जोइनिंग

जब यह LEFT JOIN के तीन या चार स्तरों पर जाता है, आपको खुद से पूछना चाहिए: "क्या मैं यह सही कर रहा हूं?" यदि आपके पास तर्कसंगत आर्गुमेंट है कि यह क्वेरी क्यों होनी चाहिए, उदाहरण के लिए, यह कम डिमांड में या एक बड़ी सांख्यिकीय (statistical) व्यू के उपयोग में केवल एक एडमिन स्क्रीन में दिखाई देता है, जिसे cached किया जा सकता है, फिर आगे बढ़ें। लेकिन यदि आपको बड़ी संख्या में जुड़ने के साथ अक्सर अपने डेटा तक पहुंचने की आवश्यकता है, तो आपको यह देखना चाहिए कि एक नई टेबल में कंपोजिटिंग कॉलम एक साथ अधिक फायदेमंद हो सकते हैं, या एक view बना सकते हैं।

निष्कर्ष

हमने काम करने के लिए ऑप्टिमाइज़ेशन के मूल सिद्धांतों और हमारे निपटान में हमारे पास टूल्स पर चर्चा की है। हमें प्रोफाइलिंग के साथ ऑडिट करना चाहिए और pt-query-digest टूल का उपयोग करना चाहिए और पहले EXPLAIN देखना है कि वास्तव में क्या हो रहा है, और फिर से हम बेहतर डिजाइन कर सकते हैं।

हमने कई उदाहरणों के मामलों और आम नुकसानों को भी देखा है, जब आप MySQL का उपयोग कर सकते हैं। इंडेक्स हिंटिंग का उपयोग करना हम यह सुनिश्चित कर सकते हैं कि MySQL जॉब के लिए सही इंडेक्स का चयन करता है और इसमें भ्रम नहीं होता है, खासकर अगर एक ही टेबल पर कई प्रश्न हैं। इस विषय पर अपनी पढ़ाई जारी रखने के लिए, अधिक जानकारी के लिए Percona प्रोजेक्ट और MySQL Performance blog देखें।

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.