मारियाडीबी/माईएसक्यूएल डाटाबेस को कंप्रेस, डीफ्रैग और ऑप्टिमाइज़ करें

इस लेख में हम MySQL/MariaDB में टेबल/डेटाबेस कम्प्रेशन और डीफ़्रैग्मेन्टेशन के कुछ तरीकों का पता लगाएंगे, जो आपको उस डिस्क पर स्थान बचाने में मदद करेंगे जिस पर डेटाबेस स्थित है।

बड़ी परियोजनाओं के डेटाबेस समय के साथ अत्यधिक बढ़ते हैं और एक प्रश्न हमेशा उठता है कि इसका क्या किया जाए। समस्या को हल करने के कई तरीके हैं। आप पुरानी जानकारी को हटाकर, डेटाबेस को छोटे में विभाजित करके, सर्वर पर डिस्क का आकार बढ़ाकर या तालिकाओं को संकुचित/संकुचित करके डेटाबेस में डेटा की मात्रा को कम कर सकते हैं।

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

InnoDB तालिकाएँ संपीड़न और अनुकूलन

ibdata1 और ib_log फ़ाइलें

InnoDB . वाले अधिकांश प्रोजेक्ट तालिकाओं में बड़ी समस्या है ibdata1 और ib_log फ़ाइलें। ज्यादातर मामलों में, यह गलत MySQL/MariaDB कॉन्फ़िगरेशन या DB आर्किटेक्चर से संबंधित है। InnoDB तालिकाओं की सभी जानकारी ibdata1 फ़ाइल में संग्रहीत की जाती है, जिसके स्थान को अपने आप पुनः प्राप्त नहीं किया जाता है। मैं टेबल डेटा को अलग ibd* . में स्टोर करना पसंद करता हूं फ़ाइलें। ऐसा करने के लिए, निम्न पंक्ति को my.cnf . में जोड़ें :

innodb_file_per_table

या

innodb_file_per_table=1

यदि आपका सर्वर कॉन्फ़िगर किया गया है और आपके पास InnoDB तालिकाओं के साथ कुछ उत्पादक डेटाबेस हैं, तो निम्न कार्य करें:

1. अपने सर्वर पर सभी डेटाबेस का बैकअप लें (mysql और performance_schema को छोड़कर)। आप इस कमांड का उपयोग करके एक डेटाबेस डंप प्राप्त कर सकते हैं: # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. डेटाबेस बैकअप बनाने के बाद, अपने mysql/mariadb सर्वर को बंद कर दें;
3. my.cfg में सेटिंग बदलें;
4. मिटाएं ibdata1 और ib_log फ़ाइलें;
5. mysql/mariadb daemon प्रारंभ करें;
6. बैकअप से सभी डेटाबेस पुनर्स्थापित करें:# mysql -u [username] –p[password] [database_name] < [dump_file.sql]

ऐसा करने के बाद, सभी InnoDB तालिकाओं को अलग-अलग फ़ाइलों में संग्रहीत किया जाएगा और ibdata1 तेजी से बढ़ना बंद कर देगा।

InnoDB तालिका संपीड़न

आप टेक्स्ट/बीएलओबी डेटा के साथ टेबल को कंप्रेस कर सकते हैं और काफी डिस्क स्थान बचा सकते हैं।

मेरे पास एक innodb_test डेटाबेस है जिसमें टेबल हैं जिन्हें संभावित रूप से संपीड़ित किया जा सकता है और इस प्रकार मैं कुछ डिस्क स्थान मुक्त कर सकता हूं। कुछ भी करने से पहले, मैं सभी डेटाबेस का बैकअप लेने की सलाह देता हूं। एक mysql सर्वर से कनेक्ट करें:

# mysql -u root -p

अपने mysql कंसोल में आवश्यक डेटाबेस का चयन करें:

# use innodb_test;

तालिकाओं और उनके आकारों की सूची प्रदर्शित करने के लिए, निम्न क्वेरी का उपयोग करें:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
ORDER BY (data_length + index_length) DESC;

जहां innodb_test आपके डेटाबेस का नाम है।

कुछ तालिकाओं को संकुचित किया जा सकता है। आइए देखें b_crm_event_relations एक उदाहरण के रूप में तालिका। यह क्वेरी चलाएँ:

mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

इसे चलाने के बाद, आप देख सकते हैं कि संपीड़न के कारण तालिका का आकार 26 एमबी से घटकर 11 एमबी हो गया है।

तालिकाओं को संपीड़ित करके, आप अपने होस्ट पर बहुत अधिक डिस्क स्थान बचा सकते हैं। हालाँकि, संपीड़ित तालिकाओं के साथ काम करते समय, CPU लोड बढ़ता है। यदि आपको CPU संसाधनों में कोई समस्या नहीं है, लेकिन डिस्क स्थान की समस्या है, तो db तालिकाओं के लिए संपीड़न का उपयोग करें।

MyISAM तालिका संपीड़न MySQL/MariDB में

मायिसम . को संपीड़ित करने के लिए टेबल, mysql कंसोल के बजाय सर्वर कंसोल में एक विशेष क्वेरी का उपयोग करें। किसी तालिका को संपीड़ित करने के लिए, निम्न चलाएँ:

