Tối ưu hiệu suất MariaDB trên VPS Linux qua my.cnf

SSH vào VPS và gõ SELECT COUNT(*) FROM orders mà thấy nó chậm như ru rùa, hoặc nhìn htop thấy MariaDB ngốn RAM gần hết mà bảng có 3 triệu dòng vẫn ì ạch, thì vấn đề gần như chắc chắn nằm ở cấu hình mặc định. Cấu hình MariaDB khi cài lên thường được đặt ở mức an toàn để chạy trên máy 256MB RAM, vì thế bất kỳ VPS nào từ 2GB trở lên cũng đang chạy dưới công suất thật của nó.

Bài này hướng dẫn bạn tinh chỉnh file my.cnf trên VPS Linux (áp dụng cho Ubuntu 24.04, Debian 12 và AlmaLinux 9) để khai thác đúng sức mạnh MariaDB. Bạn sẽ biết từng thông số làm gì, nên đặt giá trị bao nhiêu cho từng phân vùng RAM cụ thể, cách kiểm tra trước và sau khi thay đổi để biết chắc có cải thiện hay không.

Vì sao MariaDB mặc định lại chạy chậm trên VPS?

Bộ cài MariaDB qua apt hoặc dnf đi kèm file cấu hình my.cnf gần như rỗng. Phần lớn biến được để giá trị mặc định nhúng sẵn trong binary, được thiết kế cho máy tối thiểu để đảm bảo không chết máy khi cài lên. MariaDB không thể đoán được bạn đang chạy VPS 2GB RAM hay dedicated server 64GB, nên nó chọn phương án an toàn.

Hậu quả điển hình khi chạy VPS mà không tối ưu MariaDB:

• InnoDB buffer pool nhỏ (mặc định chỉ 128MB), dữ liệu phải đọc từ đĩa NVMe liên tục, trong khi đáng lẽ nằm sẵn trong RAM.
• Không cache query, cùng một câu SELECT lặp lại vẫn phải chạy lại và tính toán từ đầu.
• Số connection tối đa thấp, website đông người dễ gặp lỗi Too many connections.

Cách kiểm tra nhanh xem MariaDB có đang bị "kẹt" vì cấu hình mặc định không: nhìn vào hit rate của InnoDB buffer pool. Dưới 99% là dấu hiệu buffer pool đang quá nhỏ so với dữ liệu thật.

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

Giá trị thứ nhất là số lần đọc từ buffer pool, giá trị thứ hai là số lần phải đọc từ đĩa. Lấy thứ nhất chia tổng hai số đó, nhân 100, nếu dưới 99% thì cần tăng buffer pool.

Đọc cấu hình hiện tại và xác định vị trí file my.cnf trên VPS Linux

File cấu hình chính của MariaDB trên Ubuntu, Debian và AlmaLinux thường tại /etc/mysql/my.cnf trên Ubuntu/Debian, còn trên AlmaLinux là /etc/my.cnf. Để chắc chắn, chạy lệnh sau:

mariadb --help | grep "Default options"

Kết quả sẽ liệt kê thứ tự các file được đọc, ví dụ:

/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Thứ tự này quan trọng vì giá trị ở file đọc sau sẽ ghi đè file trước. Nếu bạn thêm thông số vào file không nằm trong danh sách này thì nó bị bỏ qua hoàn toàn.

Cấu hình hiện tại đang dùng thực tế (không phải giá trị mặc định) kiểm tra bằng:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'query_cache_type';

Trước khi sửa, sao lưu file gốc:

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak

Trên Ubuntu/Debian, cấu trúc thư mục /etc/mysql/ có các file conf.d/ và mariadb.conf.d/. Bạn nên tạo file riêng trong mariadb.conf.d/ để dễ quản lý và không đụng vào file mặc định:

sudo nano /etc/mysql/mariadb.conf.d/99-tune.cnf

Trên AlmaLinux 9, tạo file /etc/my.cnf.d/tune.cnf tương tự.

Bước 1 - Tối ưu InnoDB buffer pool, thông số quan trọng nhất trên MariaDB

InnoDB là storage engine mặc định của MariaDB và chiếm phần lớn dữ liệu. InnoDB buffer pool là vùng RAM chứa dữ liệu và index đang được sử dụng. Càng lớn thì càng ít phải đọc đĩa, truy vấn càng nhanh.

