ऑल्टर टेबल के बारे में कुछ शब्द, या यह कैसे नहीं करना है

यह शायद एक लेख नहीं है, लेकिन MySQL में बड़ी तालिकाओं के साथ काम करने की कुछ विशेषताओं के बारे में एक संक्षिप्त टिप्पणी है।

लिखने का कारण मेज पर एक नए कॉलम का प्रतीत होता है हर रोज़ जोड़ था। लेकिन सब कुछ उम्मीद के मुताबिक नहीं हुआ।

इसलिए, एक शाम, ताकि हमारे प्रिय ग्राहकों को परेशान न करें, हमें तालिका में एक स्तंभ जोड़ने की आवश्यकता थी।

इसे स्पष्ट करने के लिए, तालिका और आधार की विशेषताएं:

• तालिका का आकार 110Gb
• पंक्तियों की संख्या: 7.5 मिलियन
• भंडारण इंजन: InnoDB
• मास्टर-स्लेव स्कीम के अनुसार दो sql सर्वर जुड़े हुए हैं, जबकि मास्टर SSD पर है, और HDD पर स्लेव है

यह कॉलम जोड़ने के लिए एक स्पष्ट समाधान लगता है - ऑल्टर टेबल।

alter table table_name add source varchar(32) 

हमने इसका इस्तेमाल किया (हाँ, हम समझ गए थे कि यह बुरा था, लेकिन इस विशेष मामले में जोखिम कम से कम थे)।

परिणाम बहुत अप्रिय थे:

• विज़ार्ड पर, एक कॉलम जोड़ने की प्रक्रिया में लगभग एक घंटा लगा (!)
• दास पर, यह मास्टर पर प्रक्रिया के अंत के बाद शुरू हुआ और लगभग 8 घंटे तक चला !! (!!)
• परिवर्तन तालिका के दौरान, डेटा प्रतिकृति (!!!) पूरी तरह से दास पर रोक दी गई

लेकिन एक रजत अस्तर है: एक छोटा सा बोनस यह था कि एक कॉलम जोड़ने के बाद, तालिका का आकार 10% कम हो गया।

नीचे दिए गए ग्राफ़ में यह स्पष्ट रूप से दिखाई देता है।


विज़ार्ड पर सीपीयू लोड ग्राफ।


दास पर सीपीयू लोड ग्राफ।


प्रतिकृति अंतराल।

कौन-कौन सी मुसीबतें इंतजार करती हैं जो युद्ध की मेज पर ऐसा करते हैं?

सबसे पहले, एल्टर टेबल की अवधि के लिए, आप टेबल पर डेटा नहीं लिख सकते हैं (लेकिन आप इसे पढ़ सकते हैं)। वास्तव में, यह MySQL के संस्करण पर निर्भर करता है, बाद में यह नहीं है, लेकिन फिर भी आपको यह समझने की आवश्यकता है कि मुसीबतों से बचने के लिए आपका संस्करण वास्तव में क्या सक्षम है।

तदनुसार, यदि तालिका बड़ी है, तो अनुपलब्ध समय महत्वपूर्ण होगा (जैसा कि हमारे साथ, एसएसडी का उपयोग करते समय इसमें एक घंटा लगता था, और एक नियमित डिस्क पर - 8 घंटे), जो आपके ग्राहकों को उम्मीद नहीं है।

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

एक और गैर-स्पष्ट बिंदु जो हमने कॉलम जोड़ते समय सामना किया था (लेकिन यह दूसरी बार था) - अतिरिक्त डिस्क स्थान की आवश्यकता है ।

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

इसके अलावा, सभी प्रकार की ऑल्टर टेबल के निष्पादन के दौरान, सभी परिवर्तन लॉग फ़ाइल में लिखे जाते हैं, ताकि परिवर्तनों के बाद, उस समय पर डेटा को रोल किया जा सके, जिसके दौरान ऑपरेशन किया गया था। और यहां भी, एक अप्रिय आश्चर्य की प्रतीक्षा हो सकती है: यदि तालिका लंबे समय तक बदलती है, और संचालन की मात्रा बड़ी है, तो न केवल डिस्क स्थान समाप्त हो सकता है, बल्कि SQL सेटिंग्स में निर्दिष्ट फ़ाइल आकार सीमा भी पार हो सकती है। किसी भी स्थिति में, "ऑनलाइन डीडीएल ऑपरेशन विफल हो जाता है, और बिना समवर्ती डीएमएल संचालन वापस लुढ़का हुआ है" आपको इंतजार है।

हमें इस तथ्य से सामना करना पड़ा कि अस्थायी फ़ाइलों की निर्देशिका छोटी थी, जिसके परिणामस्वरूप हमें innodb_tmpdir को फिर से परिभाषित करना पड़ा ।

यह देखने के लिए कि चर वर्तमान में कहां इंगित कर रहा है, आप यह कर सकते हैं:

 select @@GLOBAL.innodb_tmpdir; 

ध्यान रखें कि अस्थायी निर्देशिका के आकार को तालिका + अनुक्रमित के आकार की भी आवश्यकता हो सकती है। सामान्य तौर पर, अंतरिक्ष पर स्टॉक।

प्रलेखन को दोहराने के लिए, https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.html पर और अधिक विस्तार से पढ़ें

लेकिन यह कैसे करें? वास्तव में, सभी अवसरों के लिए एक भी नुस्खा नहीं है।

संभावित विकल्पों में से एक, जैसा कि हम उन तालिकाओं के लिए करते हैं जो अद्यतन करने के लिए महत्वपूर्ण नहीं हैं:

• वांछित संरचना के साथ एक नई तालिका बनाएं
• पुरानी तालिका से खेतों में भरें
• पुरानी तालिका हटाएं या नाम बदलें
• नया नाम दें

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

युपीडी। Syavadee ने percona ऑनलाइन स्कीमा परिवर्तन का उपयोग करने का सुझाव दिया। वास्तव में, यह अतिरिक्त अच्छाइयों के साथ ऊपर वर्णित एल्गोरिथ्म को लागू करता है।

युपीडी। Arheops प्रतिकृति समस्याओं को हल करने के लिए समानांतर प्रतिकृति / gtid सक्षम करने की सिफारिश करता है।

ठीक है, संयोग से, कभी-कभी, यह समझने के लिए कि तालिका कितनी बड़ी है और इसमें कितनी पंक्तियाँ हैं, आपको यह सिखाने की आवश्यकता है कि कैसे करें

 select count(*) from table_name 

लेकिन बड़ी और भरी हुई तालिकाओं पर, यह भी सबसे तेज़ ऑपरेशन नहीं है, खासकर जब आपके पास आधा मिलियन पंक्तियाँ या अधिक हों।

इसलिए, वॉल्यूम के अनुमानित अनुमान के लिए, आप निम्न विधि का उपयोग कर सकते हैं:

 SHOW TABLE STATUS FROM express where name='table_name' 

दुर्भाग्य से, InnoDB इंजन पर, परिणामी आकार 50 प्रतिशत (हमारे मामले में, ऊपर दी गई तालिका के साथ भिन्न हो सकता है, रिकॉर्ड की वास्तविक संख्या लगभग 7.5 मिलियन है, और यह विधि केवल 5 मिलियन दर्शाती है), लेकिन यह एक संकेतक अनुमान के लिए काफी उपयुक्त है।

यह सब, मुझे उम्मीद है कि यह नोट किसी को कथित रूप से हानिरहित SQL कमांड के साथ बड़ी परेशानी से बचने में मदद करता है।