Hướng dẫn sử dụng show full processlist và watch để giám sát MySQL Real-time

SHOW FULL PROCESSLIST là công cụ chẩn đoán mạnh mẽ, cho phép quản trị viên xem tất cả các luồng đang hoạt động trên máy chủ MySQL. Lệnh này giúp xác định truy vấn chậm tại một thời điểm nhất định, nhưng không thể theo dõi liên tục. Trong bài viết này, mình sẽ hướng dẫn bạn kết hợp show full processlist với lệnh watch để xây dựng một màn hình giám sát hiệu năng real-time, giúp bạn làm chủ cơ sở dữ liệu của mình.

Những điểm chính

• Khái niệm lệnh SHOW FULL PROCESSLIST: Là một lệnh SQL dùng để hiển thị tất cả các luồng đang chạy trên máy chủ MySQL, giúp giám sát và chẩn đoán sự cố hiệu suất.
• Tại sao cần giám sát liên tục: Giúp phát hiện các truy vấn ngắn gây hiệu năng kém, chẩn đoán tình trạng khóa và hiểu rõ hơn về tải của cơ sở dữ liệu theo thời gian thực.
• Hướng dẫn giám sát SHOW FULL PROCESSLIST bằng watch: Kết hợp lệnh watch với mysql để theo dõi liên tục kết quả, giúp dễ dàng phát hiện các truy vấn chậm hoặc bị khóa.
• Các ứng dụng nâng cao khác: Kỹ thuật này còn có thể được áp dụng để giám sát các trạng thái khác của MySQL như sao chép (replication) hoặc khóa bảng.
• Lưu ý khi sử dụng: Cần chú ý bảo mật thông tin đăng nhập, sử dụng tài khoản có quyền hạn chế và cân nhắc tác động hiệu suất khi thực hiện giám sát liên tục.
• Giải đáp thắc mắc cho các câu hỏi thực tế về cách cấu hình an toàn và ý nghĩa của các tùy chọn khi giám sát.

SHOW FULL PROCESSLIST là gì?

SHOW FULL PROCESSLIST là một lệnh SQL dùng để hiển thị tất cả các luồng (threads) đang được thực thi hoặc đang chờ đợi trên máy chủ MySQL tại thời điểm hiện tại. Lệnh này cho bạn thấy ai đang kết nối, họ đang kết nối từ đâu, họ đang thực thi câu lệnh gì và câu lệnh đó đã chạy được bao lâu, rất quan trọng trong việc giám sát hiệu suất, chẩn đoán các vấn đề tắc nghẽn (bottleneck) và tìm ra các truy vấn chậm.

Tại sao cần giám sát show full processlist liên tục?

Việc theo dõi liên tục thay vì chỉ xem một lần mang lại nhiều lợi ích chiến lược trong việc quản lý hiệu năng cơ sở dữ liệu:

• Phát hiện các query ngắn gây hiệu năng kém: Nhiều query chạy rất nhanh (dưới 1 giây) nhưng được gọi hàng ngàn lần có thể gây quá tải. Giám sát liên tục giúp bạn nhận ra các mẫu query lặp đi lặp lại này.
• Chẩn đoán tình trạng khóa: Quan sát cột State thay đổi theo thời gian giúp bạn nhanh chóng xác định các query đang bị kẹt ở trạng thái “Locked” hoặc chờ đợi tài nguyên.
• Hiểu rõ hơn về tải của cơ sở dữ liệu: Thấy được những loại query nào chiếm nhiều thời gian nhất trong một khoảng thời gian giúp bạn có cái nhìn tổng quan và đưa ra quyết định tối ưu hóa chính xác hơn.

Bước 1: Cấu hình truy MySQL cập an toàn

Để watch có thể gọi mysql, bạn cần một phương thức đăng nhập không yêu cầu nhập mật khẩu tương tác. Việc đặt mật khẩu trực tiếp trên dòng lệnh là cực kỳ không an toàn vì mật khẩu sẽ bị lưu vào lịch sử shell và hiển thị cho các người dùng khác. Giải pháp chuyên nghiệp là sử dụng file tùy chọn ~/.my.cnf, MySQL sẽ tự động đọc file này để lấy thông tin đăng nhập.

• Đầu tiên, hãy tạo và mở file ~/.my.cnf trong thư mục home của user bạn dùng để chạy lệnh:

nano ~/.my.cnf

• Bạn thêm nội dung sau vào file, thay thế bằng thông tin của bạn:

[client] user=your_user password=your_secret_password

• Để phân quyền an toàn cho file này để chỉ bạn mới có thể đọc, hãy chạy lệnh dưới đây:

chmod 600 ~/.my.cnf

Từ bây giờ, bạn có thể chạy lệnh mysql mà không cần nhập user hay password.

Bước 2: Sử dụng watch để theo dõi SHOW FULL PROCESSLIST

Sau khi có thể truy cập an toàn, bạn hãy watch để theo dõi SHOW FULL PROCESSLIST theo real-time. Cú pháp cơ bản của watch là watch [options] 'command'. Lệnh watch sẽ thực thi command lặp đi lặp lại và hiển thị kết quả toàn màn hình.

