Hướng dẫn tối ưu hóa và chống phân mảnh MySQL Table

19/10/2019

Tối ưu và chống phân mảnh MySQL Table là một công tác quan trọng cần thực hiện định kì.

Nếu ứng dụng thực hiện nhiều thao tác xóa (delete) và cập nhật (update) trên cơ sở dữ liệu MySQL thì tệp tin Cơ sở dữ liệu (CSDL) MySQL có khả năng bị phân mảnh. Điều đó dẫn tới việc rất nhiều tài nguyên không sử dụng đến cũng như ảnh hưởng tới hiệu năng.

Bài hướng dẫn sẽ giải thích cách thức tối ưu MySQL nhằm chống phân mảnh bảng và thu hồi dung lượng trống không sử dụng.

1. Xác định bảng cần tối ưu

Bước đầu tiên cần xác định dữ liệu CSDL MySQL có bị phân mảnh hay không.

Thực hiện kết nối đến CSDL MySQL, thực thi câu truy vấn dưới đây sẽ hiển thị dung lượng trống khả dụng trong mỗi bảng.

mysql> use vnist;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) > 500  order by data_free_mb;+------------+----------------+--------------+| table_name | data_length_mb | data_free_mb |+------------+----------------+--------------+| BENEFITS   |           7743 |         4775 || DEPARTMENT |          14295 |        13315 || EMPLOYEE   |          21633 |        19834 |+------------+----------------+--------------+

Output trên cho ta biết:

  • Hiển thị danh sách tất cả các bảng có ít nhất 500MB dung lượng trống. Ở ví dụ trên là 3 bảng.
  • Cột data_length_mb hiển thị tổng dung lượng của bảng theo đơn vị MB. Ví dụ, bảng EMPLOYEE có dung lượng khoảng 21GB
  • Cột data_free_mb column hiển thị dung lượng trống của từng bảng. Ví dụ, bảng EMPLOYEE có khoảng 19GB dung lượng trống.
  • Cả ba bảng (EMPLOYEE, DEPARTMENT và BENEFITS) đều bị phân mảnh nặng và cần tối ưu để lấy lại dung lượng trống không sử dụng.

Ở mức tệp tin hệ thống, bạn có thể thấy được dung lượng của từng bảng như dưới đây. Dung lượng của các tệp tin tương tự với cột “data_length_mb”.

# ls -lh /var/lib/mysql/vnist/..-rw-rw----. 1 mysql mysql  7.6G Apr 23 10:55 BENEFITS.MYD-rw-rw----. 1 mysql mysql   14G Apr 23 12:53 DEPARTMENT.MYD-rw-rw----. 1 mysql mysql   22G Apr 23 12:03 EMPLOYEE.MYD..

Tệp tin EMPLOYEE.MYD chiếm khoảng 22GB trong ổ đĩa cứng nhưng chứa rất nhiều dung lượng trống. Nếu ta tối ưu hóa bảng này, dung lượng của tệp tin sẽ giảm đi đáng kể.

2. Chống phân mảnh sử dụng câu lệnh OPTIMIZE TABLE

Có hai cách tối ưu hóa một bảng.

Phương pháp đầu tiên là sử dụng câu lệnh Optimize table 

mysql> use vnist;mysql> OPTIMIZE TABLE EMPLOYEE;

Bạn có thể tối ưu nhiều bảng cùng một lúc.

mysql> OPTIMIZE TABLE EMPLOYEE, DEPARTMENT, BENEFITS

Một vài điểm cần lưu ý:

  • Tối ưu hóa bảng có thể thực hiện cho InnoDB engine, hoặc MyISAM engine, hoặc bảng ARCHIVE.
  • Với bảng MyISAM, câu lệnh sẽ phân tích bảng, sau đó chống phân mảnh tệp tin dữ liệu MySQL tương ứng và lấy lại dung lượng trống không sử dụng.
  • Với bảng InnoDB, tối ưu bảng sẽ thực hiện đơn giản trên một bảng thay thế.
  • Nếu bạn có đánh chỉ mục (index), câu lệnh sẽ sắp xếp lại trang index và cập nhật thống kê.

Trong quá trình tối ưu hóa, MySQL sẽ tạo ra một bảng tạm thời, và sau khi hoàn thành tối ưu hóa sẽ tự động xóa đi bảng gốc, thay đổi tên bảng tạm thời thành bảng gốc.

Ở ví dụ tối ưu hóa trên, bảng EMPLOYEE là một bảng MyISAM

