Bitrix24 में एक और 12 MySQL "व्यंजनों"

हमारे पिछले लेख में, "11 MySQL पाक कला व्यंजनों को Bitrix24 में," हमने मुख्य रूप से वास्तुशिल्प समाधानों पर ध्यान दिया: क्या यह क्लाउड सेवाओं (जैसे अमेज़ॅन आरडीएस) का उपयोग करने के लायक है, जो कि चुनने के लिए MySQL का उपयोग करता है, और इसी तरह।

समीक्षाओं को देखते हुए, बड़ी "हाईलोड" परियोजनाओं में MySQL के सक्षम संचालन का विषय बहुत बड़ा और महत्वपूर्ण है। इसलिए, हमने बिट्रिक्स 24 के विकास के दौरान ट्यूनिंग की कुछ बारीकियों के बारे में और उन डेटाबेस के बारे में और बात करने का निर्णय लिया, जिनका हमने सामना किया।

मैं आपको एक बार फिर याद दिलाता हूं कि यह लेख (साथ ही पिछले एक) सभी अवसरों के लिए एकदम सही MySQL ट्यूनिंग के लिए एक सार्वभौमिक "नुस्खा" नहीं है। :) ऐसा नहीं होता है। :) लेकिन मुझे पूरा विश्वास है कि व्यक्तिगत विशिष्ट समस्याओं को हल करने के लिए यह आपके लिए उपयोगी होगा।

और लेख के अंत में - सबसे अधिक रोगी पाठकों के लिए एक आश्चर्य। :)


1. QUERY CACHE की स्थापना

MySQL में क्वेरी कैश कैसे काम करता है, और इसे कैसे कॉन्फ़िगर और उपयोग किया जाए, यह वर्णन करते हुए भारी संख्या में लेख लिखे गए हैं।

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

ऐसा नहीं है।

MySQL बड़े क्वेरी कैश को हैंडल नहीं करता है। व्यवहार में, हम इस तथ्य के साथ आए हैं कि 512_ से अधिक_से_चुनें, प्रक्रियाएँ अधिक बार दिखाई देती हैं जो "क्वेरी कैश लॉक के लिए प्रतीक्षा" स्थिति में थोड़े समय के लिए हैंग हो जाती हैं (SHOW PROCESSLIST में देखी गई)।

इसके अलावा, यदि आपके सभी अनुरोध कैश में आते हैं, तो इसे बढ़ाना बुद्धिमानी नहीं है। सिस्टम में रैम कीमती है!

इसलिए, यह समझना हमेशा महत्वपूर्ण है कि आपके प्रोजेक्ट में क्या हो रहा है, और वर्तमान सेटिंग्स के साथ क्वेरी कैश का उपयोग कितनी प्रभावी रूप से किया जाता है।

आपके लिए महत्वपूर्ण जानकारी यहाँ है:

mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 10541 | | Qcache_free_memory | 36381984 | | Qcache_hits | 18888719 | | Qcache_inserts | 5677585 | | Qcache_lowmem_prunes | 1725258 | | Qcache_not_cached | 6096307 | | Qcache_queries_in_cache | 36919 | | Qcache_total_blocks | 97285 | +-------------------------+----------+ 8 rows in set (0.00 sec) 

सबसे महत्वपूर्ण Qcache_hits और Qcache_inserts, Qcache_inserts और Qcache_not_cached के अनुपात हैं, साथ ही Qcache_lowmem_prunes - कैश से कैश किए गए अनुरोधों की संख्या - और Qcache_free_memory हैं।

इन आँकड़ों को केवल कभी-कभार ही नहीं, बल्कि एनालिटिक्स को हाथ में लेकर देखना सबसे अच्छा है। इसे विभिन्न निगरानी उपकरणों का उपयोग करके एकत्र किया जा सकता है। उदाहरण के लिए, मुनिन:





डायनामिक्स में ग्राफ़ के माध्यम से - दिन में एक बार, उदाहरण के लिए, हम अंततः इस निष्कर्ष पर पहुंचे कि हमारे लिए एक सर्वर के लिए ये सेटिंग्स काफी पर्याप्त हैं:

 query_cache_size = 128M query_cache_limit = 2M 

बेशक, शायद आपके प्रोजेक्ट के लिए तस्वीर अलग होगी।

