MySQL सर्वर को ट्यूनिंग और ऑप्टिमाइज़ करना

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



तो, संस्करण 4 में, कई चर का अंत _size



। यह thread_cache_size



चर और बफ़र्स अनुभाग से चर पर लागू होता है। और संस्करण 4 से पहले वेरिएबल read_buffer_size



को record_buffer



कहा जाता था। इसके अलावा, वर्जन 4 से पहले बेसिक सेटिंग्स सेक्शन से skip_external_locking



वेरिएबल को skip_locking



कहा जाता था।

चर दो मुख्य श्रेणियों में विभाजित हैं: मान और चर झंडे के साथ चर। मानों के साथ चर को कॉन्फ़िगरेशन फ़ाइल में variable = value



रूप में लिखा जाता है, और ध्वज चर को केवल निर्दिष्ट किया जाता है। आपने शायद यह भी देखा कि कुछ मामलों में चर नाम " -



" का उपयोग करते हैं, और कुछ " _



" में। एक हाइफ़न के साथ चर सर्वर के शुरुआती विकल्प हैं और जब सर्वर चल रहा हो (एसईटी का उपयोग करके) बदला नहीं जा सकता; रेखांकित चर सर्वर विकल्प हैं और इसे मक्खी पर बदला जा सकता है। अगर हम "स्टेट वैरिएबल" के बारे में बात कर रहे हैं या किसी वैरिएबल के मान को देखने की सिफारिश की गई है जिसका नाम Variable_Name



के रूप में लिखा गया है, तो आपको इस वैरिएबल का मान पाने के लिए SHOW STATUS LIKE "Variable_Name"



अनुरोध चलाना चाहिए, या phpMyAdmin में स्थिति टैब देखें, जहां अतिरिक्त टिप्पणियां होंगी। इस चर के मूल्य से।

अब आइए चर और उनके संभावित मूल्यों पर एक नज़र डालते हैं।

मूल सेटिंग्स

• low-priority-updates
  
  
  
  - यह विकल्प SELECT की तुलना में INSERT / UPDATE परिचालनों की प्राथमिकता को कम करता है। दरअसल, अगर डेटा को तेजी से लिखने की तुलना में तेजी से पढ़ना महत्वपूर्ण है।
• skip-external-locking
  
  
  
  - विकल्प डिफ़ॉल्ट रूप से इंस्टॉल किया जाता है, जो संस्करण 4 से शुरू होता है। डेटाबेस के साथ काम करते समय MySQL सर्वर को बाहरी ताले का उपयोग नहीं करने के लिए कहता है। बाहरी लॉक उन स्थितियों में आवश्यक होते हैं जब कई सर्वर एक ही डेटा फ़ाइलों के साथ काम करते हैं, अर्थात। वही datadir
  
  
  
  , जो व्यवहार में प्रयोग नहीं किया जाता है।
• skip-name-resolve
  
  
  
  - कनेक्ट करने वाले ग्राहकों के आईपी पते के लिए डोमेन नाम को परिभाषित न करें। उसी समय, उपयोगकर्ता अनुमतियों को मेजबानों पर नहीं, बल्कि आईपी पते (लोकलहोस्ट को छोड़कर) पर कॉन्फ़िगर करने की आवश्यकता है। यदि आप केवल स्थानीय मशीन से सर्वर से कनेक्ट करते हैं, तो यह वास्तव में कोई फर्क नहीं पड़ता। बाहरी कनेक्शन के लिए, कनेक्शन सेटअप को गति दें।
• skip-networking
  
  
  
  - एक नेटवर्क का उपयोग न करें, अर्थात्। टीसीपी / आईपी कनेक्शन को संसाधित न करें। सर्वर के साथ संचार सॉकेट के माध्यम से विशेष रूप से होगा। अनुशंसित यदि आपके पास सॉफ़्टवेयर नहीं है जो सर्वर से संवाद करने के लिए केवल टीसीपी / आईपी का उपयोग करता है।

प्रतिबंध