Nguyên tắc đặt giá trị: khoảng 50-60% RAM khả dụng cho VPS chạy riêng MariaDB, hoặc 30-40% nếu VPS còn chạy cả Nginx, PHP-FPM. Đừng đặt tối đa vì hệ điều hành và tiến trình khác cũng cần RAM.

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1

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

• innodb_buffer_pool_size = 1G: với VPS 2GB RAM chạy cả web server, đặt 1G là hợp lý. VPS 4GB có thể đặt 2G. VPS 8GB chạy database chuyên dụng đặt 4-5G.
• innodb_log_file_size = 256M: đủ lớn để ghi log hiệu quả, không cần quá lớn gây chậm recovery khi mất điện đột ngột.
• innodb_flush_method = O_DIRECT: bỏ qua page cache của OS, tránh ghi đúp dữ liệu vào RAM, giảm áp lực I/O. Cần kiểm tra ổ đĩa là NVMe trên VPS, nếu dùng ổ mạng NFS thì không nên bật.
• innodb_flush_log_at_trx_commit = 1: an toàn nhất, mỗi transaction đều ghi xuống đĩa. Nếu chấp nhận rủi ro mất 1 giây dữ liệu khi mất điện, có thể đặt 2 để nhanh hơn.

Verify cấu hình mới có hiệu lực sau restart:

sudo systemctl restart mariadb
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Giá trị trả về phải là 1073741824 (tương đương 1GB).

Bước 2 - Tinh chỉnh cache và giới hạn kết nối cho MariaDB

MariaDB 10.4 trở lên đã loại bỏ query cache vì không còn hiệu quả trên CPU đa nhân, nên thông số này không cần đụng tới khi tối ưu VPS. Thứ đáng quan tâm là bộ đệm cho các hoạt động khác.

[mysqld]
max_connections = 100
thread_cache_size = 32
table_open_cache = 1024
tmp_table_size = 32M
max_heap_table_size = 32M

Với WordPress hoặc ứng dụng web trung bình, 100 connection là đủ. Mỗi connection của MariaDB ngốn vài MB RAM, để quá cao sẽ tự làm cạn RAM VPS. Kiểm tra số connection đang dùng thực tế:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

Nếu Max_used_connections chỉ quanh 10-20 mà đặt max_connections = 200 thì đang lãng phí RAM. Con số để max_connections gấp 5-10 lần Max_used_connections là chuẩn.

tmp_table_size và max_heap_table_size kiểm soát bảng tạm trong RAM trước khi tràn ra đĩa. Nếu bạn hay chạy câu query có GROUP BY, ORDER BY trên dữ liệu lớn, để 32M hoặc 64M giúp giảm việc ghi file tạm xuống ổ cứng.

Kiểm tra số bảng tạm bị tràn ra đĩa:

SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';

Tỷ lệ giữa hai giá trị trên khi nào vượt 25% thì nên tăng tmp_table_size.

Bước 3 - Cấu hình log chậm để tìm câu query gây tắc nghẽn

Không có cấu hình nào cứu được hệ thống nếu ứng dụng chạy toàn câu query không dùng index. Bước quan trọng nhất trong tối ưu MariaDB trên VPS là tìm ra những câu query đó và xử lý chúng.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2

Thông số này ghi lại mọi query chạy lâu hơn 2 giây vào file log. Sau khi bật vài ngày, xem file log để biết câu nào đang làm chậm hệ thống:

sudo tail -50 /var/log/mysql/mariadb-slow.log

Với ứng dụng mới bắt đầu, có thể đặt long_query_time = 1 trước để soi kỹ hơn, rồi sau đó nới lên 2 hoặc 3 khi hệ thống đã ổn định để tránh log phình to.

MariaDB còn có công cụ mysqldumpslow để gom nhóm các câu query chậm tương tự nhau, giúp bạn nhìn ra pattern thay vì đọc từng dòng một:

sudo mysqldumpslow -t 10 /var/log/mysql/mariadb-slow.log

Output sẽ hiện top 10 nhóm query chậm nhất, kèm số lần xuất hiện và thời gian trung bình. Đây chính là danh sách ưu tiên cần thêm index hoặc viết lại query.

Bước 4 - Giảm RAM lãng phí và tinh chỉnh cho VPS cấu hình thấp

