Gecikmeyi azaltan Postgres üretim ayarı

postgresql.conf, varsayılan durumunda, trafiğinizi emmemek için bir kurulumu asla başarısızlığa uğratmayacak şekilde tasarlanmıştır. shared_buffers'nin tarihsel değeri olan 128 MB, Postgres'in herhangi bir kritik kaynak ayırmadan minimum makinede başlamasına olanak tanır. max_connections 100'de küçük bir paylaşılan sunucuya sığar. İkisi de gerçek yükünüz için seçilmedi.

Yani sorun Postgres'in varsayılan olarak kötü ayarlanmış olması değil: sizin için hiçbir zaman ayarlanmamış olmasıdır. Aşağıdaki bölümlerde ayarlar, en yaygın darboğazdan (bağlantılar) en yavaş görünene (WAL) kadar en fazla getiri sağlayan sıraya göre ele alınmaktadır.

İlk proje hafıza değil, bağlantılardır. Her Postgres bağlantısı, boşta olsa bile RAM ve bağlam CPU süresini tüketen özel bir sunucu işlemini açar. "Çok fazla bağlantı" türü hataları önlemek için max_connections değerini artırmak sorunu değiştirir: Belirli sayıda eş zamanlı etkin bağlantının ötesinde, CPU çekişmesi, en hızlı olanlar da dahil olmak üzere tüm isteklerin gecikme süresini azaltır.

Doğru yaklaşım olağan sırayı tersine çevirir: max_connections'yi gerçek sunucu eşzamanlılığına göre ölçeklendirin, ardından uygulama tarafı eşzamanlılığını PgBouncer gibi bir havuzlayıcıyla pool_mode=transactioniçine emdirin. Havuz oluşturucu, yüzlerce istemci bağlantısını bir avuç fiili sunucu bağlantısına çoğaltır. Özel makalemiz, işlem modu mekanizmasını ve sınırlarını (hazırlanan ifadeler, LISTEN/NOTIFY) ve uygulamayı seçmek için PgBouncer, Supavisor ve PgCat arasındaki karşılaştırmayı ayrıntılarıyla anlatır.

max_connections bir "postmaster" içerik parametresidir: bunu değiştirmek basit bir yeniden yükleme değil, sunucunun tam olarak yeniden başlatılmasını gerektirir. Aurabase'e özel Postgres örneklerinde (Pro ve Enterprise planları, proje başına bir CNPG kümesi), bu ayar, varsayılan değerinde bırakılmak yerine plan katmanı başına yapılandırılır. Yeniden başlatma, üretimde tekrarlanacak önemsiz bir işlem değildir; bu da bu seçimi sabit bir değer yerine aşamalar halinde haklı çıkarır. Sınırlarıyla birlikte kendi değerinizi seçme formülü ayrı bir makalenin konusudur: size max_connections.

Dört bellek ayarı, diğerlerinin toplamından daha ağırdır: shared_buffers, effective_cache_size, work_mem ve maintenance_work_mem. İlk üçü, Postgres'in diske dönmeden önce bellekte ne kadar veri tutacağını belirler; dördüncüsü bir VAKUM veya indeks oluşturmanın hızını belirler.

shared_buffers tüm bağlantılar tarafından paylaşılan dahili önbelleği ayarlar. PostgreSQL projesi tarafından genel olarak belgelenen kıyaslama, özel bir veritabanı sunucusunda mevcut olan RAM'in yaklaşık %25'idir. Bunun ötesinde kazançlar azalır ve işletim sisteminin disk önbelleği devreye girer. effective_cache_size hiçbir şey ayırmaz: sorgu planlayıcıya önbellek için kullanılabilir toplam belleğin (Postgres ve işletim sistemi birleştirilmiş) verilen bir tahminidir. Boyutunun küçük olması, zamanlayıcıyı sıralı taramalara doğru iterken, bir dizin büyük ölçüde önbelleğe alınır; mevcut kıyaslama RAM'in %50 ila 75'i arasındadır.

