Posted inMySQL

ALTER TABLE bảng MySQL hàng chục GB không gây Downtime: Bí kíp với pt-online-schema-change

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

Nỗi ám ảnh mang tên ALTER TABLE trên Production

Quản lý hệ thống MySQL với những bảng lên tới hàng trăm triệu bản ghi chưa bao giờ là dễ dàng. Chỉ một yêu cầu “thêm cột mới” hay “đổi kiểu dữ liệu” cũng đủ khiến các DBA toát mồ hôi hột.

Mình nhớ hồi mới vào nghề, có lần phải xử lý bảng logs nặng 50GB trên MySQL 8.0. Lúc đó đang giờ cao điểm với khoảng 500 người dùng truy cập đồng thời. Mình tự tin gõ lệnh: ALTER TABLE logs ADD COLUMN metadata JSON;.

Chỉ 2 giây sau, monitor báo đỏ rực. Toàn bộ query rơi vào trạng thái “Waiting for table metadata lock”, website quay vòng vòng còn sếp thì đứng ngay sau lưng. Phải mất 30 phút nghẹt thở để lệnh chạy xong. Đó thực sự là một bài học đắt giá về sự ngây thơ khi làm việc với database lớn.

Tại sao MySQL lại gây downtime khi đổi cấu trúc bảng?

Vấn đề nằm ở chỗ Online DDL của MySQL (từ bản 5.6 trở đi) vẫn còn nhiều kẽ hở. Dù mang tiếng là “online”, nhưng thực tế nó vẫn có thể khiến hệ thống “chết lâm sàng”.

Về kỹ thuật, khi bạn thay đổi cấu trúc, MySQL thường thực hiện theo quy trình: tạo bảng tạm, copy dữ liệu và hoán đổi. Trong quá trình này, MySQL thường đặt một cái khóa (Exclusive Lock) lên bảng chính. Mọi thao tác ghi dữ liệu từ ứng dụng sẽ bị chặn lại và xếp hàng chờ. Với bảng 50GB như của mình, việc copy mất hàng chục phút đồng nghĩa với việc ứng dụng bị “đóng băng” hoàn toàn trong suốt thời gian đó.

Các cách giải quyết phổ biến và rủi ro đi kèm

Trước khi tìm thấy “chân ái”, team mình đã từng thử qua nhiều phương án thủ công:

1. Dùng MySQL Online DDL (ALGORITHM=INPLACE)

Lý thuyết thì rất hay, nhưng thực tế nó vẫn cần Metadata lock ở đầu và cuối quá trình. Nếu server đang có các query chạy lâu (long-running query), việc lấy khóa này sẽ gây ra tình trạng tắc nghẽn (queue) cực kỳ nghiêm trọng.

2. Bảo trì vào lúc 2 giờ sáng

Đây là cách “lấy sức người bù công nghệ”. Cả đội phải thức đêm, ngắt kết nối ứng dụng để chạy lệnh. Cách này vừa tốn sức, vừa không khả thi với các hệ thống toàn cầu cần chạy 24/7.

3. Thủ công Shadow Table

Bạn tự tạo bảng mới, copy dữ liệu theo từng block 10.000 dòng rồi dùng RENAME TABLE. Cách này an toàn hơn nhưng lại rất khó để đồng bộ những dữ liệu mới phát sinh trong lúc đang copy.

pt-online-schema-change: Vũ khí đắc lực cho DBA

Sau những lần “đau thương”, mình đã tìm thấy pt-online-schema-change trong bộ Percona Toolkit. Đây được coi là lựa chọn hàng đầu để xử lý các bảng lớn mà không lo bị lock.

Cách thức vận hành thông minh

Công cụ này không can thiệp trực tiếp vào bảng cũ mà sử dụng cơ chế thông minh hơn:

  1. Tạo một bảng “shadow” trống với cấu trúc mới.
  2. Thiết lập các Triggers (INSERT, UPDATE, DELETE) để tự động đồng bộ mọi thay đổi từ bảng gốc sang bảng shadow ngay lập tức.
  3. Chia dữ liệu cũ thành từng khối nhỏ (ví dụ 1.000 dòng mỗi lần) để copy dần dần. Điều này giúp tránh nghẽn I/O và không làm CPU server tăng vọt.
  4. Khi copy xong, nó dùng RENAME TABLE để tráo đổi hai bảng trong vài mili giây.
  5. Cuối cùng là dọn dẹp bảng cũ và các triggers.

Cài đặt và sử dụng thực tế

Trên môi trường Ubuntu, bạn cài đặt rất đơn giản:

sudo apt-get update
sudo apt-get install percona-toolkit

Giả sử bạn cần thêm cột status vào bảng orders. Đừng chạy lệnh trực tiếp, hãy dùng bộ lệnh sau:

# Bước 1: Chạy thử (Dry Run) để check lỗi
pt-online-schema-change --dry-run \
  --alter "ADD COLUMN status tinyint(1) DEFAULT 1" \
  h=localhost,D=my_database,t=orders,u=admin,p=password

# Bước 2: Thực thi thật (Execute)
pt-online-schema-change --execute \
  --max-load Threads_running=50 \
  --alter "ADD COLUMN status tinyint(1) DEFAULT 1" \
  h=localhost,D=my_database,t=orders,u=admin,p=password

Lưu ý tham số quan trọng:

  • --dry-run: Giúp bạn kiểm tra cú pháp và các ràng buộc mà chưa tác động vào dữ liệu.
  • --max-load: Tự động tạm dừng nếu số lượng thread đang chạy vượt quá 50, bảo vệ server khỏi bị overload.
  • --alter: Nội dung thay đổi cấu trúc bạn muốn thực hiện.

Kinh nghiệm thực chiến cần ghi nhớ

Dựa trên trải nghiệm thực tế xử lý các bảng hàng chục GB, bạn cần lưu ý 3 điểm mấu chốt:

1. Cẩn thận với Trigger: Vì công cụ dựa vào trigger để đồng bộ, nếu bảng của bạn đã có trigger sẵn, hãy kiểm tra kỹ tài liệu của Percona. Các phiên bản cũ có thể xung đột và gây mất dữ liệu.

2. Bài toán dung lượng: Bảng shadow sẽ chiếm dung lượng tương đương bảng gốc. Nếu bảng nặng 50GB, hãy chắc chắn server còn trống ít nhất 60-70GB để quá trình copy không làm đầy ổ cứng.

3. Xử lý Khóa ngoại (Foreign Keys): Đây là phần phức tạp nhất. Mình khuyên bạn nên dùng tham số --alter-foreign-keys-method=auto và luôn luôn test kỹ trên môi trường Staging trước khi đụng vào Production.

Nhờ có pt-online-schema-change, việc bảo trì database của mình giờ đây nhẹ nhàng hơn hẳn. Mình có thể thêm index hay đổi kiểu dữ liệu ngay giữa ban ngày mà người dùng không hề nhận ra bất kỳ sự chậm trễ nào.

Share:
Tags: