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

• So sánh độ trễ truy vấn ở bách phân vị thứ 90 bằng Join Order Benchmark từ PostgreSQL 8 đến 16 cho thấy rõ ràng bằng thực nghiệm sự cải thiện dài hạn của hiệu năng đuôi
• So với PostgreSQL 8, PostgreSQL 16 đã giảm gần một nửa độ trễ đuôi; giai đoạn 13~16 nhìn chung duy trì ở mức ổn định
• Theo phân tích hồi quy, mỗi khi tăng một phiên bản major thì hiệu năng trung bình cải thiện 15%, nhưng mô hình tuyến tính có thể không mô tả tốt diễn biến thay đổi thực tế
• Thí nghiệm cố định điều kiện với GCC 13.2, Docker Arch Linux, shared_buffers 8GB và work_mem 8MB để tập trung vào chất lượng của bộ tối ưu hóa truy vấn
• Khi diễn giải mức cải thiện, cần xét không chỉ bộ tối ưu hóa mà còn cả các thay đổi ở bộ máy thực thi như parallel worker và biên dịch JIT

---

Cấu hình benchmark PostgreSQL 8~16

• Đối tượng phân tích là các phiên bản major từ 8 đến 16 của PostgreSQL, một bộ tối ưu hóa truy vấn mã nguồn mở
• Benchmark sử dụng Join Order Benchmark, một tập truy vấn có nhiều phép join phức tạp
  • Benchmark này được giới thiệu trong bài báo “How Good are Query Optimizers, Really?”
• Mỗi phiên bản PostgreSQL được build bằng GCC 13.2 trong container Docker Arch Linux
• Môi trường đo được thiết lập để quan sát chất lượng của bộ tối ưu hóa truy vấn thay vì hiệu năng chỉ mục hay I/O
  • shared_buffers được đặt ở mức 8GB, đủ lớn để chứa toàn bộ cơ sở dữ liệu
  • work_mem được cố định ở mức 8MB cho mọi phiên bản
• Mỗi truy vấn được chạy một lần để làm nóng cache, sau đó ghi lại độ trễ trung vị của 5 lần chạy bổ sung
• Mỗi phiên bản major sử dụng phiên bản minor mới nhất
  • Ví dụ, PostgreSQL 8 tương ứng với 8.4.22
  • Các phiên bản minor đó thường được phát hành sau phiên bản major mới hơn, nhưng nhìn chung chỉ gồm sửa lỗi, không thêm tính năng mới hay cải thiện hiệu năng

Kết quả đo và cách diễn giải

• Hiệu năng đuôi của PostgreSQL nhìn chung đã cải thiện mạnh
  • So sánh PostgreSQL 8 và 16 cho thấy độ trễ đuôi đã giảm gần một nửa
  • Từ PostgreSQL 13 đến 16, mức này nhìn chung vẫn ổn định
• Phân tích hồi quy được dùng để xác nhận liệu xu hướng giảm giữa số phiên bản major và độ trễ truy vấn có ý nghĩa thống kê hay không, đồng thời định lượng mức cải thiện theo từng phiên bản
  • Theo hồi quy tuyến tính, mỗi phiên bản major mới mang lại mức cải thiện hiệu năng trung bình 15% trên Join Order Benchmark
  • Tuy vậy, mô hình tuyến tính có thể không phù hợp để đo chính xác diễn biến thay đổi thực tế
• Khó có thể giải thích toàn bộ mức cải thiện chỉ bằng bộ tối ưu hóa truy vấn
  • Những cải tiến ở bộ máy thực thi như parallel worker và biên dịch JIT cũng ảnh hưởng đến hiệu năng
  • Việc các execution plan của từng truy vấn trong JOB đã thay đổi ra sao theo từng năm vẫn là chủ đề cần phân tích riêng