• bind-address
  
  
  
  - इंटरफ़ेस जिसे सर्वर सुनेंगे। सुरक्षा कारणों से, यह अनुशंसा की जाती है कि आप 127.0.0.1 को यहां सेट करें यदि आप सर्वर से बाहरी कनेक्शन का उपयोग नहीं कर रहे हैं।
• max_allowed_packet
  
  
  
  - एक अनुरोध में स्थानांतरित किए जा सकने वाले डेटा का अधिकतम आकार। यदि आपको "पैकेट बहुत बड़ी" त्रुटि आती है तो इसे बढ़ाया जाना चाहिए।
• max_connections
  
  
  
  - सर्वर के समवर्ती कनेक्शन की अधिकतम संख्या। यदि आप "बहुत अधिक कनेक्शन" समस्या का सामना करते हैं तो इसे बढ़ाएं।
• max_join_size
  
  
  
  - उन max_join_size
  
  
  
  कथनों पर प्रतिबंध लगाता है, जिन्हें निर्दिष्ट पंक्तियों की संख्या या डिस्क खोजों की निर्दिष्ट संख्या से अधिक पार्स करने के लिए माना जाता है। लाखों पंक्तियों को पढ़ने की कोशिश करने वाले कुटिल प्रश्नों से बचाने के लिए उपयोग किया जाता है। डिफ़ॉल्ट मूल्य 4 बिलियन से अधिक है, इसलिए आप सबसे अधिक संभवतया इसे कम करना चाहते हैं।
• max_sort_length
  
  
  
  - इंगित करता है कि सॉर्ट करते समय उपयोग करने के लिए टाइप BLOB या TEXT के क्षेत्रों की शुरुआत से कितने बाइट्स। डिफ़ॉल्ट मान 1024 है, यदि आप गलत तरीके से डिज़ाइन किए गए तालिकाओं या प्रश्नों से डरते हैं, तो आपको इसे कम करना चाहिए।

स्ट्रीम सेटिंग्स

• thread_cache_size
  
  
  
  - कैश्ड थ्रेड्स की संख्या को इंगित करता है। अनुरोध को संसाधित करने के बाद, सर्वर स्ट्रीम को समाप्त नहीं करेगा, लेकिन इसे कैश में रखेगा यदि कैश में पाए जाने वाले थ्रेड्स की संख्या निर्दिष्ट मूल्य से कम है। डिफ़ॉल्ट मान 0 है, इसे बढ़ाकर 8 या तुरंत 16 कर दें। यदि Threads_Created
  
  
  
  स्टेट वैरिएबल का मान Threads_Created
  
  
  
  , तो thread_cache_size
  
  
  
  को भी thread_cache_size
  
  
  
  जाना चाहिए।
• thread_concurrency
  
  
  
  - केवल Solaris / SunOS के लिए प्रासंगिक है जो वे नेटवर्क पर लिखते हैं। "टिप्स" सिस्टम को थ्रेश_सेटकोन्क्युलर फ़ंक्शन को कॉल करके एक ही समय में कितने थ्रेड शुरू करने हैं। अनुशंसित मूल्य प्रोसेसर कोर की दोहरी या तिगुनी संख्या है।

कैशिंग का अनुरोध करें

• query_cache_limit
  
  
  
  - कैश्ड अनुरोध का अधिकतम आकार।
• query_cache_min_res_unit
  
  
  
  - कैश में संग्रहीत ब्लॉक का न्यूनतम आकार।
• query_cache_size
  
  
  
  - कैश आकार। 0 कैश उपयोग अक्षम करता है। इष्टतम मूल्य का चयन करने के लिए, Qcache_lowmem_prunes
  
  
  
  राज्य Qcache_lowmem_prunes
  
  
  
  निगरानी करना और यह सुनिश्चित करना आवश्यक है कि इसका मूल्य महत्वपूर्ण रूप से नहीं बढ़ता है। आपको यह भी याद रखने की आवश्यकता है कि एक अत्यधिक बड़े कैश अनावश्यक भार पैदा करेगा।
• query_cache_type
  
  
  
  - (OFF, DEMAND, ON)। कैशिंग को निष्क्रिय करता है, DEMAND - कैशिंग केवल तभी किया जाएगा जब SQL_CACHE निर्देश क्वेरी में मौजूद हो, ON कैशिंग सक्षम करता है।
• query_cache_wlock_invalidate
  
  
  
  - यह निर्धारित करता है कि क्या डेटा को कैश से लिया जाएगा यदि वह जिस तालिका से संबंधित है वह पढ़ने के लिए लॉक है।

क्वेरी कैश को हैश सरणी के रूप में माना जा सकता है, जिनमें से कुंजी प्रश्न हैं, और मूल्य प्रश्नों के परिणाम हैं। परिणामों के अलावा, MySQL कैश में तालिकाओं की एक सूची संग्रहीत करता है, जिनमें से चयन कैश किया जाता है। यदि किसी भी तालिका में परिवर्तन होता है जिसका चयन कैश में होता है, तो MySQL कैश से ऐसे नमूने निकालता है। इसके अलावा, MySQL प्रश्नों को कैश नहीं करता है जिसके परिणाम बदल सकते हैं।

स्टार्टअप पर, MySQL एक मेमोरी ब्लॉक को query_cache_size



के आकार को आवंटित करता है। क्वेरी निष्पादित करते समय, जैसे ही परिणाम की पहली पंक्तियाँ प्राप्त होती हैं, सर्वर उन्हें कैश करना शुरू कर देता है: यह कैश में एक मेमोरी ब्लॉक को query_cache_min_res_unit



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



से बड़ा है, तो रिकॉर्डिंग बंद हो जाती है और अधिकृत स्थान खाली हो जाता है, इसलिए, यदि आप पहले से जानते हैं कि चयन का परिणाम बड़ा होगा, तो यह निर्देश के साथ करने योग्य है SQL_NO_CACHE ।

समय

• interactive_timeout
  
  
  
  - सेकंड में वह समय जिसके दौरान सर्वर इंटरएक्टिव कनेक्शन की तरफ से गतिविधि का इंतजार करता है (इसे बंद करने से पहले CLIENT_INTERACTIVE ध्वज का उपयोग करके)।
• log_slow_queries
  
  
  
  - सर्वर को लंबे ("धीमे") अनुरोधों ( long_query_time
  
  
  
  से अधिक समय तक चलने) को long_query_time
  
  
  
  । मान पूरी तरह से योग्य फ़ाइल नाम है (उदाहरण के लिए, /var/log/slow_queries
  
  
  
  )।
• long_query_time
  
  
  
  - यदि क्वेरी निर्दिष्ट समय (सेकंड में) से अधिक समय तक निष्पादित होती है, तो इसे "धीमा" माना जाएगा।
• net_read_timeout
  
  
  
  - उस सेकंड में समय जिसके दौरान सर्वर कनेक्शन बाधित होने से पहले डेटा प्राप्त होने की प्रतीक्षा करेगा। यदि सर्वर बहुत धीमी या अस्थिर चैनलों के साथ ग्राहकों की सेवा नहीं करता है, तो 15 सेकंड पर्याप्त होंगे।
• net_write_timeout
  
  
  
  - सेकंड में समय, जिसके दौरान सर्वर कनेक्शन बाधित होने से पहले डेटा प्राप्त होने की प्रतीक्षा करेगा। यदि सर्वर बहुत धीमी या अस्थिर चैनलों के साथ ग्राहकों की सेवा नहीं करता है, तो 15 सेकंड पर्याप्त होंगे।
• wait_timeout
  
  
  
  - उस सेकंड में समय जिसके दौरान सर्वर बाधित होने से पहले कनेक्शन गतिविधि की प्रतीक्षा करता है। सामान्य तौर पर, 30 सेकंड पर्याप्त होंगे।

बफ़र्स

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

