धीमी गति और अद्यतन के लिए MYSQL तालिका कारण पर कई सूचकांक हैं?

Dec 10 2020

मेरी (LAMP स्टैक) साइट का प्रदर्शन बिना किसी कोड अपडेट के बावजूद पिछले कुछ दिनों में काफी कम हो गया है। ऐसा लगता है कि किसी विशेष MySQL तालिका के केवल आवेषण, अद्यतन और हटाए जाने से समस्या होती है। कोई भी पृष्ठ जो "तालिका" नौकरियों की प्रविष्टि को अपडेट, सम्मिलित या हटाता है, उसे लोड करने में लगभग 10 सेकंड लगते हैं। (ईजी UPDATE jobs SET title = 'sdfldsfjlk' WHERE job_id = 134324)

SELECT प्रश्न पहले की तरह ही निष्पादित होते हैं, हालांकि यदि कोई अपडेट उसी समय हो रहा हो तो वे धीमे लगते हैं।

तालिका में लगभग 180,000 प्रविष्टियाँ हैं। मैंने PHPMyAdmin दृश्य में देखा, कि प्राथमिक क्षेत्र पर "सामान्य" सूचकांक के अलावा, "प्रविष्टि_डेट" फ़ील्ड (छवि देखें) पर एक सूचकांक है। क्या इस मामले में कोई मुद्दा हो सकता है? मुझे नहीं पता कि उस क्षेत्र पर एक सूचकांक क्यों बनाया गया होगा।

यदि नहीं, तो समस्या का स्रोत क्या हो सकता है? मैंने डिस्क पर जगह की जाँच की है, जो ठीक लगता है। (7 GB उपलब्ध) df के अनुसार।

SHOW CREATE TABLE job\G

