Bạn cần nạp một trăm nghìn dòng vào một bảng — nhập từ file, chuyển dữ liệu, khởi tạo. Cách hiển nhiên là chạy một trăm nghìn câu INSERT. Nếu làm vậy rồi ngồi đợi, bạn dễ kết luận "ghi vào cơ sở dữ liệu vốn chậm". Nhưng con số đó gần như hoàn toàn không phản ánh chi phí ghi dữ liệu — nó phản ánh cách bạn nạp. Bài này đo bốn cách nạp cùng một trăm nghìn dòng, và khoảng cách giữa cách chậm nhất và nhanh nhất là hơn một trăm lần.
Bốn cách nạp cùng một dữ liệu
Tất cả bốn cách dưới đây nạp y hệt 100.000 dòng vào cùng một bảng, cho ra cùng kết quả với cùng độ bền. Chúng chỉ khác nhau ở cách đưa dữ liệu vào:
- (a) INSERT từng dòng, mỗi dòng một giao dịch: chạy 100.000 câu
INSERTriêng lẻ, mỗi câu tự động commit (autocommit) — kiểu mặc định nếu bạn không bọc gì. - (b) INSERT từng dòng, gom một giao dịch: vẫn 100.000 câu, nhưng bọc trong một cặp
BEGIN ... COMMIT. - (c) multi-row INSERT: gom nhiều dòng vào một câu —
INSERT INTO t VALUES (...),(...),...với 1000 dòng mỗi câu. - (d) COPY: nạp cả khối dữ liệu thành một luồng bằng lệnh
COPY.
Đo: từ 8.5 giây xuống 62 mili giây
Đây là thời gian đo được, cùng một bảng, cùng 100.000 dòng:
(a) INSERT lẻ, mỗi dòng 1 giao dịch: 8487 ms
(b) INSERT lẻ, gom 1 giao dịch: 3065 ms
(c) multi-row INSERT (1000 dòng/câu): 152 ms
(d) COPY: 62 ms
COPY nhanh hơn INSERT lẻ khoảng 137 lần cho đúng cùng một công việc. Đó không phải một chút cải thiện — nó là khác biệt giữa 8 giây rưỡi ngồi chờ và một cái chớp mắt gần như không kịp nhận ra. Và điều quan trọng: dữ liệu nạp vào giống hệt nhau, độ bền như nhau (COPY cũng ghi WAL, cũng bền sau khi xong). Vậy 137 lần đó đến từ đâu?
Chậm không nằm ở việc ghi
Từng bước một trong bảng trên gỡ đi một loại chi phí, và nhìn vào đó thấy rõ tiền đi đâu.
Từ (a) sang (b): bỏ commit mỗi dòng — nhanh 2.8 lần. Mỗi lần COMMIT buộc CSDL ghi WAL và (mặc định) fsync xuống đĩa để đảm bảo bền. Với autocommit, mỗi dòng là một commit riêng — 100.000 lần ghi-và-đồng-bộ. Gom cả 100.000 dòng vào một giao dịch nghĩa là chỉ commit một lần. Phần tiết kiệm 5422ms đó chính là chi phí commit lặp lại — không liên quan gì tới việc ghi dòng dữ liệu, như bài WAL và độ bền đã đo về giá của mỗi lần đồng bộ đĩa.
Từ (b) sang (c): bỏ parse và round-trip mỗi câu — nhanh thêm 20 lần. Ngay cả trong một giao dịch, 100.000 câu INSERT riêng vẫn bắt CSDL phân tích cú pháp 100.000 lần, và client gửi-nhận 100.000 lượt qua kết nối. Gom 1000 dòng vào một câu biến 100.000 lần parse+round-trip thành 100 — phần lớn overhead per-statement biến mất. Cái round-trip đó là chi phí thật: như bài kết nối đã đo, mỗi lần gửi một câu và chờ phản hồi tốn một vòng khứ hồi trên kết nối; nhân với 100.000 câu, nó thành một khối thời gian lớn ngay cả khi kết nối là socket nội bộ, và lớn hơn nhiều qua mạng thật.
Từ (c) sang (d): COPY nạp thành luồng — nhanh thêm 2.5 lần. COPY không phải là SQL với các giá trị cần phân tích; nó là một giao thức nạp khối chuyên dụng, đọc thẳng dòng dữ liệu vào bảng, bỏ qua cả bước dựng và phân tích câu INSERT. Đó là lý do nó là cách nhanh nhất để đưa nhiều dữ liệu vào PostgreSQL. Một điểm cần biết: COPY nạp cả khối như một giao dịch, nên nếu một dòng sai kiểu, cả mẻ bị hủy — dữ liệu nguồn phải sạch, hoặc bạn nạp vào một bảng tạm rồi lọc. Đổi lại sự khắt khe đó là tốc độ.
Một lần tôi đo hớ: đổ lỗi cho "ghi chậm"
Khi lần đầu đo cách (a) và thấy 8.5 giây cho 100.000 dòng, phản xạ của tôi là kết luận "ghi vào CSDL tốn cỡ này, chắc cái bảng hay cái đĩa chậm". Đó là một suy diễn sai về nguyên nhân. Cùng 100.000 dòng đó, cùng cái bảng, cùng cái đĩa, COPY làm xong trong 62ms. Nếu việc ghi thật sự tốn 8.5 giây thì COPY không thể nhanh hơn 137 lần được — nó cũng phải ghi đúng ngần ấy byte. Con số bất khả này là dấu hiệu tôi đo nhầm đại lượng: 8.5 giây không đo tốc độ ghi, nó đo chi phí cách nạp — 100.000 lần commit+fsync cộng 100.000 lần parse+round-trip. Việc ghi dữ liệu thật chỉ là một phần nhỏ trong đó.
Một lưu ý trung thực về môi trường: đĩa của container này là đĩa máy ảo, nơi fsync rẻ. Trên một đĩa vật lý thật, mỗi COMMIT phải chờ một lần đồng bộ đĩa thực (thường vài mili giây), nên cách (a) sẽ còn thảm hơn nhiều — có thể lên tới hàng phút cho 100.000 dòng. Nghĩa là khoảng cách 2.8 lần giữa (a) và (b) mà tôi đo ở đây là cận dưới; trên phần cứng thật, gom giao dịch còn quan trọng hơn.
Vì sao điều này quan trọng khi lập trình
Hệ quả đầu tiên: để nạp khối, dùng COPY. Khi nhập một file lớn, chuyển dữ liệu, hay seed một bảng, COPY (hay lệnh tương đương của thư viện bạn dùng — copy_from của psycopg, CopyManager của JDBC) nhanh hơn INSERT hàng chục tới hàng trăm lần. Đừng vòng lặp INSERT từng dòng cho việc nạp lớn. Nhiều thư viện có API COPY sẵn; nếu ORM của bạn chỉ sinh INSERT lẻ, hãy tìm cách xuống thẳng tầng driver cho các job nạp dữ liệu nặng.
Hệ quả thứ hai: nếu buộc phải INSERT, ít nhất hãy gom giao dịch và gom dòng. Không phải lúc nào cũng dùng được COPY (dữ liệu tới lẻ tẻ, cần logic mỗi dòng), nhưng bọc nhiều INSERT trong một giao dịch và gom nhiều dòng vào một câu đã lấy lại phần lớn tốc độ. Con số mang theo: cùng 100.000 dòng, INSERT lẻ mỗi dòng một giao dịch mất 8487ms còn COPY chỉ 62ms — nhanh 137 lần; khoảng cách không đến từ tốc độ ghi mà từ commit+fsync mỗi dòng (gom giao dịch: 2.8 lần) và parse+round-trip mỗi câu (gom dòng: thêm 20 lần), COPY gỡ nốt cả phần parse SQL. Khi một thao tác nạp chậm lê thê, đừng đổ cho "CSDL chậm" — hỏi bạn đang trả phí gì cho mỗi dòng.
Thử ba mươi giây
Trong psql, bật \timing, tạo CREATE TABLE t (n int);. Chạy một vòng INSERT lẻ trong một khối vô danh: DO $$ BEGIN FOR i IN 1..50000 LOOP INSERT INTO t VALUES (i); END LOOP; END $$; và xem thời gian (đây đã là một giao dịch, nên đại diện cho cách (b)). Giờ TRUNCATE t; rồi thử COPY từ một lệnh sinh dữ liệu: COPY t FROM PROGRAM 'seq 1 50000'; và so thời gian — COPY sẽ nhanh hơn hẳn. Nếu muốn thấy cách (a) chậm cỡ nào, chạy 50.000 câu INSERT riêng từ dòng lệnh với autocommit; bạn sẽ đợi lâu hơn nhiều. Ba phép đo đó, trong nửa phút, cho bạn thấy phần lớn "thời gian ghi" thật ra là thời gian commit và parse.