• key_buffer_size
  
  
  
  - अनुक्रमित और सभी थ्रेड के लिए उपलब्ध बफर के आकार। एक बहुत ही महत्वपूर्ण सेटिंग जो प्रदर्शन को प्रभावित करती है। डिफ़ॉल्ट मान 8 एमबी है, यह निश्चित रूप से बढ़ने लायक है। कुल रैम का 15-30% अनुशंसित है, हालांकि, यह सभी .MYI फ़ाइलों के कुल आकार से अधिक स्थापित करने के लिए कोई मतलब नहीं है। Key_reads
  
  
  
  और Key_read_requests
  
  
  
  राज्य चर का निरीक्षण करें, Key_reads/Key_read_requests
  
  
  
  का अनुपात Key_reads/Key_read_requests
  
  
  
  छोटा होना चाहिए (<0.01)। यदि यह अनुपात बड़ा है, तो बफर का आकार बढ़ाया जाना चाहिए।
• max_heap_table_size
  
  
  
  - मेमोरी में संग्रहीत तालिका का अधिकतम स्वीकार्य आकार (प्रकार की मेमोरी)। डिफ़ॉल्ट मान 16 MB है, यदि आप MEMORY तालिकाओं का उपयोग नहीं करते हैं, तो इस मान को tmp_table_size
  
  
  
  सेट करें।
• myisam_sort_buffer_size
  
  
  
  - myisam_sort_buffer_size
  
  
  
  द्वारा आवंटित बफर का आकार REPAIR TABLE के साथ अनुक्रमित करने के लिए या CREATE INDEX, ALTER TABLE के साथ इंडेक्स बनाने के लिए। डिफ़ॉल्ट मान 8 एमबी है, इसे 30-40% रैम तक बढ़ाया जाना चाहिए। प्रदर्शन लाभ, क्रमशः, उपरोक्त अनुरोधों को पूरा करने पर ही होगा।
• net_buffer_length
  
  
  
  - कनेक्शन बफर के लिए आवंटित मेमोरी की मात्रा और प्रति स्ट्रीम बफर परिणाम के लिए। कनेक्शन बफर निर्दिष्ट आकार होगा और परिणाम बफर समान आकार होगा, अर्थात प्रत्येक स्ट्रीम के लिए डबल स्ट्रीम net_buffer_length
  
  
  
  आवंटित की जाएगी। निर्दिष्ट मूल्य प्रारंभिक है और, यदि आवश्यक हो, तो बफ़र्स max_allowed_packet
  
  
  
  तक बढ़ जाएंगे। डिफ़ॉल्ट आकार 16 KB है। सीमित मेमोरी के साथ या केवल छोटे प्रश्नों का उपयोग करके, मूल्य को कम किया जा सकता है। बड़े प्रश्नों और पर्याप्त मेमोरी के निरंतर उपयोग के मामले में, मूल्य को अनुरोध के अनुमानित औसत आकार तक बढ़ाया जाना चाहिए।
• read_buffer_size
  
  
  
  - अनुक्रमिक तालिका स्कैन के दौरान प्रत्येक थ्रेड प्रत्येक तालिका के लिए एक निश्चित मात्रा में मेमोरी आवंटित करता है। जैसा कि परीक्षण दिखाते हैं, इस मूल्य को विशेष रूप से नहीं बढ़ाया जाना चाहिए। डिफ़ॉल्ट आकार 128 KB है, इसे बढ़ाकर 256 KB करने का प्रयास करें, और फिर 512 KB तक का SELECT COUNT(*) FROM table WHERE expr LIKE "a%";
  
  
  
  जैसे SELECT COUNT(*) FROM table WHERE expr LIKE "a%";
  
  
  
  की निष्पादन गति का निरीक्षण SELECT COUNT(*) FROM table WHERE expr LIKE "a%";
  
  
  
  बड़े तालिकाओं पर।
