हर कोई झूठ या क्यों MySQL में विभाजन का उपयोग नहीं करना बेहतर है

संस्करण 5.1 के साथ शुरू, MySQL ने विभाजन के रूप में इस तरह की एक उपयोगी सुविधा पेश की। बेशक, अधिकांश डेटाबेस डेवलपर्स ने इसका उपयोग करने के लिए तुरंत तिरस्कार नहीं किया। कुछ वर्षों के काम के बाद, मैंने अंततः MySQL AB विशेषज्ञों द्वारा इस तकनीक के सभी त्रुटिपूर्ण कार्यान्वयन के फल प्राप्त किए ...

एक मामूली गीतात्मक विषयांतर।

5 अक्टूबर को, Highload ++ नामक एक कंपनी ने MySQL डेवलपर्स के लिए एक गहन एक दिवसीय प्रशिक्षण आयोजित करने के लिए पीटर ज़ैतसेव को सम्मेलन में आमंत्रित किया । व्यक्ति ठोस प्रतीत होता है, और विवरण स्पष्ट रूप से इंगित किया गया था

प्रस्तावित प्रशिक्षण एक उन्नत पाठ्यक्रम है
...
पाठ्यक्रम एक गहन एक दिवसीय प्रशिक्षण है। छात्रों को लिनक्स पर्सनल कंप्यूटर उपलब्ध कराया जाएगा। कार्यों को पूरा करने का समय सीमित रहेगा।

अरे वाह, मुझे लगता है कि कुछ नया, कुछ प्रकार के कार्य, सबसे कुशल आर्किटेक्चर पर विचार ... आपको जाना होगा! अंत में, मुझे लगता है कि मन को सिखाया जाएगा मन। जब दोपहर में उन्होंने सम्मेलन में MySQL के बारे में बात करना शुरू किया तो मुझे कोई आश्चर्य नहीं हुआ, कोई काम नहीं था, और उन्होंने लिनक्स के साथ एक स्पष्ट मामला नहीं दिया। ठीक है, हां, मुझे बैठने और ऐसा सोचने पर गर्व नहीं है, हम सुनेंगे, लेकिन जब दोपहर के भोजन के बाद हम इस बिंदु पर पहुंचे कि क्वेरी निष्पादन योजनाओं का अध्ययन करके MySQL अनुकूलन प्राप्त किया जा सकता है, और कमरे में एक चर्चा छिड़ गई - मैं सही ढंग से क्वेरी योजनाओं को पढ़ने के लिए कैसे खड़ा नहीं हो सकता। । अगले कॉफी ब्रेक पर, वह पीटर के पास गया और पूछा कि क्या सम्मेलन में डेटाबेस डेवलपर्स के लिए वास्तव में उन्नत जानकारी होगी (पिछले दो घंटों के लिए), जिसके लिए उसे एक अद्भुत जवाब मिला। कुछ इस तरह

सामान्य तौर पर, उन्नत MySQL अनुकूलन पाठ्यक्रम प्रकृति में मौजूद नहीं हैं। इस गलतफहमी का कारण यह है कि पाठ्यक्रमों का बड़ा हिस्सा राज्यों के लिए है, और वहां "भारतीय" विकास में लगे हुए हैं और उन्हें इसके लिए गहन ज्ञान की आवश्यकता नहीं है।

इसका मतलब है कि कैसे।
मुझे पीटर द्वारा व्यक्त विचार भी वास्तव में पसंद आया कि MySQL गंदगी का एक बड़ा गुच्छा है जो बहुत ही अच्छे तरीके से काम नहीं करता है क्योंकि यह बहुत कुटिल तरीके से लिखा गया है, शायद सबसे अच्छे कारणों के लिए, लेकिन यह निकला कि क्या हुआ। और कोई फर्क नहीं पड़ता कि आप अपने डेटाबेस सर्वर को कैसे अनुकूलित करते हैं, जितनी जल्दी या बाद में आप इस तथ्य का सामना करेंगे कि आप प्रदर्शन में गिरावट का ठीक-ठीक अनुभव कर रहे हैं क्योंकि स्रोत कोड बग्स और आर्किटेक्चरल मिसकल्चुअल्स से भरा है। इन समस्याओं से दूर होने का एकमात्र संभव तरीका बड़ी मात्रा में रैम (512 जीबी +) और एसएसडी डिस्क का उपयोग करना है (जैसा कि सम्मेलन में दिखाया गया है, 10,000 पुनर्मिलन चक्र के साथ 2 टीबी इसके लिए सबसे उपयुक्त हैं, उनकी रिकॉर्डिंग गति से आप कम से कम 3 वर्षों में उन्हें मार देंगे) )।
बेशक, मैं अधिक कुशल वास्तुकला का उपयोग करने के बजाय, लोहे को फेंककर समस्याओं को हल करने का समर्थक नहीं हूं, लेकिन विभाजन के साथ समस्याओं ने उनके शब्दों का पूरा सच दिखाया।

