home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> ระบบ >> Linux

บีบอัด, Defrag และเพิ่มประสิทธิภาพฐานข้อมูล MariaDB/MySQL

ฐานข้อมูลขนาดใหญ่เติบโตเรื่อยๆ ทำให้ดิสก์เต็มและประสิทธิภาพลดลง วิธีแก้: ลบข้อมูลเก่า, แยก Database, เพิ่มดิสก์, หรือ Compress/Defrag Tables

InnoDB: Compression & Optimization

ปัญหา ibdata1 และ ib_log เติบโตไม่รู้จบ

เพิ่มใน my.cnf:

innodb_file_per_table = 1

ขั้นตอนย้ายข้อมูล (ต้อง Backup ก่อน):

  1. mysqldump -u user -p dbname > dump.sql (ทุก DB ยกเว้น mysql, performance_schema)
  2. systemctl stop mariadb
  3. เพิ่ม innodb_file_per_table=1 ใน my.cnf
  4. ลบ ibdata1 และ ib_logfile*
  5. systemctl start mariadb
  6. mysql -u user -p dbname < dump.sql

ผล: แต่ละ Table จะมีไฟล์ .ibd แยกต่างหาก ibdata1 จะไม่เติบโตเร็ว

บีบอัดตาราง InnoDB (Table Compression)

# ดูขนาดตาราง
SELECT table_name AS "Table", ROUND(((data_length + index_length)/1024/1024),2) AS "Size (MB)"
FROM information_schema.TABLES WHERE table_schema = "your_db" ORDER BY (data_length+index_length) DESC;

# บีบอัด (ต้องเป็น Barracuda File Format)
ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED;
# หรือ KEY_BLOCK_SIZE=8 (สำหรับ Page Compression)
ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

ตัวอย่าง: ตาราง b_crm_event_relations ลดจาก 26 MB → 11 MB (CPU Load เพิ่มเล็กน้อย)

MyISAM Table Compression

ใช้ myisampack (Read-only หลังบีบอัด):

myisampack -b /var/lib/mysql/test/modx_session
# -b = Backup ตารางเดิมเป็น .OLD
# หลังบีบอัด ต้องรัน: myisamchk -rq /var/lib/mysql/test/modx_session

ผล: 25 MB → 18 MB (Compression ~30%)

Optimize / Defragment Tables

หาตารางที่มี Free Space มาก (Fragmented)

SELECT table_name, ROUND(data_length/1024/1024) AS data_mb, ROUND(data_free/1024/1024) AS free_mb
FROM information_schema.tables
WHERE ROUND(data_free/1024/1024) > 50
ORDER BY free_mb DESC;

รัน OPTIMIZE TABLE

OPTIMIZE TABLE `table1`, `table2`, `table3`;

หรือใช้ mysqlcheck:

mysqlcheck -o database_name -u root -p           # Optimize all tables in DB
mysqlcheck -o --all-databases -u root -p         # Optimize all DBs

ผลลัพธ์

Free Space → 0, ขนาดตารางลดลง 3-4 เท่า, ขนาด Database รวมลดจาก 2.5 GB → 1.7 GB

ข้อควรระวัง

  • Backup ก่อนทุกครั้ง
  • OPTIMIZE TABLE จะ Lock Table (ใช้ pt-online-schema-change สำหรับ Production)
  • Compression เพิ่ม CPU Load – ใช้เมื่อ Disk เป็นจุดคอขวด
  • MyISAM หลัง myisampack จะ Read-only ต้อง Unpack ก่อนเขียนได้