PostgreSQL optimize edicisinin 10 yıllık gelişimi

• PostgreSQL 8’den 16’ya kadar Join Order Benchmark ile 90. yüzdelik dilim sorgu gecikmesi karşılaştırılarak, uzun vadeli kuyruk performansı iyileşmesi deneysel olarak doğrulandı
• PostgreSQL 16’da, 8’e kıyasla kuyruk gecikmesi neredeyse yarıya indi; 13–16 aralığı ise genel olarak kararlı bir seviyede kaldı
• Regresyon analizine göre her majör sürüm artışında ortalama %15 performans iyileşmesi görüldü; ancak doğrusal model değişim desenini iyi açıklamayabilir
• Deney, sorgu optimize edicisi kalitesine odaklanmak için GCC 13.2, Arch Linux Docker, 8 GB shared_buffers ve 8 MB work_mem koşulları sabitlenerek yapıldı
• İyileşme miktarı yorumlanırken yalnızca optimize edici değil, paralel worker’lar ve JIT derleme gibi yürütme motoru değişiklikleri de dikkate alınmalı

---

PostgreSQL 8–16 benchmark ayarları

• Analiz konusu, açık kaynak sorgu optimize edicisi PostgreSQL’in 8’den 16’ya kadar majör sürümleridir
• Benchmark için karmaşık join’lerin yoğun olduğu bir sorgu kümesi olan Join Order Benchmark kullanıldı
  • Bu benchmark, “How Good are Query Optimizers, Really?” makalesinde tanıtıldı
• Her PostgreSQL sürümü, Arch Linux Docker container’ı içinde GCC 13.2 ile derlendi
• Ölçüm ortamı, indeks veya I/O performansından çok sorgu optimize edicisi kalitesini görecek şekilde ayarlandı
  • shared_buffers, tüm veritabanını tutabilecek kadar büyük olan 8 GB olarak ayarlandı
  • work_mem, tüm sürümlerde 8 MB olarak sabitlendi
• Her sorgu, cache’i ısıtmak için bir kez çalıştırıldıktan sonra, ek 5 çalıştırmanın medyan gecikmesi kaydedildi
• Her majör sürüm için en güncel minör sürüm kullanıldı
  • Örneğin PostgreSQL 8 için 8.4.22 hedef alındı
  • Bu minör sürümler genellikle yeni majör sürümden sonra çıkmış olsa da, çoğunlukla yalnızca hata düzeltmeleri içerir; yeni özellik veya performans iyileştirmesi içermez

Ölçüm sonuçları ve yorum

• PostgreSQL’in kuyruk performansı genel olarak önemli ölçüde iyileşti
  • PostgreSQL 8 ile 16 karşılaştırıldığında kuyruk gecikmesi neredeyse yarıya indi
  • PostgreSQL 13’ten 16’ya kadar genel olarak kararlı bir seviye korundu
• Regresyon analizi, majör sürüm numarası ile sorgu gecikmesi arasındaki düşüş eğiliminin anlamlı olup olmadığını doğrulamak ve sürüm bazındaki iyileşme miktarını nicelleştirmek için kullanıldı
  • Doğrusal regresyon temel alındığında, her yeni majör sürümde Join Order Benchmark’ta ortalama %15 performans iyileşmesi görüldü
  • Ancak doğrusal model, gerçek değişim desenini ölçmek için uygun olmayabilir
• Tüm iyileşmeleri yalnızca sorgu optimize edicisi ile açıklamak zor
  • Paralel worker’lar ve JIT derleme gibi yürütme motoru iyileştirmeleri de performansı etkiler
  • JOB’daki her sorgu planının yıllar içinde nasıl değiştiği ayrı bir analiz konusu olarak duruyor