work_mem en yaygın tuzaktır. Bu küresel bir sınır değildir. Bir sorgudaki her sıralama veya karma işlemi, kendi payını tüketebilir ve birden çok birleşimi olan bir sorgu, kendi payını birkaç kez ayırabilir. Yüksek bir max_connections ile birleştirilmiş çok cömert bir değer, tek başına alınan her istek makul görünse bile, eşzamanlı yük altında sunucu RAM'ini tüketebilir. maintenance_work_memise tersine, önemli ölçüde daha cömert kalabilir: yalnızca birbiriyle nadiren eşzamanlı olan bakım işlemleri (VACUUM, CREATE INDEX) için geçerlidir.

Yalnızca shared_buffers yeniden başlatma gerektirir. Diğer üçü, yalıtılmış bir oturum da dahil olmak üzere çalışırken yeniden yüklenir: SET work_mem = '64MB';, genel ayarı etkilemeden, tek bir açgözlü istek süresince.

En yüksek yük sırasında "kaynakları boşaltmak" için geçici olarak bile olsa üretimde otomatik vakumu asla devre dışı bırakmayın. Postgres MVCC'yi kullanır: her UPDATE ve her DELETE, yalnızca vakumun kurtarabileceği bir son tarih bırakır. Bu olmadan, tablolar şişer, dizinler bozulur ve yürütme planları yavaş yavaş bozulur ve geç olana kadar hiçbir hata görülmez.

Varsayılanautovacuum_vacuum_scale_factor değeri (tetiklemeden önce %20 ölü satır), yazma yoğunluklu multi-milyon satırlı bir tablo için değil, küçük bir tablo için uygundur. 10 milyon satırlık bir tabloda bu %20, ilk geçişten önce biriken 2 milyon ölü satırı temsil eder. Tüm veritabanının genel değerini değiştirmek yerine bu eşik tablosunu tablo bazında düşürün.

-- Genel olarak değil, yazma ağırlıklı bir tabloda eşiği düşürün
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

Aurabase Studio'ya entegre edilen Danışman, bu konfigürasyonu her proje analizinde, birincil anahtarları olmayan veya indekslenmemiş yabancı anahtarları olmayan tablolarla aynı şekilde kontrol eder. Bu, çok geç keşfedilen sessiz bir bozulmadan ziyade açık bir sinyaldir.

Üretimdeki gecikme sorunlarının çoğu ne CPU'dan ne de RAM'den kaynaklanır: eksik veya kötü seçilmiş bir dizinden kaynaklanırlar. Tek bir postgresql.confparametresine dokunmadan önce, söz konusu sorgudaki EXPLAIN (ANALYZE, BUFFERS) en güvenilir teşhis olmaya devam ediyor. Kaynak eklemeden önce Postgres istek gecikmesini oldukça azaltır.

-- Yapılandırmaya dokunmadan önce yavaş bir sorguyu teşhis edin
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

Index Scan beklendiği multimilyonluk satır tablosundaki Seq Scan, hemen hemen her zaman bir dizin sorununa işaret eder. En sık üç neden ortaya çıkar: eksik bir dizin, mevcut dizinle uyumsuz bir sütun türü veya ANALYZEolmadan büyük bir içe aktarma işleminden sonra eski istatistikler. RAM eklemek veya work_mem değerini artırmak bazen bu belirtiyi küçük bir veri hacminde gizler; tablo büyüdükçe sorun yeniden ortaya çıkıyor.

Bu istekleri tek tek aramadan bulmak için pg_stat_statements, sunucunun tüm isteklerinin yürütme istatistiklerini toplar. Yaygın bir tuzak: Uzantının ilk önce yeniden başlatma gerektiren bir "postmaster" bağlam parametresi olan shared_preload_librariesiçinde listelenmesi gerekir. Bu adım olmadan CREATE EXTENSION pg_stat_statements; sessizce başarılı olur ancak hiçbir şey toplamaz.

# Tam bir yeniden başlatma gerektirir ("postmaster" parametresi)
shared_preload_libraries = 'pg_stat_statements'

