7.1 डीबी पक्ष पर कैशिंग

तालिकाओं के साथ काम करते समय MySQL अत्यधिक स्केलेबल एल्गोरिदम का उपयोग करता है, इसलिए MySQL कम मात्रा में मेमोरी के साथ भी चल सकता है। स्वाभाविक रूप से, बेहतर प्रदर्शन के लिए आपको अधिक रैम की आवश्यकता होती है।

वर्तमान सेटिंग देखने के लिए, डेटाबेस से कनेक्ट करें


#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

सबसे पहले, सुनिश्चित करें कि कैशिंग सक्षम है। चर:

  • query_cache_typeऑन (1) या डिमांड (2) होना चाहिए
  • query_cache_limit- कैश में आने वाले परिणाम का अधिकतम आकार निर्धारित करता है
  • query_cache_sizeशून्य से भिन्न हो। डिमांड का उपयोग करते समय, केवल निर्देश वाले अनुरोधों को कैश किया जाएगाSQL_CACHE;
  • query_cache_min_res_unitकैश्ड क्वेरी के परिणामों को संग्रहीत करने के लिए मेमोरी के आवंटित ब्लॉक का न्यूनतम आकार। MySQL कैश को मेमोरी के एक बड़े हिस्से में संग्रहीत नहीं करता है, इसके बजाय यह query_cache_min_res_unitमांग पर न्यूनतम आकार (=4KB डिफ़ॉल्ट रूप से) के साथ ब्लॉक आवंटित करता है। इस तरह के अंतिम ब्लॉक को डेटा आकार में छोटा कर दिया जाता है, और शेष मेमोरी को मुक्त कर दिया जाता है।

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

और अगर आपके पास कैश में बहुत सारे छोटे अनुरोध हैं, तो यह बड़ी संख्या में मुक्त ब्लॉकों के कारण मेमोरी विखंडन का कारण बन सकता है। और यह, बदले में, मेमोरी की कमी के कारण कैश्ड प्रविष्टियों को हटाने का कारण बनता है। इस मामले में, के मूल्य को कम करना समझ में आता है query_cache_min_res_unit। यदि आपकी अधिकांश क्वेरीज़ से बड़े परिणाम प्राप्त होते हैं, तो इस सेटिंग को बढ़ाने से प्रदर्शन में सुधार हो सकता है.

Java webinar

आइए प्रभाव का मूल्यांकन करने का प्रयास करें। हम देखते हैं कि कैश हिट काउंटर कैसे बदलते हैं (Qcahe_hits), मेमोरी की कमी (Qcache_lowmem_prunes) के कारण अमान्य घोषित किए गए अनुरोधों की संख्या, SELECT प्रकार के अनुरोधों की कुल संख्या (और केवल उन्हें कैश किया जाता है):


#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 वर्तमान कैश स्थिति

क्वेरी कैश की निगरानी के लिए प्रयोग किया जाता है SHOW STATUS:


mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)
  • Qcache_free_blocksदिखाता है कि कैश में कितने फ्री ब्लॉक हैं (कैश किए गए अनुरोधों में वृद्धि के रूप में कमी आएगी);
  • Qcache_total_blocks- कब्जे वाले ब्लॉकों की संख्या;
  • Qcache_free_memory- कैशिंग के लिए मुफ्त "उपलब्ध" मेमोरी दिखाता है;
  • Qcache_hits- अनुरोधों की संख्या, जिसके परिणाम वास्तव में डेटाबेस तक पहुँचने के बिना कैश से लिए गए थे;
  • Qcache_inserts- कैश में जोड़े गए अनुरोधों की संख्या;
  • Qcache_lowmem_prunes- स्मृति की कमी के कारण कैश से हटाए गए अनुरोधों की संख्या;
  • Qcache_not_cached- समय प्रबंधन कार्यों, आदि के उपयोग के कारण कैश में नहीं लिखे गए अनुरोधों की संख्या;
  • Qcache_queries_in_cache- कैश में अनुरोधों की संख्या।

आप SELECT प्रश्नों की कुल संख्या देख सकते हैं:


mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 कैशिंग दक्षता

“चर के मान को विभाजित करके कैश की प्रभावशीलता का मूल्यांकन करने की सिफारिश की जाती है Qcache_hits on Qcache_hits + Com_select, क्योंकि अनुरोध को संसाधित करते समय, Qcache_hits काउंटर बढ़ता है (यदि अनुरोध कैश से संसाधित किया जाता है) या Com_select (यदि अनुरोध कैश नहीं किया गया है)। यह विधि "माइस्क्ल प्रदर्शन अनुकूलन" ओरेली में सुझाई गई है

ऑनलाइन एक और तरीका है

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

यदि यह मान > 0.8 है, तो आपके 80% अनुरोध कैश किए गए हैं, जो एक बहुत अच्छा संकेतक है।

यदि %कैश हिट कम है, तो आपको मान बढ़ाने की आवश्यकता है query_cache_size।

वर्तमान मूल्य को इस प्रकार देखा जा सकता है:

SHOW VARIABLES LIKE 'query_cache_size';

फिर से, सवाल उठता है: पर्याप्त मूल्य कैसे चुनेंquery_cache_size?