Trước khi tối ưu, bạn sẽ thấy một tệp tin .MYD :

# ls -lh /var/lib/mysql/vnist/EMPLOYEE.*-rw-rw----. 1 mysql mysql   22G Apr 23 12:03 EMPLOYEE.MYD

Khi câu lệnh “OPTIMIZE TABLE” chạy sẽ tạo ra một tệp tin tạm thời với phần mở rộng .TMD. Dung lượng của tệp tin này sẽ tăng lên dần dần.

# ls -lh /var/lib/mysql/vnist/EMPLOYEE.*-rw-rw----. 1 mysql mysql   22G Apr 23 12:03 EMPLOYEE.MYD-rw-rw----. 1 mysql mysql  500M Apr 23 14:10 EMPLOYEE.TMD

Sau khi tối ưu hoàn thành, bảng tạm thời sẽ biến mất. Thay vào đó ta chỉ thấy bảng gốc EMPLOYEE.MYD đã được giảm dung lượng.

# ls -lh /var/lib/mysql/vnist/EMPLOYEE.*-rw-rw----. 1 mysql mysql    2G Apr 23 14:20 EMPLOYEE.MYD

3. Chống phân mảnh sử dụng lệnh mysqlcheck

Phương pháp thứ hai giúp tối ưu hóa một bảng là sử dụng câu lệnh mysqlcheck. Ví dụ dưới đây sẽ tối ưu bảng DEPARTMENT. Thực thi lệnh sau từ dấu nhắc của Linux (không phải dấu nhắc của MySQL)

# mysqlcheck -o vnist DEPARTMENT -u root -pPasswordvnist.DEPARTMENT  OK

Lưu ý: Bên trong lệnh mysqlcheck sẽ sử dụng lệnh “OPTIMIZE TABLE”

  • mysqlcheck là câu lệnh được thư thi từ dấu nhắc của Linux.
  • Tùy chọn -o xác định việc mysqlcheck thực hiện thao tác “optimize table”.
  • vnist là tên cơ sở dữ liệu
  • DEPARTMENT là bảng sẽ được tối ưu hóa nằm trong cơ sở dữ liệu vnist.
  • -u root xác định người dùng gốc
  • -pPassword xác định mật khẩu của tài khoản gốc (Lưu ý không có dấu cách giữa -p và mật khẩu

Ngoài tối ưu, bạn có thể sử dụng lệnh mysqlcheck để kiểm tra, phân tích và sửa chữa bảng trong CSDL MySql.

4. Chống phân mảnh toàn bộ bảng hoặc toàn bộ cơ sở dữ liệu

Nếu muốn tối ưu hóa toàn bộ bảng trong một CSDL cụ thể, sử dụng lệnh sau:

# mysqlcheck -o vnist -u root -pPassword

Nếu có nhiều cơ sở dữ liệu, bạn có thể tối ưu hóa toàn bộ các bảng trong tất cả cơ sở dữ liệu bằng lệnh sau:

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

5. Sau khi tối ưu hóa

Sau quá trình tối ưu hóa, sử dụng truy vấn sau để kiểm tra lại tổng dung lượng và dung lượng trống không sử dụng của 3 bảng trong ví dụ:

mysql> use vnist;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 table_name in  ( 'EMPLOYEE', 'DEPARTMENT', 'BENEFITS' ); +------------+----------------+--------------+| table_name | data_length_mb | data_free_mb |+------------+----------------+--------------+| BENEFITS   |           2968 |            0 || DEPARTMENT |            980 |            0 || EMPLOYEE   |           1799 |            0 |+------------+----------------+--------------+

Kết quả dung lượng đã giảm đi đáng kể. Dung lượng trống đã về 0 và bảng đã không còn bị phân mảnh.

Trên ổ đĩa cứng lưu trữ, khoảng 37GB dung lượng trống đã được thu hồi.

# ls -lh /var/lib/mysql/vnist/..-rw-rw----. 1 mysql mysql    3G Apr 23 14:23 BENEFITS.MYD-rw-rw----. 1 mysql mysql  980M Apr 23 14:30 DEPARTMENT.MYD-rw-rw----. 1 mysql mysql    2G Apr 23 14:45 EMPLOYEE.MYD...

thegeekstuff - Security Daily

 

Tin liên quan

07/03/2024

Khám phá 3 ứng dụng nổi bật của mô hình trí tuệ nhân tạo tạo sinh

Trí tuệ nhân tạo tạo sinh đang tạo nên những đột phá mới, mở ra cánh cửa cho vô số ứng dụng sáng tạo. Bài viết này sẽ giới thiệu 3 ví dụ điển hình về cách thức các mô hình trí tuệ nhân tạo tạo sinh đang được ứng dụng, giúp bạn hiểu hơn về công nghệ AI này.

07/03/2024

SSL miễn phí và trả phí - Đâu là lựa chọn thông minh?

Với sự xuất hiện của nhiều loại SSL khác nhau, không ít người băn khoăn có nên lựa chọn SSL miễn phí hay không. Trong bài viết này, Viettel IDC sẽ điểm qua các thông tin quan trọng, giúp bạn hiểu rõ lợi ích và hạn chế khi đăng ký SSL miễn phí.

20/05/2024

NPU là gì? Khám phá lợi ích nổi bật của NPU có thể bạn chưa biết

Ứng dụng NPU được xem là công cụ giúp người dùng khai thác sức mạnh của trí tuệ nhân tạo hiệu quả hơn. Trong bài viết này, chúng ta sẽ khám phá chi tiết hơn về NPU là gì, bao gồm cách thức hoạt động, lợi ích cũng như tầm quan trọng của chúng trong việc giải quyết các nhiệm vụ AI trong đời sống.

11/04/2024

Cyber attack là gì? Các loại hình tấn công và giải pháp ngăn chặn phổ biến

Cyber attack được xem là vấn đề an ninh mạng mà không một cá nhân, tổ chức nào có thể xem nhẹ. Trong bài viết này, hãy cùng Viettel IDC tìm hiểu tổng quan Cyber Attack là gì cũng như giải pháp phòng chống đáng cân nhắc nhé.

18/04/2024

Hiểu về 3 loại lưu trữ dữ liệu đám mây chính hiện nay

Với khả năng lưu trữ và quản lý dữ liệu một cách dễ dàng và hiệu quả, Cloud Storage - lưu trữ đám mây đang dần trở thành giải pháp linh hoạt và hiệu quả, được nhiều người ưa chuộng. Hãy cùng Viettel IDC khám phá về 3 loại lưu trữ đám mây chính hiện nay với bài viết sau nhé.

16/04/2024

Mức độ nguy hiểm của lỗ hổng Zero Day Attack có thể bạn chưa biết

Một trong những nguy hiểm tiềm ẩn mà người dùng cần đặc biệt lưu ý là lỗ hổng Zero Day Attack. Vậy Zero Day Attack là gì? Mức độ nguy hiểm của chúng có gì đáng lưu tâm? Hãy cùng Viettel IDC tìm hiểu chi tiết hơn qua bài viết sau, giúp bạn nâng cao ý thức phòng tránh khỏi những mối đe dọa tiềm ẩn này nhé.

15/04/2024

Bí quyết tăng tốc website nhanh chóng và dễ dàng

Trong bài viết này, Viettel IDC sẽ hướng dẫn bạn những bí quyết giúp tăng tốc website nhanh chóng và hiệu quả, giúp tối ưu hiệu suất, tốc độ website của mình, hãy cùng điểm qua nhé.

03/04/2024

3 lý do không thể bỏ qua Custom Domain cho doanh nghiệp

Việc sở hữu một tên miền riêng, được cá nhân hóa cho doanh nghiệp đóng vai trò vô cùng quan trọng, không chỉ giúp nâng cao uy tín và độ nhận diện thương hiệu mà còn tạo dựng niềm tin vững chắc với khách hàng. Hãy cùng khám phá ba lý do cốt lõi giải thích vì sao Custom Domain là một yếu tố không thể thiếu trong hành trình xây dựng và phát triển thương hiệu của doanh nghiệp nhé.

22/04/2024

Nguy cơ tiềm ẩn của Cross Site Scripting XSS có thể bạn chưa biết

Trong khi lĩnh vực công nghệ thông tin ngày càng phát triển, Cross Site Scripting vẫn được xem là mối đe dọa an ninh mạng đáng e ngại đối với người dùng. Mặc dù các biện pháp phòng ngừa đã được cải thiện đáng kể trong những năm qua, XSS vẫn có thể gây ra hậu quả nghiêm trọng cho cá nhân, doanh nghiệp và tổ chức.

24/04/2024

Tìm hiểu chi tiết về hình thức kiểm thử xâm nhập Pentest

Để chủ động trước những cuộc tấn công có thể xảy ra, gây nên những tổn thất không mong muốn, nhiều doanh nghiệp đã và đang triển khai giải pháp kiểm thử xâm nhập cho toàn bộ hệ thống của mình

// doi link