Giảm RAM MySQL trên VPS nhỏ: tuning buffer và kết nối

Một tình huống hay gặp trên Cloud VPS database cấu hình nhỏ: dashboard báo RAM đứng yên ở mức báo động ngày này qua ngày khác. Máy chưa sập, website vẫn chạy, nhưng với 1 GB RAM và không có swap, chỉ cần một đợt tải là kernel sẽ giết mysqld. Bài này là quy trình xử lý RAM MySQL cao trên VPS nhỏ: đo xem bộ nhớ đi đâu, loại bỏ hướng nghi sai, hiểu các vùng nhớ cố định của MySQL 8.0, và tinh chỉnh từng tham số theo tài nguyên thật thay vì giữ mặc định.

Triệu chứng

• Máy 1 vCPU, khoảng 1 GB RAM, không swap, chạy MySQL 8.0 với cấu hình gần như mặc định.
• RAM dùng trên 90%, RAM available chỉ còn vài chục MB.
• mysqld chiếm phần lớn RAM, dù dữ liệu trên đĩa nhỏ hơn thế.
• Không có lỗi, không có site chậm. MySQL đã chạy liên tục nhiều tháng không restart.

Đây là kiểu sự cố "chưa xảy ra". Một máy cùng cỡ RAM, không swap, sớm muộn sẽ gặp cảnh như bài OOM nginx trên balancer: khi một máy nhỏ gánh nhiều website, chỉ khác là tiến trình bị giết là database.

Điều tra RAM MySQL

Bước 1: RAM MySQL hay tiến trình khác

free -h -w
ps aux --sort=-%mem | head -10
ss -nt 'sport = :3306'
vmstat 1 3

So RSS của mysqld với các tiến trình khác (journald, exporter), phần kernel (Slab, PageTables) và page cache mà hệ điều hành thu hồi được. Nếu mysqld chiếm phần lớn, câu hỏi tiếp theo là bên trong tiến trình có gì.

Bước 2: nhìn vào RAM MySQL ở cấp tiến trình

PID=$(pidof mysqld)
cat /proc/$PID/smaps_rollup
grep -E 'VmPeak|VmRSS|VmSwap|VmData|RssAnon|RssFile' /proc/$PID/status
du -sh /var/lib/mysql/*/

Nếu tổng dữ liệu trên đĩa nhỏ hơn hẳn RSS, phần lớn RAM MySQL không phải dữ liệu được cache, mà là các vùng nhớ cố định và heap tích luỹ.

Bước 3: so cấu hình RAM MySQL với mặc định

[mysqld]
datadir     = /var/lib/mysql
log-error   = /var/log/mysql/error.log
max_connections = 400
skip-log-bin

Một file như trên để gần như mọi thứ ở mặc định của MySQL 8.0. Theo tài liệu cách MySQL dùng bộ nhớ, các thành phần chính gồm:

Cần đọc bảng cho đúng: bộ đệm theo kết nối chỉ được cấp khi có kết nối và khi câu lệnh cần, nên đó là trần chứ không phải số đang dùng. max_connections cao gây hại theo cách khác: Performance Schema định cỡ nhiều bảng nội bộ theo số kết nối và số bảng mở tối đa, xem mô hình cấp phát bộ nhớ của Performance Schema.

Bước 4: loại giả thuyết query treo hay rò bộ nhớ

Giả thuyết đầu tiên của nhiều người khi thấy RAM MySQL cao là "có query nặng đang chạy" hoặc "MySQL rò bộ nhớ". Chụp vài snapshot cách nhau vài giây:

for i in 1 2 3; do
  ss -nt 'sport = :3306' | tail -n +2 | wc -l
  grep VmRSS /proc/$PID/status
  cat /proc/$PID/task/*/status | grep '^State' | sort | uniq -c
  sleep 3
done

Nếu chỉ có một hai kết nối, mọi thread đều ở trạng thái ngủ và RSS không đổi giữa các lần chụp, thì không có query treo, RAM không tăng theo truy vấn. Giả thuyết bị loại chỉ trong vài giây. Có thể đối chiếu thêm bằng số MySQL tự báo:

SELECT event_name, FORMAT_BYTES(current_number_of_bytes_used) AS used
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY current_number_of_bytes_used DESC LIMIT 10;

Nguyên nhân gốc

RAM MySQL cao trên máy nhỏ thường là tổng của các yếu tố sau:

• Cấu hình mặc định giả định máy lớn hơn. Giá trị mặc định của MySQL 8.0 hợp với máy vài GB RAM.
• Performance Schema bật và được định cỡ theo max_connections lớn, trong khi thực tế chỉ có vài kết nối.
• table_open_cache quá lớn so với số bảng thật.
• Không swap, nên không có vùng đệm khi RAM đầy.
• Chạy rất lâu không restart. Bộ cấp phát của glibc không nhất thiết trả lại heap đã giải phóng cho hệ điều hành, nên RSS phản ánh đỉnh cũ chứ không phải nhu cầu hiện tại. Đây là giả thuyết; RSS giảm rõ ngay sau restart là dấu hiệu củng cố nó.

Khắc phục RAM MySQL cao

Thêm index thiếu, qua migration

Nếu processlist hoặc slow log cho thấy bảng bị quét toàn bộ, thêm index qua migration của dự án, không tạo tay trên server. Index giảm I/O và CPU; phần RAM giảm ngay sau khi restart chủ yếu đến từ việc xoá heap tích luỹ, không phải từ index.

Thêm swap làm lưới an toàn

fallocate -l 512M /swapfile
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
echo '/swapfile none swap sw 0 0' >> /etc/fstab
echo 'vm.swappiness=10' > /etc/sysctl.d/99-swappiness.conf
sysctl --system

vm.swappiness=10 để kernel chỉ đẩy trang ra swap khi thật sự thiếu RAM. Swap ở đây là lưới chống OOM, không phải phần mở rộng của RAM MySQL cho việc chạy hằng ngày.

Tinh chỉnh mysqld.cnf để giảm RAM MySQL

# /etc/mysql/mysql.conf.d/mysqld.cnf: ví dụ cho máy 1 GB RAM, vài trăm bảng
max_connections = 100
table_open_cache = 1000
table_definition_cache = 400
innodb_buffer_pool_size = 128M
tmp_table_size = 12M
max_heap_table_size = 12M
thread_cache_size = 12

Trước khi restart, kiểm tra cấu hình bằng mysqld --validate-config. Một dòng sai cú pháp trên máy chỉ có một database là cả ứng dụng ngừng. Cũng kiểm tra không có tham số khai báo hai lần: MySQL lấy giá trị đọc sau cùng, nhưng hai dòng mâu thuẫn là cái bẫy cho người sửa sau.

grep -rn 'max_connections' /etc/mysql/
mysqld --validate-config && systemctl restart mysql

Đo lại sau mỗi bước

Ghi RAM dùng, RAM available và RSS của mysqld (RAM MySQL thật) trước và sau từng thay đổi, rồi theo dõi trên dashboard vài tuần để xem RAM có bò lên lại không. Số đo ngay sau restart luôn đẹp hơn thực tế.

Phòng ngừa RAM MySQL cao

• Với RAM MySQL trên máy nhỏ, mọi Cloud VPS database dưới 2 GB RAM nên có swap và vm.swappiness thấp.
• Đặt max_connections theo số kết nối thật: SHOW GLOBAL STATUS LIKE 'Max_used_connections' sau vài tuần chạy.
• Đặt table_open_cache và table_definition_cache theo SELECT COUNT(*) FROM information_schema.tables.
• So bộ nhớ MySQL tự báo với RSS của tiến trình. Chênh lệch lớn là dấu hiệu heap tích luỹ.
• Cảnh báo theo RAM available, không chỉ theo phần trăm đã dùng: page cache cũng bị tính là "đã dùng" trên nhiều dashboard.
• Giới hạn journald (SystemMaxUse=50M) trên máy đĩa và RAM nhỏ.
• Nếu RAM MySQL vẫn tăng lại, ưu tiên nâng RAM máy thay vì tắt Performance Schema, vì đó là công cụ chẩn đoán cần cho các sự cố truy vấn.

Bài học

• RAM MySQL cao chưa chắc là vấn đề truy vấn. Vài snapshot thread đều ngủ đủ loại giả thuyết query treo trong vài giây.
• Mặc định không phải là trung lập. Như bài Tầng Database: MySQL đa tenant trên VPS cấu hình nhỏ, mỗi tham số cần tính theo RAM thật.
• Restart là thuốc giảm đau. Nó trả lại một phần RAM, nhưng không có cấu hình đúng thì RAM MySQL sẽ bò lên lại theo thời gian.
• Đo trước, đổi sau, đo lại. Mỗi bước có số trước và sau thì mới biết bước nào thật sự có tác dụng.

Bài tiếp theo

MySQL 8.4 chọn sai kế hoạch với điều kiện <>: tách query hai bước. Khi đã có index mà query vẫn treo rất lâu, và vì sao một điều kiện "khác" nhỏ xíu đủ làm optimizer đi sai đường.