PostgreSQL optimizasyon aracında 10 yıllık iyileştirme

PostgreSQL optimizasyon aracındaki 10 yıllık iyileştirmeler

• Sorgu optimizasyonu araştırmacısı olarak son 10 yıldır PostgreSQL'in gelişmiş açık kaynaklı sorgu optimizasyon aracını kullanarak araştırmalar yürütüyorum
• Veritabanı çalışmalarına başladıktan sonraki 10 yılda PostgreSQL'in ne kadar geliştiğini merak ettim
• Değişiklik kayıtları ve görüşler çoktu, ancak güçlü bir ampirik karşılaştırma bulamayınca PostgreSQL 8'den 16'ya kadar Join Order Benchmark (JOB) çalıştırmaya karar verdim
• Her veritabanı sürümü için 90. yüzdelik dilimdeki sorgu gecikmesini kaydettim

Test ortamının kurulumu

• Her PostgreSQL sürümünü Arch Linux Docker konteyneri içinde GCC 13.2 kullanarak derledim
• Sorgu optimizasyon aracının kalitesini ölçmek için shared_buffers değerini 8GB olarak ayarladım (tüm veritabanını tutabilecek kadar büyük)
• Tüm sürümler için work_mem değerini 8MB olarak ayarladım
• Her sorgu önbelleği ısıtmak için bir kez çalıştırıldı, ardından ek olarak 5 kez daha çalıştırılarak medyan gecikme kaydedildi

Genel performans iyileşmesi

• PostgreSQL'in kuyruk performansı büyük ölçüde iyileşti, ancak 13~16 sürümleri genel olarak istikrarlıydı
• Sürüm 8 ile sürüm 16 karşılaştırıldığında, PostgreSQL'in optimizasyon aracı son 10 yılda kuyruk gecikmesini neredeyse yarıya indirdi
• Tüm sorgu dağılımı incelenebiliyor (log ölçeğine dikkat edin)

Regresyon analiziyle iyileştirmelerin nicelendirilmesi

• Regresyon analizi kullanarak gecikme süresindeki düşüş eğiminin anlamlı olup olmadığı doğrulanabilir ve her PostgreSQL sürümünün ne kadar iyileştirme getirdiği nicel olarak ölçülebilir
• PostgreSQL ana sürüm numarası ile sorgu gecikmesi arasında regresyon analizi yapıldığında, PostgreSQL'in her yeni ana sürümü ortalama olarak Join Order Benchmark'ta %15 performans artışı sağlıyor
• Ancak doğrusal model, değişimi ölçmek için tartışmalı biçimde zayıf bir ölçü olabilir

Ek değerlendirmeler

• Elbette bu iyileştirmelerin tamamı sorgu optimizasyon aracından kaynaklanmıyor. Paralel worker'lardan just-in-time (JIT) derlemeye kadar yürütme motorundaki iyileştirmeler de rol oynuyor
• JOB içindeki her sorgu planının yıllara göre nasıl değiştiğini incelemek de ilginç olacaktır

Ana noktalar

• Veritabanınızı yükseltin! PostgreSQL 8'den 16'ya geçmek, iş yükünüzün kuyruk gecikmesini büyük ölçüde iyileştirebilir
• Araştırmacılar, PostgreSQL'in hareketli bir hedef olduğunu göz önünde bulundurmalı
  • Öğrenilmiş sorgu optimizasyonu araştırmaları zaman içinde PostgreSQL'in farklı sürümleriyle karşılaştırma yaptı
  • Eski bir tekniğin PostgreSQL'i %30 iyileştirmesi ve yeni bir tekniğin PostgreSQL'i %25 iyileştirmesi, yeni tekniğin daha güçlü bir PostgreSQL sürümüyle karşılaştırılıyor olabileceği anlamına gelebilir

GN⁺ görüşü

• PostgreSQL performansı sürekli olarak iyileştirildi, ancak son sürümlerde ilerleme hızı azalmış görünüyor. Bunun nedeni, zaten önemli ölçüde optimizasyon yapılmış olması olabilir. Gelecekteki iyileştirmeler daha ayrıntılı alanlara odaklanacak gibi görünüyor

• Performans artışına yalnızca sorgu optimizasyon aracı değil, yürütme motorundaki iyileştirmeler de katkı sağlıyor. Paralel işleme ve JIT derleme gibi çeşitli alanlarda optimizasyonlar yapılıyor

• Bu deney yalnızca Join Order Benchmark ile sınırlı; gerçek iş ortamındaki performans artışı iş yüküne göre farklılık gösterebilir. Kendi iş yükünüzün özelliklerine uygun benchmark'lar çalıştırmak faydalı olacaktır

• Araştırmacılar PostgreSQL sürüm değişimlerini dikkate almalı. Çünkü aynı algoritma bile, karşılaştırılan PostgreSQL sürümüne bağlı olarak göreli performans artışı açısından farklı sonuçlar verebilir

• Eski bir PostgreSQL sürümü kullanıyorsanız yükseltmeyi ciddi biçimde değerlendirmekte fayda var. En yeni sürümler, 10 yıl önceki sürümlere kıyasla belirgin performans iyileştirmeleri sunuyor. Elbette yükseltmeyle birlikte gelebilecek uyumluluk gibi konular da hesaba katılmalı

[GN⁺]

Hacker News görüşü

Özet:

• Optimizasyon sorunlarını iyi çözmek için maliyete ilişkin veriler önemlidir. PostgreSQL'ün gelişime açık çok alanı var. Özellikle syscall latency verileri ve foreign key istatistikleri yetersiz.
• Büyük ölçekli sorgular için, çalışma sırasında planı değiştirebilen deferred planning gibi tekniklerin devreye alınması gerekiyor.
• Makine öğrenimini maliyet tahmin modellerini iyileştirmek için kullanmak uygundur. Makine öğrenimiyle doğrudan sorgu planı oluşturmak ise uygun değildir.
• shared buffer'ı büyük tutup tüm veriyi belleğe alarak benchmark yapmak, optimizer'ın gerçek performansını doğru değerlendirmeyi zorlaştırır.
• JIT derleyici hâlâ çoğu durumda yalnızca performans düşüşüne yol açıyor.
• PostgreSQL sürüm numaralandırması 10. sürümden itibaren değiştiği için, 8.x ve 9.x sürümlerini major sürümler olarak kabul edip performans eğilimini analiz etmek de ilginç olabilir.
• Yalnızca sunulan grafiklerle performans iyileşmesi eğilimini net biçimde doğrulamak zor. Tail latency iyileşmiş gibi görünüyor, ancak diğerleri ayrı ayrı farklı olabilir.
• Harika bir optimizer yapmak oldukça zorlayıcı bir iştir.
• Sorgu optimizasyonunun SQL düzeyinde mi yoksa algoritma düzeyinde mi olduğu merak ediliyor.