MariaDB/MySQL ডাটাবেস কমপ্রেস, ডিফ্র্যাগ এবং অপ্টিমাইজ করুন

এই আর্টিকেলে আমরা MySQL/MariaDB-এ টেবিল/ডাটাবেস কমপ্রেশন এবং ডিফ্র্যাগমেন্টেশনের কিছু মেথড এক্সপ্লোর করব, যা আপনাকে ডাটাবেস অবস্থিত ডিস্কে স্পেস বাঁচাতে সাহায্য করবে।

বড় প্রজেক্টের ডাটাবেস সময়ের সাথে সাথে 엄청나게 বেড়ে যায় এবং একটি প্রশ্ন সবসময় উঠে যে এর সাথে কী করা যায়। সমস্যা সমাধানের জন্য кільটি উপায় রয়েছে। আপনি পুরনো তথ্য মুছে ডাটাবেসে ডেটার পরিমাণ কমানো পারেন, ডাটাবেসকে ছোট ছোট টুকরোতে ভাগ করতে পারেন, সার্ভারে ডিস্ক সাইজ বাড়াতে পারেন বা টেবিল কমপ্রেস/শ্রিংক করতে পারেন।

ডাটাবেস ফাংশনিং-এর আরেকটি গুরুত্বপূর্ণ দিক হলো সময়-सময়ে টেবিল এবং ডাটাবেস ডিফ্র্যাগমেন্ট করা যাতে তাদের পারফরম্যান্স উন্নত হয়।

InnoDB টেবিল কমপ্রেশন ও অপ্টিমাইজেশন

ibdata1 এবং ib_log ফাইল

InnoDB টেবিল ব্যবহার করে অধিকাংশ প্রজেক্টের বড় ibdata1 এবং ib_log ফাইল সমস্যা থাকে। অধিকাংশ ক্ষেত্রে, এটি ভুল MySQL/MariaDB কনফিগারেশন বা ডিবি আর্কিটেকচারের সাথে সম্পর্কিত। InnoDB টেবিল থেকে সকল তথ্য ibdata1 ফাইলে সংরক্ষিত থাকে, যার স্পেস tự-ক্রমে রিক্লেইম করা হয় না। আমি টেবিল ডেটা আলাদা ibd* ফাইলগুলিতে সংরক্ষণ করতে পছন্দ করি। এর জন্য, my.cnf-এ নিচের লাইনটি যোগ করুন:

innodb_file_per_table

অথবা

innodb_file_per_table=1

যদি আপনার সার্ভার কনফিগার করা থাকে এবং InnoDB টেবিল সহ কিছু প্রোডাক্টিভ ডাটাবেস থাকে, তবে নিচের ধাপগুলো অনুসরণ করুন:

আপনার সার্ভারের সকল ডাটাবেস ব্যাকআপ নিন (mysql এবং performance_schema বাদে)। আপনি এই কমান্ড ব্যবহার করে ডাটাবেস ডাম্প নিতে পারেন: # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
ডাটাবেস ব্যাকআপ তৈরি করার পর, আপনার mysql/mariadb সার্ভার বন্ধ করুন;
my.cfg-এ সেটিংস পরিবর্তন করুন;
ibdata1 এবং ib_log ফাইলগুলো মুছে ফেলুন;
mysql/mariadb ডিমন শুরু করুন;
ব্যাকআপ থেকে সকল ডাটাবেস পুনরুদ্ধার করুন: # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

এটি করার পর, সকল InnoDB টেবিল আলাদা ফাইলগুলিতে সংরক্ষিত হবে এবং ibdata1 এক্সপোনেনশিয়ালি বেড়ে যাবে না।

InnoDB টেবিল কমপ্রেশন

আপনি টেক্সট/BLOB ডেটা সহ টেবিল কমপ্রেস করতে পারেন এবং ডিস্ক স্পেস অনেকটাই বাঁচাতে পারেন।

আমার একটি 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;

এটি চালানোর পর, আপনি দেখতে পাবেন টেবিলের সাইজ ২৬ MB থেকে ১১ MB-এ কমে গেছে কমপ্রেশনের কারণে।

