Một website Laravel vừa chạy trên pod MySQL 8 trong Kubernetes thì trang chậm dần rồi treo. Log ứng dụng có lỗi 1114: The table '/tmp/#sql...' is full, dù đĩa gốc của node còn trống. Nghi phạm là bảng tạm MySQL: bảng nội bộ server tự tạo khi chạy truy vấn nặng. Thư mục #innodb_temp có thể phình lên hàng chục GB trên volume dữ liệu, nhưng việc nó gây ra lỗi 1114 vẫn chưa được chứng minh. Bài này trình bày cách chẩn đoán trong pod, các cơ chế bảng tạm MySQL, và ba hướng xử lý bảng tạm MySQL kèm mức bằng chứng. Nguyên nhân gốc chưa được chốt, nên bài nói rõ đâu là quan sát và đâu là giả thuyết.

Nếu volume đầy vì lý do khác (binlog, dữ liệu thật), xem MySQL đầy volume trên Kubernetes: binlog, dựng lại PVC và bẫy khi nới PVC.

Triệu chứng: lỗi 1114 ở truy vấn UNION

  • Log Laravel có SQLSTATE[HY000]: General error: 1114 The table '/tmp/#sql1_..._6' is full.
  • Truy vấn gây lỗi là dạng "bài trước, bài sau": hai nhánh SELECT ... WHERE EXISTS (...) ORDER BY id LIMIT 3 nối bằng UNION, thứ dễ sinh bảng tạm MySQL.
  • CPU của pod MySQL cao, còn số kết nối chưa chạm max_connections.

Mã 1114 là "bảng đã đầy", không nói bảng nào và vì sao: có thể đĩa hết chỗ, cũng có thể là giới hạn của engine. Việc đầu tiên là xác định đó là bảng tạm MySQL hay bảng thật.

Đọc processlist: nhiều kết nối cùng một dạng

Dùng shell tương tác để MySQL tự hỏi mật khẩu, không đặt mật khẩu trên dòng lệnh:

kubectl -n db-1 exec -it mysql-0 -- mysql -u root -p

mysql> SHOW FULL PROCESSLIST;
mysql> SELECT state, COUNT(*) FROM performance_schema.processlist GROUP BY state;

Một dấu hiệu điển hình là nhiều kết nối cùng chạy một dạng truy vấn SELECT ... WHERE EXISTS (SELECT ... FROM post_meta ...) với các giá trị id khác nhau, nhiều cái chạy rất lâu. Một số kết nối ở trạng thái converting HEAP to ondisk: đây là lúc bảng tạm MySQL đang nằm trong bộ nhớ bị chuyển xuống đĩa vì vượt ngưỡng. Nhiều kết nối đồng thời, mỗi cái dựng một bảng tạm MySQL lớn, là dấu hiệu dung lượng tạm đang tích tụ.

Đo bảng tạm MySQL trên đĩa trong pod

Đo bằng ba lệnh, từ ngoài vào trong:

kubectl -n db-1 exec -it mysql-0 -- df -h
kubectl -n db-1 exec -it mysql-0 -- bash -c 'du -hsx /var/lib/mysql/* | sort -h | tail'

Một lần đo điển hình cho thấy hai điểm. Thứ nhất, filesystem overlay của container chỉ dùng vài phần trăm, còn volume dữ liệu gắn tại /var/lib/mysql đã dùng hơn một nửa. Thứ hai, trong du, thư mục #innodb_temp chiếm hàng chục GB, trong khi các database thật cộng lại chỉ vài GB. Nghĩa là dung lượng bất thường không nằm ở dữ liệu mà ở nơi InnoDB giữ bảng tạm MySQL.

Có một mâu thuẫn không nên bỏ qua: lần đo df này cho thấy volume còn trống đáng kể, và diễn ra vào thời điểm khác với dòng log lỗi 1114, nên chưa có bằng chứng volume đã đầy lúc lỗi. Hãy đo df và du đúng lúc đang lỗi. Nếu volume còn trống mà vẫn gặp 1114, cần xét giới hạn khác như tmpdir, temptable_max_ram, temptable_max_mmap hoặc quota của storage.

Ba nơi bảng tạm MySQL có thể nằm

