Sorunun Tarihçesi
PostgreSQL, tekrar tekrar yürütülen SQL sorguları için analiz ve planlama yükünü ortadan kaldırmak amacıyla hazırlanan ifadeleri tanıttı. İlk sürümler her zaman belirli parametre değerlerine özel özelleştirilmiş yürütme planları oluşturuyordu, ancak bu karmaşık sorgular için önemli CPU maliyetleri getiriyordu. Bunu optimize etmek için, PostgreSQL 9.2, birden çok yürütme sırasında tek bir plan yapısını yeniden kullanan genel bir plan mekanizması ile plan önbelleği ekledi. Bu yaklaşım, planlama gecikmesini azaltırken tüm parametreleri başlangıçtaki planlama aşamasında opak yer tutucu olarak değerlendirir.
Sorun
PostgreSQL'de bölümlendirme kesimi iki ayrı aşamada çalışır: plan zamanı kesimi, sorgu planlaması sırasında planlayıcının bölümlendirme kısıtlamalarını literal değerlerle incelemesi ve çalışma zamanı kesimi, yürütme sırasında bölümlere göre birleştirme veya ek düğüm filtrelemesi kullanarak bölümleri filtrelemesi. Hazırlanan ifadeler için oluşturulan genel planlar, planlama zamanında somut parametre değerlerinin eksikliğinden dolayı plan zamanı kesimini imkansız hale getirir. Sonuç olarak, planlayıcı yürütme sırasında sağlanan gerçek parametre değerlerine bakılmaksızın tüm bölümler üzerinde bir tarama üretir ve bu da büyük bölümlendirilmiş tablolarda felaket düzeyinde performans düşüşüne yol açar.
Çözüm
Çözüm, PostgreSQL'i planlama aşamasında gerçek parametre değerlerini içeren özelleştirilmiş planlar üretmeye zorlamayı gerektirir. Bu, oturum veya belirli sorgu için plan_cache_mode yapılandırma parametresini force_custom_plan olarak ayarlayarak genel plan önbelleğini tamamen atlamasını sağlar. Alternatif olarak, protokol düzeyinde geçici çözümler, bind mesajını içeren literal değerlerle, parametreler yerine uzatılmış sorgu protokolü kullanmayı veya diğer parametreleri saklarken bölümlendirme anahtarları için literal değerleri içeren istemci tarafı sorgu oluşturucularını kullanmayı içerir.
-- Bu oturum için özel plan zorla SET plan_cache_mode = force_custom_plan; -- Veya literal değerleri güvenli bir şekilde inline yapmak için format() ile dinamik SQL kullanın EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
Yüksek frekanslı ticaret analitiği platformu, intraday fiyat hareketlerini sorgularken her sabah ciddi gecikme yükselmeleri yaşıyordu. Veritabanı, ticaret oturumu tarihine göre bölümlendirilmiş bir tabloda 2,000'den fazla bölümlendirme içeren tik verilerini saklıyordu. Uygulama, SQL enjeksiyonunu önlemek ve analiz yükünü azaltmak amacıyla tarih parametresi için ? yer tutucularını içeren JDBC hazırlanan ifadeleri kullanıyordu.
Geliştirme ekibi başlangıçta "bugünün" verilerini filtreleyen sorguların tarihi bölümlerde tarandığını gözlemledi ve bu durum 300 milisaniye beklenenin yerine 45 saniye sürdü. Bu performans düşüklüğü, genel planın planlama aşamasında alakasız bölümleri ortadan kaldıramamasından kaynaklanıyordu.
Bir yaklaşım, sıcak veriler için ayrı bir günlüğe kaydedilmeyen tablo oluşturmak ve kayıtları gece göç ettirmekti. Bu strateji, son sorgular için tamamen bölümlendirilmiş tabloyu atlayabilirdi, ancak karmaşık ETL mantığı getirdi ve sistem çökmesi sırasında veri kaybı riskini artırdı.
Başka bir öneri, JDBC bağlantı havuzunda global olarak hazırlanan ifadeleri devre dışı bırakmaktı. Bu, plan zamanında kesimi geri getirirken, planlayıcıya literal değerleri açığa çıkardığı için, yine de tekrar eden analiz ve planlama yükü nedeniyle veritabanı sunucusundaki CPU kullanımını %40 artırdı.
Ekip ayrıca, PostgreSQL'in sürüm 11'de tanıtılan çalışma zamanı bölümlendirme kesim yeteneklerini değerlendirdi. Ancak, çalışma zamanı kesimi yalnızca yürütücü taramaya başladığında bölümleri ortadan kaldırır; bu durumda planlayıcı yine de tüm bölümler için kaynak ayırır ve bölüm sınırlarını göz ardı eden suboptimal bir birleştirme düzeni üretir.
Sonuç olarak, ekip bağlantı düzeyinde yapılandırma geçişi gerçekleştirmeyi seçti. Bağlantı havuzunu bölümlü tabloları hedefleyen sorguları algılayacak şekilde yapılandırdılar ve bu belirli ifadeleri iletmeden önce SET plan_cache_mode = force_custom_plan çalıştırdılar. Bu, kullanıcı girişi filtreleri için parametreli sorguların güvenlik faydalarını korurken, planlayıcıya bölümlendirme anahtar değerlerini görünür hale getiriyordu.
Sonuç, sorgu gecikmesini 280 milisaniyeye düşürdü ve toplam veritabanı CPU kullanımını %15 azalttı, çünkü planlayıcı artık yürütmeden önce 1,999 bölümü ortadan kaldırabilirdi. Bu optimizasyon, ticaret platformunun veri bütünlüğü veya güvenliğinden ödün vermeden sıkı sabah gecikme gereksinimlerini karşılamasına olanak tanıdı.
plan_cache_mode ayarı auto iken PostgreSQL nasıl genel ve özel planlar arasında karar verir?
auto modda PostgreSQL, sorguyu ilk beş yürütmede özelleştirilmiş bir plan ile planlar ve yürütür, planlama maliyetini biriktirir. Beşinci yürütmeden sonra, genel planın tahmini maliyetini (ilk yürütme sırasında tahmin edilen) özelleştirilmiş planların ortalama yürütme süresi ile karşılaştırır. Eğer genel planın tahmini maliyeti, özelleştirilmiş planların ortalama maliyetinden düşükse, sistem bu hazırlanan ifade için kalıcı olarak genel plana geçer. Adaylar genellikle bu karşılaştırmanın, genel planın yeniden kullanılan planlama yükünü tasarruf ederek kaydedilen planlama maliyetini de içerdiğini ve kararın, açıkça yeniden planlanmadıkça, hazırlanan ifadenin ömrü boyunca kalıcı olduğunu gözden kaçırmaktadırlar.
Hazırlanan ifadeler bağlamında plan zamanı ve çalışma zamanı bölümlendirme kesimi arasındaki ayrım nedir?
Plan zamanı kesimi, planlayıcının belli bölümlerin ilgili satırları içeremeyeceğini kanıtladığı planlama aşamasında meydana gelir ve bu, bölümlendirme kısıtlamaları ve sorgudaki literal değerlerle desteklenir. Çalışma zamanı kesimi, yürütme sırasında yürütücünün, gerçek parametre değerlerine karşı bölümlendirme kısıtlamalarını kontrol etmesiyle gerçekleşir. Genel hazırlanmış planlar, PostgreSQL 11'den itibaren çalışma zamanı kesimini destekler, ancak plan zamanı kesimini desteklemez çünkü parametre değerleri bilinmemektedir. Adaylar genellikle bu mekanizmaları karıştırır ve çalışma zamanı kesiminin tüm hazırlanmış ifade bölümlendirme sorunlarını çözdüğünü düşünür; oysa plan zamanı kesimi, verimli bir birleştirme planlaması ve dizin seçimi için çok önemlidir.
Neden force_custom_plan, PostgreSQL sürüm 10'dan önce bölümlendirme kesim sorunlarını çözmekte başarısız olabilir?
Sürüm 10'dan önce, PostgreSQL çalışma zamanı bölümlendirme kesimini tamamen desteklemiyordu ve hazırlanan ifadeler, uzatılmış sorgu protokolü ile Bind mesajı üzerinden parametreler geçirilse bile kısıtlama hariç tutma avantajı sağlayamazdı. Planlayıcı, tüm bağlanan parametreleri planlama sürecinin dışı olarak değerlendirir, bu da kısıtlama hariç tutmayı tetiklemek için sorgu dizesinde açık literal değerlerin olmasını zorunlu kılardı. Bu tarihsel sınırlama, eski sistemlerde, özelleştirilmiş planların bile tüm bölümleri taraması anlamına gelir ve dinamik SQL üretimini EXECUTE ... USING ile literal değerler veya uygun escaping ile istemci tarafı dize birleştirme gerektirir; modern protokol düzeyi parametre bağlamanın aksine.