• read_rnd_buffer_size
  
  
  
  - " ORDER BY " के साथ प्रश्नों के लिए प्रासंगिक, अर्थात उन प्रश्नों के लिए जिनके परिणाम को क्रमबद्ध किया जाना चाहिए और जो उस तालिका का संदर्भ देते हैं जिसमें अनुक्रमित हैं। डिफ़ॉल्ट मान 256 KB है, इसे 1 एमबी या अधिक बढ़ाएं, यदि स्मृति अनुमति देती है। ध्यान दें कि संकेतित स्मृति मान भी प्रत्येक थ्रेड के लिए आवंटित किया गया है।
• sort_buffer_size
  
  
  
  - सॉर्टिंग ( ORDER BY ) या समूहीकरण ( GROUP BY ) करने वाले प्रत्येक थ्रेड निर्दिष्ट आकार का एक बफर आवंटित करता है। डिफ़ॉल्ट मान 2 एमबी है, यदि आप निर्दिष्ट प्रकार के प्रश्नों का उपयोग करते हैं और यदि मेमोरी अनुमति देती है, तो मूल्य को बढ़ाया जाना चाहिए। राज्य चर Sort_merge_passes
  
  
  
  का बड़ा मान Sort_merge_passes
  
  
  
  बढ़ाने की आवश्यकता को इंगित करता है। यह फॉर्म के प्रश्नों को निष्पादित करने की गति की जांच करने के लायक भी है। बड़ी तालिका पर SELECT * FROM table ORDER BY name DESC
  
  
  
  , संभवतः बफर बढ़ाना केवल धीमा हो जाएगा ( कुछ परीक्षणों में यह मामला है)।
• table_cache
  
  
  
  (संस्करण 5.1.3 के बाद से table_open_cache
  
  
  
  ) - सभी थ्रेड्स के लिए कैश्ड ओपन टेबल की संख्या। एक टेबल फ़ाइल खोलना काफी संसाधन-गहन ऑपरेशन हो सकता है, इसलिए कैश में ओपन टेबल रखना सबसे अच्छा है। यह ध्यान दिया जाना चाहिए कि इस कैश में प्रत्येक प्रविष्टि एक सिस्टम डिस्क्रिप्टर का उपयोग करती है, इसलिए आपको डिस्क्रिप्टर की संख्या ( ulimit ) बढ़ानी पड़ सकती है। डिफ़ॉल्ट मान 64 है; यह तालिका की कुल संख्या में इसे बढ़ाने के लिए सबसे अच्छा है, अगर उनकी संख्या अनुमेय सीमा के भीतर है। Opened_tables
  
  
  
  राज्य Opened_tables
  
  
  
  आपको कैश को दरकिनार करके खोले गए तालिकाओं की संख्या की निगरानी करने की अनुमति देता है, यह वांछनीय है कि इसका मूल्य यथासंभव कम हो।
• tmp_table_size
  
  
  
  - उनकी आंतरिक आवश्यकताओं के लिए MySQL द्वारा बनाई गई अस्थायी तालिकाओं के लिए आवंटित अधिकतम आकार की मेमोरी। यह मान भी चर max_heap_table_size
  
  
  
  द्वारा सीमित है, इसलिए, परिणामस्वरूप, न्यूनतम मान max_heap_table_size
  
  
  
  और tmp_table_size
  
  
  
  से चुना जाएगा, और शेष अस्थायी तालिकाओं को डिस्क के साथ बनाया जाएगा। डिफ़ॉल्ट मान सिस्टम पर निर्भर करता है, इसे 32 एमबी पर सेट करने का प्रयास करें और Created_tmp_disk_tables
  
  
  
  स्थिति चर का निरीक्षण करें, इसका मान यथासंभव छोटा होना चाहिए।

कॉन्फ़िगरेशन फ़ाइल में मान बाइट्स में निर्दिष्ट हैं, क्रमशः किलोबाइट और मेगाबाइट को बाइट में परिवर्तित किया जाना चाहिए।

InnoDB

• innodb_additional_mem_pool_size
  
  
  
  - विभिन्न आंतरिक संरचनाओं को संग्रहीत करने के लिए InnoDB द्वारा आवंटित स्मृति का आकार। यदि InnoDB में इस मेमोरी के लिए पर्याप्त नहीं है, तो ओएस से मेमोरी का अनुरोध किया जाएगा और MySQL त्रुटि लॉग को एक चेतावनी लिखी जाएगी।
