Tối Ưu MySQL & MariaDB Trên VPS TrumVPS - Tăng Tốc Database, Hết Lag Website

Website WordPress của bạn load chậm? Trang admin mất 5-10 giây mới mở? Rất có thể "nút thắt cổ chai" nằm ở database. MySQL/MariaDB mặc định được cấu hình cho máy chủ 512MB RAM — nếu VPS của bạn có 2GB, 4GB hay 8GB RAM, bạn đang lãng phí tài nguyên. Bài này sẽ hướng dẫn bạn tối ưu MySQL/MariaDB để tận dụng tối đa sức mạnh VPS TrumVPS, giúp website tăng tốc gấp 3-5 lần.

Trước Khi Bắt Đầu: Kiểm Tra Hiện Trạng

Xem cấu hình hiện tại của MySQL/MariaDB:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

mysql -u root -p -e "SHOW VARIABLES LIKE 'query_cache_size';"

Kiểm tra dung lượng RAM dùng cho database:

mysql -u root -p -e "SELECT table_schema, SUM(data_length + index_length)/1024/1024 AS size_mb FROM information_schema.tables GROUP BY table_schema ORDER BY size_mb DESC;"

Yêu Cầu

• VPS TrumVPS chạy MySQL 8.0+ hoặc MariaDB 10.6+
• Quyền sudo/root
• Biết đường dẫn file cấu hình: /etc/mysql/my.cnf hoặc /etc/mysql/mariadb.conf.d/50-server.cnf

Cấu Hình Tối Ưu Cho Từng Gói VPS

Gói VPS 40K (1GB RAM)

[mysqld]
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
query_cache_type = 0
max_connections = 50
table_open_cache = 256
tmp_table_size = 32M
max_heap_table_size = 32M
thread_cache_size = 8
sort_buffer_size = 256K
join_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 512K

Gói VPS 80K (2GB RAM)

[mysqld]
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 400
query_cache_type = 0
max_connections = 100
table_open_cache = 512
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 16
sort_buffer_size = 512K
join_buffer_size = 512K
read_rnd_buffer_size = 1M
table_definition_cache = 512

Gói VPS 160K (4GB RAM)

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
query_cache_type = 0
max_connections = 200
table_open_cache = 1024
tmp_table_size = 128M
max_heap_table_size = 128M
thread_cache_size = 32
sort_buffer_size = 1M
join_buffer_size = 1M
read_rnd_buffer_size = 2M
table_definition_cache = 1024

Áp Dụng Cấu Hình

Sau khi edit file cấu hình, chạy:

systemctl restart mysql (hoặc mariadb)

Kiểm tra xem cấu hình mới đã áp dụng chưa:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Bật Slow Query Log — Bắt "Sát Thủ" Hiệu Năng

Thêm vào my.cnf:

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

Phân tích slow queries bằng pt-query-digest (từ Percona Toolkit):

apt install percona-toolkit -y

pt-query-digest /var/log/mysql/slow.log

Công cụ này sẽ hiển thị top queries chậm nhất, thời gian trung bình, và gợi ý index cần tạo.

Tối Ưu WordPress Database

Cài Index Cho WordPress

Chạy các lệnh SQL sau để tạo index cho các bảng WordPress thường xuyên query:

ALTER TABLE wp_postmeta ADD INDEX meta_key_value (meta_key, meta_value(191));
ALTER TABLE wp_posts ADD INDEX post_type_date (post_type, post_date);
ALTER TABLE wp_comments ADD INDEX comment_approved_date (comment_approved, comment_date);
ALTER TABLE wp_options ADD INDEX autoload (autoload);

Tối Ưu Autoload Options

WordPress load tất cả options có autoload=yes mỗi request. Nhiều plugin để lại options rác:

mysql -u root -p wordpress_db -e "SELECT COUNT(*), SUM(LENGTH(option_value)) as size_bytes FROM wp_options WHERE autoload='yes';"

Nếu tổng > 1MB → xóa options của plugin đã gỡ, hoặc set autoload=no cho những options ít dùng.

Dọn Database Định Kỳ

mysqlcheck -u root -p --optimize --databases wordpress_db

Thêm vào cron hàng tuần:

0 3 * * 0 mysqlcheck -u root -pYourP… --optimize --databases wordpress_db

Dùng Redis Cache Giảm Tải MySQL

Cài Redis + plugin Redis Object Cache cho WordPress. Cấu hình:

define('WP_REDIS_HOST', '127.0.0.1');

Sau khi bật, số query MySQL có thể giảm 60-80% vì hầu hết data được cache trên RAM.

Công Cụ Giám Sát Database

• mytop: apt install mytop -y && mytop -u root -p — như htop cho MySQL
• phpMyAdmin → Status → Monitor: đồ thị query/sec, connections realtime
• MySQLTuner: wget mysqltuner.pl && perl mysqltuner.pl — script tự động phân tích và gợi ý cấu hình

So Sánh Trước/Sau Tối Ưu