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

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

30/11/2019

Ưu, nhược điểm của dịch vụ VPS là gì? Nhóm khách hàng nào nên sử dụng VPS?

Dịch vụ VPS – Virtual Private Server được tạo thành từ phương pháp phân chia một máy chủ vật lý thành nhiều máy chủ ảo dựa trên công nghệ ảo hóa. Một VPS có vai trò tương đương với đầy đủ các chức năng quản trị và cấu hình như một máy chủ vật lý, nên sẽ có những nhóm đối tượng khách hàng cụ thể nên sử dụng dịch vụ VPS.

29/11/2019

Tại sao doanh nghiệp nên chuyển từ VPS sang thuê Cloud Server để sử dụng?

Bài viết sau sẽ đi sâu vào nghiên cứu lý do tại sao Cloud Server đang trở thành lựa chọn nền tảng tối ưu hơn VPS, qua đó doanh nghiệp sẽ có được cái nhìn bao quát hơn trước khi dịch chuyển lên đám mây.

29/11/2019

Giải đáp các câu hỏi thường gặp của khách hàng khi thuê Cloud Server

Thuê Cloud Server là việc thuê máy chủ ảo được khởi tạo từ một hạ tầng ảo hoá có năng lực xử lý và bảo mật cao. Hạ tầng này gồm nhiều server vật lý liên kết với nhau, mỗi server (hoặc nhóm server) đóng một chức năng riêng biệt: tính toán, lưu trữ, tường lửa, cân bằng tải, dự phòng, sao lưu dữ liệu,…

28/11/2019

5 yếu tố cần lưu ý giúp bạn mua được VPS giá rẻ với chất lượng tốt

Hiện tại, có nhiều giải pháp lưu trữ như hosting, VPS, máy chủ riêng,… Với shared hosting, website sẽ không đảm bảo hiệu suất khi phải chia sẻ tài nguyên máy chủ với người dùng khác. Trong khi đó, sẽ rất tốn kém tài nguyên nếu bạn mua cả một server riêng nhưng không dùng hết. VPS giá rẻ sẽ giúp chúng ta giải quyết vấn đề này.

27/11/2019

Tìm hiểu những điểm khác nhau nổi trội giữa dịch vụ VPS và Cloud Server

Khi bắt đầu vào hành trình tạo ra trang web của riêng mình, bạn sẽ phải đối mặt với rất nhiều lựa chọn và quyết định. Mỗi quyết định và lựa chọn này sẽ tạo ra sự khác biệt trong hiệu suất và thành công của trang web. Khi này có thể bạn sẽ phải băn khoăn nên lựa chọn sử dụng cloud hay virtual private server (VPS) làm nền tảng cho website?

27/11/2019

Những tiêu chí cốt lõi cần phải đánh giá khi thuê Cloud Server

Hiện nay, hầu hết các doanh nghiệp đều nhận thức được những ưu điểm có được từ việc sử dụng Cloud Server máy chủ ảo so với hạ tầng công nghệ thông tin truyền thống. Khi chuyển đổi sang Cloud, doanh nghiệp sẽ hưởng lợi trong việc tiết kiệm chi phí, linh hoạt trong triển khai, thay đổi, đơn giản hóa việc quản lý IT.

26/11/2019

Dịch vụ VPS - Ứng dụng thực tế và cách thức quản trị VPS hiệu quả

Dịch vụ máy chủ ảo VPS là gì, có những ứng dụng thực tế nào và đâu là cách thức quản lý VPS tốt nhất là những thắc mắc thường được đặt ra đối với người mới bắt đầu sử dụng dịch vụ này. Cùng tháo gỡ những khúc mắc trong bài viết này nhé!

26/11/2019

Doanh nghiệp nên ứng dụng dịch vụ VPS vào những mục đích nào?

Dịch vụ VPS là gì và cách sử dụng như thế nào? Mọi thông tin chi tiết về dịch vụ VPS sẽ đều được Viettel IDC hướng dẫn chi tiết cách dùng trong bài viết này. Hãy cùng xem ngay nhé.

25/11/2019

5 lý do chứng minh hiệu quả vượt trội của Cloud VPS so với VPS truyền thống

Không ít khách hàng mặc dù đang sử dụng VPS nhưng đã biết đến Cloud VPS và đang có ý định chuyển từ VPS lên Cloud VPS. Bài viết dưới đây sẽ chỉ ra 5 lý do vì sao Cloud VPS lại tốt hơn VPS truyền thống

23/11/2019

Vì sao sử dụng Cloud VPS của Viettel IDC là lựa chọn đáng tin cậy nhất?

Cloud VPS là một giải pháp lưu trữ web vô cùng tối ưu giúp máy chủ web có thể uptime 99,99% thời gian hoạt động với hệ thống dự phòng đầy đủ và khả năng mở rộng cao.