Postgres sorgusunu 1.000 kat daha hızlı hale getirmek

 (mattermost.com)
22 puan yazan xguru 2024-05-22 | 3 yorum | WhatsApp'ta paylaş
  • Mattermost, veritabanı yükünü azaltmak ve çok daha hızlı arama sonuçları sunmak için Elasticsearch kullanıyor
    • Elasticsearch'ün düzgün çalışması için arama hedefi olan tüm verilerin indekslenmesi gerekiyor
    • Zaten indekslenmiş veriler için sonradan eklenen yeni yazı ve dosyaların indekslenmesi oldukça hızlı
    • Ancak, çok büyük bir veritabanını (100 milyon gönderi) sıfırdan tamamen indekslemek çok yavaş (18 saat boyunca yarısına bile ulaşamıyor ve giderek yavaşlıyor)
  • Veritabanı çağrısı başına geçen süre grafiği üzerinden sorunun PostStore.GetPostsBatchForIndexing metodundaki SQL sorgusu olduğu tespit edildi
    • Bu sorgu temel olarak gönderileri oluşturulma zaman damgasına göre sıralıyor ve verilen zaman damgasından daha yeni olan N adet gönderiyi döndürüyor
    • Tüm gönderiler indekslenene kadar indeksleme işi bu sorgu tekrar tekrar çalıştırılarak yürütülüyor
  • EXPLAIN (ANALYZE, BUFFERS) kullanılarak sorgu yürütme planı analiz edildi:
    • Posts tablosunda indeks taraması yapılırken Filter koşulunu uygulamak için 40 milyon blok işleniyor (309GB)
    • Channels tablosuyla yapılan JOIN sorun değil
    • WHERE koşulundaki OR ifadesinde yalnızca Posts.CreateAt > ?1 kısmı uygulanırsa çok daha hızlı oluyor (30ms)
    • Buraya Posts.CreateAt = ?1 AND Posts.Id > ?2 koşulu eklendiğinde ise aşırı derecede hızlanıyor (0.047ms)
  • Sebebin tespiti:
    • Orijinal sorgu Posts içindeki tüm satırları tarayıp Filter ile elerken, düzeltilen sorgu yalnızca indeksi kontrol edip gereken satırları çıkarıyor
    • Sorgunun zamanla giderek yavaşlamasının nedeni, elenmesi gereken satır sayısının sürekli artmasıydı
  • Çözüm:
    • PostgreSQL'in row constructor comparison özelliğinden yararlanılarak koşul (Posts.CreateAt, Posts.Id) > (?1, ?2) olarak değiştirildi
    • Bu şekilde değiştirilen sorgunun çalışma süresi 34 milisaniyeye kadar düştü
    • Ancak MySQL'de değiştirilen sorgu aksine daha yavaş çalıştı. MySQL'de orijinal sorgu daha hızlı olduğu için veritabanına göre farklı sorgular kullanacak şekilde kod dallandırıldı
  • Çıkarılan dersler:
    • EXPLAIN kullanırken BUFFERS seçeneğini her zaman kullanın
    • Filter yerine Index Cond kullanılacak şekilde tasarlayın
    • PostgreSQL ve MySQL'in neredeyse her zaman farklı davrandığını varsayın
  • Sonuç
    • Yapılan optimizasyonla sorgu yürütme süresi 1000 kattan fazla azaltıldı
    • Bu optimizasyon Mattermost v9.7.0 ve v9.5 ESR sürümlerine yansıtıldı
    • Bu optimizasyon çalışması sayesinde çok şey öğrenildi

İlgili okumalar

3 yorum

 
dontcryme 2024-05-23

Son yazıda da geçtiği gibi, bu yazının başlığı biraz clickbait gibi duruyor ama... daha pratik bir şekilde yeniden ifade edecek olursak

'postgresql'i kullanırken hatalardan öğrenilen bir örnek olay'

olabilir sanırım..
 
vwjdalsgkv 2024-05-23

Hmm... Şahsen, bu seviyedeki bir yazı belirli bir şirketi/ürünü öne çıkararak yazılmışsa, o ürünle ilgili güvenim aksine epey azalır diye düşünüyorum.
Düzenli ve anlaşılır şekilde toparlanmış, ancak içinde barındırdığı teknik değer biraz yetersiz göründüğü için üzücü.
 
savvykang 2024-05-23

Ben de bu yazıyı gördükten sonra aksine güvenilirliği daha düşük buldum. Sonuçta para karşılığı sattıkları bir ürünün büyük hacimli işlem testleri bile yapılmadan bir özelliği yayınlamışlar. O kadar basit bir indeksin, özellik geliştirme aşamasında ayarlanması gerekmiyor muydu diye düşünüyorum. Yazılım geliştirme sürecinde epey çok adım atlanmış gibi görünüyor.