Cấu Hình MariaDB Cho WordPress: Tối Ưu InnoDB Giảm 60% Query Time

Bạn đã cấu hình PHP-FPM, bật OPcache, thiết lập Redis Object Cache, tối ưu Nginx FastCGI Cache — nhưng TTFB vẫn cao khi traffic tăng? Rất có thể bottleneck nằm ở MariaDB. Database là nền móng của WordPress, và nếu server config không tối ưu, mọi layer caching phía trên đều bị kéo xuống.

Mình từng quản lý một site WooCommerce 50.000 sản phẩm, database quá 2GB. Mỗi lần search product, query time nhảy lên 800ms. Sau khi tune MariaDB config đúng cách — tăng innodb_buffer_pool_size, tối ưu log file, thêm custom index — query time giảm xuống dưới 50ms. Bài này mình sẽ hướng dẫn từ A đến Z cho VPS chạy WordPress.

MariaDB Config Affect WordPress Performance Như Thế Nào?

WordPress thực hiện 20-200 database queries cho mỗi page load. WooCommerce product page có thể lên tới 200+ queries. Mỗi query chưa tối ưu cộng dồn thành hàng giây delay. Nếu MariaDB config mặc định, InnoDB buffer pool chỉ 128MB — quá nhỏ cho production WordPress.

Khi buffer pool đầy, MariaDB phải đọc từ disk thay vì RAM. Disk I/O chậm hơn RAM 100-1000 lần. Đây là lý do site chậm dù server có nhiều RAM free. Tối ưu MariaDB config đảm bảo WordPress đọc data từ memory thay vì disk.

Kiểm Tra MariaDB Config Hiện Tại Trên VPS

Trước khi thay đổi, cần biết server đang chạy gì. Mình luôn bắt đầu bằng 3 bước kiểm tra:

# Bước 1: Kiểm tra phiên bản MariaDB
mysql -V
# Output: mysql  Ver 15.1 Distrib 11.4.5-MariaDB, for debian-linux-gnu (x86_64)

# Bước 2: Xem config hiện tại
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# Output: | innodb_buffer_pool_size | 134217728 | (= 128MB mặc định)

# Bước 3: Kiểm tra storage engine
sudo mysql -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'tenthaovan_cua_ban' LIMIT 5;"
# Đảm bảo tất cả bảng dùng InnoDB

Nếu innodb_buffer_pool_size vẫn ở 128MB mặc định, đây là cơ hội cải thiện lớn nhất. Mình thấy 90% VPS chạy WordPress mà không ai tune thông số này.

Kiểm tra dung lượng database để ước lượng buffer pool cần thiết:

# Xem tổng dung lượng database
sudo mysql -e "
SELECT table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'tenthaovan_cua_ban'
GROUP BY table_schema;"

# Xem top 10 bảng lớn nhất
sudo mysql -e "
SELECT table_name,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)',
table_rows
FROM information_schema.TABLES
WHERE table_schema = 'tenthaovan_cua_ban'
ORDER BY (data_length + index_length) DESC
LIMIT 10;"

Site WordPress điển hình với 1.000 bài viết + WooCommerce thường có database 200-500MB. Site lớn 10.000+ bài có thể 1-3GB.

Cấu Hình InnoDB Buffer Pool: Thông Số Quan Trọng Nhất

innodb_buffer_pool_size là thông số #1 quyết định performance MariaDB cho WordPress. Đây là vùng RAM cache dữ liệu và index của InnoDB tables. Khi WordPress query, MariaDB tìm trong buffer pool trước — nếu hit, trả kết quả ngay; nếu miss, đọc từ disk.

Nguyên tắc mình áp dụng cho VPS chạy WordPress:

# VPS 2GB RAM: buffer pool = 512MB (25% RAM)
# VPS 4GB RAM: buffer pool = 1GB (25% RAM)
# VPS 8GB RAM: buffer pool = 2GB (25% RAM)
# VPS 16GB RAM: buffer pool = 4-6GB (25-37% RAM)

# Đừng vượt quá 50% tổng RAM — PHP-FPM, Nginx, Redis cũng cần memory

Mở file config MariaDB:

# Trên Debian/Ubuntu
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# Trên CentOS/RHEL/AlmaLinux
sudo nano /etc/my.cnf.d/server.cnn

# Nếu không tìm thấy, kiểm tra:
sudo mariadb --help | grep "Default options" -A 1
# Output sẽ liệt kê thứ tự file config được load

Cấu hình InnoDB tối ưu cho WordPress VPS 4GB RAM:

[mysqld]
# InnoDB Buffer Pool - quan trọng nhất
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1

# Đối với buffer pool < 1GB, đặt instances = 1
# Đối với buffer pool 1GB+, đặt instances = 2-4
# Mỗi instance tối thiểu 512MB