টেবিল কমপ্রেস করে আপনি আপনার হোস্টে অনেক ডিস্ক স্পেস বাঁচাতে পারেন। তবে, কমপ্রেসড টেবিল নিয়ে কাজ করার সময় CPU লোড বাড়ে। ডিবি টেবিল কমপ্রেশন ব্যবহার করুন যদি আপনার CPU রিসোর্সের সাথে কোনো সমস্যা না থাকে, কিন্তু ডিস্ক স্পেস সমস্যা থাকে।

MySQL/MariaDB-এ MyISAM টেবিল কমপ্রেশন

Myisam টেবিল কমপ্রেস করতে, mysql কনসোলের বদলে সার্ভার কনসোলে একটি বিশেষ কোয়ারি ব্যবহার করুন। একটি টেবিল কমপ্রেস করতে, নিচের কমান্ডটি চালান:

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

যেখানে /var/lib/mysql/test/modx_session আপনার টেবিলের পাথ। দুর্ভাগ্যবশত, আমার কাছে কোনো বড় টেবিল ছিল না এবং আমাকে ছোট টেবিল কমপ্রেস করতে হলো, তবে ফলাফলটিও দেখা যায় (ফাইলটি ২৫ MB থেকে ১৮ MB-এ কমপ্রেস হয়েছে):

# du -sh modx_session.MYD

25M modx_session.MYD

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

Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records)
- Calculating statistics
- Compressing file 29.84%
Remember to run myisamchk -rq on compressed tables

# du -sh modx_session.MYD

18M modx_session.MYD

আমি কমান্ডে -b কী ব্যবহার করেছিলাম। আপনি যখন এটি যোগ করেন, কমপ্রেশনের আগে টেবিলটি ব্যাকআপ নেয় এবং OLD লেবেল দিয়ে চিহ্নিত করে:

# ls -la modx_session.OLD

-rw-r----- 1 mysql mysql 25550000 Dec 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;

এভাবে, আপনি কমপক্ষে ৫০ MB অনুপযোগী স্পেস সহ সকল টেবিল দেখতে পাবেন:

+-------------------------------+----------------+--------------+
| TABLE_NAME                    | data_length_mb | data_free_mb |
+-------------------------------+----------------+--------------+
| b_disk_deleted_log_v2         | 402            | 64           |
| b_crm_timeline_bind           | 827            | 150          |
| b_disk_object_path            | 980            | 72           |
+-------------------------------+----------------+--------------+

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 mysql 981M Oct 17 11:54 b_disk_object_path.MYD

এই টেবিলগুলো অপ্টিমাইজ করতে, mysql কনসোলে নিচের কমান্ডটি চালান:

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

সফল ডিফ্র্যাগমেন্টেশনের পর, আপনি এভাবে আউটপুট দেখবেন:

+-------------------------------+----------------+--------------+
| TABLE_NAME                    | data_length_mb | data_free_mb |
+-------------------------------+----------------+--------------+
| b_disk_deleted_log_v2         | 74             | 0            |
| b_crm_timeline_bind           | 115            | 0            |
| b_disk_object_path            | 201            | 0            |
+-------------------------------+----------------+--------------+

আপনি দেখতে পাচ্ছেন, data_free_mb এখন ০ এবং টেবিলের সাইজ উল্লেখযোগ্যভাবে কমে গেছে (৩-৪ গুণ)।

আপনি আপনার সার্ভার কনসোলে 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_notify note : Table does not support optimize, doing recreate + analyze instead status : OK
innodb_test.b_admin_notify_lang note : Table does not support optimize, doing recreate + analyze instead status : OK
innodb_test.b_adv_banner note : Table does not support optimize, doing recreate + analyze instead status : OK

# du -sh

1.7G

এভাবে, আপনার সার্ভারে স্পেস বাঁচাতে, আপনি সময়-सময়ে আপনার MySQL/MariaDB টেবিল এবং ডাটাবেস অপ্টিমাইজ এবং কমপ্রেস করতে পারেন। যেকোনো অপ্টিমাইজেশন কাজ করার আগে ডাটাবেস ব্যাকআপ নেওয়া মনে রাখবেন।