बड़े डेटा वॉल्यूम पर MySQL InnoDB का व्यावहारिक अनुकूलन और मापनीयता

यह पोस्ट इंडेक्स, क्वेरी प्लान, एग्रीगेट्स के निर्माण के लिए ट्रिगर और प्रश्नों और डेटाबेस संरचना को अनुकूलित करने के अन्य सामान्य तरीकों के बारे में बात नहीं करेगी। यह innodb_ उपसर्ग के साथ इष्टतम सेटिंग्स के बारे में भी बात नहीं करेगा। शायद नीचे दिए गए पाठ को पढ़ने से आप उनमें से कुछ का अर्थ बेहतर समझ पाएंगे। इस पोस्ट में हम InnoDB और इसके संचालन के बारे में बात करेंगे।

इस पोस्ट से क्या समस्याएं हल हो सकती हैं?

• अगर आपको किसी को परेशान करने की कोई प्रक्रिया नहीं है, तो सूची के कई चयनों का क्या करें?
• यदि सब कुछ अच्छी तरह से कॉन्फ़िगर किया गया हो, तो मुझे क्या करना चाहिए, अनुरोध रॉकेट की तरह उड़ते हैं और प्रक्रियाओं की सूची लगातार खाली है, लेकिन सर्वर में उच्च एलए है और अनुरोध थोड़ा धीमा काम करना शुरू करते हैं, ठीक है, उदाहरण के लिए, 100ms के बजाय यह 500ms निकला है?
• जब सबकुछ फिर से करने का कोई तरीका नहीं है, तो सिस्टम को जल्दी से कैसे स्केल करें?
• क्या आपके पास प्रतिस्पर्धी माहौल में एक वाणिज्यिक परियोजना है और आपको समस्या को तुरंत हल करने की आवश्यकता है?
• एक ही क्वेरी तेजी या धीमी गति से क्यों काम करती है?
• त्वरित कैश कैसे व्यवस्थित करें और इसे अद्यतित रखें?

हमेशा की तरह, डेटाबेस के साथ काम करना

लगभग काम की योजना आमतौर पर ऐसी है

1. जांच
2. अनुरोध योजना
3. सूचकांक खोज
4. तालिकाओं से डेटा पुनर्प्राप्त करना
5. ग्राहक को डेटा भेजना

यहां तक ​​कि अगर आपने लेन-देन शुरू नहीं किया है , तो आपका प्रत्येक व्यक्तिगत अनुरोध अनिवार्य रूप से एक अनुरोध से एक लेनदेन होगा। जैसा कि आप जानते हैं, लेन-देन में अलगाव का एक स्तर होता है जो MySQL को READATABLE READ के लिए डिफॉल्ट करता है। हमारे लिए इसका क्या मतलब है? और यह तथ्य कि जब आप किसी लेन-देन में "किसी भी तालिका को स्पर्श करते हैं", तो इसका संस्करण उसी क्षण तय हो जाता है और आप अन्य लेनदेन में किए गए परिवर्तनों को देखना बंद कर देते हैं। आपकी क्वेरी या लेन-देन जितना लंबा होगा, उतना ही अधिक "पुराना" डेटा MySQL को जमा करता रहता है और यह विश्वास करने का कारण है कि यह मुख्य मेमोरी पूल के सक्रिय उपयोग के साथ होता है। यानी डेटाबेस के साथ सक्रिय कार्य के मामले में, प्राथमिक कुंजी द्वारा 10 तालिकाओं को संयोजित करने के आपके प्रत्येक हानिरहित चयनों में अधिक गंभीर दुष्प्रभाव होने लगते हैं। Oracle की तरह PostgreSQL, READ COMMITTED का डिफ़ॉल्ट आइसोलेशन स्तर है जो REPEATAT READ की तुलना में अधिक आसानी से कार्य करता है। बेशक, READ COMMITTED आइसोलेशन स्तर के साथ, आपको पंक्ति-आधारित प्रतिकृति का उपयोग करना होगा। आप आसानी से दो क्लाइंट को डेटाबेस से जोड़कर ट्रांजेक्शन आइसोलेशन लेवल की जांच कर सकते हैं और एक ही टेबल पर सेलेक्ट, डिलीट और अपडेट कर सकते हैं। यह ठंड चयन के बारे में सवाल का जवाब है, डेटाबेस अलगाव स्तर को बदलने की कोशिश करें, यह आपकी मदद कर सकता है।

कैसे InnoDB डेटा के साथ काम करता है