Nếu VPS của bạn chỉ có 1GB hoặc 2GB RAM mà phải chạy cả MariaDB lẫn web server, mục tiêu không phải là chạy nhanh mà là tránh bị OOM killer tấn công. Có vài thông số khác cần đặc biệt lưu ý.

[mysqld]
performance_schema = OFF
skip-name-resolve
innodb_buffer_pool_size = 384M
innodb_log_file_size = 128M

• performance_schema = OFF: bản thân nó ngốn 100-200MB RAM và nhiều CPU. Tắt trên VPS RAM thấp là giải pháp hiệu quả, chỉ mất khả năng giám sát chi tiết ở mức performance_schema.
• skip-name-resolve: bỏ bước phân giải ngược DNS cho từng connection, giúp connection nhanh hơn. VPS có IP tĩnh không cần tính năng này. Nếu user trong MariaDB đang được tạo dạng 'user'@'%' thì không ảnh hưởng gì.
• innodb_buffer_pool_size = 384M: dành 384M buffer pool cho 1GB RAM VPS, 768M cho 2GB RAM. Vẫn đủ cache dữ liệu WordPress nhỏ nhưng không làm hệ thống chết vì thiếu RAM.

Theo dõi sau khi chỉnh có bị swap hay không:

sudo systemctl restart mariadb
free -h

Dòng Swap mà giá trị used tăng đều sau vài ngày là MariaDB đang thiếu RAM thật. Lúc đó cần giảm buffer pool thêm hoặc nâng cấp VPS, đừng để hệ thống chạy trong tình trạng swap liên tục vì sẽ rất chậm.

Bước 5 - Kiểm tra tổng thể và xử lý một số lỗi hay gặp

Sau khi đã điều chỉnh xong, khởi động lại và kiểm tra các giá trị quan trọng:

sudo systemctl restart mariadb
sudo systemctl status mariadb

Kết quả mong đợi là trạng thái active (running). Nếu không khởi động được, xem log lỗi:

sudo journalctl -u mariadb -n 30

Lỗi phổ biến khi tối ưu MariaDB trên VPS:

• Lỗi Unable to allocate memory: đặt buffer pool quá cao so với RAM thật. Giảm xuống và thử lại.
• Không tìm thấy file log: thư mục chứa slow query log chưa có quyền cho user mysql. Tạo thư mục rồi cấp quyền: sudo mkdir -p /var/log/mysql && sudo chown mysql:mysql /var/log/mysql.
• Cảnh báo về innodb_log_file_size: nếu thay đổi giá trị này trên MariaDB đang chạy có thể cần shutdown sạch trước khi sửa.

So sánh nhanh các thông số quan trọng nên đặt theo phân vùng RAM VPS

Bảng dưới đây là điểm khởi đầu hợp lý cho VPS chạy MariaDB cùng web server (Nginx + PHP-FPM), không phải con số rất cao. Kiểm tra lại bằng các lệnh SHOW VARIABLES sau đó tinh chỉnh tiếp cho khớp workload của bạn.

Nguyên tắc vàng là chỉ thay đổi một vài thông số mỗi lần, đo hiệu năng, rồi mới tiếp tục. Thay tất cả cùng lúc khiến bạn không biết thông số nào thực sự tạo khác biệt, và nếu máy chết không biết cái nào gây lỗi.

Kết luận

MariaDB mặc định được cài để "chạy được", không phải để "chạy nhanh" trên VPS. Việc dành 15 phút tinh chỉnh my.cnf đúng cách giúp tận dụng RAM bạn đã trả tiền, giảm tải I/O lên ổ NVMe và cải thiện thời gian phản hồi của toàn bộ ứng dụng. Quan trọng nhất là bật slow query log và định kỳ xem nó, vì config chỉ tốt khi đi kèm query tốt.

Hãy bắt đầu bằng việc kiểm tra Innodb_buffer_pool_reads trên VPS hiện tại để xem buffer pool có đang quá nhỏ hay không, rồi áp dụng các giá trị trong bảng ở trên. Sau một tuần, so sánh hit rate trước và sau để thấy sự khác biệt rõ rệt. Nếu VPS hiện tại sắp hết RAM hoặc chỉ có 1GB, cân nhắc nâng cấp lên VPS Linux với 2GB RAM trở lên để thoải mái cho cả database và web server hoạt động.

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

Bài viết liên quan

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