Tối ưu MariaDB trên Ubuntu VPS để giảm tải tài nguyên

VPS Ubuntu của bạn có thể chạy web mượt cho tới khi lượng truy cập tăng, và thủ phạm ngốn RAM đầu tiên thường là MariaDB. Mặc định MariaDB cài xong đã chiếm vài trăm MB, chưa kể InnoDB buffer pool tự phình theo mặc định. Bài này đi thẳng vào những tham số cần sửa trong /etc/mysql/mariadb.conf.d/50-server.cnf trên Ubuntu 24.04 LTS, kèm lệnh kiểm tra trước và sau để bạn biết mình vừa tiết kiệm được bao nhiêu.

• Đặt InnoDB buffer pool ~50-60% RAM khả dụng, không phải càng cao càng tốt.
• Siết max_connections và table_open_cache để tránh thread rác ngốn RAM.
• Bật slow query log để tìm query ăn CPU, thay vì đoán mò tham số.

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

• VPS Ubuntu 22.04 hoặc 24.04 LTS, quyền sudo hoặc root.
• MariaDB đã cài và đang chạy (bản 10.6+ trên Ubuntu 22.04, 10.11+ trên 24.04).
• Đã sao lưu dữ liệu trước khi sửa config. Với VPS thueVPS bạn có thể tạo snapshot một phát là yên tâm, sai thì rollback.
• Kiến thức cơ bản về SQL và thao tác dòng lệnh.

Vì sao MariaDB lại ngốn tài nguyên trên VPS

MariaDB không tự nhiên ăn RAM. Có ba lý do chính. Thứ nhất, InnoDB buffer pool mặc định nhỏ, nhưng khi workload tăng thì InnoDB đòi thêm cache, và mỗi connection lại cấp phát một bộ buffer riêng (sort buffer, join buffer, read buffer). Thứ hai, MariaDB giữ thread cho mỗi kết nối, nên ứng dụng mở pool 100 connection thì có 100 thread chờ, mỗi thread vài chục MB là hết RAM. Thứ ba, index thiếu và query không tối ưu khiến MariaDB phải quét toàn bảng, đẩy CPU lên cao trong khi đĩa NVMe cày liên tục.

Trên một VPS 2GB RAM, việc để nguyên config mặc định rất dễ dẫn tới tình trạng OOM killer, khiến MariaDB và cả PHP-FPM bị kill giữa chừng. Tối ưu MariaDB trên Ubuntu VPS thực chất là làm cho tải tài nguyên tỉ lệ với dữ liệu thật sự cần, chứ không phải với số kết nối tối đa có thể.

Bước 1 - Kiểm tra tài nguyên và config hiện tại

Trước khi sửa gì, đo đã. Ta cần biết MariaDB đang dùng bao nhiêu RAM và file config nào đang được nạp.

free -h
systemctl status mariadb --no-pager
sudo mariadb -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('version','innodb_buffer_pool_size','max_connections','table_open_cache','thread_cache_size');"

Ghi lại các con số innodb_buffer_pool_size và max_connections. Kiểm tra file config đang được nạp:

mariadb --help --verbose | grep -A1 "Default options"
ls -la /etc/mysql/mariadb.conf.d/

Output mong đợi: dòng /etc/mysql/mariadb.conf.d/50-server.cnf nằm trong danh sách. Đây là file ta sẽ sửa. Đừng quên xem log lỗi để biết vì sao process restart lần trước:

sudo tail -n 50 /var/log/mysql/error.log

Nếu thấy dòng kiểu Out of memory hay Killed process thì xác nhận đã tới lúc phải siết lại buffer.

Bước 2 - Đặt InnoDB buffer pool đúng theo RAM

Đây là chỉnh sửa có tác động lớn nhất. InnoDB buffer pool là vùng RAM đệm dữ liệu và index; nếu để quá nhỏ, MariaDB phải đọc đĩa liên tục; nếu để quá lớn, hệ điều hành bắt đầu swap. Nguyên tắc thực tế mình hay dùng: buffer pool = 50-60% RAM khả dụng trên VPS chỉ chạy MariaDB, hạ xuống 30-40% nếu VPS còn chạy web, PHP-FPM, Docker.

Ví dụ VPS 2GB RAM, web nặng, mình để buffer pool 512M. Mở file cấu hình:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Thêm hoặc sửa trong khối [mariadb] (hoặc [mysqld] tuỳ bản):

[mariadb]
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 1
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

Giải thích nhanh từng dòng:

• innodb_buffer_pool_size: cache chính. 512M cho VPS 2GB, 1G cho 4GB, 2-4G cho 8GB trở lên (web nặng).
• innodb_buffer_pool_instances: chia nhiều instance khi buffer pool > 1G để giảm tranh chấp mutex. Dưới 1G để 1 là đủ.
• innodb_log_file_size: log lớn giúp giảm checkpoint, nhưng tăng thời gian crash recovery. 128M là điểm cân bằng tốt cho VPS.
• innodb_flush_method = O_DIRECT: tránh double buffering giữa OS cache và InnoDB khi chạy trên NVMe.
• innodb_flush_log_at_trx_commit = 2: ghi log mỗi giây thay vì mỗi commit, đánh đổi chút an toàn lấy hiệu năng. Giữ 1 nếu dữ liệu tài chính, không được mất một giao dịch nào.