InnoDB पृष्ठों में हार्ड ड्राइव पर डेटा संग्रहीत करता है। वांछित पृष्ठ तक पहुंचने पर, इसे रैम में लोड किया जाता है और फिर इसके साथ विभिन्न क्रियाएं होती हैं, चाहे वह रिकॉर्ड पढ़ रहा हो या कुछ और। यह स्मृति है कि Innodb_buffer_pool का आकार है, जिसे आप innodb_buffer_pool_size में सेट करते हैं। काम की योजना काफी शास्त्रीय है और इसमें कुछ भी असामान्य नहीं है। InnoDB प्रदर्शन रिपोर्ट निम्नानुसार देखें:

SHOW VARIABLES like 'Innodb%';
SHOW GLOBAL STATUS like 'Innodb%';
SHOW ENGINE INNODB STATUS;


कुल मिलाकर, हमें डेटाबेस में रीड या राइट ऑपरेशन करने के लिए निम्नलिखित समय की लागतें मिलती हैं

1. हार्ड डिस्क से मेमोरी में डेटा लोड करने का समय
2. मेमोरी प्रोसेसिंग का समय
3. यदि आवश्यक हो तो हार्ड डिस्क पर डेटा लिखने का समय (यह ध्यान देने योग्य है कि सभी डेटा डिस्क पर तुरंत नहीं लिखे जाते हैं, मुख्य बात यह है कि वे लॉग में दर्ज हैं)

मुझे लगता है कि यह स्पष्ट रूप से स्पष्ट है कि यदि डेटा अनुरोध के समय इनोबीडी पूल में है, तो यह डिस्क से लोड नहीं होता है, जो किसी भी डेटाबेस संचालन के निष्पादन समय को बहुत कम करता है।

डेटाबेस का सबसे तेज़ तरीका तब होता है जब सभी डेटा और इंडेक्स आसानी से पूल में फिट हो जाते हैं और वास्तव में, डेटाबेस हमेशा मेमोरी से काम करता है। 5.6.5 संस्करण में, डेटाबेस को फिर से शुरू करने पर पूरे पूल को डिस्क पर सहेजना संभव है, जो एक ठंड शुरू से बचा जाता है।

अब आइए घटनाओं के एक अलग संस्करण को देखें जब डिस्क पर डेटा की मात्रा मेमोरी पूल के आकार से अधिक हो जाती है। बता दें कि पूल का आकार 4 पेज है, यह क्रमशः [0,0,0,0] और 16 डेटा पेज 1..16 जैसा दिखेगा। हमारे उपयोगकर्ता सबसे अधिक बार पेज 15 और 16 का अनुरोध करते हैं क्योंकि उनके पास नवीनतम डेटा है और वे हमेशा मेमोरी में रहते हैं। जाहिर है, ऊपर वर्णित मामले में सब कुछ उतनी ही तेजी से काम करता है।

खैर, एक बुरा विकल्प जब आपके पास उपयोगकर्ताओं और 8 पेजों द्वारा सक्रिय रूप से अनुरोध किए गए 2 पृष्ठ होते हैं जो आंतरिक स्क्रिप्ट और विभिन्न डेमों द्वारा लगातार उपयोग किए जाते हैं। इस प्रकार, बफर में 4 पृष्ठ पर एक निरंतर संघर्ष होता है, जो डिस्क से अनन्त पढ़ने में बदल जाता है और उपयोगकर्ताओं के लिए सिस्टम को धीमा कर देता है दानव आमतौर पर बहुत अधिक सक्रिय होते हैं।

इस मोड में, एक और MySQL सर्वर के साथ प्रतिकृति स्थापित करने से आपको प्रश्नों का हिस्सा लेने और मेमोरी पूल के लिए संघर्ष को कम करने में मदद मिल सकती है। लेकिन जैसा कि आप जानते हैं, MySQL में प्रतिकृति में एक महत्वपूर्ण कमी है, अर्थात्, 1 थ्रेड में परिवर्तन का अनुप्रयोग। यानी कुछ शर्तों के तहत, आपका दास या तो पिछड़ना शुरू कर देगा या उत्पादकता में पूरी तरह से वृद्धि कर सकता है। इस स्थिति में, सीमित संख्या में तालिकाओं के साथ दास बनाने की क्षमता मदद कर सकती है। यह आपको परिवर्तन के आवेदन और मेमोरी पूल के उपयोग पर दोनों को प्राप्त करने की अनुमति देगा। कई मामलों में, जब उपयोगकर्ताओं द्वारा अनुरोध किए जाने वाले डेटा को अक्सर जाना जाता है, तो आप केवल आवश्यक तालिकाओं से डेटा संग्रहीत करने के लिए उनके लिए एक कैश बना सकते हैं। एक सफल मामले में, आपको एक कैश मिलेगा जो स्वचालित रूप से इसकी प्रासंगिकता बनाए रखता है। उन लोगों के लिए जो जल्दी से एक और गुलाम बनाने के लिए इच्छुक हैं, मैं सुझाव देता हूं कि पक्ष की ओर देखो