• Nếu nâng cấp từ PostgreSQL 8 lên 16, độ trễ đuôi của workload có thể giảm đáng kể
• Trong các so sánh nghiên cứu, điều quan trọng là chính PostgreSQL cũng liên tục trở thành một mốc tham chiếu mạnh hơn
  • Neo và Bao được so sánh với PostgreSQL 11, trong khi các nghiên cứu mới hơn so với PostgreSQL 14, 15 và 16
  • Ngay cả khi một phương pháp cũ cải thiện 30% so với PostgreSQL và một phương pháp mới cải thiện 25%, phương pháp mới có thể đã được so với một phiên bản PostgreSQL mạnh hơn
• Có thể xem số liệu gốc tại raw data

[GN⁺]

Ý kiến trên Hacker News

• Tôi đã dùng Postgres 15 năm và dành phần lớn sự nghiệp để mô hình hóa, giải các bài toán tối ưu hóa toán học; với chủ đề này, tôi nghĩ có ba điểm cốt lõi
  Mọi bài toán tối ưu hóa đều cần dữ liệu chi phí, và dữ liệu càng nhiều, càng tốt thì kết quả càng khá hơn. Postgres đã có các cải tiến như thống kê chéo giữa các cột, nhưng vẫn còn những khoảng trống lớn như độ trễ lời gọi hệ thống. Độ trễ khi đọc trang từ đĩa khác nhau rất nhiều giữa các hệ thống, nhưng Postgres không đo trực tiếp mà dựa vào giá trị cấu hình. Thống kê khóa ngoại cũng còn thiếu, nên các phép join đi theo khóa ngoại lẽ ra không được tạo ra kế hoạch tệ, nhưng đôi khi điều đó vẫn xảy ra
  Đặc biệt với các truy vấn lớn và đắt đỏ, cần có lập kế hoạch trì hoãn hoặc lập kế hoạch cho các kịch bản thay thế. Hiện tại kế hoạch được chốt trước khi thực thi, nhưng số hàng hoặc ước lượng cardinality thu được ở giai đoạn đầu thực thi có thể cải thiện đáng kể kế hoạch ở giai đoạn sau
  Học máy cũng là một lĩnh vực còn có thể cải thiện, nhưng các thử nghiệm tôi từng thấy đến nay chưa ấn tượng. Thay vì dùng học máy cho chính việc lập kế hoạch, nên dùng nó cho phát hiện và ước lượng chi phí. Cần xây dựng mô hình chi phí tốt hơn và để engine tối ưu hóa tận dụng dữ liệu đó

  • Muốn nghe thêm ý kiến. Ví dụ, việc độ trễ lời gọi hệ thống nằm trong danh sách ưu tiên là điều tôi thấy bất ngờ. Theo tôi, quan điểm chung trong cộng đồng cơ sở dữ liệu là mô hình chi phí nhìn chung ổn, còn ước lượng cardinality mới thật sự tệ
    Về lập kế hoạch trì hoãn/thay thế, tôi tò mò liệu thực thi truy vấn thích ứng có phải cách hợp lý không. Có thể để thông tin ở đầu quá trình thực thi truy vấn ảnh hưởng đến kế hoạch về sau, nhưng tôi lo rằng nếu chọn sai vài phép join đầu tiên — điều này khá thường gặp — thì sẽ khó phục hồi nếu không có những thứ như Yannakakis/SIPs
    Về “học máy cho tối ưu hóa truy vấn”, chắc chắn tôi có thiên kiến. Tuy vậy, mọi cách tiếp cận “học máy cho lập kế hoạch” mà tôi thấy rốt cuộc bên trong đều dùng học máy cho phát hiện/ước lượng chi phí. Các cách này cố cân bằng giữa dữ liệu thu thập được, tức thăm dò, và chất lượng kế hoạch tạo ra, tức khai thác. Điều thú vị là nếu dùng học máy theo cách tách hoàn toàn khỏi việc lập kế hoạch, ước lượng có thể chính xác hơn nhưng kế hoạch truy vấn thực tế lại tệ hơn: https://people.csail.mit.edu/tatbul/publications/flowloss_vl...
    Tôi có lợi ích liên quan trong lĩnh vực này, nên hãy cân nhắc điều đó khi đọc ý kiến của tôi
  • Kế hoạch thay thế nghe thật sự rất hay. Một kế hoạch truy vấn tôi thấy gần đây dự kiến một truy vấn con nào đó trả về khoảng 1.000 hàng nên gắn nested loop với index scan, nhưng thực tế là khoảng 1 tỷ hàng
    Tôi vẫn chưa biết vì sao ước lượng lại sai đến vậy, nhưng nếu có thể chuyển từ nested loop sang hash join khi số hàng vượt một ngưỡng nào đó, thì có vẻ sẽ giúp rất nhiều trong việc tránh các kế hoạch thảm họa
  • Tôi tò mò chính xác ý nói thiếu thống kê khóa ngoại là gì. Postgres, giống nhiều cơ sở dữ liệu quan hệ, không tự động tạo chỉ mục trên khóa ngoại, điều đó chắc bạn đã biết
    Có phải đang nói đến vấn đề thứ tự join không?
  • Tôi tò mò liệu bạn có cho rằng MSSQL làm tốt hơn ở phần này không