इससे मदद मिलेगी Qcache_lowmem_prunes। यह चर नए अनुरोधों को कैश करने की आवश्यकता के कारण कैश से हटाए गए अनुरोधों की संख्या को संग्रहीत करता है। ऐसे कैश आकार के लिए प्रयास करना आवश्यक है जिस पर Qcache_lowmem_prunesयह थोड़ा ही बढ़ेगा। ऐसा करने के लिए, प्रति घंटे मूल्यों में अंतर Qcache_lowmem_prunesऔर उसी घंटे में MySQL द्वारा प्राप्त अनुरोधों की संख्या की तुलना करने की अनुशंसा की जाती है।

"व्यवहार में, 2 सूत्रों में से एक का उपयोग query_cache_size की गणना के लिए किया जा सकता है:

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

या

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (10 मिनट में ट्रैफिक वॉल्यूम) * 1.2 "

यह 10 मिनट के लिए अनुरोधों को कैश करेगा + कैश विखंडन और अतिरिक्त कैशिंग रिजर्व के लिए अतिरिक्त 20% मेमोरी देगा

आप क्रमशः Bytes_sent चर का उपयोग करके अनुरोध की प्रतिक्रिया की संख्या और औसत मात्रा की गणना कर सकते हैं

और इसलिए query_cache_sizeहमने मूल्यों में वृद्धि की, जिसके बाद आपको मूल्यों पर ध्यान देना चाहिए Qcache_total_blocks, Qcache_free_blocksऔर Qcache_queries_in_cache। MySQL कैश को ब्लॉक में स्टोर करता है। 1 अनुरोध के लिए, 2 ब्लॉक आवश्यक हैं: एक अनुरोध टेक्स्ट के लिए, दूसरा परिणाम के लिए।

यदि हम मूल्य से तालिका पर विचार करेंQcache%

कैश ब्लॉक की कुल संख्याQcache_total_blocks – 28

अनुरोध 6 अब कैश हो गया है, जिसका अर्थ है कि 6 * 2 = 12 ब्लॉक व्यस्त हैं

मुक्त ब्लॉक Qcache_free_blocks – 10। अधिक निष्क्रिय Qcache_free_blocks, कैश के "विखंडन" की डिग्री जितनी अधिक होगी।

यदि अधिकांश प्रश्नों में परिणामी डेटा की थोड़ी मात्रा होती है, तो यह न्यूनतम कैश ब्लॉक आकार को कम करने के लायक है query_cache_min_res_unit, जो कि डिफ़ॉल्ट रूप से 4 KB है।

यदि अधिकांश अनुरोध बहुत अधिक डेटा लौटाते हैं, तो यह कैश ब्लॉक के आकार को बढ़ाने के लायक है।

मुख्य बात न्यूनतम मूल्य प्राप्त करना है Qcache_free_blocks।

यदि काउंटर Qcache_not_cachedबड़ा है, तो आप चर को बढ़ाने का प्रयास कर सकते हैं query_cache_limit- यह आपको सीमा बढ़ाने और "फिट नहीं होने वाले" प्रश्नों के परिणामों को कैश करने की अनुमति देगा।

क्वेरी कैश का उपयोग करने के लिए निम्न कॉन्फ़िगरेशन चर जिम्मेदार हैं:

  • query_cache_size- क्वेरी कैश का आकार। query_cache_size = 0कैश उपयोग अक्षम करता है;
  • query_cache_limit- कैश में संग्रहीत अधिकतम नमूने का आकार;
  • query_cache_wlock_invalidate- यह निर्धारित करता है कि क्या कैश से डेटा लिया जाएगा यदि वे जिस तालिका से संबंधित हैं उसे पढ़ने के लिए बंद कर दिया गया है।
    • =

Mysql क्वेरी कैशिंग को सक्षम करने के लिए, बस निम्नलिखित पंक्तियों को my.cnf (अनुभाग [mysqld]) में जोड़ें:


query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

और सेवा को पुनः आरंभ करें।

7.4 जहां कैश का उपयोग नहीं किया जाता है

कैश नहीं किया गया:

  • से अनुरोधSQL_NO_CACHE
  • तैयार प्रश्न(Prepared statements);
  • वे क्वेरीज़ जो बाहरी क्वेरी की सबक्वेरीज़ हैं;
  • संग्रहित प्रक्रियाओं और कार्यों के अंदर प्रश्न;
  • फ़ंक्शंस का उपयोग करने वाली क्वेरीज़:

    BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()एक तर्क के साथ , , FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP ()बिना तर्क के, ,SYSDATE ()UNIX_TIMESTAMP ()USER ()UUID ();

  • Mysql सिस्टम डेटाबेस या INFORMATION_SCHEMA में संग्रहीत फ़ंक्शंस, उपयोगकर्ता चर या संदर्भित तालिकाओं का उपयोग करने वाली क्वेरी ;
  • • निम्नलिखित रूपों में अनुरोध:
    • SELECT ... IN SHARE MODE
    • SELECT ... FOR UPDATE
    • SELECT ... INTO OUTFILE ...
    • SELECT ... INTO DUMPFILE ...
    • SELECT * FROM ... WHERE autoincrement_col IS NULL
  • अस्थायी तालिकाओं के साथ प्रश्न या तालिकाओं का उपयोग बिल्कुल नहीं करना;
  • चेतावनियाँ उत्पन्न करने वाले अनुरोध (चेतावनियाँ);

आप कैश को कमांड से डीफ़्रैग्मेन्ट कर सकते हैं:

mysql>flush query cache;

साफ़ करें - आदेश:

mysql>flush query cache;

सबसे महत्वपूर्ण

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