• PostgreSQL 8’den 16’ya yükseltme yapılırsa, iş yükünün kuyruk gecikmesinin önemli ölçüde azalması olasıdır
• Araştırma karşılaştırmalarında PostgreSQL’in kendisinin sürekli güçlenen bir referans noktası olması önemlidir
  • Neo ve Bao, PostgreSQL 11 ile karşılaştırılmıştı; ancak daha yeni çalışmalar PostgreSQL 14, 15, 16 ile karşılaştırıyor
  • Eski bir teknik PostgreSQL’e göre %30, yeni bir teknik ise %25 iyileşme sağlasa bile, yeni teknik daha güçlü bir PostgreSQL ile karşılaştırılmış olabilir
• Orijinal ölçüm değerleri raw data üzerinden görülebilir

[GN⁺]

Hacker News yorumları

• Postgres’i 15 yıldır kullanıyorum ve kariyerimin büyük kısmını matematiksel optimizasyon problemlerini modellemeye ve çözmeye harcadım; bu konuda kilit noktanın üç şey olduğunu düşünüyorum.
  Her optimizasyon problemi maliyet verisine ihtiyaç duyar; veri ne kadar çok ve iyi olursa sonuç o kadar iyi olur. Postgres’te çapraz sütun istatistikleri gibi iyileştirmeler yapıldı, ancak hâlâ sistem çağrısı gecikmesi gibi büyük boşluklar var. Diskten sayfa okuma gecikmesi sistemden sisteme çok değiştiği hâlde Postgres bunu doğrudan ölçmüyor, ayar değerlerine dayanıyor. Yabancı anahtar istatistikleri de eksik; bu yüzden yabancı anahtarları izleyen join’lerde kötü plan çıkmaması gerekirken bazen hâlâ çıkıyor.
  Özellikle büyük ve pahalı sorgular için gecikmeli planlama ya da alternatif senaryo planlaması gerekli. Şu anda plan yürütmeden önce kesinleşiyor, ancak yürütmenin erken aşamalarında elde edilen satır sayıları veya kardinalite tahminleri, sonraki aşamaların planını ciddi biçimde iyileştirebilir.
  Makine öğrenmesi de iyileştirme potansiyeli olan bir alan, ancak şimdiye kadar gördüğüm denemeler etkileyici değildi. Makine öğrenmesini planın kendisi için değil, maliyet keşfi ve tahmini için kullanmak gerekiyor. Daha iyi bir maliyet modeli oluşturup optimizasyon motorunun bu veriyi kullanmasını sağlamak lazım.

  • Daha fazla görüş duymak isterim. Örneğin sistem çağrısı gecikmesinin üst sıralarda olması bana şaşırtıcı geliyor. Veritabanı topluluğundaki genel bakışın, maliyet modelinin genel olarak fena olmadığı; asıl çok kötü olanın kardinalite tahmini olduğu yönünde olduğunu düşünüyorum.
    Gecikmeli/alternatif planlamada uyarlamalı sorgu yürütmenin makul bir yöntem olup olmadığını merak ediyorum. Sorgu yürütmenin başındaki bilgilerin sonraki planı etkilemesi sağlanabilir, ancak ilk birkaç join’i yanlış seçmek yaygın bir durum ve Yannakakis/SIP’ler gibi bir şey yoksa toparlamanın zor olacağından endişeliyim.
    “Sorgu optimizasyonu için makine öğrenmesi” konusunda elbette önyargım var. Yine de gördüğüm tüm “planlama için makine öğrenmesi” yaklaşımları, içeride nihayetinde maliyet keşfi/tahmini için makine öğrenmesi kullanıyor. Bu yaklaşımlar, topladıkları veri, yani keşif ile ürettikleri plan kalitesi, yani kullanım arasında denge kurmaya çalışıyor. İlginç şekilde, planlamadan tamamen ayrı bir biçimde makine öğrenmesi kullanıldığında tahminler daha doğru hâle gelse bile gerçek sorgu planı daha kötüleşiyor: https://people.csail.mit.edu/tatbul/publications/flowloss_vl...
    Bu alanda çıkarım olduğu için görüşümü buna göre değerlendirmek gerekir.
  • Alternatif planlar gerçekten iyi görünüyor. Kısa süre önce gördüğüm bir sorgu planı, bir alt sorgudan yaklaşık 1.000 satır çıkacağını tahmin edip indeks taramasının üstüne nested loop koymuştu; gerçekte ise yaklaşık 1 milyar satır vardı.
    Tahminin neden bu kadar yanlış olduğunu hâlâ bilmiyorum, ancak satır sayısı belirli bir eşiği aşınca nested loop’tan hash join’e geçilebilse, felaket planları önlemede çok yardımcı olur gibi görünüyor.
  • Yabancı anahtar istatistiklerinin eksik olmasıyla tam olarak ne kastedildiğini merak ediyorum. Postgres de birçok ilişkisel veritabanı gibi yabancı anahtarlara indeks otomatik oluşturmuyor; bunu zaten biliyor olmalısınız.
    Join sırası sorunundan mı bahsediliyor?
  • MSSQL’in bu konuda daha iyi olup olmadığını merak ediyorum.