• Bộ tối ưu hóa truy vấn của Postgres cố giảm số trang đọc từ đĩa và số trang ghi ra đĩa dưới dạng kết quả trung gian. Vì vậy, đặt shared buffers đủ lớn để chứa toàn bộ dữ liệu rồi benchmark bộ tối ưu hóa truy vấn có vẻ là sai
  Khi đó, thứ được đo là tốc độ của bộ tối ưu hóa truy vấn và bộ xử lý join, chứ không phải chất lượng của kế hoạch truy vấn được tạo ra. Trên thực tế, cũng không ngạc nhiên nếu tất cả kế hoạch được tạo ở từng phiên bản đều giống nhau, và họ chỉ đo tốc độ thực thi

  • Không phải vậy. Mục tiêu tối ưu hóa là chi phí, bao gồm không chỉ các trang đọc từ đĩa mà cả những thứ như mức dùng CPU
    Chi phí là một đơn vị tùy ý được thiết kế để tương quan với thời gian tiêu tốn, chứ không phải số lần đọc đĩa, nên việc so sánh kế hoạch khi mọi thứ đã nằm trong RAM cũng hoàn toàn hợp lý. Theo quy ước, việc đọc một trang từ đĩa được scale thành 1.0, nhưng điều đó khác với nói “bộ tối ưu hóa giảm thiểu số lần đọc trang đĩa”. Họ cũng có thể đã đặt 1ms trên một máy tùy ý là 1.0
  • Rõ ràng có khả năng các kế hoạch giống nhau và họ đã đo các cải tiến của engine thực thi. Join Order Benchmark được thiết kế để kiểm tra chất lượng bộ tối ưu hóa
    Bộ tối ưu hóa của PG không chỉ cố giảm số trang đọc từ đĩa mà còn giảm số tuple CPU phải kiểm tra, số lần đánh giá biểu thức điều kiện, v.v.; tất cả các con số này được gộp thành “chi phí”, tức hàm mà bộ tối ưu hóa tìm cách tối thiểu hóa
    Đo hiệu năng với cold cache và warm cache có thể cho kết quả khác nhau, và thí nghiệm này chắc chắn là kịch bản warm cache. Nhưng cold cache cũng có vấn đề như đã nêu. Với kích thước dữ liệu của Join Order Benchmark, hiệu ứng tiết kiệm vài lần I/O nhờ cải tiến B-tree của PG có thể lấn át các cải tiến dựa trên CPU
    Nhân tiện, kế hoạch của truy vấn có độ trễ P90 đã đổi từ kế hoạch dùng loop join và merge join trong PG 8.4 sang kế hoạch dùng hash join trong PG 16, và truy vấn này không còn là truy vấn P90 nữa. Điều này ít nhất có thể xem là một phần bằng chứng cho cải tiến của bộ tối ưu hóa

• Bài viết có nhắc đến trình biên dịch JIT của PostgreSQL, nhưng đến nay tôi chỉ thấy nó làm giảm hiệu năng truy vấn. Tôi đã đưa việc vô hiệu hóa nó vào checklist cài đặt

  • Một khách hàng đã gặp vấn đề hiệu năng tệ nhất sau khi chuyển sang Postgres. Kỳ lạ là nó chỉ xảy ra trong thiết lập Docker và máy chủ thử nghiệm, còn trên máy của lập trình viên thì không. Lập trình viên đó đang chạy Postgres bằng Homebrew
    Hóa ra Homebrew cài Postgres không có hỗ trợ JIT, và trên máy lập trình viên có một truy vấn chạy xong trong 200ms, nhưng trong môi trường bật JIT thì mất 4–5 giây. Tôi không dùng Postgres quá sâu nên mất một thời gian mới tìm ra nguyên nhân, và từ đó về sau tôi luôn tắt JIT rồi không ngoái lại nữa
  • Trình biên dịch JIT rất tuyệt cho truy vấn phân tích
    Trong PostgreSQL, bạn cũng có thể cấu hình ngưỡng bật JIT, nên có thể nâng tiêu chuẩn để JIT được kích hoạt cao hơn
  • JIT của pg cho thấy khá rõ rằng LLVM không thật sự tốt cho JIT, và việc Postgres không có cache truy vấn chia sẻ bền vững còn làm tình hình tệ hơn
    Nếu có thể biên dịch bất đồng bộ cho các truy vấn trong tương lai thì có lẽ sẽ ít gây hại hơn. Thật ra JIT nói chung, đặc biệt là backend tối ưu hóa, gần với cách đó hơn
  • Postgres không thể JIT-compile một truy vấn một lần rồi chạy truy vấn đã biên dịch đó nhiều lần sao?

• Thú vị, nhưng hệ thống đánh số phiên bản của Postgres đã thay đổi ở v10. 9.6, 9.5, 9.4, 9.3, 9.2, 9.1, 9.0, 8.4, 8.3, 8.2, 8.1, 8.0 thực ra đều là các phiên bản major riêng biệt
  Có lẽ cũng sẽ thú vị nếu xem hiệu năng đã thay đổi thế nào qua các phiên bản đó

  • Dù vậy, tôi vẫn biết ơn việc từ v9.0 đến 9.6 họ đã duy trì khả năng tương thích filesystem giữa các phiên bản major, để có thể nâng cấp tại chỗ nhanh hơn bằng cách chỉ thay binary
    Điều đó có thể đã khiến họ bị ràng buộc, nhưng các bản cập nhật hằng năm đòi hỏi nhiều downtime hơn hoặc phải reindex thì không vui chút nào, và đó có thể là lý do nhiều site trì hoãn nâng cấp cho đến khi phiên bản cũ hết hỗ trợ. Điều này đặc biệt đúng với người dùng AWS RDS
    Nâng cấp bằng logical replication sau v10 có lợi thế về tính sẵn sàng, nhưng nếu schema không tương đối đơn giản thì đó là một dự án lớn với chi phí không thể tránh khỏi và rủi ro đáng kể
  • Hoàn toàn đồng ý. Tôi đã diễn giải số phiên bản theo kiểu semver và chọn phiên bản mới nhất của mỗi major version, nhưng cách đó khác với cách PostgreSQL truyền thống xử lý số phiên bản major
    Ví dụ PG 8.2 và 8.1 là các phiên bản major khác nhau, nhưng tôi đã diễn giải chúng như minor version. Lý do chính tôi làm vậy là để giảm số phiên bản cần kiểm thử, và tôi đồng ý rằng một phân tích đầy đủ hơn nên kiểm thử từng major version thực tế

• Bài viết nói “tất nhiên không phải toàn bộ cải thiện này đều nhờ trình tối ưu hóa truy vấn”, nên có lẽ sẽ thú vị nếu xem liệu có thay đổi execution plan theo từng phiên bản hay không