STOP SLAVE;
SHOW SLAVE STATUS;//Master_Log_File Exec_Master_Log_Pos

START SLAVE;


जब आप डेटा को एक नए दास सर्वर में पेस्ट करते हैं, तो आपको केवल करने की आवश्यकता होती है

CHANGE MASTER TO ... ;
START SLAVE;


बेशक, सब कुछ डेटा की मात्रा पर निर्भर करता है, लेकिन एक नियम के रूप में, आप इस तरह के गुलाम को बहुत जल्दी उठा सकते हैं।

आंशिक प्रतिकृति का उपयोग करके किसी सिस्टम को मॉड्यूल में बदलना

और इसलिए हम मुख्य डेटाबेस की आंशिक प्रतिकृतियां बना सकते हैं, जो हमें डेटा के कुछ समूहों के बीच स्मृति के वितरण को नियंत्रित करने की अनुमति देता है। यह हमारे लिए क्या अवसर खोलता है?
जैसा कि आप अनुभव कर सकते हैं, कोई भी आपको गुलाम सर्वर पर अपनी खुद की टेबल बनाने से नहीं रोक रहा है और यहां तक ​​कि उनमें प्रतिकृति डेटा के लिए विदेशी कुंजी भी बना रहा है। यानी आपके पास न केवल एक अभिन्न मुख्य डेटाबेस हो सकता है, बल्कि तालिकाओं के विस्तारित सेट के साथ अभिन्न दास भी हो सकते हैं। उदाहरण के लिए, आपके मुख्य डेटाबेस में एक उपयोगकर्ता तालिका और विभिन्न प्रकार के भुगतान की सहायक तालिकाएँ हैं । आपके पास एक ब्लॉगिंग सेवा भी है जो उपयोगकर्ताओं को पोस्ट लिखने की अनुमति देती है। आप उपयोगकर्ताओं को किसी अन्य डेटाबेस में दोहराते हैं जिसमें आप पोस्ट टेबल बनाते हैं। यदि डेटाबेस तालिका पर उच्च रीड लोड है, तो आप उपयोगकर्ता और पोस्ट तालिका वाले प्रतिकृतियां बनाते हैं। इस प्रकार, तब तक विघटित करना संभव है जब तक कि तालिकाओं के आवश्यक सेट का डेटा वॉल्यूम उचित सीमा से अधिक न हो। इस मामले में, यह पहले से ही विशाल तालिकाओं की दिशा में देखने के लायक है, उदाहरण के लिए उपयोगकर्ता आईडी के हैश द्वारा, और आवश्यक कर्मचारियों को एमक्यू के माध्यम से अनुरोध भेजें।

परिणाम

MySQL केवल निर्दिष्ट तालिकाओं को डेटा लिखकर एक सरल प्रतिकृति तंत्र प्रदान करता है। यह डेटाबेस के अभिन्न अंगों से युक्त अतिरिक्त सेवाओं को तैनात करने का पर्याप्त अवसर देता है।

युपीडी। डेटाबेस में बड़ी संख्या में छोटे लेनदेन के मामले में, आप innodb_flush_log_at_trx_commit के मान को बदलने का प्रयास कर सकते हैं, इससे हार्ड ड्राइव पर लोड कम हो जाएगा, लेकिन सावधान रहें! सैद्धांतिक रूप से, इससे डेटा हानि हो सकती है । इस चर के परिणाम को देखना काफी आसान है, क्योंकि आप इसे सेट ग्लोबल के माध्यम से स्थापित कर सकते हैं।

UPD2। सिस्टम का सफल अपघटन आपके द्वारा किए जाने वाले कार्यों के आधार पर अलग-अलग सर्वर की सेटिंग्स को भिन्न करने की अनुमति देता है। उदाहरण के लिए, भुगतान सर्वर पर, आपके पास SERIALIZABLE और innodb_flush_log_at_trx_commit = 1. हो सकता है और पोस्ट सर्वर पर COMMITTED और innodb_flush_log_at_trx_commit = 2. पोस्ट किया गया है। यह सभी कार्य किए गए कार्य, डेटा की जीवन शक्ति, और विफलता की संभावना पर निर्भर करता है।