• Postgres sorgu optimize edicisi, diskten okunan sayfa sayısını ve ara sonuçlar için diske yazılan sayfa sayısını azaltmaya çalışır. Bu yüzden tüm veriyi sığdıracak kadar shared buffers’ı büyük ayarlayıp sorgu optimize edicisini benchmark etmek yanlış gibi görünüyor.
  Böyle olunca üretilen sorgu planının kalitesini değil, sorgu optimize edicisinin ve join işleyicisinin hızını ölçmüş olursunuz. Aslında her sürümde üretilen planların aynı olup yalnızca yürütme hızının ölçülmüş olması da şaşırtıcı olmaz.

  • Öyle değil. Optimize edilen şey yalnızca diskten okunan sayfalar değil, CPU kullanımı gibi şeyleri de içeren maliyettir.
    Maliyet, disk okuma sayısı değil, geçen süreyle ilişkili olacak şekilde tasarlanmış keyfî bir birimdir; bu yüzden her şey RAM’deyken planları karşılaştırmak da gayet geçerlidir. Geleneksel olarak diskten bir sayfa okumak 1.0’a ölçeklenir, ama bu “optimize edici disk sayfası okuma sayısını en aza indirir” demek değildir. Rastgele bir makinede 1 ms’yi 1.0 kabul etmek de mümkün olurdu.
  • Planların benzer olup yürütme motorundaki iyileştirmelerin ölçülmüş olması kesinlikle mümkün. Join Order Benchmark, optimize edici kalitesini test etmek için tasarlanmıştır.
    PG optimize edicisi yalnızca diskten okunan sayfa sayısını değil, CPU’nun incelediği tuple sayısını, koşul ifadelerinin değerlendirilme sayısını vb. de azaltmaya çalışır; bu sayıların hepsi “maliyet” altında birleştirilir ve optimize edicinin minimize ettiği fonksiyon hâline gelir.
    Soğuk cache ve sıcak cache performans ölçümleri farklı sonuçlar verebilir; bu deney kesinlikle sıcak cache senaryosu. Ancak soğuk cache’in de bahsedilen sorunu var. Join Order Benchmark veri boyutunda PG’nin B-tree iyileştirmeleriyle birkaç I/O’dan tasarruf etmesinin etkisi, CPU tabanlı iyileştirmelerden baskın olabilir.
    Not olarak, P90 gecikme sorgusunun planı PG 8.4’te loop join ve merge join kullanan bir plandan PG 16’da hash join kullanan bir plana değişti ve bu sorgu artık P90 sorgusu değil. Bu, en azından optimize edici iyileştirmelerinin bir kısmına kanıt sayılabilir.

• Yazıda PostgreSQL’in JIT derleyicisinden bahsedilmiş, ancak şimdiye kadar yalnızca sorgu performansını düşürdüğünü gördüm. Kurulum kontrol listeme devre dışı bırakmayı ekledim.

  • Bir müşteri Postgres’e geçtikten sonra en kötü performans sorununu yaşadı. Garip şekilde yalnızca Docker ve test sunucusu ayarlarında ortaya çıkıyor, geliştirici makinelerinde olmuyordu. Geliştirici Postgres’i Homebrew ile çalıştırıyordu.
    Meğer Homebrew Postgres’i JIT desteği olmadan kuruyormuş; geliştirici makinesinde 200 ms’de biten bir sorgu, JIT açık ortamda 4-5 saniye sürüyordu. Postgres’i çok derin kullanan biri olmadığım için nedeni bulmak biraz zaman aldı; o zamandan beri JIT’i hep kapatıyorum ve arkama bakmıyorum.
  • JIT derleyicisi analitik sorgular için harikadır.
    PostgreSQL’de JIT etkinleştirme eşiğini de ayarlayabilirsiniz; böylece JIT’in devreye girme kriterini daha yükseğe çekebilirsiniz.
  • pg’nin JIT’i, LLVM’in JIT için pek iyi olmadığı gerçeğini oldukça iyi gösteriyor; Postgres’te kalıcı, paylaşımlı bir sorgu cache’i olmaması bunu daha da kötüleştiriyor.
    Gelecekteki sorgular için asenkron derleme yapılabilse daha az zararlı olur gibi. Aslında genel JIT’ler, özellikle de optimizasyon backend’leri, daha çok bu yaklaşıma yakın.
  • Postgres bir sorguyu bir kez JIT ile derleyip sonra derlenmiş sorguyu birden çok kez çalıştıramıyor mu?

• İlginç ama Postgres’in sürüm numaralandırma sistemi v10’da değişti. 9.6, 9.5, 9.4, 9.3, 9.2, 9.1, 9.0, 8.4, 8.3, 8.2, 8.1, 8.0 aslında hepsi ayrı ayrı majör sürümler
  Bu sürümlerde performansın nasıl değiştiğine bakmak da ilginç olurdu

  • Yine de v9.0’dan 9.6’ya kadar yalnızca ikili dosyaları değiştirerek daha hızlı yerinde yükseltme yapılabilmesi için majör sürüm dosya sistemi uyumluluğunu korumuş olmalarına minnettardım
    Bu onları kısıtlamış olabilir, ama daha fazla kesinti süresi veya yeniden indeksleme gerektiren yıllık güncellemeler pek keyifli değil; birçok sitenin eski sürüm desteği bitene kadar yükseltmeyi ertelemesinin nedeni de bu olabilir. Özellikle AWS RDS kullanıcıları için böyle olacaktır
    v10 sonrası mantıksal replikasyon yükseltmeleri kullanılabilirlik açısından avantajlı olsa da, şema nispeten basit değilse kaçınılmaz maliyetleri ve büyük riskleri olan büyük projelerdir
  • Tamamen katılıyorum. Sürüm numaralarını semver tarzında yorumlayıp her majör sürümün en son sürümünü seçtim; bu, PostgreSQL’in geleneksel olarak majör sürüm numaralarını ele alma biçiminden farklı
    Örneğin PG 8.2 ve 8.1 birbirinden farklı majör sürümlerdir, ama ben bunları minör sürüm gibi yorumladım. Bunu yapmamın ana nedeni test edilmesi gereken sürüm sayısını azaltmaktı; daha eksiksiz bir analizde her gerçek majör sürümün test edilmesi gerektiğine katılıyorum

• “Elbette bu iyileşmenin tamamı sorgu iyileştiricisi sayesinde değil” denmiş; sürümler arasında çalıştırma planı değişiklikleri olup olmadığına bakmak ilginç olurdu

• Aklıma Proebsting’in yasası geliyor: https://proebsting.cs.arizona.edu/law.html

  • Derleyici optimizasyonunun güzel yanı, mevcut CPU’ya fiziksel olarak dokunmadan performansı artırabilmesidir. Her yıl birinin tasarladığı makineden daha fazla performans çıkarırsınız ve birikince büyür
    Python performansını %1 optimize etmenin çevresel etkisinin ne olacağını hayal edin. Atmosferdeki CO2’yi ne kadar azaltırdı? Muhtemelen sizin, ailenizin ve tüm arkadaşlarınızın çevresel ayak izinin toplamından daha büyük olurdu. Belki de yaşadığınız şehrin tamamıyla yarışırdı. Sırf birileri birkaç bit işlemi hilesi uygulamaya zaman ayırdı diye böyle olurdu
  • Neden böyle olduğunu anlamıyorum. O yasa, yazılım performans iyileştirmelerinin pek anlamlı olmadığı yönünde konuşuyor gibi; bu yazı ise Postgres iyileştirmelerinin kayda değer olduğunu söylüyor
    %15’i düşük bir sayı olarak gördüğü için mi? Bu bağlamda hiç düşük değil. Bağlantı verilen yasadaki %60’tan küçük ve 15/10 diye bölerseniz daha da küçük olur; ama Postgres performansını donanım iyileşmeleriyle karşılaştırmamak gerekir. Burada ölçülen şeyde %1 performans artışına denk gelmek için muazzam bir donanım iyileştirmesi gerekir
    O yasanın başkalarının söylediği kadar gülünç olmadığını düşünüyorum, ama konu programlama dili derleme süresi. Böyle görece önemsiz bir şeyi, bilgisayar biliminin en önemli şeylerinden biri sayılabilecek veri depolama ve tüketimiyle karşılaştırmam
  • Bu durumda araştırmacı tüm PostgreSQL sürümlerini aynı GCC 13.2 ile derlemiş ve aynı işletim sisteminde test etmiş
  • Oldukça zayıf bir “yasa” gibi görünüyor. Şaka olarak mı yapılmış? Dayanağı nereden geldiği belirsiz sayılar, “varsayalım” düzeyinde ve sonucu da epey isabetsiz. Dünya genelinde pek çok yazılımın performansını her yıl %4 artıran optimizasyonların zaman kaybı olduğunu ima ediyor gibi
    Karşılaştırma olarak yalnızca Murphy yasası sunulmuş. Daha hızlı donanım geliştirmenin maliyetiyle derleyicileri sürekli iyileştirmenin maliyeti arasında ne kadar fark olduğunu merak ediyorum. Yatırım getirisini yüzde performans artışı başına dolar gibi bir yöntemle karşılaştırdığınızda, bu “yasaya” bir ölçüde ağırlık verilebilir
    Öte yandan bu Postgres yazısı optimizasyonda azalan getirileri gösteriyor gibi ve bu, o “yasanın” kazancın her yıl sabit olduğu varsayımını çürütüyor. Aynı zamanda Proebsting’in uzun vadede optimizasyonun kötü bir yatırım olduğu imasını da doğrulayabilir

• Bu analiz biraz kafa karıştırıcı. Grafikte görünmeyen düşüş eğiliminin verilerde nasıl doğrulandığını anlamıyorum
  Medyan ilk birkaç sürümde biraz düşüp son birkaç sürümde tekrar yükseliyor gibi görünüyor. R² çok düşük olduğu için korelasyon ikna edici gelmiyor. Temelde kuyruk gecikmesinin iyileştiği, geri kalanının ise ortama bağlı olduğu izlenimi veriyor

  • Blog yazısının yazarıyım
    “Kuyruk gecikmesi iyileşti, geri kalanı ortama bağlı” yorumu makul ama muhafazakâr bir okuma bence. Elbette birçok, belki de çoğu uygulamada kuyruk gecikmesi çok önemlidir. Ayrıca kuyruk gecikmesi, iyileştirici mühendislerinin genelde hedeflediği şeydir: yani en uzun süren sorguların çalışma süresini azaltmak