# myisampack -b /var/lib/mysql/test/modx_session

जहां /var/lib/mysql/test/modx_session आपकी तालिका का पथ है। दुर्भाग्य से, मेरे पास एक बड़ी तालिका नहीं थी और छोटे लोगों को संपीड़ित करना पड़ा, लेकिन परिणाम अभी भी देखा जा सकता था (फ़ाइल 25 एमबी से 18 एमबी तक संकुचित थी):

# du -sh modx_session.MYD

25M modx_session.MYD

# myisampack -b /var/lib/mysql/test/modx_session

संपीड़ित /var/lib/mysql/test/modx_session.MYD:(4933 अभिलेख)- आंकड़ों की गणना करना- फ़ाइल को संपीड़ित करना29.84%संपीड़ित तालिकाओं पर myisamchk -rq चलाना याद रखें

# du -sh modx_session.MYD

18M modx_session.MYD

मैंने -b . का इस्तेमाल किया कमांड में कुंजी। जब आप इसे जोड़ते हैं, तो संपीड़न से पहले एक तालिका का बैकअप लिया जाता है और पुराने लेबल के साथ चिह्नित किया जाता है:

# ls -la modx_session.OLD

-rw-r----- 1 mysql mysql 25550000 दिसंबर 17 15:20 modx_session.OLD

# du -sh modx_session.OLD

25M modx_session.OLD

MySQL और MariaDB में टेबल और डेटाबेस को ऑप्टिमाइज़ करना

तालिकाओं और डेटाबेस को अनुकूलित करने के लिए, उन्हें डीफ़्रैग्मेन्ट करने की अनुशंसा की जाती है। सुनिश्चित करें कि क्या डेटाबेस में कोई तालिका है जिसके लिए डीफ़्रैग्मेन्टेशन की आवश्यकता है।

MySQL कंसोल खोलें, एक डेटाबेस चुनें और इस क्वेरी को चलाएं:

select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;

इस प्रकार, आप कम से कम 50 एमबी अप्रयुक्त स्थान वाली सभी तालिकाएँ प्रदर्शित करेंगे:

data_length_mb — तालिका का कुल आकार

data_free_mb — तालिका में अप्रयुक्त स्थान

ये वे टेबल हैं जिन्हें हम डीफ़्रैग्मेन्ट कर सकते हैं। जांचें कि वे डिस्क पर कितनी जगह घेरते हैं:

# ls -lh /var/lib/mysql/innodb_test/ | grep b_

-rw-r----- 1 mysql mysql 402M Oct 17 12:12 b_disk_deleted_log_v2.MYD-rw-r----- 1 mysql mysql 828M Oct 17 13:23 b_crm_timeline_bind.MYD-rw-r- ---- 1 mysql 981M अक्टूबर 17 11:54 b_disk_object_path.MYD

इन तालिकाओं को अनुकूलित करने के लिए, mysql कंसोल में निम्न कमांड चलाएँ:

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

सफल डीफ़्रैग्मेन्टेशन के बाद, आप इस तरह एक आउटपुट देखेंगे:

जैसा कि आप देख सकते हैं, data_free_mb अब 0 के बराबर है और तालिका का आकार काफी कम हो गया है (3 - 4 गुना)।

आप mysqlcheck . का उपयोग करके डीफ़्रेग्मेंटेशन भी चला सकते हैं आपके सर्वर कंसोल में:

# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file

जहां innodb_test क्या आपका डेटाबेस
और b_workflow_file है तालिका का नाम है

डेटाबेस में सभी तालिकाओं को अनुकूलित करने के लिए, इस कमांड को अपने सर्वर कंसोल में चलाएँ:
# mysqlcheck -o innodb_test -u root -p

जहां innodb_test एक डेटाबेस नाम है

या सर्वर पर सभी डेटाबेस का अनुकूलन चलाएँ:

# mysqlcheck -o --all-databases -u root -p

यदि आप अनुकूलन से पहले और बाद में डेटाबेस आकार की जांच करते हैं, तो आप देखेंगे कि कुल आकार कम हो गया है:

# du -sh

2.5G

# mysqlcheck -o innodb_test -u root -p

innodb_test.b_admin_notifynote:तालिका अनुकूलन का समर्थन नहीं करती है, इसके बजाय फिर से बनाना + विश्लेषण करना स्थिति:OKinnodb_test.b_admin_notify_langnote:तालिका अनुकूलन का समर्थन नहीं करती है, इसके बजाय फिर से बनाएं + विश्लेषण करें स्थिति:OKinnodb_test.b_adv_bannernote:तालिका अनुकूलन का समर्थन नहीं करती है, इसके बजाय पुन:निर्माण + विश्लेषण करती है। :ठीक है

# du -sh

1.7G

इस प्रकार, अपने सर्वर पर स्थान बचाने के लिए, आप समय-समय पर अपने MySQL/MariDB तालिकाओं और डेटाबेस को अनुकूलित और संपीड़ित कर सकते हैं। कोई भी अनुकूलन कार्य करने से पहले डेटाबेस का बैकअप लेना याद रखें।