Trong bài viết này, chúng ta sẽ khám phá một số phương pháp nén và defragment bảng/cơ sở dữ liệu trong MySQL/MariaDB, giúp bạn tiết kiệm dung lượng ổ cứng nơi cơ sở dữ liệu đặt.
Cơ sở dữ liệu của các dự án lớn tăng trưởng khổng lồ theo thời gian và câu hỏi luôn nảy ra là làm sao xử lý nó. Có một vài cách giải quyết. Bạn có thể giảm lượng dữ liệu trong cơ sở dữ liệu bằng cách xóa thông tin cũ, chia cơ sở dữ liệu thành các cái nhỏ hơn, tăng kích thước ổ cứng trên máy chủ hoặc nén/thu nhỏ bảng.
Một khía cạnh quan trọng khác của hoạt động cơ sở dữ liệu là cần defragment bảng và cơ sở dữ liệu theo thời gian để cải thiện hiệu suất của chúng.
Nén & Tối ưu bảng InnoDB
File ibdata1 và ib_log
Hầu hết các dự án với bảng InnoDB gặp vấn đề về file ibdata1 và ib_log lớn. Trong hầu hết các trường hợp, điều này liên quan đến cấu hình MySQL/MariaDB sai hoặc kiến trúc DB. Tất cả thông tin từ bảng InnoDB được lưu trong file ibdata1, không gian của nó không tự thu hồi. Tôi thích lưu trữ dữ liệu bảng trong các file ibd* riêng biệt. Để làm điều đó, thêm dòng sau vào my.cnf:
innodb_file_per_table
hoặc
innodb_file_per_table=1
Nếu máy chủ của bạn đã được cấu hình và bạn có một số cơ sở dữ liệu sản xuất với bảng InnoDB, hãy làm như sau:
- Sao lưu tất cả cơ sở dữ liệu trên máy chủ (trừ mysql và performance_schema). Bạn có thể lấy dump cơ sở dữ liệu bằng lệnh:
# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql] - Sau khi tạo bản sao lưu cơ sở dữ liệu, dừng máy chủ mysql/mariadb;
- Thay đổi cài đặt trong my.cfg;
- Xóa file ibdata1 và ib_log;
- Khởi động daemon mysql/mariadb;
- Khôi phục tất cả cơ sở dữ liệu từ bản sao lưu:
# mysql -u [username] –p[password] [database_name] < [dump_file.sql]
Sau khi làm vậy, tất cả bảng InnoDB sẽ được lưu trong các file riêng biệt và ibdata1 sẽ ngừng tăng trưởng theo cấp số nhân.
Nén bảng InnoDB
Bạn có thể nén các bảng chứa dữ liệu text/BLOB và tiết kiệm đáng kể dung lượng ổ cứng.
Tôi có cơ sở dữ liệu innodb_test chứa các bảng có thể nén và do đó tôi có thể giải phóng một chút dung lượng ổ cứng. Trước khi làm bất cứ điều gì, tôi khuyên sao lưu tất cả cơ sở dữ liệu. Kết nối với máy chủ mysql:
# mysql -u root -pChọn cơ sở dữ liệu bạn cần trong console mysql:
# use innodb_test;
Để hiển thị danh sách bảng và kích thước của chúng, dùng truy vấn sau:
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;Trong đó innodb_test là tên cơ sở dữ liệu của bạn.
Một số bảng có thể được nén. Hãy lấy bảng b_crm_event_relations làm ví dụ. Chạy truy vấn này:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;Sau khi chạy, bạn có thể thấy kích thước bảng giảm từ 26 MB xuống 11 MB nhờ nén.
Bằng cách nén bảng, bạn có thể tiết kiệm nhiều dung lượng ổ cứng trên máy chủ. Tuy nhiên, khi làm việc với bảng đã nén, tải CPU tăng lên. Hãy dùng nén cho bảng db nếu bạn không có vấn đề về tài nguyên CPU, nhưng gặp vấn đề về dung lượng ổ cứng.
Nén bảng MyISAM trong MySQL/MariaDB
Để nén bảng Myisam, dùng truy vấn đặc biệt trong console máy chủ thay vì console mysql. Để nén một bảng, chạy lệnh sau:
# myisampack -b /var/lib/mysql/test/modx_sessionTrong đó /var/lib/mysql/test/modx_session là đường dẫn đến bảng của bạn. Rất tiếc tôi không có bảng lớn và phải nén các bảng nhỏ, nhưng kết quả vẫn có thể thấy (file được nén từ 25 MB xuống 18 MB):
# du -sh modx_session.MYD25M modx_session.MYD
# myisampack -b /var/lib/mysql/test/modx_sessionCompressing /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.MYD18M modx_session.MYD
Tôi dùng khóa -b trong lệnh. Khi thêm nó, bảng được sao lưu trước khi nén và được đánh dấu nhãn OLD:
# ls -la modx_session.OLD-rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD
# du -sh modx_session.OLD25M modx_session.OLD
Tối ưu bảng và Cơ sở dữ liệu trong MySQL và MariaDB
Để tối ưu bảng và cơ sở dữ liệu, khuyến khích defragment chúng. Kiểm tra xem có bảng nào trong cơ sở dữ liệu cần defragment không.
Mở console MySQL, chọn một cơ sở dữ liệu và chạy truy vấn này:
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;Như vậy, bạn sẽ hiển thị tất cả bảng có ít nhất 50 MB không gian không sử dụng:
+-------------------------------+----------------+--------------+ | 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 — kích thước tổng của một bảng
data_free_mb — không gian không sử dụng trong một bảng
Đây là các bảng chúng ta có thể defragment. Kiểm tra chúng chiếm bao nhiêu dung lượng trên ổ cứng:
# 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
Để tối ưu các bảng này, chạy lệnh sau trong console mysql:
# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
Sau khi defragment thành công, bạn sẽ thấy đầu ra như sau:
+-------------------------------+----------------+--------------+ | 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 |
Như bạn thấy, data_free_mb bằng 0 bây giờ và kích thước bảng giảm đáng kể (3-4 lần).
Bạn cũng có thể chạy defragmentation bằng mysqlcheck trong console máy chủ:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Trong đó innodb_test là cơ sở dữ liệu của bạn
Và b_workflow_file là tên bảng
Để tối ưu tất cả bảng trong một cơ sở dữ liệu, chạy lệnh này trong console máy chủ:
# mysqlcheck -o innodb_test -u root -p
Trong đó innodb_test là tên cơ sở dữ liệu
Hoặc chạy tối ưu hóa tất cả cơ sở dữ liệu trên máy chủ:
# mysqlcheck -o --all-databases -u root -pNếu bạn kiểm tra kích thước cơ sở dữ liệu trước và sau khi tối ưu, bạn sẽ thấy tổng kích thước đã giảm:
# du -sh2.5G
# mysqlcheck -o innodb_test -u root -pinnodb_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 -sh1.7G
Như vậy, để tiết kiệm dung lượng trên máy chủ, bạn có thể tối ưu và nén bảng/cơ sở dữ liệu MySQL/MariaDB theo thời gian. Nhớ sao lưu cơ sở dữ liệu trước khi làm bất kỳ công việc tối ưu nào.