• Sorgu optimizasyonu nasıl bir şey? SQL düzeyinde mi optimize ediliyor, yoksa algoritma düzeyinde mi optimize ediliyor, merak ediyorum

  • PostgreSQL hariç, kullandığım veritabanlarında optimizasyonların çoğu algoritma düzeyinde gerçekleşiyor. Yani belirli bir sorgu için kullanılacak en iyi algoritmayı ve yürütme sırasını seçmek anlamına geliyor.
    Birçok farklı SQL sorgusu aynı “komuta” veya yürütme planına dönüştürülebiliyor; bunun nedeni de SQL semantiğinin kendisinin dil düzeyinde optimizasyona çok fazla alan bırakmaması gibi görünüyor.
    Diğer yanıtlarda söylendiği gibi önemli kararlardan biri, tam tablo taramasının indeks aramasına veya indeks taramasına dönüştürülüp dönüştürülemeyeceği.
    Örneğin tam tablo taraması gerekiyorsa ve her satır için sonuç kümesine dahil edilip edilmeyeceğine karar vermek üzere kayda değer hesaplama yapmak gerekiyorsa, optimizer tam tablo taramasını paralel tablo taramasına dönüştürüp her paralel işin sonuçlarını birleştirebilir.
    Derleyiciler için yüksek performanslı kod yazarken, derleyici optimizer’ının kaynak kodu makine koduna nasıl dönüştürdüğünü bilmek gerekir. Böylece optimizer’ın iyi işlediği kodu tercih edebilir, daha yavaş makine kodu üreten kalıplardan kaçınabilirsiniz. Sonuçta optimizer, belirli kalıpları algılayıp dönüştürecek şekilde programlanmıştır.
    Sorgu optimizer’ları ve yürütme planları için de aynı şey geçerli. Kullandığınız veritabanının sorgu optimizer’ının hangi kalıpları işleyip verimli yürütme planları oluşturabildiğini öğrenmeniz gerekir.
  • SQL’i çalıştırmanın tüm yolları tanımlanır, ardından daha hızlı plan seçilir. Örneğin user_id değeri xx olan kullanıcı satırını bulacaksanız, tüm tabloyu okuyup filtrelemek mi, yoksa özel bir veri yapısı kullanmak mı gerektiğine karar vermek gibi.
    İndeks kullanırsanız satır sayısına göre logaritmik zamanda bulabilirsiniz. Bunun dışında join sırasını seçme, join stratejisini seçme, filtre koşullarını kaynak tarafa itme gibi pek çok şey mümkündür. SQL optimizasyonunun geniş alanı budur.
  • Çok yüksek seviyeden bakıldığında sorgu planlayıcısının hedefi, diskten veri okuma maliyetini en aza indirmektir. Satır sayısı ve benzersiz değer sayısı gibi önceden hesaplanmış kolon istatistiklerini toplayarak sorgunun eşleşeceği satır sayısını tahmin eder.
    Bu bilgiyi kullanarak join sırasını belirler, indeks seçer vb. Join’ler hash, loop, merge gibi çeşitli algoritmalarla gerçekleştirilebilir. En ucuz seçenek; taraflardan birinin çalışma belleğine sığıp sığmadığına, iki tarafın zaten sıralı olup olmadığına, örneğin bunun bir indeks taraması sayesinde olup olmadığına benzer etkenlere bağlıdır.
  • Sorgu optimizasyonu, SQL’in istediği sonucu sağlayan algoritmayı seçme işidir.

• Site çökmüş gibi göründüğünden bunun yerine şuna bakabilirsiniz: https://web.archive.org/web/20240417050840/https://rmarcus.i...