Chương 14. Mẹo cho hiệu năng
14.4. Đưa dữ liệu vào cơ sở dữ liệu
Bạn có thể cần chền một lượng lớn dữ liệu khi lần đầu đưa dữ liệu vào cơ sở dữ liệu. Phần này bao
gồm một số gợi ý về cách để tiến hành qui trình này có hiệu quả nhất có thể.
14.4.1. Vô hiệu hóa thực hiện tự động (Autocommit)
Khi sử dụng nhiều lệnh chèn INSERT, hãy tắt tự động thực hiện (autocommit) và chỉ tiến hành một thực hiện (commit) lúc kết thúc. (Trong SQL thông thường, điều này có nghĩa là đưa ra BEGIN ở đầu và COMMIT ở cuối. Một số thư viện máy trạm có thể làm điều này sau lưng bạn, trong trường hợp đó bạn cần chắc chắn thư viện đó thực hiện điều đó khi bạn muốn nó được thực hiện). Nếu bạn cho phép từng sự chèn được thực hiện riêng rẽ, thì PostgreSQL đang làm nhiều công việc cho từng hàng mà được bổ sung thêm vào. Một lợi ích bổ sung của việc tiến hành tất cả sự chèn trong một giao dịch là nếu sự chèn một hàng từng hỏng thì sau đó sự chèn của tất cả các hàng được chèn vào tới điểm đó có thể phải quay lại, sao cho bạn sẽ không bị kẹt với các dữ liệu được tải lên một phần.
14.4.2. Sử dụng COPY
Hãy sử dụng COPY để tải tất cả các hàng trong một lệnh, thay vì sử dụng một loạt các lệnh INSERT. Lệnh COPY được tối ưu hóa cho việc tải số lượng lớn các hàng; là ít mềm dẻo hơn so với INSERT, nhưng chịu ít hơn đáng kể tổng chi phí cho các tải dữ liệu lớn. Vì COPY là một lệnh duy nhất, nên không cần vô hiệu hóa autocommit nếu bạn sử dụng phương pháp này để đưa dữ liệu vào một bảng.
Nếu bạn không thể sử dụng COPY, có thể giúp sử dụng PREPARE để tạo một lệnh INSERT được chuẩn bị trước, và sau đó sử dụng EXECUTE bao nhiều lần tùy theo yêu cầu. Điều này tránh được một số tổng chi phí của việc phân tích lặp đi lặp lại và lên kế hoạch cho lệnh INSERT. Các giao diện khác nhau đưa ra cơ sở này theo các cách thức khác nhau; hãy tìm kiếm “prepared statements” (“các lệnh được chuẩn bị”) trong tài liệu giao diện.
Lưu ý rằng việc tải một số lượng lớn các hàng bằng việc sử dụng COPY hầu như luôn nhanh hơn so với việc sử dụng INSERT, thậm chí nếu PREPARE được sử dụng và nhiều sự chèn được tạo thành bó trong một giao dịch duy nhất.
COPY là nhanh nhất khi được sử dụng trong giao dịch y hệt như một lệnh CREATE TABLE hoặc
TRUNCATE trước đó. Trong các trường hợp như vậy không WAL nào cần phải được viết, vì trong trường hợp có một lỗi, thì các tệp chứa các dữ liệu mới được tải sẽ bị loại bỏ bằng mọi cách. Tuy nhiên, sự xem xét chỉ áp dụng khi wal_level là minimal như tất cả các lệnh nếu không phải viết WAL.
14.4.3. Loại bỏ chỉ số
Nếu bạn đang tải một bảng được tạo mới, thì phương pháp nhanh nhất là tạo bảng đó, tải theo bó các dữ liệu bảng bằng việc sử dụng COPY, rồi tạo các chỉ số bất kỳ cần thiết cho bảng đó. Việc tạo một chỉ số trong các dữ liệu tồn tại trước đó là nhanh hơn so với việc cập nhật nó từng chút một khi từng hàng được tải.
Nếu bạn đang thêm các lượng lớn các dữ liệu vào một bảng đang tồn tại, có thể là một thành công để loại bỏ các chỉ số, tải bảng đó, và sau đó tạo lại các chỉ số. Tất nhiên, hiệu năng của cơ sở dữ liệu
đối với những người sử dụng khác có thể phải chịu trong thời gian các chỉ số không có. Bạn cũng nên nghĩ 2 lần trước khi bỏ một chỉ số duy nhất, vì việc kiểm tra lỗi kham được bằng sự ràng buộc duy nhất sẽ bị mất trong khi chỉ số không còn.
14.4.4. Loại bỏ ràng buộc khóa ngoại
Hệt như với các chỉ số, một ràng buộc khóa ngoại có thể được kiểm tra “theo bó” hiệu quả hơn so với theo từng hàng một. Vì thế có thể là hữu dụng để bỏ các ràng buộc khóa ngoại, tải dữ liệu, và tái tạo lại các ràng buộc. Một lần nữa, có một sự bù trừ giữa tốc độ tải dữ liệu và mất kiểm tra lỗi khi không có ràng buộc.
Hơn nữa, khi bạn tải dữ liệu vào một bảng với các ràng buộc khóa ngoại đang tồn tại, thì từng hàng mới đòi hỏi một khoản đầu vào trong danh sách máy chủ của các sự kiện treo trigger (vì nó là sự phát hỏa của một trigger mà kiểm tra ràng buộc khóa ngoại của hàng). Việc tải nhiều triệu hàng có thể làm cho hàng đợi các sự kiện trigger gây quá tải cho bộ nhớ có sẵn, dẫn tới việc hoán đổi không chịu nổi hoặc thậm chí thất bại hoàn toàn của lệnh. Vì thế có thể là cần thiết, không chỉ mong muốn, bỏ và áp dụng lại các khóa ngoại khi tải các lượng lớn dữ liệu. Nếu việc loại bỏ tạm thời ràng buộc là không chấp nhận được, chỉ quá trình khác có thể chia hoạt động tải thành các giao dịch nhỏ hơn.
14.4.5. Gia tăng maintenance_work_mem
Tăng tạm thời biến cấu hình maintenance_work_mem khi tải lượng lớn các dữ liệu có thể dẫn tới hiệu năng được cải thiện. Điều này sẽ giúp tăng tốc độ các lệnh CREATE INDEX và các lệnh ALTER TABLE ADD FOREIGN KEY. Nó sẽ không làm nhiều cho bản thân lệnh COPY, nên tư vấn này chỉ hữu dụng khi bạn đang sử dụng 1 hoặc 2 kỹ thuật ở trên.
14.4.6. Tăng checkpoint_segments
Tăng tạm thời biến cấu hình checkpoint_segments cũng có thể làm cho dữ liệu lớn tải nhanh hơn.
Điều này là vì việc tải một lượng dữ liệu lớn vào PostgreSQL sẽ làm cho các điểm kiểm tra xảy ra thường xuyên hơn so với tần suất kiểm tra điểm thông thường (được biến checkpoint_timeout chỉ định). Bất kỳ khi nào một điểm kiểm tra xảy ra, tất cả các trang bẩn phải được phun ra đĩa. Bằng việc tăng tạm thời checkpoint_segments trong quá trình bó dữ liệu tải lên, số lượng các điểm kiểm tra được yêu cầu có thể sẽ bị giảm đi.
14.4.7. Nhân bản dòng và lưu trữ WAL vô hiệu hóa được
Khi tải lượng lớn các dữ liệu vào một cài đặt mà sử dụng nhân bản dòng hoặc lưu trữ WAL, có thể là nhanh hơn để thực hiện một sao lưu cơ bản mới sau khi tải đó đã hoàn tất so với để xử lý một lượng lớn các dữ liệu WAL từng chút một. Để ngăn chặn việc lưu ký WAL từng chút một trong khi tải, vô hiệu hóa nhân bản dòng và lưu trữ, bằng việc thiết lập wal_level về minimal, archive_mode
về off, và max_wal_senders về 0. Nhưng hãy lưu ý rằng việc thay đổi các thiết lập đó đòi hỏi một sự khởi động lại máy chủ.
Ngoài việc tránh thời gian cho lưu trữ hoặc gửi WAL để xử lý các dữ liệu WAL, làm thế này cũng sẽ thực sự tiến hành các lệnh nhất định nhanh hơn, vì chúng được thiết kế để không viết WAL hoàn toàn nếu wal_level là minimal. (Chúng có thể đảm bảo sự mất an toàn rẻ hơn bằng việc thực hiện một
fsync ở cuối hơn là bằng việc viết WAL). Điều này áp dụng cho các lệnh sau:
• CREATE TABLE AS SELECT
• CREATE INDEX (and variants such as ALTER TABLE ADD PRIMARY KEY)
• ALTER TABLE SET TABLESPACE
• CLUSTER
• COPY FROM, khi bảng đích từng được tạo ra hoặc cắt bớt trước đó trong cùng giao dịch y hệt.
14.4.8. Chạy ANALYZE sau đó
Bất kỳ khi nào bạn tùy biến đáng kể sự phân phối dữ liệu trong một bảng, thì việc chạy ANALYZE
được khuyến cáo mạnh mẽ. Điều này bao gồm việc tải theo đống lượng dữ liệu lớn vào bảng đó.
Việc chạy ANALYZE (hoặc VACUUM ANALYZE) đảm bảo rằng trình hoạch định có các số liệu cập nhật về bảng đó. Nếu không có các số liệu thống kê hoặc chúng lỗi thời, thì trình hoạch định có thể đưa ra quyết định nghèo nàn trong việc lên kế hoạch truy vấn, dẫn tới hiệu năng nghèo nàn trong bất kỳ bảng nào với các số liệu thống kê không chính xác hoặc không tồn tại. Lưu ý rằng nếu autovacuum daemon được kích hoạt, thì nó có thể chạy ANALYZE một cách tự động; xem Phần 23.1.3 và Phần 23.1.5 để có thêm thông tin.
14.4.9. Vài lưu ý về pg_dump
Các scripts chữa đổ bể được pg_dump sinh ra tự động áp dụng một vài, nếu không nói là tất cả, các chỉ dẫn ở trên. Để tải lại một sự đổ vỡ pg_dump nhanh nhất có thể, bạn cần làm thêm vài điều bằng tay. (Lưu ý là các điểm đó áp dụng khi phục hồi một đổ vỡ, không phải khi tạo ra nó. Các điểm y hệt áp dụng hoặc việc tải một đổ vỡ văn bản với psql hoặc việc sử dụng pg_restore để tải từ một tệp lưu trữ pg_dump).
Mặc định, pg_dump sử dụng COPY, và khi nó đang sinh ra một sự đổ vỡ sơ đồ và dữ liệu, hãy cẩn thận để tải dữ liệu trước khi tạo ra các chỉ số và các khóa ngoại. Vì thế trong trường hợp này vài chỉ dẫn được điều khiển tự động. Những gì còn lại để bạn phải làm là:
• Thiết lập các giá trị phù hợp (như, lớn hơn là bình thường) cho maintenance_work_mem và
checkpoint_segments.
• Nếu việc sử dụng nhân bản dòng hoặc lưu trữ WAL, hãy cân nhắc việc vô hiệu hóa chúng trong quá trình phục hồi. Để làm điều đó, hãy thiết lập archive_mode về off, wal_level về
minimal, và max_wal_senders về 0 trước khi tải sự đổ vỡ đó. Sau đó, hãy thiết lập chúng ngược về các giá trị đúng và tiến hành sao lưu cơ bản mới lại.
• Cân nhắc liệu toàn bộ sự đổ vỡ có nên được phục hồi lại như một giao dịch duy nhất hay không. Để làm điều đó, hãy truyền lựa chọn dòng lệnh -1 hoặc --single-transaction tới psql hoặc pg_restore. Khi sử dụng chế độ này, thậm chí các lỗi nhỏ nhất sẽ quay lại phục hồi
toàn bộ, có khả năng hủy bỏ nhiều giờ xử lý. Phụ thuộc vào cách dữ liệu có liên quan tới nhau như thế nào, điều đó có thể coi là được ưu tiên để làm sạch bằng tay, hay là không.
Các lệnh COPY sẽ chạy nhanh nhất nếu bạn sử dụng một giao dịch duy nhất và có lưu trữ
WAL được tắt.
• Nếu nhiều CPU là sẵn sàng trong máy chủ cơ sở dữ liệu, hãy cân nhắc sử dụng lựa chọn
--jobs của pg_restore. Điều này cho phép tải các dữ liệu hiện hành và tạo chỉ số.
• Chạy ANALYZE sau đó.
Một sự đổ vỡ chỉ dữ liệu sẽ vẫn sử dụng COPY, nhưng nó không bỏ hoặc tái tạo lại các chỉ số, và nó thường không động chạm tới các khóa ngoại2. Vì thế khi tải một đổ vỡ chỉ dữ liệu, tùy bạn bỏ và tái tạo lại các chỉ số và các khóa ngoại nếu bạn muốn sử dụng các kỹ thuật đó. Vẫn là hữu dụng để tăng
checkpoint_segments trong khi tải các dữ liệu đó, nhưng đừng có bận tâm tới việc gia tăng
maintenance_work_mem; thay vào đó, bạn nên làm thế trong khi tái tạo bằng tay các chỉ số và các khóa ngoại sau đó. Và đừng quên ANALYZE khi bạn thực hiện xong; xem Phần 23.1.3 và Phần 23.1.5 để có thêm thông tin.