Posted inMySQL

Xử lý lỗi “The table is full” trong MySQL: Khi ổ cứng vẫn trống hàng trăm GB

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Tưởng tượng một ngày hệ thống đang chạy ổn định, bỗng nhiên log báo lỗi Error 1114 (HY000): The table 'users' is full. Bạn lập tức gõ df -h để kiểm tra và thấy ổ cứng vẫn còn dư hơn 200GB. Tại sao MySQL lại báo “đầy” trong khi không gian lưu trữ thực tế vẫn còn rất nhiều?

Đây là tình huống trớ trêu mà nhiều quản trị viên hệ thống (DBA) thường gặp phải. Lỗi này không đơn thuần là hết dung lượng vật lý. Nó liên quan trực tiếp đến cách Storage Engine InnoDB phân bổ không gian lưu trữ qua khái niệm Tablespace.

Ba thủ phạm chính khiến bảng bị “đầy”

Trước khi can thiệp vào cấu hình, bạn cần xác định chính xác nguyên nhân thông qua 3 kịch bản sau:

  1. Hết dung lượng đĩa thực tế: Partition chứa data của MySQL (thường là /var/lib/mysql) đã cạn kiệt.
  2. Giới hạn của System Tablespace: File ibdata1 chạm ngưỡng dung lượng tối đa (max size) được quy định trong cấu hình.
  3. Tràn bảng tạm (Temporary Table): Các câu truy vấn nặng (như JOIN 5-6 bảng lớn) tạo ra file tạm vượt quá giới hạn tmp_table_size.

Chúng ta sẽ tập trung xử lý nguyên nhân thứ 2 – vấn đề với innodb_data_file_path. Đây là lỗi gây lúng túng nhất vì nó đánh lừa cảm giác của người quản trị về dung lượng trống của server.

Lựa chọn chiến lược quản lý Tablespace

InnoDB cung cấp hai cách để lưu trữ dữ liệu. Việc chọn sai chiến lược ngay từ đầu có thể dẫn đến gánh nặng bảo trì sau này.

1. System Tablespace (File ibdata1 tập trung)

Mặc định, MySQL có thể gom toàn bộ dữ liệu từ tất cả database vào một file duy nhất mang tên ibdata1.

  • Rủi ro: File này chỉ có chiều tăng, không có chiều giảm. Ngay cả khi bạn xóa 100GB dữ liệu, file ibdata1 vẫn giữ nguyên kích thước cũ trên ổ cứng, tạo ra những khoảng trống vô nghĩa (fragmentation).

2. File-Per-Table (innodb_file_per_table)

Mỗi bảng sẽ sở hữu một file .ibd riêng. Đây là tiêu chuẩn vàng cho các hệ thống hiện đại.

  • Lợi ích: Khi bạn thực hiện DROP hoặc TRUNCATE một bảng, dung lượng sẽ được trả lại cho hệ điều hành ngay lập tức. Việc quản lý và di chuyển dữ liệu giữa các server cũng linh hoạt hơn nhiều.

Khắc phục lỗi giới hạn innodb_data_file_path

Nếu bạn đang sử dụng System Tablespace, hãy kiểm tra file cấu hình my.cnf (Linux) hoặc my.ini (Windows). Lỗi “The table is full” thường xuất hiện do một dòng cấu hình giới hạn cứng.

Bước 1: Kiểm tra thực trạng

Chạy lệnh SQL sau để xem file dữ liệu đang được định nghĩa như thế nào:

SHOW VARIABLES LIKE 'innodb_data_file_path';

Nếu kết quả trả về là ibdata1:10M:autoextend:max:512M, nghĩa là file dữ liệu của bạn không bao giờ được vượt quá 512MB. Chỉ cần database chạm mốc này, MySQL sẽ từ chối ghi thêm dữ liệu dù ổ cứng còn trống cả Terabyte.

Bước 2: Nới lỏng rào cản dung lượng

Mở file my.cnf và tìm dòng innodb_data_file_path. Bạn có hai cách xử lý:

Cách 1: Nâng giới hạn max lên mức an toàn (ví dụ 20GB)

innodb_data_file_path = ibdata1:10M:autoextend:max:20G

Cách 2: Cho phép tăng trưởng không giới hạn (Khuyên dùng)

innodb_data_file_path = ibdata1:10M:autoextend

Sau khi sửa, bạn cần restart MySQL. Lưu ý quan trọng: Giá trị dung lượng khởi tạo (ở đây là 10M) không được nhỏ hơn kích thước hiện tại của file ibdata1 trên đĩa, nếu không service sẽ không thể khởi động.

Giải pháp lâu dài: Chuyển sang innodb_file_per_table

Trong một dự án thương mại điện tử mình từng xử lý, database đạt ngưỡng 100GB data khiến việc backup file ibdata1 cực kỳ chậm chạp. Giải pháp triệt để là kích hoạt innodb_file_per_table.

Thêm cấu hình sau vào mục [mysqld]:

[mysqld]
innodb_file_per_table = 1

Tuy nhiên, thiết lập này chỉ áp dụng cho các bảng tạo mới. Để “giải cứu” các bảng cũ đang kẹt trong ibdata1, bạn cần chạy lệnh rebuild:

ALTER TABLE users ENGINE=InnoDB;

Thao tác này sẽ nhấc bảng users ra khỏi system tablespace và tạo cho nó một file .ibd riêng biệt.

Xử lý khi bảng tạm (Temporary Table) bị đầy

Nếu cấu hình tablespace vẫn ổn mà lỗi vẫn xảy ra khi chạy báo cáo lớn, thủ phạm chính là bảng tạm. MySQL sẽ ưu tiên dùng RAM cho bảng tạm, nếu thiếu sẽ ghi xuống đĩa.

Hãy kiểm tra hai thông số sau:

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

Nếu hệ thống của bạn có RAM dư dả, hãy nâng các giá trị này lên 256MB hoặc 512MB để tăng tốc độ xử lý và tránh lỗi tràn bảng:

SET GLOBAL tmp_table_size = 256 * 1024 * 1024;
SET GLOBAL max_heap_table_size = 256 * 1024 * 1024;

Mẹo nhỏ khi dọn dẹp dữ liệu

Nhiều bạn lầm tưởng lệnh DELETE sẽ làm giảm dung lượng file trên ổ cứng. Thực tế, MySQL chỉ đánh dấu các ô nhớ đó là trống (free space) để dùng lại sau này. Để thực sự thu hồi dung lượng (shrink file), bạn phải dùng:

OPTIMIZE TABLE ten_table;

Cảnh báo: Lệnh này sẽ khóa bảng (Table Lock). Với bảng dữ liệu lớn khoảng 50GB, quá trình này có thể mất 15-30 phút tùy tốc độ ổ cứng I/O. Tốt nhất nên thực hiện vào khung giờ 2-3 giờ sáng hoặc sử dụng công cụ pt-online-schema-change để tránh gây downtime cho người dùng.

Kết luận

Lỗi “The table is full” thường là hệ quả của việc cấu hình mặc định quá thấp so với quy mô dữ liệu thực tế. Để hệ thống vận hành trơn tru, bạn nên ưu tiên bật innodb_file_per_table và gỡ bỏ giới hạn max size trong cấu hình ibdata1. Việc hiểu rõ cơ chế lưu trữ của InnoDB sẽ giúp bạn xử lý sự cố bình tĩnh và chính xác hơn.

Share:
Tags: