Tối ưu MySQL MariaDB buffer pool index slow query log

VPS mới cài xong MySQL hay MariaDB, chạy vài ngày thấy swap dâng cao, CPU load lúc nào cũng ì đạch, hoặc web cứ chậm dần theo thời gian. Nguyên nhân thường đến từ 3 thứ: buffer pool không được thiết lập, index đánh thiếu hoặc thừa, và không có slow query log để biết câu nào đang kéo chết máy. Bài này sẽ hướng dẫn bạn xử lý cả 3 vấn đề đó, chạy ngay trên VPS Ubuntu 24.04 với MySQL 8.0 hoặc MariaDB 11.x.

• Buffer pool: Đặt InnoDB buffer pool size đúng giúp giảm đọc/ghi đĩa.
• Index: Đánh đúng column thường JOIN/WHERE, tránh index thừa làm chậm ghi.
• Slow query log: Bật nhẹ, để log những câu chạy quá 2 giây, từ đó biết cần tối ưu gì.

Yêu cầu trước khi bắt đầu

• VPS chạy Ubuntu 24.04 LTS hoặc Debian 12, đã cài MySQL 8.0 hoặc MariaDB 11.x.
• User sudo non-root, hoặc truy cập root trực tiếp.
• Đã mở port 3306 nếu cần kết nối từ xa (không khuyến khích để public).
• Có quyền mysql hoặc mariadb client để kiểm tra.

Khi nào cần tối ưu MySQL/MariaDB

Không phải VPS nào cũng cần tune database ngay. Bạn chỉ nên động vào cấu hình MySQL khi thấy một trong các dấu hiệu: sử dụng RAM database vượt 70% tổng RAM VPS, % disk I/O wait trên htop luôn > 10%, hoặc web response time tăng đều theo số lượng user. Đây là lúc buffer pool và index phát huy tác dụng rõ nhất. Nếu bạn chỉ chạy blog cá nhân vài trăm view/ngày, mặc định MySQL trên Ubuntu là đã OK.

Bước 1, Cấu hình InnoDB buffer pool

Buffer pool là vùng RAM dùng để cache dữ liệu và index của InnoDB. Đặt quá thấp sẽ gây đọc đĩa liên tục, quá cao dễ OOM. Nguyên tắc phổ biến: 50-60% tổng RAM khả dụng. Ví dụ VPS 4GB RAM thì buffer pool khoảng 2G-2.5G.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Với MariaDB, file thường là /etc/mysql/mariadb.conf.d/50-server.cnf. Tìm hoặc thêm vào section [mysqld]:

# VPS 4GB RAM: đặt 2G-2.5G
innodb_buffer_pool_size = 2G
# VPS 2GB RAM: đặt 1G-1.2G
# innodb_buffer_pool_size = 1G

# Bật pool instances (mặc định 1, nên tăng nếu buffer pool > 1G)
innodb_buffer_pool_instances = 4

# Kích thước chunk để quản lý pool mượt hơn
innodb_buffer_pool_chunk_size = 128M

Giải thích: innodb_buffer_pool_size là tham số quan trọng nhất. instances chia pool thành nhiều vùng, giảm tranh chấp concurrent. chunk_size nên giữ nguyên 128M (mặc định) để tránh lãng phí bộ nhớ.

Verify:

sudo systemctl restart mysql
# Kiểm tra giá trị đã áp dụng
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_%';"

Output sẽ hiển thị innodb_buffer_pool_size = 2147483648 (tương đương 2G).

Cảnh báo: Nếu VPS của bạn cũng chạy các dịch vụ khác (Nginx, PHP-FPM, Redis), đừng đẩy buffer pool lên 60% RAM. Hãy giữ ở 40-50%, dành phần còn lại cho PHP worker và cache.

Bước 2, Đánh index đúng cách

Index là cấu trúc giúp MySQL tìm dòng nhanh hơn, giống như mục lục sách. Thiếu index trên cột JOIN hoặc WHERE sẽ gây full table scan, chết ngay khi bảng lớn. Ngược lại, thừa index cũng làm chậm INSERT/UPDATE vì mỗi lần ghi phải cập nhật nhiều index.

Cách phát hiện cần index

Dùng EXPLAIN trước câu SELECT nghi ngờ chậm. Dấu hiệu cần index: cột type hiển thị ALL (full table scan), hoặc rows ước lượng > 10000 mà bảng bạn biết có thể lọc nhỏ hơn.

-- Ví dụ: tìm user chưa verify
EXPLAIN SELECT * FROM users WHERE verified = 0\G

