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

19/01/2023

Dự báo TOP các xu hướng công nghệ hàng đầu trong năm 2023

​Các xu hướng công nghệ 2023 nổi bật được các chuyên gia dự đoán sẽ phát triển mạnh mẽ trong những năm tới là trí tuệ nhân tạo AI, công nghệ chuỗi khối Blockchain, vũ trụ ảo Metaverse. Việc bắt kịp và thích ứng với các công nghệ này sẽ giúp các doanh nghiệp có thể nâng cao khả năng cạnh tranh của mình trong thị trường khốc liệt sắp tới. Dưới đây, hãy cùng Viettel IDC điểm qua những xu hướng công nghệ 2023 nổi bật nhé!

23/01/2023

Xu hướng Cloud 2023 hàng đầu các doanh nghiệp cần biết

Nhiều doanh nghiệp đang tích cực chạy đua với cuộc đua “số hóa”, để có được khả năng cạnh tranh cao nhất trên thị trường. Vây nên, xu hướng Cloud 2023 là những điều đang được các doanh nghiệp rất quan tâm hiện nay. Trong bài viết này, Viettel IDC sẽ đề cập đến bạn những xu hướng về điện toán đám mây hàng đầu trong năm 2023 chúng ta cần biết nhé, bên cạnh các công nghệ thực tế ảo VR, metaverse.

13/01/2023

2022 - Thời kỳ bùng nổ của Cloud tại Việt Nam

​Công nghệ điện toán đám mây - Computing Cloud năm 2022 đã phát triển và bùng nổ cực kỳ mạnh mẽ, từ đó ảnh hưởng tới nhiều doanh nghiệp lớn và nhỏ. Dưới đây, chúng ta hãy cùng xem lại các xu hướng Cloud năm 2022 phổ biến nhất với Viettel IDC nhé!

11/10/2022

Những thông tin nhất định phải biết về Ethereum mới phiên bản 2.0

Ethereum 2.0, còn được biết đến là Eth2 hay “Serenity”, là một bản nâng cấp dành cho Ethereum Node, hứa hẹn sẽ cải thiện đáng kể chức năng và trải nghiệm của toàn bộ mạng. Tuy nhiên, đó chỉ là phần nổi của tảng băng chìm. Với việc Ethereum là một trong những loại tiền điện tử phổ biến nhất trên hành tinh, việc tìm hiểu Ethereum 2.0 thực sự là gì và nó sẽ ảnh hưởng như thế nào đến toàn bộ lĩnh vực tiền mã hóa vô cùng quan trọng. Ở bài viết này, Viettel IDC sẽ cung cấp cho bạn những thông tin nhất định phải biết về Ethereum phiên bản 2.0 nhé!

13/10/2022

Công nghệ chuỗi khối Blockchain là gì? Cơ chế hoạt động của chuỗi khối Blockchain?

Công nghệ Blockchain (chuỗi khối) đang dần trở thành xu hướng mới trên thị trường đầu tư và công nghệ toàn cầu. Công nghệ này có tiềm năng ứng dụng to lớn trong các ngành từ dịch vụ tài chính, sản xuất và khu vực công cho đến chuỗi cung ứng, giáo dục và năng lượng. Việt Nam cũng không nằm ngoài xu thế này. Chính vì vậy, việc tìm hiểu về Blockchain ngay từ bây giờ là rất cần thiết đối với các bạn trẻ.

14/10/2022

Tìm hiểu kiến thức về Public Chain và Private Chain

Nền tảng blockchain đã phát triển mạnh mẽ và được ứng dụng rộng rãi trong nhiều lĩnh vực. Các phân loại của blockchain như Private, Public mang những sự khác biệt dẫn đến trải nghiệm người dùng khác nhau và đa dạng hóa sự lựa chọn loại blockchain phù hợp. Hãy cùng CryptoLeakvn tìm hiểu sự khác nhau giữa Public và Private blockchain, cũng như tìm ra lựa chọn tối ưu nhất trong các loại blockchain này thông qua bài viết hôm nay.

05/10/2022

Công nghệ Blockchain là gì? Lợi thế vượt trội khi doanh nghiệp ứng dụng Blockchain

Trong thời gian gần đây, công nghệ Blockchain đã và đang dần trở thành xu hướng trên toàn cầu, trong đó có cả Việt Nam. Có thể nói, ngành công nghệ này đã mang lại nhiều lợi ích to lớn cho doanh nghiệp, từ lĩnh vực tài chính, sản xuất cho đến cả giáo dục hoặc năng lượng.

12/10/2022

​Tất tần tật kiến thức quan trọng về hạ tầng Blockchain

Mọi hệ thống phức tạp đều yêu cầu cơ sở hạ tầng thích hợp, hoặc tài nguyên và một khuôn khổ cơ bản để hoạt động. Cũng giống như lưới điện, các trạm phát điện và đường ống bao gồm cơ sở hạ tầng năng lượng cần thiết để cung cấp điện cho một quốc gia. Do đó, các Node, phần mềm và hệ thống dựa trên đám mây hoặc phần cứng được yêu cầu để chạy các mạng Proof of Stake (PoS).

09/10/2022

Tất tần tật từ A - Z về dịch vụ Blockchain

Hiện nay, dịch vụ Blockchain đã phát triển mạnh mẽ và mở ra một xu hướng mới cho nhiều lĩnh vực khác nhau như trong tài chính, điện tử viễn thông, kế toán, logistics,... Vậy, cụ thể thì Blockchain là gì? Chúng có thể mang lại những lợi ích gì cho chúng ta? Hãy cùng Viettel IDC tìm hiểu câu trả lời cho những vấn đề này nhé! Bài viết dưới đây sẽ giải đáp chi tiết giúp bạn.

08/10/2022

Node là gì? Nên thuê Ethereum Node hay Bitcoin Node?

Bạn mới tìm hiểu về Node Blockchain, và đang phân vân không biết nên thuê Ethereum Node hay Bitcoin Note? Cách hoạt động của Node là gì? Trong bài viết này, Viettel IDC sẽ giải đáp các câu hỏi này cho bạn đọc, cùng nhau theo dõi bài viết bên dưới nhé!

// doi link