Kiểm tra sau khi restart service:

sudo systemctl restart mariadb
sudo mariadb -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Output mong đợi: innodb_buffer_pool_size | 536870912 (đúng 512M).

Bước 3 - Siết connection và thread để giảm RAM thừa

Mỗi connection MariaDB cấp phát một bộ buffer riêng. max_connections mặc định 151 nhưng ứng dụng của bạn có thật sự mở 151 kết nối? Đặt vừa phải giúp RAM không bị phình khi traffic đột biến.

[mariadb]
max_connections = 60
thread_cache_size = 16
table_open_cache = 400
table_definition_cache = 400
key_buffer_size = 32M
tmp_table_size = 64M
max_heap_table_size = 64M
join_buffer_size = 1M
sort_buffer_size = 1M
read_buffer_size = 1M

Điểm đáng lưu ý: join_buffer_size, sort_buffer_size, read_buffer_size tính theo mỗi connection. Để 8M mặc định nhân với 60 connection là đủ OOM ngay. Mình thường để 1M cho cả ba, chỉ nâng lên khi có query cần sort nặng mà đã index đầy đủ. Nếu web dùng connection pool như PDO, giữ pool nhỏ và ổn định còn tốt hơn mở rộng max_connections.

Kiểm tra:

sudo systemctl restart mariadb
sudo mariadb -e "SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';"

Nếu Threads_connected gần sát max_connections mà chưa siết lại, đó là dấu hiệu cần tìm nguyên nhân ở tầng ứng dụng, không phải mở rộng thêm ở phía MariaDB.

Bước 4 - Bật slow query log để tìm query ăn tài nguyên

Đoán mò tham số là cách nhanh nhất để tốn thời gian. Slow query log cho bạn biết chính xác query nào chậm, query nào không dùng index.

[mariadb]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

long_query_time = 1 ghi log query trên 1 giây. Nếu log quá nhiều dòng, tăng lên 2-3. Sau vài ngày, phân tích log:

sudo tail -n 100 /var/log/mysql/mariadb-slow.log
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log

mysqldumpslow -s t sắp xếp theo tổng thời gian thực thi, giúp bạn thấy ngay query nào cần thêm index. Tối ưu query là cách giảm tải MariaDB bền vững nhất, vì nó cắt đứt tải tài nguyên ngay tại nguồn.

Bước 5 - Xử lý bảng và index định kỳ

Bảng bị phân mảnh (fragmentation) sau nhiều lần xóa/sửa sẽ khiến MariaDB đọc dư. Kiểm tra dung lượng và xác định bảng cần tối ưu:

SELECT table_schema, table_name,
       ROUND((data_length + index_length)/1024/1024, 2) AS total_mb,
       ROUND(data_free/1024/1024, 2) AS free_mb
FROM information_schema.tables
WHERE data_free > 0
ORDER BY free_mb DESC
LIMIT 10;

Với bảng có free_mb lớn, chạy OPTIMIZE TABLE ngoài giờ cao điểm (nó khoá bảng):

OPTIMIZE TABLE ten_bang;

Với database WordPress hoặc WooCommerce, hai bảng wp_options và wp_postmeta thường thiếu index khiến truy vấn quét toàn bảng. Thêm index cho cột hay dùng trong WHERE và JOIN giảm tải CPU đáng kể.

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

MariaDB không start sau khi sửa config

Kiểm tra log ngay:

sudo journalctl -u mariadb -n 30 --no-pager
sudo tail -n 40 /var/log/mysql/error.log

Lỗi unknown variable nghĩa là bạn gõ sai tên tham số hoặc đặt sai khối. Sửa lại hoặc comment dòng lỗi, rồi sudo systemctl restart mariadb.

VPS hết RAM sau khi tăng buffer pool

Nếu buffer pool lớn hơn RAM thật, OS bắt đầu swap và MariaDB chậm hơn cả trước. Kiểm tra:

free -h
cat /proc/meminfo | grep -E "MemAvailable|SwapUsed"

Nếu MemAvailable thấp và swap đầy, hạ buffer pool xuống 30-40% RAM. Đừng cố giữ buffer pool lớn chỉ vì output đẹp.

Query vẫn chậm dù buffer pool lớn

Vấn đề nằm ở index hoặc schema. Dùng EXPLAIN trên query đang chậm để xác định có dùng index hay không:

EXPLAIN SELECT * FROM bang WHERE cot = 'gia_tri';

Nếu cột key trong output là NULL, query đang quét toàn bảng và cần thêm index.

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

ps aux | grep mariadbd
sudo ps_mem | grep mariadbd

Bài viết liên quan

• Tối ưu MariaDB trên VPS 2GB RAM hiệu quả
• Tối ưu hiệu suất MariaDB trên VPS Linux qua my.cnf
• Cài đặt và tối ưu MariaDB trên VPS Ubuntu 24.04
• Vì sao VPS bị đầy RAM và cách xử lý hiệu quả