# Log file - ảnh hưởng write performance
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# Flush method - tránh double buffering trên Linux
innodb_flush_method = O_DIRECT

# I/O capacity - quan trọng cho SSD
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# File per table - dễ quản lý, tối ưu riêng từng bảng
innodb_file_per_table = 1

# Autoextend - cho phép InnoDB tự tăng file size
innodb_autoextend_increment = 64M

innodb_buffer_pool_instances chia buffer pool thành nhiều instance để giảm contention khi concurrent access. Với WordPress, mình đặt 1 cho VPS nhỏ (buffer pool dưới 1GB) và 2 cho VPS lớn hơn.

innodb_flush_method = O_DIRECT rất quan trọng. Mặc định MariaDB dùng fsync, gây double buffering — data được cache bởi cả OS và InnoDB. O_DIRECT bỏ OS cache, tránh trùng lặp. Mình từng giảm 15% write latency chỉ bằng thay đổi này.

Tối Ưu innodb_log_file_size Cho WordPress

InnoDB log file (redo log) lưu mọi thay đổi trước khi write vào table. Log file quá nhỏ gây frequent checkpoint — MariaDB phải flush data ra disk liên tục, chậm lại. Log file quá lớn tốn disk và slow recovery khi crash.

Mình calculate dựa trên write throughput:

# Kiểm tra bytes written per hour
sudo mysql -e "
SHOW STATUS LIKE 'Innodb_os_log_written';"
# Đợi 1 giờ, chạy lại, tính hiệu số

# Công thức: log_file_size = bytes_per_hour / 2
# Mục tiêu: mỗi log file giữ 1 giờ write activity

Thực tế, cho WordPress site thông thường:

# Site nhỏ (<1GB DB): innodb_log_file_size = 64M
# Site trung bình (1-5GB DB): innodb_log_file_size = 256M
# Site lớn (5GB+ DB): innodb_log_file_size = 512M-1G

Lưu ý: Từ MariaDB 10.9+, bạn không cần手动 xóa old log file khi thay đổi size. MariaDB tự xử lý. Nhưng nếu chạy version cũ, sau khi đổi innodb_log_file_size, cần:

# Stop MariaDB
sudo systemctl stop mariadb

# Xóa old log files (backup trước!)
sudo mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
sudo mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak

# Start MariaDB - sẽ tạo log file mới với size đúng
sudo systemctl start mariadb

tmp_table_size Và max_heap_table_size: Tránh Disk-Based Temp Tables

Khi WordPress thực hiện query với ORDER BY, GROUP BY, hoặc JOIN, MariaDB tạo temporary table. Nếu table nhỏ hơn tmp_table_size, nó nằm trong RAM (nhanh). Nếu lớn hơn, write ra disk (chậm 10-100 lần).

WP_Query với meta_query phức tạp (WooCommerce product filter, ACF relationship) là thủ phạm chính gây large temp tables.

[mysqld]
# Temporary tables trong memory
tmp_table_size = 64M
max_heap_table_size = 64M

# Hai thông số này phải bằng nhau
# tmp_table_size không thể vượt max_heap_table_size

# Sort buffer - cho ORDER BY queries
sort_buffer_size = 4M
# Đừng tăng quá cao — mỗi connection allocate riêng
# 4M x 100 connections = 400MB extra RAM

# Join buffer - cho JOIN không dùng index
join_buffer_size = 4M

# Read buffer - cho full table scan
read_buffer_size = 2M
read_rnd_buffer_size = 4M

Mình từng profile một site WooCommerce mà product filter query tạo temp table 128MB mỗi lần — vì tmp_table_size mặc định chỉ 16MB, temp table bị flush ra disk. Tăng lên 64M giảm query time từ 2.5s xuống 0.3s.

MariaDB 11+ Đã Loại Query Cache: Cái Gì Thay Thế?

Nhiều hướng dẫn cũ vẫn khuyên bật query cache. Nhưng MariaDB 10.4+ đã deprecate query cache, và MariaDB 11+ loại bỏ hoàn toàn. Lý do: query cache gây lock contention, overhead lớn hơn benefit, đặc biệt với WordPress có nhiều write operations.

Thay vào đó, dùng Redis Object Cache — hiệu quả hơn nhiều:

# Cài đặt Redis server
sudo apt install redis-server

# Cài plugin Redis Object Cache trong WordPress
wp plugin install redis-cache --activate --path=/var/www/thienlv.com/public_html

# Kiểm tra Redis đang hoạt động
wp redis status --path=/var/www/thienlv.com/public_html
# Output: Status: Connected
#         Client: PhpRedis
#         Database: 0

Mình đã hướng dẫn chi tiết trong bài Cấu Hình Redis Object Cache Cho WordPress. Redis cache query results trong RAM, giảm 70-90% database hits. Kết hợp với MariaDB tuning là combo tối ưu nhất.