• innodb_buffer_pool_size
  
  
  
  दोनों इंडेक्स और डेटा को संग्रहीत करने के लिए InnoDB द्वारा आवंटित मेमोरी का आकार है। अर्थ - जितना बेहतर होगा। आप सभी InnoDB तालिकाओं के कुल आकार तक या 80% RAM तक बढ़ा सकते हैं, जो भी छोटा हो।
• innodb_flush_log_at_trx_commit
  
  
  
  - में तीन मान्य मान हैं: 0, 1, 2. यदि मान 0 है , तो लॉग चल रही लेनदेन की परवाह किए बिना, डिस्क को प्रति सेकंड एक बार फ़्लश किया जाता है। 1 के मान के साथ, लॉग को प्रत्येक लेनदेन के साथ डिस्क में फ्लश किया जाता है। 2 के मान के साथ, लॉग प्रत्येक लेन-देन के लिए लिखा जाता है, लेकिन इसे डिस्क पर फ्लश नहीं किया जाता है, जिससे यह ओएस के विवेक पर छोड़ देता है। डिफ़ॉल्ट 1 है, जो सबसे विश्वसनीय सेटिंग है, लेकिन सबसे तेज़ नहीं है। सामान्य तौर पर, आप सुरक्षित रूप से 2 का उपयोग कर सकते हैं, डेटा केवल ओएस क्रैश की स्थिति में और केवल कुछ सेकंड में (ओएस सेटिंग्स के आधार पर) खो सकता है। 0 सबसे तेज़ मोड है, लेकिन OS के क्रैश होने के दौरान और MySQL सर्वर के स्वयं क्रैश होने के दौरान (हालांकि, डेटा केवल 1-2 सेकंड के लिए है) डेटा खो सकता है।
• innodb_log_buffer_size
  
  
  
  - लॉग बफर का आकार। डिफ़ॉल्ट मान 1 MB है, यह बढ़ने के लायक है यदि आप जानते हैं कि बड़ी संख्या में InnoDB लेनदेन होंगे या यदि Innodb_log_waits
  
  
  
  राज्य Innodb_log_waits
  
  
  
  मान बढ़ रहा है। आपको इसे 8 एमबी से ऊपर बढ़ाने की संभावना नहीं है।
• innodb_log_file_size
  
  
  
  - एक लॉग फ़ाइल का अधिकतम आकार। इस आकार तक पहुँचने पर, InnoDB एक नई फ़ाइल बनाएगा। डिफ़ॉल्ट मान 5 एमबी है, आकार में वृद्धि से प्रदर्शन में सुधार होगा, लेकिन डेटा पुनर्प्राप्ति समय में वृद्धि होगी। इस मान को सर्वर के आकार के आधार पर 32 एमबी - 512 एमबी की सीमा में निर्धारित करें (इसे विषय के आधार पर देखते हुए)।

सर्वर ऑपरेशन की निगरानी के लिए phpMyAdmin का उपयोग करना भी सुविधाजनक है; स्थिति और चर टैब रुचि के हैं। इसके अतिरिक्त phpMyAdmin सर्वर ऑपरेशन मापदंडों के आधार पर विभिन्न चर को ट्यून करने की सलाह देता है।

आधिकारिक दस्तावेज और स्वयं के सिर के अलावा, लेख तैयार करने में, निम्नलिखित सामग्रियों का उपयोग किया गया था:

• http://blogfreebsd.com/freebsd/optimizaciya-mysql.html
• http://habrahabr.ru/blogs/mysql/41166/
• http://www.mysqlperformanceblog.com/
• http://mysyslog.ru/posts/223

मैं रुचि के साथ सुनूंगा और टिप्पणियों, टिप्पणियों और परिवर्धन को पढ़ूंगा।

आप लेख की अतिरिक्त चर्चा यहाँ पा सकते हैं।