शुरुआत

इसलिए, आपने अपनी परियोजना में विभाजन का उपयोग करने का निर्णय लिया।
सबसे पहले, विभाजन को खोलने वाले कोड को देखें और इसके बारे में सोचें, क्या आपको इसकी आवश्यकता है?

is_part = strstr(norm_name, "#P#"); retry: /* Get pointer to a table object in InnoDB dictionary cache */ ib_table = dict_table_get(norm_name, TRUE); if (NULL == ib_table) { if (is_part && retries < 10) { ++retries; os_thread_sleep(100000); goto retry; } 

आपकी माँ !!!
खैर, दसवें पर, यह 100% खोलने का प्रयास करता है!
मुख्य अवधारणाएं जिन्हें आप सबसे अधिक हल करना चाहते हैं, वे हैं:

• संग्रह (उदाहरण के लिए, 99% मामलों में आपको केवल पिछले महीने के लिए डेटा की आवश्यकता होती है, फिर यह मासिक विभाजन का उपयोग करने के लिए समझ में आता है, पुराने डेटा को हर महीने संग्रह में छोड़ देता है)
• डेटा वेयरहाउसिंग (आपने MySQL आधारित OLAP सिस्टम बनाने का निर्णय लिया और वहां गायब OLAP क्यूब्स के बजाय, आप प्रति घंटा, दैनिक, मासिक और वार्षिक विभाजन के साथ पूर्व-एकत्रित तालिकाओं का एक गुच्छा उपयोग करते हैं)
• अन्य मामले (मैं इस पर विचार नहीं करूंगा क्योंकि वे अधिक विदेशी हैं और सबसे महत्वपूर्ण वर्गीकरण के अनुसार, जो आपको MySQL में विभाजन के बारे में भूल जाते हैं 5.6 संस्करण तक, ये मामले पहले दो बिंदुओं में आते हैं)

मैंने इस विभाजन को केवल एक मानदंड द्वारा चुना - विभाजन की संख्या। पहले मामले में, उनमें से शायद ही 100 से अधिक हैं, दूसरे में, उनमें से 10,000+ हैं।
जैसा कि आप जानते हैं, MySQL बड़ी संख्या में तालिकाओं के साथ काम कर सकता है - 100,000 एक अनुमानित नैतिक सीमा है जिस पर यह वास्तुकला को बदलने के बारे में सोचने योग्य है। इसलिए, सभी तर्क से, दसियों हजार विभाजन उसके लिए समस्या नहीं होनी चाहिए। दुर्भाग्य से ऐसा नहीं है।
इससे पहले, मैंने एक डीबीएमएस के साथ काम किया, जो अपनी सभी मेटाडेटा को रैम में रखता है और डिक्शनरी क्वेश्चन इसके लिए कोई मुश्किल पैदा नहीं करते हैं, इस कारण से, जब विभाजन उपयोगिताओं के लिए समर्थन को लागू करते हैं, तो मैं INFORMATION_SCHITA.PARTITIONS से क्वेरी करने से डरता नहीं था। यह एक घातक गलती थी ... हम यह कैसे पता लगा सकते हैं कि इस तालिका में कौन से विभाजन हैं:
- इस सूची को स्वयं रखें
- कुछ अनुरोधों का प्रदर्शन करें (उदाहरण के लिए, दैनिक विभाजन)

  select count(1) into v_av_partitions_count from information_schema.partitions where table_name = i_table_name and substring(partition_name, length(i_table_name) + 2) = cast(date_format(i_data_date, '%Y%m%d') as signed) and table_schema = database(); select min(partition_name), min(partition_description) into v_future_partition, v_future_partition_desc from information_schema.partitions where table_name = i_table_name and substring(partition_name, length(i_table_name) + 2) > cast(date_format(i_data_date, '%Y%m%d') as signed) and table_schema = database(); 

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

ह्यूस्टन हमें समस्याएं हैं

मेरी समस्या तब शुरू हुई जब मैंने डेटाबेस संरचना (लगभग 7000 खाली विभाजन) में भरा और हर आधे घंटे में event_scheduler पर एक विभाजन जांच स्थापित की। जैसे ही समय आया ( INFORMATION_SCHEMA.PARTITIONS से 2 अनुरोध शुरू करने), मेरा लैपटॉप बंद हो गया, जिससे अधिकतम संभव डिस्क लोड 3-5 मिनट के लिए हो गया। मुझे अजीब लगा ... मेरे पास एक खाली डेटाबेस है। अफवाह होने के बाद, मैंने महसूस किया कि इस दुर्भाग्यपूर्ण तालिका से प्रश्नों की कॉल वास्तव में कैसे काम करती है, यह जानना आवश्यक था। PERFORMANCE_SCHEMA ने मुझे भरोसा दिलाया कि मैंने 7000 फाइलें पढ़ी हैं, और इस तथ्य के कारण कि मेरे पास काफी छंटनी की गई डेटाबेस है, मैं इन रीडिंग को डिस्क से करता हूं। स्रोत को खोलने पर, मुझे एहसास हुआ कि वास्तविक समस्या क्या थी। डेटा शब्दकोश - रैम में संग्रहीत नहीं है। जब आप किसी विभाजन का वर्णन करने वाली तालिका से एक अनुरोध करते हैं, तो निम्न ऑपरेशन होते हैं:

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

इस प्रकार, डेटा शब्दकोश से एक क्वेरी के साथ, हम

• हम टेबल कैश को धोते हैं - सभी थ्रेड्स के लिए साझा किया गया
• वॉश बफर कैश - सभी सत्रों द्वारा उपयोग किया जाता है
• हम अनुकूली कैश को धोते हैं - जिसमें लेखन और पठन को इसकी स्थिरता सुनिश्चित करने के लिए केवल एक लॉक द्वारा नियंत्रित किया जाता है

TABLE_ONE द्वारा अनुरोध के दौरान gdb द्वारा लिए गए ट्रेस का एक उदाहरण यहां दिया गया है

 Thread 6 (Thread 0x7fccff1be700 (LWP 2561)): #0 0x00007fcd3af4c493 in pread64 () from /lib/libpthread.so.0 ... #5 fil_io (type=10, sync=<value optimized out>, space_id=<value optimized out>, zip_size=<value optimized out>, block_offset=<value optimized out>, byte_offset=<value optimized out>, len=16384, buf=0x7fcd1a0b4000, message=0x7fcd08a6e800) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/fil/fil0fil.c:4481 #6 0x00000000007e527a in buf_read_page_low (err=0x7fccff1b85c8, sync=1, mode=<value optimized out>, space=52456, zip_size=0, unzip=<value optimized out>, tablespace_version=312, offset=4) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/buf/buf0rea.c:148 ... #9 0x00000000007bb6f1 in btr_block_get_func (index=0x7fccf4adfb78, mtr=0x4000) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/include/btr0btr.ic:55 ... #14 0x00000000007f1edf in dict_table_get (table_name=<value optimized out>, inc_mysql_count=1) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/dict/dict0dict.c:753 #15 0x0000000000776533 in ha_innobase::open (this=0x7fccf4128130, name=0x7fccff1b9230 "./DATABASE/TABLE_TWO#P#TABLE_TWO_20100823", mode=<value optimized out>, test_if_locked=<value optimized out>) at /home/mcshadow/svn/mysql-5.5.15/storage/innobase/handler/ha_innodb.cc:3737 ... #18 0x000000000064716d in handler::ha_open (this=0x28, table_arg=<value optimized out>, name=0x7fccf4076d08 "./DATABASE/TABLE_TWO", mode=2, test_if_locked=26128432) at /home/mcshadow/svn/mysql-5.5.15/sql/handler.cc:2150 ... #23 0x000000000052d6a4 in open_normal_and_derived_tables (thd=0x28, tables=0x7fccf4027968, flags=4294967295) at /home/mcshadow/svn/mysql-5.5.15/sql/sql_base.cc:5489 ... #25 0x00000000005aa51f in get_all_tables (thd=<value optimized out>, tables=<value optimized out>, cond=<value optimized out>) at /home/mcshadow/svn/mysql-5.5.15/sql/sql_show.cc:3781 ... 

इस तरह के एक दु: खद परिणाम को देखकर, मैं, निश्चित रूप से, बहुत परेशान था, क्योंकि स्रोत कोड के माध्यम से अफवाह करने के बाद, मुझे एहसास हुआ कि इसे केवल ठीक करना संभव नहीं होगा। लेकिन वह सब से बहुत दूर था।

जंगल में और अधिक जलाऊ लकड़ी

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

• क्वेरी में भाग लेने वाले सभी तालिकाओं के सभी विभाजन खोलेंगे
• उपयोग किए गए इंजन के नियमों के अनुसार प्रत्येक विभाजन पर एक रीड लॉक लटकाता है
• यदि यह INNODB एक्स लॉक को भूल जाने के बिना तालिका और उसके सूचकांकों के आंकड़ों की गणना करता है - तो, ​​इसके बिना यह कैसे हो सकता है
• और केवल इस सब के बाद ही विभाजन विभाजन किया जाएगा

विश्वास नहीं होता? कोड पेस्ट करें

  char* is_part = NULL; is_part = strstr(table->name, "#P#"); if (is_part) { ut_print_timestamp(stderr); fprintf(stderr, " TRACE dict_update_statistics %s %s\n", table->name, only_calc_if_missing_stats?"true":"false"); } 

तानाशाही_उपकरण_शास्त्रीय प्रक्रिया की शुरुआत में जो आँकड़ों की गणना करती है और परिणाम आपको आश्चर्यचकित करेंगे। डिफ़ॉल्ट DBMS सेटिंग्स के साथ क्या है, आँकड़े पुनर्गणना को कैश नहीं किया जाता है, लेकिन हर बार जब आप मेटाडेटा प्राप्त करने का प्रयास करते हैं, तो किया जाता है।
उपरोक्त के प्रकाश में, बग का समापन # 19588 है

एक बैरल टार में एक चम्मच शहद

MySQL 5.6 जल्द ही जारी किया जाएगा, वे उपयोगी चीजों की एक गुच्छा का वादा करते हैं
- INFORMATION_SCHEMA के लिए एक स्थिर शब्दकोश (मुझे पता नहीं है कि क्या विभाजन होंगे, लेकिन मुझे वास्तव में उम्मीद है)
- select * from table partition (p1) कंस्ट्रक्शन select * from table partition (p1) जब टेबल खोलने और ताले सेट करने से पहले कौन सा पार्टिशन प्रूनिंग काम करेगा

निष्कर्ष या इसके साथ कैसे रहना है

- अगर आपके आर्किटेक्चर में बहुत सारे 1000+ विभाजन हैं, तो बेहतर सोचें और आर्किटेक्चर को संशोधित करें, MySQL के लिए विभाजन इंजन अभी भी सैंडबॉक्स नहीं बचा है
- पैरामीटर innodb_stats_on_metadata = OFF सेट करें innodb_stats_on_metadata = OFF यह innodb_stats_on_metadata = OFF करें प्रत्येक अनुरोध के लिए विभाजन पर आंकड़े फिर से पढ़ने की अनुमति नहीं देगा, हालांकि यह अभी भी होगा, लेकिन ऐसा अक्सर नहीं
- अपने सिस्टम में विभाजन की अधिकतम संभावित संख्या में 2 से गुणा करके ulimit -n को बढ़ाएं
- अपने सिस्टम में विभाजनों की अधिकतम संभव संख्या के लिए table_open_cache पैरामीटर table_open_cache 2 से गुणा करके यह आपको टेबल कैश को table_open_cache से रोकने की अनुमति देगा।
- यदि आप INNODB टेबल के साथ काम करते हैं, तो myisam_use_mmap पैरामीटर को शामिल करने की दिशा में देखें, MyISAM केवल इंडेक्स को कैश करता है, और ऑपरेटिंग सिस्टम को डेटा फ़ाइलों को कैश करता है, इसलिए, बढ़े हुए लोड के साथ, आपके शब्दकोश डिस्क पर जाएंगे, जो निश्चित रूप से अवांछनीय है।
- INFORMATION_SCHEMA.PARTITIONS तालिका में प्रश्न न करें, हालांकि जैसा कि आप जानते हैं, यह अभी भी आपको इस्तेमाल की गई तालिकाओं के सभी विभाजनों के लिए किसी भी डीएमएल ऑपरेशन पर ब्रेक और लॉक से नहीं बचाएगा।