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
Website không bao giờ chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn muốn tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ kinh nghiệm, thủ tục, quy trình, bí kíp thực chiến mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin1_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