• Tôi nhớ đến định luật Proebsting: https://proebsting.cs.arizona.edu/law.html

  • Điểm hay của tối ưu hóa compiler là có thể cải thiện hiệu năng mà không cần đụng chạm vật lý đến CPU hiện có. Mỗi năm lại có người vắt thêm hiệu năng từ cỗ máy do ai đó thiết kế, và khi tích lũy lại thì con số lớn lên
    Hãy tưởng tượng việc tối ưu hiệu năng Python thêm 1% sẽ có tác động môi trường thế nào. Nó sẽ giảm được bao nhiêu CO2 trong khí quyển? Có lẽ còn lớn hơn tổng dấu chân môi trường của bản thân, gia đình và toàn bộ bạn bè bạn. Có khi còn ngang với cả thành phố nơi bạn sống. Tất cả chỉ vì ai đó đã dành thời gian triển khai vài mẹo thao tác bit
  • Tôi không hiểu vì sao. Định luật đó dường như nói rằng cải thiện hiệu năng phần mềm không có nhiều ý nghĩa, còn bài này thì nói các cải thiện của Postgres là đáng kể
    Có phải vì xem 15% là con số thấp không? Trong ngữ cảnh này thì hoàn toàn không thấp. Nó nhỏ hơn 60% trong định luật được link, và nếu chia kiểu 15/10 thì còn nhỏ hơn nữa, nhưng không nên so hiệu năng Postgres với cải tiến phần cứng. Để đạt mức tương đương với cải thiện hiệu năng 1% ở thứ đang được đo ở đây sẽ cần cải tiến phần cứng khổng lồ
    Tôi không nghĩ định luật đó buồn cười như nhiều người nói, nhưng nó nói về thời gian biên dịch của ngôn ngữ lập trình. Tôi sẽ không so một thứ tương đối kém quan trọng như vậy với việc lưu trữ và tiêu thụ dữ liệu, vốn có thể gọi là một trong những thứ quan trọng nhất trong khoa học máy tính
  • Trong trường hợp này, nhà nghiên cứu đã build tất cả phiên bản PostgreSQL bằng cùng GCC 13.2 và kiểm thử trên cùng hệ điều hành
  • Trông như một “định luật” khá yếu. Nó được đặt ra như trò đùa à? Cơ sở là những con số không rõ từ đâu, kiểu “hãy giả sử”, và kết luận cũng lệch khá xa. Nó dường như ngụ ý rằng những tối ưu giúp cải thiện hiệu năng của rất nhiều phần mềm trên thế giới thêm 4% mỗi năm là lãng phí thời gian
    Đối tượng so sánh duy nhất được đưa ra là định luật Murphy. Tôi tò mò chi phí phát triển phần cứng nhanh hơn khác bao nhiêu so với chi phí tiếp tục cải tiến compiler. Tùy việc so ROI theo kiểu đô la trên mỗi phần trăm cải thiện hiệu năng, “định luật” này có thể có phần nào trọng lượng
    Ngược lại, bài Postgres này dường như cho thấy lợi suất giảm dần trong tối ưu hóa, điều này phản bác tiền đề của “định luật” kia rằng lợi ích hằng năm là cố định. Đồng thời, nó cũng có thể chứng minh hàm ý của Proebsting rằng về dài hạn tối ưu hóa là một khoản đầu tư tệ

• Phân tích này hơi khó hiểu. Tôi không rõ làm sao họ xác nhận được xu hướng giảm trong dữ liệu khi nó không hiện trên đồ thị
  Median có vẻ giảm một chút ở vài phiên bản đầu rồi lại tăng lên ở vài phiên bản gần đây. R² rất thấp, nên tương quan không có vẻ thuyết phục. Về cơ bản, tôi thấy tail latency đã được cải thiện, còn phần còn lại thì tùy môi trường

  • Tôi là tác giả bài blog
    Cách diễn giải “tail latency đã được cải thiện, còn phần còn lại thì tùy môi trường” là hợp lý, nhưng tôi xem đó là cách đọc thận trọng. Tất nhiên trong nhiều, có lẽ là phần lớn ứng dụng, tail latency rất quan trọng. Ngoài ra, tail latency cũng là mục tiêu mà các kỹ sư tối ưu hóa thường nhắm tới: giảm thời gian chạy của các truy vấn lâu nhất