-- Sunucu yeniden başlatıldığında, denetlenecek her veritabanında:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Bu, tam olarak bu uzantı olmadığında Aurabase arka ucunun döndürdüğü hatadır: "yavaş sorgu yok" ile karıştırılabilecek sessiz, boş bir liste yerine açık bir mesaj. Studio Advisor daha da ileri giderek, ortalama süresi 150 ms'nin üzerinde olan tüm istekleri otomatik olarak uyarı, 500 ms'nin üzerinde ise kritik olarak sınıflandırır. Bu eşikler aynı pg_stat_statementsistatistiklerini temel alır.

Bir kontrol noktası Postgres'i öncekinden bu yana bellekte değiştirilen tüm sayfaları diske yazmaya zorlar. Varsayılan olarak bu yazı çok kısa bir zaman aralığına odaklanabilir. Sonuç, uygulama tarafında disk gecikmesinde gözle görülür bir artıştır; belirli bir istekle ilişkilendirilmesi zor olan periyodik yavaşlama türüdür.

checkpoint_completion_target bu yazının iki kontrol noktası arasındaki aralığa yayılmasını kontrol eder. Eski kontrol listelerinde eksik olan bir ayrıntı: 2021'de yayınlanan PostgreSQL 14, varsayılan değerini 0,5'ten 0,9'a değiştirdi. Bu nedenle, özel Aurabase kiracı kümeleri gibi bir PostgreSQL 16 örneğinde bu ayar varsayılan olarak zaten doğrudur; manuel olarak ayarlamak yalnızca 14'ten önceki bir sürümde anlamlıdır. Ayarlamayı etkileyen diğer sürüm değişiklikleri için PostgreSQL 16 vs 17 vs 18 karşılaştırmamıza bakın.

max_wal_size aynı yönde hareket eder: çok düşük bir değer, checkpoint_timeout henüz ulaşılmasa bile beklenenden daha sık denetim noktaları tetikler. Bunu artırmak, tekrar oynatılacak daha fazla WAL olduğundan, bir çökme sonrasında daha uzun iyileşme süresi pahasına kontrol noktalarının sıklığını azaltır. Evrensel bir değer değil, erişilemezliğe karşı toleransınıza göre karar verilecek bir uzlaşma.

Bu kontrol listesi, üretime geçmeden önce bir kez kontrol edilecek tek seferlik bir denetim değildir. Hacmi iki katına çıkan bir taban veya üçe katlanan trafik, başlangıçta seçilen kriterlerin geçerliliğini yitirmesine neden olur, çoğu zaman açık bir hata olmadan, yalnızca p95 gecikmesinin aşamalı olarak bozulması.

Üç sinyal sürekli izlemeyi hak ediyor. pg_stat_statements zamanla bozulan istekleri tanımlar. pg_stat_activity, engellenen veya anormal derecede uzun süren sorguları bildirir ve etkin bağlantıların max_connections'ye oranı, uygulama tarafında hatalar üretmeden önce doygunluğu öngörür.

Studio'nun Gözlemlenebilirlik sekmesi, herhangi bir Aurabase projesi için bu temelin bir kısmını kapsar ve hiçbir üçüncü taraf aracının yüklenmesini gerektirmez. Yavaş istekleri listeler, etkin bir isteği PID ile iptal etmenize veya sonlandırmanıza olanak tanır ve %80 kullanımın üzerinde uyarı veren bir havuz doygunluk göstergesi görüntüler. Şirket içinde barındırılan bir örnekte, aynı izleme, pg_stat_statements etkinleştirilerek ve harici bir izleme aracı takılı olarak elle oluşturulur.

Dokunmadan önce bilmeniz gerekenleri içeren, en çok kazandırdıkları sıraya göre sekiz ayar.

Aurabase Studio Advisor'ın izlediği 150 ms ve %80 havuz doygunluğu gibi burada belirtilen eşikler, evrensel bir gerçek değil, kodla doğrulanmış bir başlangıç noktasıdır. Asıl suçlamanız tek nihai yargıç olmaya devam edecek. Genel bir kurala uymak yerine max_connections boyutunu tam olarak belirlemek için, özel makalesi formülün ve sınırlarının ayrıntılarını verir.

Kontrol listesinin ilk kez uygulanmasıyla sistematik olarak ortaya çıkan üç soru.