2. innodb_buffer_pool_size

बफर पूल का आकार InnoDB की सबसे महत्वपूर्ण सेटिंग्स में से एक है। यह डेटा कैश और टेबल इंडेक्स के लिए काम के दौरान MySQL द्वारा उपयोग की जाने वाली मेमोरी बफर का आकार है (हम तुरंत याद करेंगे कि MySQL और ऑपरेटिंग सिस्टम द्वारा डबल कैशिंग से बचने के लिए, यह सेटिंग्स में innodb_flush -method = O_DIRECT को निर्दिष्ट करने के लायक है)।

MySQL डेवलपर्स की ओर से, सबसे बड़े अपराधों में से एक डिफ़ॉल्ट मान को 8M पर सेट करना है। :)

वास्तव में, आदर्श रूप से, innodb_buffer_pool_size का मूल्य ऐसा होना चाहिए कि आपका पूरा डेटाबेस स्मृति में फिट हो। इसी समय, यह याद रखना महत्वपूर्ण है कि सिस्टम मेमोरी से संतुलित है (हमने पिछले लेख में इस बारे में बात की थी) - यदि आप innodb_buffer_pool_size के मूल्य को "फाड़ देते हैं" ताकि पूरी प्रणाली स्वैप में चली जाए, इससे अच्छा कुछ नहीं होगा।

उचित सेटअप का एक अच्छा संकेतक बफर पूल हिट दर है:

 mysql> SHOW ENGINE InnoDB STATUS\G ... ---------------------- BUFFER POOL AND MEMORY ---------------------- ... Buffer pool hit rate 994 / 1000, young-making rate 6 / 1000 not 0 / 1000 ... 

यदि मान "1000/1000" के करीब है - सब ठीक है। अन्यथा, आपको innodb_buffer_pool_size बढ़ाना होगा । यदि पर्याप्त मेमोरी नहीं है, तो मेमोरी जोड़ें।

3. innodb_buffer_pool_instances

डिफ़ॉल्ट रूप से, InnoDB बफर पूल के लिए एक उदाहरण का उपयोग करता है।

इसी समय, कई ब्लॉकों का चयन करना संभव है - और MySQL उनके साथ कुछ मामलों में InnoDB में बहुत अधिक कुशलता से काम करता है।

यदि आपके पास 2 जीबी से अधिक है, तो बफ़र पूल को कई उदाहरणों में विभाजित किया जाना चाहिए। प्रत्येक उदाहरण का आकार 1 जीबी या अधिक करने के लायक है।

और यहाँ एक महत्वपूर्ण सवाल यह है कि बहुत से भ्रमित हैं: innodb_buffer_pool_size कुल पूल आकार या एक उदाहरण का आकार?

दस्तावेज़ में उत्तर सही है - यह कुल आकार है। इसलिए, उदाहरण के लिए, इस तरह का एक विन्यास है:

 innodb_buffer_pool_size = 4096M innodb_buffer_pool_instances = 4 

... कहते हैं कि प्रत्येक उदाहरण 1 जीबी पर कब्जा करेगा।

सावधान रहें कि कोई घटना नहीं है (MySQL DBA की सदस्यता लेने वाला एक मित्र MySQL के संचालन में अपने अनुभव को सेटिंग innodb_buffer_pool_size = 1024M, innodb_buffer_pool_instances [64) के साथ वर्णित करता है - 64 जीबी आवंटित करने की योजना बना रहा था - और नाराज था कि यह प्रदर्शन के साथ बुरा था ... :))

4. innodb_io_capacity

एक दिलचस्प पैरामीटर, जो प्रतीत होता है (यदि आप आधिकारिक दस्तावेज :) मानते हैं), और प्रदर्शन को बहुत प्रभावित नहीं करता है, फिर भी व्यवहार में यह सही ढंग से कॉन्फ़िगर होने पर कुछ लाभ देता है।

innodb_io_capacity इनपुट-आउटपुट संचालन की सीमा (IOPS में) उन InnoDB संचालन के लिए सेट करता है जो पृष्ठभूमि में किए जाते हैं (उदाहरण के लिए, बफ़र पूल से डिस्क पर पृष्ठ डंपिंग)।

डिफ़ॉल्ट मान 200 है।

