Trong bài viết này, chúng ta sẽ tìm hiểu một số phương pháp nén bảng / cơ sở dữ liệu và chống phân mảnh trong MySQL / MariaDB, điều này sẽ giúp bạn tiết kiệm dung lượng trên đĩa chứa cơ sở dữ liệu.
Cơ sở dữ liệu của các dự án lớn phát triển vô cùng lớn theo thời gian và một câu hỏi luôn được đặt ra là phải làm gì với nó. Có một số cách để giải quyết vấn đề. 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 những phần nhỏ hơn, tăng kích thước đĩa 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 phải chống phân mảnh các 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 và tối ưu hóa bảng InnoDB
Tệp ibdata1 và ib_log
Hầu hết các dự án với InnoDB bảng có vấn đề về ibdata1 lớn và ib_log các tập tin. Trong hầu hết các trường hợp, nó 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ừ các bảng InnoDB được lưu trữ trong tệp ibdata1, không gian của tệp này không được lấy lại bởi chính nó. Tôi muốn lưu trữ dữ liệu bảng trong ibd * riêng biệt các tập tin. Để thực hiện, hãy 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 định cấu hình và bạn có một số cơ sở dữ liệu hiệu quả 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ủ của bạn (ngoại trừ mysql và performance_schema). Bạn có thể lấy kết xuất cơ sở dữ liệu bằng cách sử dụng lệnh sau:
# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql] - Sau khi tạo bản sao lưu cơ sở dữ liệu, hãy dừng máy chủ mysql / mariadb của bạn;
- Thay đổi cài đặt trong my.cfg;
- Xóa ibdata1 và ib_log tệp;
- Khởi động trình nền 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 thực hiện, tất cả các bảng InnoDB sẽ được lưu trữ trong các tệp riêng biệt và ibdata1 sẽ ngừng phát triển theo cấp số nhân.
Nén bảng InnoDB
Bạn có thể nén bảng với dữ liệu văn bản / BLOB và tiết kiệm khá nhiều dung lượng đĩa.
Tôi có một cơ sở dữ liệu innodb_test chứa các bảng có khả năng được nén và do đó tôi có thể giải phóng một số không gian đĩa. Trước khi làm bất cứ điều gì, tôi khuyên bạn nê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 -p
Chọn cơ sở dữ liệu bạn cần trong bảng điều khiển mysql của bạn:
# use innodb_test;
Để hiển thị danh sách các bảng và kích thước của chúng, hãy sử 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 xem xét b_crm_event_relations bảng 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 nó, bạn có thể thấy rằng kích thước của bảng đã giảm từ 26 MB xuống 11 MB do nén.
Bằng cách nén các bảng, bạn có thể tiết kiệm nhiều dung lượng đĩa trên máy chủ của mình. Tuy nhiên, khi làm việc với các bảng nén, tải CPU sẽ tăng lên. Sử dụng tính năng nén cho bảng db nếu bạn không gặp sự cố với tài nguyên CPU nhưng gặp sự cố về dung lượng ổ đĩa.
Nén bảng MyISAM trong MySQL / MariDB
Để nén Myisam bảng, sử dụng một truy vấn đặc biệt trong bảng điều khiển máy chủ thay vì bảng điều khiển mysql. Để nén bảng, hãy chạy như sau:
# myisampack -b /var/lib/mysql/test/modx_session
Trong đó / 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 bảng nhỏ, nhưng vẫn có thể thấy kết quả (tệp được nén từ 25 MB xuống 18 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
Tôi đã sử dụng -b phím trong lệnh. Khi bạn thêm nó, một bảng sẽ được sao lưu trước khi nén và được đánh dấu bằng nhãn CŨ:
# 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
Tối ưu hóa Bảng và Cơ sở dữ liệu trong MySQL và MariaDB
Để tối ưu hóa các bảng và cơ sở dữ liệu, bạn nên chống phân mảnh chúng. Đảm bảo nếu có bất kỳ bảng nào trong cơ sở dữ liệu yêu cầu chống phân mảnh.
Mở bảng điều khiển 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;
Do đó, bạn sẽ hiển thị tất cả các bảng có ít nhất 50 MB dung lượng chưa 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 - tổng kích thước của một bảng
data_free_mb - không gian chưa sử dụng trong bảng
Đây là những bảng chúng ta có thể chống phân mảnh. Kiểm tra dung lượng chúng chiếm trên đĩa:
# 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 hóa các bảng này, hãy chạy lệnh sau trong bảng điều khiển mysql:
# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
Sau khi chống phân mảnh thành công, bạn sẽ thấy kết quả 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 có thể thấy, data_free_mb hiện bằng 0 và kích thước bảng đã giảm đáng kể (3 - 4 lần).
Bạn cũng có thể chạy chống phân mảnh bằng mysqlcheck trong bảng điều khiển máy chủ của bạn:
# 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 của bảng
Để tối ưu hóa tất cả các bảng trong cơ sở dữ liệu, hãy chạy lệnh này trong bảng điều khiển máy chủ của bạn:
# 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 -p
Nếu bạn kiểm tra kích thước cơ sở dữ liệu trước và sau khi tối ưu hóa, bạn sẽ thấy rằng tổng kích thước đã giảm:
# 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
Do đó, để tiết kiệm không gian trên máy chủ của bạn, bạn có thể tối ưu hóa và nén các bảng và cơ sở dữ liệu MySQL / MariDB của mình theo thời gian. Hãy nhớ sao lưu cơ sở dữ liệu trước khi thực hiện bất kỳ công việc tối ưu hóa nào.