Có một mâu thuẫn cần nói rõ: lỗi trỏ vào /tmp/#sql..., nhưng dung lượng lại nằm ở #innodb_temp. Để hiểu, cần biết MySQL 8 có hai cơ chế cho bảng tạm MySQL nội bộ:

  • Trong bộ nhớ: mặc định dùng engine TempTable (biến internal_tmp_mem_storage_engine), bị chặn bởi temptable_max_ram.
  • Tràn xuống đĩa: khi vượt ngưỡng đó, tuỳ phiên bản và cấu hình, dữ liệu đi vào tệp ánh xạ bộ nhớ (mmap) trong tmpdir, hoặc đi vào bảng InnoDB trong session temporary tablespace, nằm ở thư mục #innodb_temp. temptable_use_mmap (deprecated từ 8.0.26) và temptable_max_mmap điều khiển phần tràn ra mmap; khi mmap tắt hoặc hết hạn mức, dữ liệu đi vào bảng InnoDB trong session temporary tablespace. Hãy đối chiếu bằng SHOW VARIABLES trên đúng bản đang chạy.

Session temporary tablespace chỉ được thu nhỏ khi phiên kết nối kết thúc, nên với connection pool giữ kết nối sống lâu, dung lượng không tự co lại. Khác với tablespace tạm toàn cục (ibtmp1), nó không bị chặn bởi innodb_temp_data_file_path.

Chưa có bằng chứng nói tên /tmp/#sql... trong thông báo lỗi là vị trí thật của dữ liệu, hay chỉ là tên bảng nội bộ. Đừng kết luận trước khi kiểm. Các lệnh cần chạy trên đúng phiên bản đang dùng:

SHOW VARIABLES WHERE Variable_name IN
  ('tmpdir','internal_tmp_mem_storage_engine','temptable_max_ram',
   'temptable_max_mmap','temptable_use_mmap','innodb_temp_tablespaces_dir');

SELECT event_name, current_number_of_bytes_used
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name LIKE 'memory/temptable/%';

SELECT id, path, size, state, purpose
FROM information_schema.innodb_session_temp_tablespaces
ORDER BY size DESC LIMIT 10;

Dòng memory/temptable/physical_disk và danh sách session tablespace cho biết dung lượng nằm ở mmap hay ở InnoDB, và phiên nào đang giữ nhiều nhất.

Nhiễu cần loại trừ trước khi kết luận

Một ca thật hiếm khi chỉ có một nguyên nhân. Một sự cố thật có thể có nhiều nguồn gây nhiễu, ví dụ:

  • Truy vấn tổng hợp chạy lâu ở một database khác trên cùng instance cũng sinh bảng tạm MySQL và chiếm CPU.
  • Một job import dữ liệu đang chạy cùng lúc, tạo nhiều ghi và nhiều bảng tạm.
  • Máy NFS dùng chung cho các website bị treo hoặc quá tải cùng thời điểm, nên web chậm có thể do NFS chứ không do MySQL.

Vì vậy "web treo" chưa thể quy hết cho một truy vấn. Cần tách từng yếu tố: tạm dừng job import, đo NFS riêng, rồi mới quay lại bảng tạm MySQL.

Ba hướng xử lý và mức bằng chứng

Hướng 1: chỉnh my.cnf qua ConfigMap

Cấu hình đưa vào pod bằng ConfigMap, rồi xoá pod để nạp lại. Hiện tượng thường thấy: CPU và RAM hạ xuống tạm thời. Điều này không lạ: khi mysqld khởi động lại, mọi truy vấn đang chạy bị cắt và kết nối được đặt lại, còn các session temporary tablespace bị xoá và tạo lại từ đầu, nên tải và dung lượng hạ bất kể bạn đổi tham số nào. Vì vậy hiện tượng hạ tải sau khi xoá pod không chứng minh tham số mới có tác dụng.

Hướng 2: node dành riêng với nodeAffinity

Dựng một node riêng và gắn nodeAffinity để pod database chạy ở đó. Kết quả: MySQL vẫn treo và #innodb_temp lại phình lên hàng chục GB. Lý do dễ hiểu: dung lượng bảng tạm MySQL nằm trên PVC chứ không phải đĩa gốc của node, nên chuyển node không thu nhỏ nó. Muốn dành riêng node cần thêm taint và toleration. Xem thêm Requests và limits, QoS và nodeAffinity.

Hướng 3: lùi về MySQL 5.7

Đổi image sang 5.7, import lại dữ liệu, chạy stress test ngắn thì ổn. Kết luận này bị nhiễu: cùng lúc đó values bị đổi, dữ liệu import mới, NFS bị tắt, và bài stress chỉ chạy vài phút.