बहुत छोटा मान इन ऑपरेशनों को "पिछड़ने" का कारण बनेगा। बहुत अधिक बफ़र पूल से डेटा को बहुत तेज़ी से फ़्लश किया जाएगा।

आदर्श रूप से, आपको अपने डिस्क सिस्टम के वास्तविक प्रदर्शन के अनुरूप मूल्य निर्धारित करना चाहिए (फिर से, IOPS में)।

5. innodb_file_per_table

डिफ़ॉल्ट रूप से, InnoDB में MySQL एक टेबलस्पेस - ibdata1 फ़ाइल में सभी तालिकाओं के डेटा और इंडेक्स को संग्रहीत करता है।

यदि सेटिंग्स में innodb_file_per_table विकल्प का उपयोग किया जाता है , तो इस स्थिति में प्रत्येक तालिका के लिए एक अलग फ़ाइल table_name.ibd बनाई जाती है, जिसमें डेटा और इंडेक्स संग्रहीत होते हैं।

क्या यह समझ में आता है?

मेरी व्यक्तिगत राय में: यदि आप मानक MySQL का उपयोग करते हैं, तो आपको इसका उपयोग करने की आवश्यकता नहीं है। अलग-अलग फ़ाइलों में तालिकाओं को संग्रहीत करने से प्रदर्शन को कम किया जा सकता है, क्योंकि सिस्टम में "महंगी" संसाधन-गहन फ़ाइल खुले संचालन की संख्या नाटकीय रूप से बढ़ेगी।

हम खुद पेरकोना सर्वर का उपयोग करते हैं। और innodb_file_per_table का उपयोग करें ।

• यदि सामान्य MySQL में किसी एक या कई टेबल को किसी कारण से पीटा जाता है, तो पूरा डेटाबेस "स्टैंड अप" होगा। Percon में आप innodb_corrupt_table_action = मुखर विकल्प का उपयोग कर सकते हैं, और तब innodb_file_per_table का उपयोग करते हुए "टूटी हुई" तालिका को चिह्नित किया जाएगा, लेकिन एक पूरे के रूप में संपूर्ण डेटाबेस काम करना जारी रखेगा।
• पहले से ही कुछ ऑपरेशनों को "गति" करने का एक अवसर है जो स्पष्ट रूप से प्रदर्शन में खो जाते हैं जब innodb_file_per_table का उपयोग करते हैं । उदाहरण के लिए, टेबल हटाना। ऐसे उद्देश्यों के लिए, पेरकॉन के पास विकल्प innodb_lazy_drop_table = 1 है , जो पृष्ठभूमि में इस तरह के संचालन की अनुमति देता है और समग्र सिस्टम प्रदर्शन को कम नहीं करता है।
• Innodb_file_per_table विकल्प चालू होने पर, XtraBackup का उपयोग करके आप तेजी से बाइनरी आयात / तालिकाओं का निर्यात कर सकते हैं।

6. max_connect_errors

एक और अपराधी :) MySQL डेवलपर्स की साजिश। Max_connect_errors का डिफ़ॉल्ट मान 10 है।

इसका मतलब यह है कि किसी भी अप्रत्याशित विफलता के मामले में किसी भी अधिक या कम सक्रिय परियोजना में, यहां तक ​​कि एक अल्पकालिक एक (उदाहरण के लिए, स्क्रिप्ट में गलत पासवर्ड दर्ज किया गया था या कुछ नेटवर्क समस्याएं उत्पन्न हुई थीं) - कनेक्शन स्थापित करने के असफल प्रयासों की एक निश्चित संख्या के बाद, कनेक्शन स्थापित करने वाला मेजबान अवरुद्ध हो जाएगा। जब तक MySQL सर्वर को पुनरारंभ नहीं किया जाता है या FLUSH HOSTS कमांड निष्पादित नहीं होती है।

इसका मतलब यह है कि मैनुअल हस्तक्षेप से पहले (केवल अगर आपने अग्रिम में क्रोन पर एक स्क्रिप्ट पोस्ट नहीं की थी जो हर कुछ मिनट में एक बार FLUSH HOSTS निष्पादित करता है :)) आपकी परियोजना काम नहीं करेगी। यह अप्रिय है अगर यह रात में होता है, और आपके पास चौबीस घंटे निगरानी नहीं है।