Nếu output cho thấy type: ALL và rows: 50000 thì bạn cần index trên cột verified.

Đánh index cho cột thường dùng

-- Index đơn giản
ALTER TABLE users ADD INDEX idx_verified (verified);

-- Index ghép (composite) cho cặp cột hay dùng cùng nhau
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

Quy tắc chân, leftmost prefix: Index ghép trên (a, b, c) chỉ có hiệu quả khi truy vấn dùng a hoặc a+b hoặc cả 3. Truy vấn chỉ dùng b không dùng được index này.

Xóa index thừa

Kiểm tra index hiện có:

mysql -u root -p -e "SHOW INDEX FROM database_name.table_name;"

Nếu thấy nhiều index trùng lặp (vd idx_email và email trên cùng cột email), hãy bỏ bớt:

ALTER TABLE users DROP INDEX idx_email;

Mẹo: Với bảng nhỏ (< 1000 dòng), index gần như vô dụng, đừng tốn công tối ưu. Tập trung vào các bảng có vài chục nghìn dòng trở lên.

Bước 3, Bật slow query log

Slow query log ghi lại các câu truy vấn chạy lâu hơn ngưỡng cho phép. Công cụ hàng đầu để biết chính xác câu nào đang kéo chết database. Bật nó lên ở mức nhẹ, log những câu > 2 giây.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Thêm vào section [mysqld]:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Giải thích: long_query_time = 2 là ngưỡng 2 giây. log_queries_not_using_indexes ghi thêm các câu không dùng index (dù chạy nhanh), hữu ích để phát hiện truy vấn chưa tối ưu.

sudo systemctl restart mysql
# Kiểm tra log đã bật
sudo tail -f /var/log/mysql/mysql-slow.log

Sau vài giờ, mở log và xem câu nào xuất hiện nhiều lần. Dùng lệnh mysqldumpslow để tổng hợp:

sudo mysqldumpslow /var/log/mysql/mysql-slow.log

Nó sẽ nhóm các câu truy vấn giống nhau (chỉ khác giá trị) và sắp xếp theo số lần xuất hiện. Câu nào đứng đầu danh sách chính là "hung thủ".

Lưu ý: Trên VPS có traffic cao, log này có thể lớn nhanh. Cài logrotate (mặc định Ubuntu đã có) hoặc tắt log sau khi đã tối ưu xong. Chạy SET GLOBAL slow_query_log = 0; trong MySQL để tắt tạm thời.

Bước 4, Cấu hình thêm cho RAM thấp (dưới 1GB)

Nếu VPS của bạn chỉ có 1GB RAM hoặc ít hơn, buffer pool 50% là 512MB còn hơi nhiều so với overhead của OS và các dịch vụ khác. Hãy giảm xuống:

innodb_buffer_pool_size = 256M
innodb_buffer_pool_instances = 1
innodb_log_buffer_size = 8M
max_connections = 50
table_open_cache = 400

Đồng thời, cân nhắc giảm max_connections xuống 20-30 nếu web app của bạn không cần nhiều kết nối đồng thời. Mỗi kết nối MySQL ngốn khoảng 1-2MB RAM, tính ra 50 connections là 50-100MB.

Xử lý lỗi thường gặp

Lỗi "Buffer pool size không thay đổi"

Do innodb_buffer_pool_chunk_size cố định 128M, buffer pool size phải là bội số của 128M. Nếu bạn đặt 1.5G (1536M) sẽ tự động làm tròn xuống 1408M (11*128M). Đây là hành vi bình thường. Để chắc chắn, dùng SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; để kiểm tra.

Không đọc được slow query log

sudo ls -la /var/log/mysql/mysql-slow.log
sudo chmod 644 /var/log/mysql/mysql-slow.log

Nếu file không tồn tại, kiểm tra user MySQL có quyền ghi vào thư mục /var/log/mysql. Thường trên Ubuntu, user mysql đã được gán ownership từ lúc cài.

Database không restart được sau khi sửa config

sudo journalctl -u mysql --no-pager -n 50
# Hoặc với MariaDB
sudo journalctl -u mariadb --no-pager -n 50

Kiểm tra lỗi syntax trong file cnf. Lỗi phổ biến: thiếu dấu =, hoặc giá trị sai kiểu (vd innodb_buffer_pool_size = 2GB, đúng phải là 2G).

Câu hỏi thường gặp

Bài viết liên quan

• Tối ưu MariaDB cho VPS RAM thấp trên Ubuntu 24.04
• Cài đặt và tối ưu MariaDB trên VPS Ubuntu 24.04
• Tối ưu Nginx cho traffic cao: worker, gzip, buffer, cache