10 năm cải tiến của bộ tối ưu hóa PostgreSQL

Những cải tiến của bộ tối ưu hóa PostgreSQL trong 10 năm qua

• Với tư cách là một nhà nghiên cứu tối ưu hóa truy vấn, tác giả đã tận dụng bộ tối ưu hóa truy vấn mã nguồn mở tinh vi của PostgreSQL cho công việc nghiên cứu trong 10 năm qua
• Sau 10 năm làm việc với cơ sở dữ liệu, tác giả bắt đầu tò mò PostgreSQL đã cải thiện đến mức nào
• Có nhiều changelog và ý kiến, nhưng không tìm thấy được một phép so sánh thực nghiệm đủ mạnh, nên tác giả quyết định tự chạy Join Order Benchmark (JOB) từ PostgreSQL 8 đến 16
• Với mỗi phiên bản cơ sở dữ liệu, tác giả ghi lại độ trễ truy vấn ở bách phân vị thứ 90

Cấu hình môi trường kiểm thử

• Mỗi phiên bản PostgreSQL được build bằng GCC 13.2 trong container Docker trên Arch Linux
• Để đo chất lượng của bộ tối ưu hóa truy vấn, shared_buffers được đặt thành 8GB (đủ lớn để chứa toàn bộ cơ sở dữ liệu)
• work_mem được đặt là 8MB cho tất cả các phiên bản
• Mỗi truy vấn được chạy một lần để làm nóng cache, sau đó chạy thêm 5 lần và ghi lại độ trễ trung vị

Cải thiện hiệu năng tổng thể

• Hiệu năng ở phần đuôi của PostgreSQL đã được cải thiện đáng kể, nhưng các phiên bản 13~16 nhìn chung khá ổn định
• So với phiên bản 8 và 16, bộ tối ưu hóa của PostgreSQL đã giảm gần một nửa độ trễ đuôi trong 10 năm qua
• Có thể khảo sát toàn bộ phân bố truy vấn (xem thang log)

Định lượng mức cải thiện bằng phân tích hồi quy

• Có thể dùng phân tích hồi quy để xác nhận liệu độ dốc giảm của độ trễ có mang ý nghĩa thống kê hay không, đồng thời định lượng mỗi phiên bản PostgreSQL đã mang lại bao nhiêu cải thiện
• Khi hồi quy số phiên bản chính của PostgreSQL theo độ trễ truy vấn, mỗi phiên bản chính mới của PostgreSQL trung bình mang lại mức cải thiện hiệu năng 15% trên Join Order Benchmark
• Tuy nhiên, mô hình tuyến tính có thể được cho là một thước đo chưa thật phù hợp để đo sự thay đổi

Các điểm cần cân nhắc thêm

• Dĩ nhiên, không phải mọi cải thiện này đều đến từ bộ tối ưu hóa truy vấn. Những cải tiến trong engine thực thi, từ worker song song đến biên dịch just-in-time (JIT), cũng góp phần vào kết quả
• Sẽ rất thú vị nếu điều tra xem kế hoạch truy vấn của từng bài kiểm tra trong JOB đã thay đổi thế nào qua từng năm

Những điểm chính

• Hãy nâng cấp cơ sở dữ liệu! Chuyển từ PostgreSQL 8 lên 16 có thể cải thiện đáng kể độ trễ đuôi của workload
• Các nhà nghiên cứu cần lưu ý rằng PostgreSQL là một mục tiêu luôn dịch chuyển
  • Nghiên cứu về tối ưu hóa truy vấn học được đã so sánh với nhiều phiên bản PostgreSQL khác nhau theo thời gian
  • Nếu một kỹ thuật cũ cải thiện PostgreSQL 30% và kỹ thuật mới cải thiện PostgreSQL 25%, điều đó vẫn có thể có nghĩa là kỹ thuật mới đang được so với một PostgreSQL vốn đã mạnh hơn

Ý kiến của GN⁺

• PostgreSQL đã liên tục cải thiện hiệu năng, nhưng ở các phiên bản gần đây mức cải thiện đang giảm dần. Điều này có thể là do phần lớn tối ưu hóa quan trọng đã được thực hiện. Những cải thiện tiếp theo có vẻ sẽ tập trung vào các khu vực chi tiết hơn

• Không chỉ riêng bộ tối ưu hóa truy vấn mà các cải tiến của engine thực thi cũng đóng góp vào việc tăng hiệu năng. Nhiều mặt tối ưu hóa khác nhau như xử lý song song hay biên dịch JIT đều đang được triển khai

• Thử nghiệm này chỉ giới hạn trong Join Order Benchmark, nên hiệu quả cải thiện hiệu năng trong công việc thực tế có thể khác nhau tùy workload. Sẽ tốt hơn nếu tự thực hiện benchmark phù hợp với đặc tính công việc của mình

• Các nhà nghiên cứu cần tính đến sự thay đổi phiên bản của PostgreSQL. Ngay cả với cùng một thuật toán, mức cải thiện hiệu năng tương đối cũng có thể thay đổi tùy theo phiên bản PostgreSQL được dùng để so sánh

• Nếu đang dùng một phiên bản PostgreSQL cũ, rất đáng để chủ động cân nhắc nâng cấp. So với phiên bản của 10 năm trước, các phiên bản mới đã đạt được cải thiện hiệu năng rõ rệt. Tất nhiên cũng cần tính đến các vấn đề như tương thích khi nâng cấp

[GN⁺]

Ý kiến Hacker News

Tóm tắt:

• Để giải quyết tốt bài toán tối ưu hóa, dữ liệu về chi phí là rất quan trọng. PostgreSQL vẫn còn nhiều dư địa để cải thiện. Đặc biệt là còn thiếu dữ liệu về độ trễ syscall, thống kê foreign key, v.v.
• Với các truy vấn quy mô lớn, cần đưa vào các kỹ thuật như deferred planning, cho phép điều chỉnh kế hoạch trong lúc thực thi.
• Machine learning phù hợp để cải thiện mô hình dự đoán chi phí. Dùng machine learning để trực tiếp lập kế hoạch truy vấn thì không phù hợp.
• Việc đặt shared buffer quá lớn để đưa toàn bộ dữ liệu lên bộ nhớ rồi benchmark khiến khó đánh giá chính xác hiệu năng thực tế của optimizer.
• JIT compiler hiện vẫn thường chỉ gây suy giảm hiệu năng trong nhiều trường hợp.
• Vì cách đánh số phiên bản của PostgreSQL đã thay đổi từ version 10, nên cũng sẽ thú vị nếu xem các bản 8.x, 9.x là major version để phân tích xu hướng hiệu năng.
• Chỉ với các biểu đồ được đưa ra thì khó xác nhận rõ ràng xu hướng cải thiện hiệu năng. Tail latency có vẻ đã được cải thiện, nhưng các phần còn lại có thể khác nhau tùy trường hợp.
• Xây dựng một optimizer xuất sắc là một bài toán đầy thách thức.
• Có người thắc mắc việc tối ưu hóa truy vấn đang ở cấp độ SQL hay cấp độ thuật toán.