अग्रिम में खुद को बचाने के लिए बेहतर है और अधिकतम_कनेक्ट_रियर्स के मूल्य को बड़े पर सेट करें। उदाहरण के लिए:

 max-connect-errors = 10000 

7. अस्थायी टेबल

यदि सिस्टम में रैम की मात्रा की अनुमति देता है, तो मेमोरी में अस्थायी तालिकाओं के साथ हमेशा काम करना बेहतर होता है।

इसे व्यवस्थित करना काफी आसान है। MySQL सेटिंग्स में:

 tmpdir = /dev/shm 

फ़ाइल सिस्टम और विभाजन की सेटिंग में (यदि यह लिनक्स पर आता है - / etc / fstab फ़ाइल में):

 # <file system> <mount point> <type> <options> <dump> <pass> tmpfs /dev/shm tmpfs defaults 0 0 

8. अस्थायी तालिकाओं का आकार

स्मृति में तालिकाओं के आकार के लिए जिम्मेदार दो समान पैरामीटर हैं:

 max_heap_table_size = 64M tmp_table_size = 64M 

max_heap_table_size - एक उपयोगकर्ता द्वारा बनाई जा सकने वाली मेमोरी के प्रकार का अधिकतम आकार।
tmp_table_size - मेमोरी में बनाई जाने वाली अस्थायी तालिका का अधिकतम आकार (डिस्क पर अधिक)।

कम डिस्क गतिविधि, बेहतर। इसलिए, यदि सिस्टम में रैम की मात्रा मेमोरी मेमोरी को याद रखने की अनुमति देती है, तो मेमोरी में सभी अस्थायी तालिकाओं के साथ काम करना बेहतर होता है।

9. table_cache और table_definition_cache

 table_cache = 4096 table_definition_cache = 4096 

ये दो विकल्प उन अधिकतम तालिकाओं के लिए ज़िम्मेदार हैं, जिन्हें खुली तालिकाओं के कैश में संग्रहीत किया जाएगा।

Table_cache का मूल्य सीधे आपके सिस्टम में तालिकाओं की संख्या पर निर्भर करता है, क्वेरी में खोलने के लिए तालिकाओं की संख्या पर (उदाहरण के लिए, JOINs के माध्यम से लिंक) और डेटाबेस के लिए खुले कनेक्शन की संख्या पर।

table_definition_cache तालिका संरचनाओं (.frm) फ़ाइलों के लिए कैश आकार को परिभाषित करता है। सिस्टम में वे जितना अधिक होते हैं, टेबल_डेफिनिशन_चेचे के मूल्य को अधिक से अधिक सेट करने की आवश्यकता होती है।

10. लंबे अनुरोधों के लिए संघर्ष

पिछले लेख में, हमने पहले ही उल्लेख किया था कि पेरकोना सर्वर के पास समग्र प्रणाली प्रदर्शन ( इंप्रूवमेंट से कुछ जानकारी के लिए एक अच्छा उपकरण है। INFCHATION_SCHEMA.QUERY_RESPONSE_TIME )।

इसके अलावा, किसी भी MySQL में सभी "धीमी" प्रश्नों को लॉग करना संभव है और उन्हें अलग से पार्स करें।

पेरकोना सर्वर का उपयोग करते समय, धीमी क्वेरी का लॉग अधिक जानकारीपूर्ण हो जाता है।

 log_output = FILE slow_query_log = 1 slow_query_log_file = mysql_slow.log long_query_time = 1 #percona log_slow_verbosity = microtime,query_plan,innodb 

1 सेकंड से अधिक समय तक चलने वाले सभी प्रश्न mysql_slow.log पर लिखे जाते हैं। मानक लॉग के विपरीत, यह कुछ इस तरह दिखता है:

 # Time: 120712 9:43:47 # User@Host: user[user] @ [10.206.66.207] # Thread_id: 3513565 Schema: user Last_errno: 0 Killed: 0 # Query_time: 1.279800 Lock_time: 0.000053 Rows_sent: 0 Rows_examined: 1 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 52 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 33E7689B # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 # InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 4 UPDATE b_user_option SET 'COMMON' = 'N', 'VALUE' = 'a:19', 'NAME' = 'openTab', 'CATEGORY' = 'IM' WHERE ID=1719; 

हम न केवल क्वेरी निष्पादन समय, "स्कैन" पंक्तियों की संख्या आदि देखते हैं, बल्कि बहुत अधिक विस्तृत जानकारी - पूर्ण स्कैन, अस्थायी तालिकाओं का उपयोग, इनोबीडी की स्थिति।

यह सब धीमे प्रश्नों और उनके डिबगिंग के विश्लेषण में बहुत मदद करता है।

11. पेरकोना के बिना विस्तृत आँकड़े

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

प्रोफाइल जैसी चीज है। क्या आप उनका उपयोग करते हैं? नहीं? व्यर्थ में!

 mysql> SHOW PROFILES; Empty set (0.02 sec) mysql> SHOW PROFILE; Empty set (0.00 sec) 

हम प्रोफाइलिंग चालू करते हैं और किसी भी अनुरोध को देखते हैं:

 mysql> SET PROFILING=1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COUNT(*) FROM mysql.user; +----------+ | COUNT(*) | +----------+ | 3024 | +----------+ 1 row in set (0.09 sec) mysql> SHOW PROFILES; +----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.09104400 | SELECT COUNT(*) FROM mysql.user | +----------+------------+---------------------------------+ 1 row in set (0.00 sec) mysql> SHOW PROFILE; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000018 | | Waiting for query cache lock | 0.000004 | | Waiting on query cache mutex | 0.000004 | | checking query cache for query | 0.000041 | | checking permissions | 0.000007 | | Opening tables | 0.090854 | | System lock | 0.000013 | | init | 0.000012 | | optimizing | 0.000007 | | executing | 0.000010 | | end | 0.000005 | | query end | 0.000004 | | closing tables | 0.000031 | | freeing items | 0.000029 | | logging slow query | 0.000003 | | cleaning up | 0.000004 | +--------------------------------+----------+ 16 rows in set (0.00 sec) 

हम तुरंत देखते हैं कि अड़चन क्या है - नेटवर्क, डिस्क के साथ काम करना, कैश का उपयोग करना, या कुछ और।

12. प्रोफ़ाइल से जानकारी का उपयोग कैसे करें?

यदि आपने एकल लंबी क्वेरीज़ ढूंढना सीख लिया (यह इतना कठिन नहीं है - धीमे प्रश्नों की एक लॉग इन करें और मदद करने के लिए SHOW PROCESSLIST ), यदि आप सिस्टम की स्थिति का संपूर्ण रूप से सही तरीके से मूल्यांकन करते हैं ( चयन करें * FROM INFORMATION_SCHERY_QUESY_RESPONSE_TIME , सिस्टम की बाहरी निगरानी, ​​उदाहरण के लिए, nagios - वास्तविक समय मुनिन - विश्लेषिकी), यह समझना अत्यंत महत्वपूर्ण है कि किसी प्रणाली में प्रदर्शन को क्या प्रभावित कर सकता है:

• सिस्टम के सभी आंतरिक संसाधन - आधार के लिए "बहुत अधिक" न तो सीपीयू है, न ही रैम, न ही डिस्क सिस्टम;
• ताले (टेबल स्तर पर - अधिक बार MyISAM में, पंक्ति स्तर पर - InnoDB);
• आंतरिक ताले (उदाहरण के लिए, "क्वेरी कैश लॉक की प्रतीक्षा कर रहे हैं")।

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

* * *

किसी भी वॉल्यूम के डेटाबेस का सफल डिबगिंग और सफल संचालन और किसी भी लोड के साथ! :)

* * *

इस जगह को पढ़ने के लिए धन्यवाद! :)



मुझे उम्मीद है कि हमारे MySQL टिप्स आपके लिए मददगार हैं!

और जब से हम Bitrix24 परियोजना में MySQL के संचालन में अपने अनुभव के बारे में बात कर रहे हैं, हम Habré पर अपने ब्लॉग के सभी पाठकों के लिए एक छोटा सा उपहार देना चाहेंगे।

निर्दिष्ट लिंक पर Bitrix24 में रजिस्टर करें और दो बार उतना ही डिस्क लें - 10 जीबी - एक मुफ्त दर पर!

यदि अचानक आपको अभी भी पता नहीं है कि बिट्रिक्स 24 क्या है, तो इसका विस्तृत विवरण हमारी वेबसाइट पर है । :)