Bật Slow Query Log: Tìm Query Chậm Đang Kéo Site Xuống

Slow query log là công cụ debug #1 cho database performance. Mình luôn bật trên production với threshold 1 giây:

[mysqld]
# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
log_slow_rate_limit = 1000
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

# Đảm bảo file log tồn tại
sudo touch /var/log/mysql/mariadb-slow.log
sudo chown mysql:mysql /var/log/mysql/mariadb-slow.log

Sau 24 giờ, phân tích slow queries:

# Xem top 10 query chậm nhất
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log

# Output ví dụ:
# Count: 145  Time=2.34s (339s)  Lock=0.00s (0s)  Rows=0.0 (0)
#   SELECT * FROM wp_postmeta WHERE meta_key = '_wc_average_rating'
#   AND meta_value > '4' ORDER BY meta_value DESC

# Count: 89  Time=1.87s (166s)  Lock=0.00s (0s)  Rows=15.3 (1361)
#   SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt
#   ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('product_cat')

Nhìn vào Count (số lần query chạy) và Time (thời gian trung bình). Query chạy 145 lần trong 24 giờ, mỗi lần 2.34s — đó là 339 giây database time. Ưu tiên fix trước.

Sử Dụng MySQLTuner: Kiểm Tra Tổng Quát MariaDB

MySQLTuner là script Perl miễn phí phân tích config MariaDB và đưa ra khuyến nghị. Mình chạy sau mỗi lần thay đổi lớn:

# Tải MySQLTuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

# Chạy (cần password root MariaDB)
perl mysqltuner.pl --user root --pass 'password_cua_ban'

# Output sẽ hiển thị:
# -------- Recommendations ---------------------------------------------------------------------------
# General recommendations:
#   RAM upgrade: Your system has 4GB RAM, but buffer pool is only 512MB
#   Consider increasing innodb_buffer_pool_size
#
# Variables to adjust:
#   innodb_buffer_pool_size (>= 1G) if possible
#   tmp_table_size (> 64M)
#   max_connections (> 100)

Lưu ý: MySQLTuner đưa ra gợi ý dựa trên statistics tại thời điểm chạy. Chạy khi server có traffic thực tế để có data chính xác. Đừng chạy ngay sau restart — statistics cần thời gian tích lũy.

Tối Ưu wp_options Autoload: Hidden Performance Killer

Đây không phải config MariaDB, nhưng là database optimization quan trọng nhất cho WordPress. Mỗi page load, WordPress load tất cả rows trong wp_options có autoload = 'yes'. Nếu dữ liệu lớn, nó ăn RAM và CPU.

# Tìm top 20 autoload options lớn nhất
sudo mysql -e "
SELECT option_name,
       ROUND(LENGTH(option_value) / 1024, 2) AS 'Size (KB)'
FROM wp_options
WHERE autoload = 'yes'
ORDER BY LENGTH(option_value) DESC
LIMIT 20;" tenthaovan_cua_ban

# Tổng autoload size
sudo mysql -e "
SELECT ROUND(SUM(LENGTH(option_value)) / 1024 / 1024, 2) AS 'Total Autoload (MB)'
FROM wp_options WHERE autoload = 'yes';" tenthaovan_cua_ban

Quy tắc mình áp dụng: tổng autoload nên dưới 1MB. Nếu trên 2MB, cần cleanup:

# Disable autoload cho option không cần (thay plugin_name bằng option thực tế)
sudo mysql -e "
UPDATE wp_options
SET autoload = 'no'
WHERE option_name LIKE '_transient_%'
AND autoload = 'yes';" tenthaovan_cua_ban

# Xóa transients hết hạn
sudo mysql -e "
DELETE FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();" tenthaovan_cua_ban

Mình từng giảm autoload từ 4.2MB xuống 600KB trên một site — TTFB giảm 200ms ngay lập tức.

Thêm Custom Index Cho wp_postmeta: Tăng Tốc WP_Query

wp_postmeta là bảng lớn nhất trong hầu hết WordPress site. Mặc định chỉ có index trên meta_key. Khi WP_Query filter theo meta_value, MariaDB phải full table scan.

# Kiểm tra size wp_postmeta
sudo mysql -e "
SELECT COUNT(*) AS total_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'tenthaovan_cua_ban'
AND table_name = 'wp_postmeta';"

# Thêm composite index cho meta_key + meta_value
# Chỉ làm nếu site có WooCommerce hoặc ACF với meta_query phức tạp
sudo mysql -e "
ALTER TABLE wp_postmeta
ADD INDEX wp_postmeta_meta_value (meta_key(50), meta_value(50));" tenthaovan_cua_ban

Cảnh báo: Thêm index tăng read speed nhưng giảm write speed và tốn disk space. Chỉ thêm khi chắc chắn query phức tạp đang chậm. Mình khuyên dùng slow query log xác định trước, sau đó mới thêm index针对.

Backup database trước khi thay đổi index:

# Backup trước
mysqldump -u root -p tenthaovan_cua_ban | gzip > /backup/db_$(date +%Y%m%d).sql.gz

# Nếu sai, restore
gunzip < /backup/db_20260803.sql.gz | mysql -u root -p tenthaovan_cua_ban

Mình đã hướng dẫn chi tiết backup strategy trong bài Backup WordPress Trên VPS Bằng rsync + mysqldump. Đọc trước khi làm bất kỳ thay đổi database nào.

Config Hoàn Chỉnh MariaDB Cho WordPress VPS 4GB RAM

Dưới đây là config mình áp dụng cho hầu hết VPS 4GB RAM chạy WordPress production. Copy vào /etc/mysql/mariadb.conf.d/50-server.cnf hoặc tương đương:

[mysqld]
# Basic settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB - nền tảng performance
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_autoextend_increment = 64M
innodb_stats_persistent = 1

# Connection settings
max_connections = 100
max_user_connections = 80
wait_timeout = 60
interactive_timeout = 60
max_allowed_packet = 64M

# Memory allocation
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
thread_stack = 256K
thread_cache_size = 16

# Query optimization
table_open_cache = 400
table_definition_cache = 400
open_files_limit = 4096

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# Safety
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
local_infile = 0

# Performance
concurrent_insert = 2
low_priority_updates = 1
back_log = 50

Cho VPS 2GB RAM, giảm innodb_buffer_pool_size xuống 512M và max_connections xuống 50. Cho VPS 8GB+, tăng buffer pool lên 2G và instances lên 2.

Khởi Động Lại MariaDB An Toàn

Sau khi edit config, restart MariaDB:

# Kiểm tra config trước (không restart)
sudo mariadbd --validate-config

# Restart
sudo systemctl restart mariadb

# Kiểm tra status
sudo systemctl status mariadb

# Verify buffer pool đã cập nhật
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# Output: | innodb_buffer_pool_size | 1073741824 | (= 1GB)

# Kiểm tra error log
sudo tail -20 /var/log/mysql/error.log

Quan trọng: Chạy mariadbd --validate-config trước khi restart. Nếu config có syntax error, MariaDB không start được — site down. Validate giúp phát hiện lỗi trước.

Đo Lường Trước Và Sau: Benchmark Database Performance

Mình luôn đo trước và sau khi tối ưu để có con số cụ thể. Đây là cách:

# Cài mysqlslap (có sẵn trong mariadb-client)
# Benchmark với read-heavy workload (giống WordPress)

mysqlslap \
  --concurrency=50 \
  --iterations=10 \
  --number-int-cols=2 \
  --number-char-cols=3 \
  --auto-generate-sql \
  --auto-generate-sql-load-type=read \
  --host=localhost \
  --user=root \
  --password='password_cua_ban'

# Output:
# Benchmark
#   Average number of seconds to run all queries: 0.324 seconds
#   Minimum number of seconds to run all queries: 0.298 seconds
#   Maximum number of seconds to run all queries: 0.401 seconds
#   Number of clients running queries: 50
#   Average number of queries per client: 0

Hoặc đo bằng Query Monitor plugin trong WordPress:

# Cài Query Monitor
wp plugin install query-monitor --activate --path=/var/www/thienlv.com/public_html

# Mở trang frontend, check Admin Bar -> Query Monitor
# Tab "Queries by Component" - xem total query time
# Trước tối ưu: Total queries: 187 | Time: 1.24s
# Sau tối ưu: Total queries: 187 | Time: 0.31s

Trên một site thực tế mình tối ưu tháng trước, kết quả benchmark:

Kết Luận: MariaDB Config Có Đáng Đầu Tư Thời Gian?

Cấu hình MariaDB đúng là một trong những optimization có ROI cao nhất cho WordPress. Khác với plugin caching — dễ bật nhưng khó kiểm soát — database tuning cho kết quả predictable, measurable, và persistent. Mình xếp nó top 3 việc nên làm cho VPS chạy WordPress, cùng với PHP-FPM tuning và Redis Object Cache.

Nếu chỉ có 30 phút, tập trung vào 3 việc: tăng innodb_buffer_pool_size lên 25% RAM, đặt innodb_flush_method = O_DIRECT, và bật slow query log. Ba thay đổi này đơn giản nhất nhưng impact lớn nhất.

Nếu bạn đang chuẩn bị nâng cấp lên WordPress 7.1 ngày 19/8, đây là lúc tốt nhất để tune MariaDB trước. Đọc thêm hướng dẫn test WordPress 7.1 Beta 3 và đảm bảo database sẵn sàng cho bản cập nhật lớn.