• Tối ưu hóa truy vấn trông như thế nào? Tôi tò mò không biết đó là tối ưu ở cấp SQL hay tối ưu ở cấp thuật toán

  • Ngoài PostgreSQL, trong các cơ sở dữ liệu tôi từng dùng, phần lớn tối ưu hóa diễn ra ở cấp thuật toán. Tức là chọn thuật toán tối ưu và thứ tự thực thi cho một truy vấn cụ thể
    Có vẻ là vì nhiều truy vấn SQL khác nhau có thể được chuyển thành cùng một “lệnh” hoặc kế hoạch thực thi, và bản thân ngữ nghĩa SQL không để lại nhiều dư địa cho tối ưu hóa ở cấp ngôn ngữ
    Như đã nói trong bình luận khác, một trong những quyết định quan trọng là liệu có thể thay việc quét toàn bộ bảng bằng tra cứu chỉ mục hoặc quét chỉ mục hay không
    Ví dụ, nếu cần quét toàn bộ bảng và với mỗi hàng phải thực hiện khá nhiều tính toán để quyết định có đưa vào tập kết quả hay không, bộ tối ưu hóa có thể chuyển quét toàn bộ bảng thành quét bảng song song và hợp nhất kết quả của từng tác vụ song song
    Khi viết mã hiệu năng cao cho trình biên dịch, bạn cần biết bộ tối ưu hóa của trình biên dịch biến mã nguồn thành mã máy như thế nào. Nhờ vậy bạn có thể ưu tiên kiểu mã mà bộ tối ưu hóa xử lý tốt, và tránh các mẫu khiến nó sinh ra mã máy chậm hơn. Suy cho cùng, bộ tối ưu hóa được lập trình để phát hiện và biến đổi các mẫu nhất định
    Bộ tối ưu hóa truy vấn và kế hoạch thực thi cũng tương tự. Bạn cần học bộ tối ưu hóa truy vấn của cơ sở dữ liệu mình dùng xử lý được những mẫu nào để tạo ra kế hoạch thực thi hiệu quả
  • Nó mô tả tất cả các cách có thể thực thi SQL rồi chọn kế hoạch nhanh hơn. Ví dụ nếu tìm hàng người dùng có user_id là xx, thì sẽ chọn giữa việc đọc toàn bộ bảng rồi lọc, hay dùng một cấu trúc dữ liệu chuyên dụng
    Dùng chỉ mục thì có thể tìm trong thời gian logarit theo số hàng. Ngoài ra còn có thể làm nhiều việc khác như chọn thứ tự join, chọn chiến lược join, đẩy điều kiện lọc về phía nguồn, v.v. Đây là phạm vi rộng của tối ưu hóa SQL
  • Nhìn ở mức rất cao, mục tiêu của query planner là giảm thiểu chi phí đọc dữ liệu từ đĩa. Nó thu thập thống kê cột được tính trước, như số hàng và số lượng giá trị duy nhất, để ước lượng số hàng mà truy vấn sẽ khớp
    Dùng thông tin này để quyết định thứ tự join, chọn chỉ mục, và những việc tương tự. Join có thể được thực hiện bằng nhiều thuật toán như hash, loop, merge. Lựa chọn rẻ nhất phụ thuộc vào các yếu tố như một phía có nằm vừa trong bộ nhớ làm việc hay không, hai phía đã được sắp xếp sẵn chưa, chẳng hạn nhờ quét chỉ mục hay không
  • Tối ưu hóa truy vấn là việc chọn thuật toán cung cấp kết quả mà SQL yêu cầu

• Có vẻ trang đã sập, nên có thể xem cái này thay thế: https://web.archive.org/web/20240417050840/https://rmarcus.i...