Có một chi tiết quan trọng: sau khi đổi sang 5.7, vẫn có một lần web treo khi MySQL hoạt động bình thường, trong lúc máy NFS quá tải CPU, và xoá pod web thì web chạy lại. Vì vậy chưa thể quy mọi lần treo cho MySQL: NFS có thể là thủ phạm của lần treo sau, và việc này chưa được chốt. Hai ràng buộc nữa: 5.7 đã hết hỗ trợ (8.2 là bản innovation cũng đã hết hỗ trợ, nên chọn 8.4 LTS khi dựng lại), và datadir đã được MySQL 8 ghi thì 5.7 không mở lại được, hạ phiên bản phải đi qua dump và import. Chưa thể nói đã khắc phục.

Giả thuyết nguyên nhân và cách kiểm chứng

Giả thuyết hợp lý nhất, chưa được xác nhận: truy vấn UNION hai nhánh EXISTS trên bảng bài viết thiếu index phù hợp ở bảng phụ, nên server phải tạo bảng tạm MySQL lớn cho UNION (loại trùng cần bảng tạm) và cho phần semi-join, mỗi kết nối một bảng. SELECT * kéo cả cột nội dung dài vào bảng tạm MySQL sẽ khuếch đại thêm.

Cảnh báo: EXPLAIN ANALYZE thực thi truy vấn thật. Chạy nó trên DB đang lỗi có thể lặp lại 1114 hoặc làm quá tải CPU và đĩa. Hãy dùng EXPLAIN FORMAT=TREE trước vì lệnh này không thực thi truy vấn; chỉ dùng EXPLAIN ANALYZE trên bản sao hoặc staging, hoặc với LIMIT và max_execution_time nhỏ. Cách kiểm:

EXPLAIN FORMAT=TREE
(SELECT posts.* FROM posts
  WHERE EXISTS (SELECT 1 FROM post_meta m
                WHERE m.post_id = posts.id AND m.meta_type = 'translation')
    AND posts.id < 1000 ORDER BY posts.id DESC LIMIT 3)
UNION
(SELECT posts.* FROM posts
  WHERE EXISTS (SELECT 1 FROM post_meta m
                WHERE m.post_id = posts.id AND m.meta_type = 'translation')
    AND posts.id > 1000 ORDER BY posts.id ASC LIMIT 3);

SHOW CREATE TABLE post_meta\G

Tìm dòng "Materialize" hoặc "Temporary table" và xem bảng phụ có index trên cột nối không. Nếu hai nhánh không thể trùng nhau, UNION ALL bỏ được bước loại trùng. Cần nhấn mạnh: chưa có kết quả EXPLAIN hay SHOW CREATE TABLE nào xác nhận, nên "thiếu index" mới chỉ là giả thuyết.

Đừng xoá thư mục #innodb_temp khi mysqld đang chạy, và đừng xoá tệp binlog bằng rm: cả hai đều nguy hiểm.

Phòng ngừa

  • Cảnh báo dung lượng PVC trước khi bảng tạm MySQL làm đầy volume. Truy vấn mẫu: 100 * (1 - kubelet_volume_stats_available_bytes / kubelet_volume_stats_capacity_bytes) > 80. Cách tạo rule xem ở Grafana Alerting cho pod.
  • Đặt giới hạn cho truy vấn đọc: max_execution_time (mili giây, chỉ áp dụng cho SELECT) để một truy vấn lạc đường không chạy quá lâu.
  • Giới hạn kết nối theo user ứng dụng, và để connection pool đóng kết nối nhàn rỗi để bảng tạm MySQL được thu nhỏ.
  • Đặt requests và limits cho pod MySQL có tính đến temptable_max_ram và buffer pool, tránh bảng tạm MySQL trong RAM làm container bị OOMKilled.
  • Theo dõi bằng exporter như trong Cài mysqld_exporter trên Kubernetes: Created_tmp_disk_tables tăng nhanh là tín hiệu sớm.
  • Chọn phiên bản còn được hỗ trợ và kiểm tra truy vấn bằng EXPLAIN FORMAT=TREE (hoặc EXPLAIN ANALYZE trên bản sao) trước khi chuyển hệ thống.

Tài liệu chính thức: Internal Temporary Table Use in MySQL và InnoDB Temporary Tablespaces.

Bài học

  • Lỗi 1114 của bảng tạm MySQL phải được đo, không đoán. Chạy df và du trong pod trước khi sửa cấu hình.
  • Xoá pod xoá luôn bảng tạm MySQL. Hạ tải sau restart không chứng minh bản sửa có tác dụng.
  • Chuyển node không giải quyết vấn đề nằm trên PVC.

Bài tiếp theo

MongoDB trên Kubernetes: authSource, user root và mongodump bằng kubectl cp. Một database khác trên cùng cluster lỗi xác thực theo kiểu hoàn toàn khác.