Create Table: CREATE TABLE `job` (
    `job_id` int(11) NOT NULL AUTO_INCREMENT, 
    `user_id` int(11) NOT NULL DEFAULT '0', 
    `entry_date` date NOT NULL DEFAULT '0000-00-00', 
    `timescale` varchar(20) COLLATE latin1_german2_ci NOT NULL DEFAULT '0000-00-00', 
    `title` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `description` text COLLATE latin1_german2_ci NOT NULL, 
    `start_date` varchar(60) COLLATE latin1_german2_ci NOT NULL, 
    `address_town` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `address_county` varchar(40) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode1` varchar(4) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `postcode2` char(3) COLLATE latin1_german2_ci NOT NULL DEFAULT '', 
    `status` tinyint(4) NOT NULL DEFAULT '0', 
    `cat_id` int(4) NOT NULL DEFAULT '0', 
    `price` decimal(4,2) NOT NULL DEFAULT '1.00', 
    `emailcount` smallint(5) NOT NULL DEFAULT '-1', 
    `emailcount2` int(11) NOT NULL DEFAULT '-1', 
    `recemailcount` int(11) NOT NULL DEFAULT '-1', 
    `archive` tinyint(4) NOT NULL DEFAULT '0', 
    `post_url` varchar(100) COLLATE latin1_german2_ci NOT NULL, 
    PRIMARY KEY (`job_id`), 
    KEY `entrydatejob_id` (`entry_date`,`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=235844
           DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

अद्यतन - सबसे पहले सभी योगदानकर्ताओं को धन्यवाद। मैं इसकी बहुत कदर करता हूँ। इसलिए पिछले कुछ दिनों में, समस्या बंद हो गई, जिसने यह पता लगाने की कोशिश की कि क्या समस्या और भी मुश्किल हो सकती है। लेकिन अब एक बार फिर से वापसी होती दिख रही है। मुझे Google क्लाउड पर होस्ट की गई मशीन प्रकार प्रदान करके शुरू करें: g1-small (1 vCPU, 1.7 GB मेमोरी)। मैं इसे आगे की जानकारी के साथ अपडेट करना जारी रखूंगा जो टिप्पणी करने वालों द्वारा अनुरोध किया गया है।

जवाब

6 ypercubeᵀᴹ Dec 10 2020 at 19:22

विशिष्ट प्रश्न का उत्तर देने के लिए:

नहीं , एक (entry_date)या दो पर एक अतिरिक्त सूचकांक एक अलग, titleस्तंभ को अद्यतन करने के प्रदर्शन को चोट नहीं पहुंचाएगा ।

इसके अतिरिक्त:

नहीं , MySQL, 5.6 का संस्करण बहुत प्राचीन नहीं है, भले ही कुछ आधुनिक सुविधाएँ (जैसे विंडो फ़ंक्शंस) गायब हों। आपके पास सभ्य हार्डवेयर के साथ अच्छा प्रदर्शन होना चाहिए।

हम केवल हाथ में मुद्दे पर अटकलें लगा सकते हैं लेकिन क्या गलत हो सकता है या इसे समझा सकता है:

पुराना, अक्षम हार्डवेयर: डिस्क विनिर्देशों की जांच करें, मापें कि यह पूर्वरूप है।
खंडित सूचकांक / तालिका। MySQL डॉक्स और पुराने प्रश्नों / उत्तरों की जाँच करें कि यहाँ MyISAM टेबल को कैसे डिफ्रैग्मेंट किया जाए (OPTIMIZE TABLE)।

अंतिम पर कम नहीं:

आपकी तालिका MyISAM इंजन का उपयोग कर रही है , जो कि पुरानी खबर है। InnODB ने इसे MySQL में डिफ़ॉल्ट इंजन के रूप में वर्षों पहले बदल दिया है। इस इंजन का कोई सक्रिय विकास नहीं है। इसमें InnoDB (लेनदेन, विदेशी कुंजी की कमी, आदि) और अधिकांश कार्य भार में प्रदर्शन की तुलना में कई विशेषताओं का अभाव है । मैं आपको दृढ़ता से सुझाव देता हूं कि आप अपनी तालिका बदलने के बाद (कोर्स के परीक्षण के बाद कि आपके ऐप्स और प्रक्रियाएं नहीं टूटती हैं) InnoDB का उपयोग करें।
महत्वपूर्ण रूप से आपके प्रश्न के लिए, InnoDB प्रदर्शन कर सकता है SELECTऔर UPDATEएक ही समय में (आमतौर पर)। MyISAM पूरी तरह से टेबल को लॉक करता है; अद्यतन या चयन से पहले समाप्त होना चाहिए या चयन भी शुरू हो सकता है।
MyISAM से InnoDB पर स्विच करते समय, समायोजित करना key_buffer_sizeऔर सुनिश्चित करें innodb_buffer_pool_size।

3 J.D. Dec 10 2020 at 19:20

इंडेक्स INSERT और DELETE संचालन के प्रदर्शन को थोड़ा प्रभावित करते हैं, लेकिन आम तौर पर यह नगण्य है खासकर यदि आपके अनुक्रमित अच्छी तरह से नियोजित हैं और आप 30 अलग-अलग क्षेत्रों के संयोजन के साथ एक ही टेबल पर 30 इंडेक्स लगाकर ओवरबोर्ड नहीं जाते हैं। (आम तौर पर मैं 5 से 5 दिशानिर्देशों से चिपक जाता हूं - 5 सूचकांक अधिकतम, 5 क्षेत्र प्रति सूचकांक अधिकतम। बेशक यह सिर्फ एक दिशानिर्देश है और एक कठिन नियम नहीं है)।

कहा जा रहा है कि, इंडेक्स का उपयोग वास्तव में UPDATE और SELECT क्वेरीज के प्रदर्शन को बढ़ाने के लिए किया जाता है क्योंकि इंडेक्स का उपयोग UPDATE या SELECT को पंक्तियों का पता लगाने के लिए किया जाता है।

जो कठोर परिवर्तन आप देख रहे हैं, वह आपके द्वारा खोजे गए entry_date अनुक्रमणिका से संबंधित होना संदिग्ध है (क्या आपको पता है कि हाल ही में जोड़ा गया था या प्रदर्शन परिवर्तन से पहले ही हो चुका है?)।

आपको जिन दो चीजों पर ध्यान देना चाहिए, वे हैं:

यदि आपके प्रश्नों के लिए पिछले अनुक्रमणिका का उपयोग अभी भी किया जा रहा है, विशेष रूप से अद्यतन और चयनित प्रश्नों के लिए (और यदि उन्हें खोजा जा रहा है या अब स्कैन किया जा रहा है)। समय के साथ आंकड़ों की तालिका के आंकड़ों में बदलाव के कारण यह बदल सकता है। तालिका के आंकड़ों की जांच करने के लिए आप ANALYZE कथन का उपयोग कर सकते हैं ।
दूसरी चीज जिसे आप देख सकते हैं, वह है सूचकांक विखंडन , जो समय के साथ एक प्राकृतिक घटना है। ऐसा तब होता है जब एक तालिका में अधिक डेटा जोड़ा जाता है। (आम तौर पर यह आपकी तरह एक छोटी मेज पर एक चिंता का विषय नहीं होना चाहिए, लेकिन यह वैसे भी इसमें जाँच के लायक है।)

मैं अपने उत्तर को और अधिक चीजों के साथ अपडेट करना जारी रखूंगा क्योंकि मैं उनके बारे में सोचता हूं। अपने प्रश्नों पर एक अतिरिक्त चल रहा है आप इस मुद्दे पर भी सुराग में मदद कर सकते हैं। क्या UPDATE और INSERT स्टेटमेंट को अलग-अलग करने की कोशिश की जा रही है जो धीरे-धीरे चल रहे हैं और यह समझने की कोशिश करें कि क्या वे एक ही मूल कारण से प्रभावित हो रहे हैं (क्योंकि फिर से वे इंडेक्स के कुछ हद तक विपरीत कार्य करते हैं, INSERT लापरवाही से धीमे होते हैं लेकिन UPPATE को आमतौर पर सही इंडेक्स से लाभ होता है और तेज होना चाहिए।)

WilsonHauck Dec 10 2020 at 22:40

वैकल्पिक काम; विखंडन को खत्म करने के लिए और सभी अनुक्रमित बनाए गए हैं।

फिर अपनी UPDATE क्वेरी टाइमिंग की जाँच करें।

प्रोफ़ाइल देखें, प्रदर्शन ट्यूनिंग के साथ सहायता के लिए मुफ्त डाउनलोड करने योग्य उपयोगिता लिपियों के लिए नेटवर्क प्रोफ़ाइल।