Lệnh cơ bản:

# Thực thi lại lệnh mysql mỗi 2 giây (mặc định)
watch 'mysql -e "SHOW FULL PROCESSLIST"'

Ngoài ra, bạn có thể nâng cấp với các tùy chọn của watch như:

• -n <giây>: Là tùy chỉnh tần suất làm mới. Ví dụ, để bắt các query nhanh chóng, bạn có thể đặt là 1 giây.
• -d (differences): watch sẽ tô sáng những thay đổi giữa các lần cập nhật. Bất kỳ query mới nào xuất hiện, trạng thái thay đổi, hay thời gian thực thi tăng lên sẽ được làm nổi bật, giúp bạn không bỏ lỡ chi tiết quan trọng.

Để giám sát liên tục các truy vấn và tiến trình đang chạy trên hệ quản trị cơ sở dữ liệu MySQL theo thời gian thực, bạn chạy lệnh dưới đây:

watch -n 1 -d 'mysql -e "SHOW FULL PROCESSLIST"'

Bước 3: Phân tích kết quả và xác định vấn đề

Sau khi chạy lệnh watch hoàn chỉnh ở trên, hãy tái hiện lại sự cố trên ứng dụng của bạn (ví dụ website tải chậm, sử dụng curl hoặc các công cụ stress test như ab, wrk để tạo ra một lượng truy cập nhỏ) và quan sát màn hình terminal. Nhờ tùy chọn -d, việc phát hiện vấn đề trở nên cực kỳ trực quan. Hãy chú ý đến các dấu hiệu sau:

• Dòng mới được tô sáng: Một query mới vừa được thực thi.
• Cột Time tăng liên tục: Đây là dấu hiệu rõ nhất của một query chạy chậm. Nếu con số này tiếp tục tăng trong nhiều giây, bạn đã tìm thấy thủ phạm.
• Cột State hiển thị Locked: Cho thấy query đang bị kẹt do khóa bảng (table lock), một nguyên nhân phổ biến gây tắc nghẽn hệ thống.

Các ứng dụng nâng cao khác

Sự kết hợp giữa watch và mysql không chỉ giới hạn ở SHOW FULL PROCESSLIST. Bạn có thể giám sát nhiều khía cạnh khác của cơ sở dữ liệu.

• Theo dõi tình trạng Replication:

# Sử dụng \G để hiển thị kết quả theo chiều dọc, dễ đọc hơn 
watch -n 5 -d 'mysql -e "SHOW SLAVE STATUS\G"'

• Kiểm tra các Table Lock đang hoạt động:

watch -n 1 -d 'mysql -e "SHOW OPEN TABLES WHERE In_use > 0;"'

• Giám sát sự tăng trưởng của một Database:

watch -n 10 -d 'mysql -e "SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS \`Size (MB)\` FROM information_schema.TABLES WHERE table_schema = '\''ten_database_cua_ban'\'' GROUP BY table_schema;"'

Lưu ý khi dùng lệnh watch kết hợp show full processlist để giám sát MySQL

• Bảo mật thông tin đăng nhập MySQL: Bạn không nên truyền mật khẩu trực tiếp qua dòng lệnh vì sẽ bị lưu vào lịch sử shell và có thể lộ trên lệnh ps aux.
• Tránh công khai thông tin quan trọng: Nếu môi trường multi-user, bạn hãy hạn chế phân quyền chỉ user quản trị mới được đọc ~/.my.cnf, tránh để các user khác truy cập.
• Quản lý quyền truy cập MySQL: Bạn nên sử dụng account MySQL với quyền vừa đủ, tránh dùng root cho các tác vụ giám sát định kỳ nhằm giảm thiểu rủi ro bảo mật.​
• Chỉ giám sát trên mạng cục bộ hoặc đã mã hóa: Khi chạy trên môi trường production hoặc qua SSH, bạn cần đảm bảo kết nối mạng được mã hóa để tránh rò rỉ thông tin truy vấn.​
• Lưu ý hiệu suất và log: Lệnh SHOW FULL PROCESSLIST nếu thực thi liên tục có thể tạo tải nhẹ lên MySQL. Do đó bạn không nên để tần suất làm mới quá thấp (dưới 1 giây) trên hệ thống nhiều kết nối, trừ khi thực sự cần thiết.
• Khả năng truy xuất thông tin trên MySQL phiên bản mới: Tính năng SHOW PROCESSLIST truyền thống có thể bị hạn chế hoặc thay thế bằng Performance Schema trên các phiên bản MySQL mới. Bạn có thể áp dụng giám sát qua performance_schema để chi tiết hơn và hiệu quả hơn.​

Bài viết đã hướng dẫn chi tiết cách sử dụng lệnh show full processlist và tiện ích watch để theo dõi các tiến trình MySQL đang chạy theo thời gian thực. Việc giám sát này giúp bạn nhanh chóng xác định và xử lý các query chậm hoặc đang gây tắc nghẽn hệ thống. Để tiếp tục nâng cao kiến thức về hệ thống và tối ưu hóa máy chủ, mời bạn đón đọc thêm các bài viết hữu ích khác về Linux dưới đây: