VPS Üzerinde PostgreSQL İçin Ölçülebilir Bir Başlangıç
Bir VPS üzerinde rapor sorgusunun web uygulamasını yavaşlattığını düşünün. Neden bellek baskısı, disk beklemesi, kilit, bağlantı yoğunluğu veya sorgu planı olabilir. Ayar değiştirmeden önce aynı zaman aralığındaki sorgu, işletim sistemi ve disk ölçümlerini karşılaştırın. Varsayılan ayarların bulunması tek başına sorunun nedeni değildir.
Bu rehber shared_buffers, work_mem, WAL/checkpoint ve PgBouncer kararlarını birlikte ele alır. PostgreSQL açıklamaları sürüm 17 belgelerine, PgBouncer örnekleri güncel 1.26 belgelerine dayanır; kurulu sürümünüzle karşılaştırın. Sayısal örnekler 4 GiB RAM’li, kaynakları ağırlıklı olarak PostgreSQL’e ayrılmış bir VPS için değerlendirme adaylarıdır. Aynı makinedeki uygulama, işletim sistemi ve havuzlayıcının ölçülen ihtiyacı arttıkça veritabanına ayrılabilecek bütçe azalır.
shared_buffers ve effective_cache_size İçin Bütçe
shared_buffers, PostgreSQL süreçlerinin paylaştığı veri ve indeks sayfası önbelleğidir. Buradaki bir kaçırma fiziksel disk okumasını zorunlu kılmaz; işletim sistemi önbelleği de okumayı karşılayabilir. PostgreSQL belgelerindeki yaklaşık yüzde 25 başlangıç önerisi, en az 1 GB RAM’li ayrılmış veritabanı sunucusu içindir. Aynı VPS’te başka hizmetler çalışıyorsa bu oranı otomatik uygulamayın.
Örnek bütçede 1 GiB shared_buffers ayrılır. Kalan 3 GiB; işletim sistemi, dosya önbelleği, bağlantı süreçleri, sorgu çalışma alanları, bakım ve diğer hizmetler arasında paylaşılır. Tekrarlanan sıcak veri mevcut önbelleğe sığmıyorsa artış yararlı olabilir; bellek baskısı veya başka bir darboğaz varsa daha büyük değer sonucu iyileştirmeyebilir. Yük altında sorgu gecikmesini ve toplam bellek kullanımını karşılaştırın.
Aşağıdaki 2 GiB önbellek tahmini, 1 GiB PostgreSQL önbelleğine ek olarak yük altında kullanılabilir işletim sistemi önbelleği bulunduğu varsayımına dayanır; çakışan sayfaları ve eşzamanlı sorguları hesaba katın. Tahmini ölçümlere göre değiştirin. shared_buffers değişikliği PostgreSQL yeniden başlatması gerektirir.
# Active postgresql.conf; locate it with SHOW config_file;
shared_buffers = 1GB
# Planner estimate including shared and usable OS cache; reserves no RAM
effective_cache_size = 2GB
effective_cache_size bellek ayırmaz veya işletim sisteminde önbellek oluşturmaz; planlayıcının bir sorgu için kullanılabilir disk önbelleği varsayımıdır. Aşırı iyimser ya da düşük bir tahmin plan seçimini etkileyebilir. PostgreSQL önbellek isabet oranını disk gecikmesi ve işletim sistemi ölçümleriyle birlikte değerlendirin; tek bir oranı yükseltmek başlı başına amaç değildir.
PostgreSQL bellek ayarları referansını kurulu sürümünüz için okuyun. Yapılandırma dosyasındaki değerle etkin değerin aynı olduğunu, değişikliğin yeniden başlatma mı yoksa yeniden yükleme mi gerektirdiğini ayrıca doğrulayın.
work_mem: İşlem ve Eşzamanlılık Çarpanları
work_mem, sorgu başına tek bir tahsis değil; sıralama veya hash gibi sorgu işlemlerinin temel bellek sınırıdır ve baştan bütünüyle ayrılmaz. Bir sorguda birden fazla işlem aynı anda çalışabilir. Hash sınırı work_mem × hash_mem_multiplier ile belirlenir; PostgreSQL 17 varsayılan çarpanı 2’dir. Paralel çalışanların ayrı bütçeleri ve eşzamanlı sorgular toplam tüketimi artırır.
Yalnız ölçeği göstermek için, paralellik olmadan aynı anda 20 sorgunun ikişer hash işlemi yürüttüğünü varsayın. work_mem 4 MiB ve hash çarpanı 2 iken bu işlemler için yaklaşık 320 MiB bütçe gerekir. Bu, sunucunun toplam bellek sınırı değildir: süreçler, başka plan düğümleri, bakım, önbellek ve diğer hizmetler ayrıca yer kullanır. Gerçek plan ve eşzamanlılık farklıysa hesap değişir.
Genel değeri artırmadan önce geçici dosyalara taşmayı ve ilgili sorgu planını doğrulayın. İndeks veya sorgu düzenlemesi sorunu daha az bellekle çözebilir. Aşağıdaki örnekte sorgu, elle bakım ve autovacuum bütçeleri ayrı gösterilir; üç autovacuum çalışanı 64 MiB sınırla yaklaşık 192 MiB çalışma alanı kullanabilir. Bu sınırlara süreç ek yükü dahil değildir.
# Illustrative inputs for the measured 4 GiB budget
work_mem = 4MB
maintenance_work_mem = 256MB
autovacuum_work_mem = 64MB
Yalnız belirli bir rapor için artış gerekiyorsa açık işlem içinde SET LOCAL kullanın. Düz SET, işlem bittiğinde kendiliğinden geri alınmaz ve transaction havuzlamasına uygun oturum yönetimi sayılmaz. Aşağıdaki iskelet rapor sorgusunu içermez; 128 MiB yalnız sınanacak örnek değerdir, hash ve paralellik çarpanları yine geçerlidir.
BEGIN;
SET LOCAL work_mem = '128MB';
-- Run the reviewed report query here, within this transaction.
COMMIT;
SET LOCAL etkisi COMMIT veya ROLLBACK ile biter. İşlem açıkken istemciyi boşta bırakmayın. Bakım komutları için work_mem yerine ilgili maintenance_work_mem davranışını değerlendirin; autovacuum_work_mem -1 ise her autovacuum çalışanı bakım değerini miras alır. Rol varsayılanları yalnız yeni sunucu oturumlarına uygulanır; mevcut havuz bağlantılarını değiştirmiş sayılmazsınız.
WAL ve Checkpoint: Disk Alanı ile Kalıcılık
WAL, değişen veri sayfaları kalıcı depolamaya yazılmadan önce ilgili günlük kayıtlarının kalıcı depolamaya yazılmasını sağlar. Bellek içindeki her değişiklikten önce diske yazım yapıldığı anlamına gelmez. Kurtarma ve kalıcılık, doğru yapılandırma ve depolamanın yazım garantilerine bağlıdır. Performans denemesi için fsync veya full_page_writes korumasını kapatmayın.
Sık checkpoint, disk yazımını ve tam sayfa WAL kayıtlarını artırabilir. Daha büyük max_wal_size veya daha uzun checkpoint_timeout bazı iş yüklerinde sıklığı azaltabilir; ancak boş disk alanı ve kurtarma süresi bütçesi gerektirir. max_wal_size katı disk kullanım sınırı değildir. Yoğun WAL üretimi zaman aşımından önce checkpoint başlatabilir; arşivleme hatası veya çoğaltma tutma gereksinimi WAL birikimine yol açabilir.
Aşağıdaki değerler yazma hacmi ve kurtarma hedefiyle sınanacak örneklerdir. replica, fiziksel çoğaltma/arşivleme için gereken bilgiyi üretir; tek başına yedek veya PITR kurmaz. Mantıksal çözümleme kullanan sistemde logical gereksinimini koruyun. wal_level değişikliği yeniden başlatma gerektirir.
# postgresql.conf: workload-dependent candidates
wal_level = replica
max_wal_size = 2GB
min_wal_size = 256MB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
synchronous_commit = on
# Compresses full-page WAL images; compare CPU and WAL volume
wal_compression = on
wal_compression = on, PostgreSQL 17’de tam sayfa görüntülerini pglz ile sıkıştırır; tüm WAL kayıtlarını sıkıştırmaz ve CPU maliyeti vardır. synchronous_commit = on yerel WAL kalıcılığını bekler; senkron standby yapılandırılmışsa onun onayını da etkiler. off, yakın zamanda onaylanmış işlemlerin çökmede kaybına yol açabilir; kayıp aralığı evrensel olarak birkaç milisaniye değildir. remote_write yerel kalıcılığı kaldırmaz, standby’ın dosya sistemine yazmasını bekler; standby işletim sistemi çökmesine karşı aynı kalıcılığı sağlamaz. Bu ayar tablo bazlı değil, commit sırasındaki işlem/oturum kapsamındadır.
WAL ayarları referansındaki kalıcılık ve yeniden başlatma koşullarını kurulu sürümle karşılaştırın. Değişikliği değerlendirirken yalnız gecikmeyi değil; WAL birikimini, arşivleme/çoğaltma durumunu, disk boşluğunu ve kurtarma hedefini de izleyin.
PgBouncer: Havuz, Kimlik Doğrulama ve Kuyruk
PgBouncer istemciler ile PostgreSQL arasında sunucu bağlantılarını yeniden kullanır. Kısa ömürlü bağlantıların kurulum maliyetini ve açık sunucu bağlantısı sayısını azaltabilir; yavaş sorguyu veya kilidi kendi başına çözmez. Havuz doluysa istekler bekler. Uygulama zaman aşımı, kuyruk süresi ve gerçek işlem hızını birlikte ölçün.
default_pool_size kullanıcı/veritabanı çifti başınadır; reserve_pool_size ek kapasite sağlar. Örnekte tek appdb/appuser havuzu 20 normal ve 5 yedek bağlantı kullanabilir; max_db_connections aynı PgBouncer veritabanı takma adı için 25 sınırı koyar. max_client_conn = 100 istemci kabul sınırıdır, 100 etkin PostgreSQL sorgusu değildir. Tüm havuzlayıcılar, takma adlar, doğrudan bağlantılar ve yönetim rezervleri PostgreSQL max_connections bütçesine dahil edilmelidir.
Örnek, uygulama, PgBouncer ve PostgreSQL’in aynı makinede IPv4 loopback üzerinden konuştuğu mevcut bir servis kurulumunun parçasıdır. PostgreSQL’de appdb ve yalnız gerekli yetkileri olan appuser önceden oluşturulmuş olmalıdır. İstemci SCRAM desteklemeli; PostgreSQL’in etkin pg_hba.conf kuralı appdb/appuser için 127.0.0.1/32 adresinden SCRAM doğrulaması istemelidir. İlk eşleşen HBA kaydını kontrol edin; dosyanın tamamını bu örnekle değiştirmeyin.
; Existing service configuration excerpt; local clients only
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir =
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 25
server_idle_timeout = 300
max_prepared_statements = 100
auth_file içinde çift tırnaklı kullanıcı adı ve tam SCRAM doğrulayıcısı bulunur; biçim şöyledir. Köşeli yer tutucular gerçek bir parola veya çalışır doğrulayıcı değildir:
"appuser" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"
Yetkili yönetici PostgreSQL’deki aynı doğrulayıcıyı, salt ve iterasyonlar dahil birebir eşit olacak şekilde güvenli hazırlama süreciyle bu dosyaya aktarmalıdır. Aynı paroladan ayrı üretilmiş iki doğrulayıcı yeterli değildir. Dosyayı yalnız PgBouncer servis hesabı ve yetkili yönetici okuyabilsin; gerçek değerleri günlük, sohbet veya komut geçmişine taşımayın. Veritabanı satırında zorunlu user/password bulunmaması SCRAM aktarımı için bilinçlidir. Servisin mevcut başlatma, günlük ve izin ayarlarını koruyun; yeni bir istemciyle uçtan uca bağlantı testini tamamlamadan trafiği taşımayın.
transaction modu sunucu bağlantısını işlem sonunda havuza verir; sonraki işlem başka bağlantıya gidebilir. Oturum düzeyindeki SET/RESET, LISTEN, oturum danışma kilitleri ve korunan geçici tablolar gibi bağımlılıkları test edin; gerekiyorsa session havuzlaması veya ayrı doğrudan yol kullanın. Protokol düzeyindeki adlandırılmış prepared statement desteği, uyumlu sürüm ve pozitif max_prepared_statements gerektirir; örnekteki 100 sınırı iş yüküyle ayarlanmalıdır. SQL PREPARE aynı destek değildir. server_reset_query transaction modunda çalışmaz; DISCARD ALL eklemek uyumsuz oturum durumunu düzeltmez.
PgBouncer yapılandırma referansındaki havuz, zaman aşımı ve dosya tanıtıcısı sınırlarını kontrol edin. Örnekte Unix soketi kapalıdır; yalnız 127.0.0.1:6432 dinlenir. Başka makinelerdeki uygulamalar için ağ erişimi, TLS kimlik doğrulaması ve iki bağlantı ayağı ayrıca tasarlanmalıdır. INI yorumlarını ayrı satırda tutun; satır içindeki noktalı virgül yorum sayılmaz.
Etkin Ayarlar, İzleme ve Uygulama Sırası
Önce normal ve yoğun dönemleri kapsayan bir başlangıç ölçümü alın: sorgu gecikmeleri, disk beklemesi, etkin bağlantılar, havuz kuyruğu, geçici dosyalar, WAL hacmi ve autovacuum ilerlemesi. Birikimli sayaçlarda zaman aralığı farkını ve sıfırlanma zamanını kullanın. PostgreSQL 17’de checkpoint ölçümlerini pg_stat_checkpointer üzerinden inceleyin; sürümler arasında görünüm alanları değişebilir.
Prometheus ve Grafana ile VPS izleme rehberi altyapı ölçümlerini toplamaya yardımcı olur. PostgreSQL ve PgBouncer ölçümlerini aynı zaman çizelgesine ekleyin. Merkezi loglama rehberindeki yaklaşımı değerlendirirken kurulu toplayıcının destek durumunu kontrol edin; sorgu metinleri ve kimlik bilgileri için erişim ve saklama sınırları belirleyin.
Yeniden başlatma gerektiren değişiklikleri uygun bakım planıyla uygulayın; dosyayı yeniden yüklemek her parametreyi etkinleştirmez. VPS CI/CD dağıtım rehberi dosya sürümleme için yararlı olabilir, ancak uygulama symlink değişimi PostgreSQL yeniden başlatmasını kesintisiz hâle getirmez.
Aşağıdaki sorgular yapılandırma ve anlık durum okumalarıdır; bir değişikliğin performansı iyileştirdiğini kanıtlamaz. pg_settings içindeki context ve pending_restart değerlerini, ayarın beklediğiniz kapsamda etkin olduğunu doğrulamak için kullanın.
SHOW config_file;
SHOW shared_buffers;
SHOW work_mem;
SHOW checkpoint_timeout;
SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'max_connections', 'work_mem',
'wal_level', 'checkpoint_timeout');
SELECT pid, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY pid
LIMIT 5;
Autovacuum yalnız sakin saatlerde çalışan bakım değildir. Ölü satırlar, uzun işlemler, temizleme ilerlemesi ve işlem kimliği yaşı izlenmelidir. Yoğun tablolarda eşik veya maliyet ayarı değiştirirken tüm çalışanların toplam bellek ve I/O bütçesini değerlendirin. Bakımı durdurmak bellek sorununu çözmüş sayılmaz; geciken iş daha sonra büyüyebilir.
Geliştirme, staging ve canlı ortam dağıtım rehberindeki aşamalı yaklaşımı veritabanına uyarlarken uygulama uyumluluğunu, bağlantıların yeniden kurulmasını ve veri kurtarmasını ayrıca sınayın. Yapılandırma geri alma planı, tutarlı yedek ve doğrulanmış geri yükleme sürecinin yerine geçmez.
Sorgu Planları ve İndeksleri Birlikte İnceleyin
Yükün çoğunu oluşturan sorguları belirleyin; eksik indeks, fazla satır okuma veya uzun kilit bekleme yalnız bellek ayarıyla çözülmeyebilir. Önce EXPLAIN ile planı inceleyin. EXPLAIN ANALYZE sorguyu gerçekten çalıştırır; değişiklik yapan veya pahalı sorgularda yan etkileri ve yükü kontrollü test ortamında değerlendirin. Yeni indeksi gerçek plan ve yazma maliyetiyle karşılaştırın.
Örneğin ürünleri kategori ve yayında olma durumuna göre süzen bir sorguda bileşik ya da kısmi indeks değerlendirilebilir. Doğru seçim; veri dağılımına, sorgunun koşullarına, sıralamasına ve döndürdüğü satır sayısına bağlıdır. İndeksin varlığı kullanılacağını garanti etmez; ölçümde plan, okunan bloklar ve işlem süresini birlikte inceleyin.
WAL yaklaşımının resmi açıklaması, veri sayfası yazımı ile günlük kalıcılığını ayırır. Sorgu iyileştirmesi, bellek bütçesi ve kalıcılık farklı karar alanlarıdır; birindeki hızlanmayı diğerinin güvencesi olarak değerlendirmeyin.
Tek Bir Ölçülebilir Değişiklikle İlerleyin
Önce darboğazı belirleyin, sonra ilgili ayarı seçin. Bellek değişikliğinde işlem sayısı ve eşzamanlılığı; WAL değişikliğinde disk alanı ve kurtarmayı; havuz değişikliğinde uygulama uyumluluğu ve kuyruk süresini sınayın. Aynı yük altında önceki sonuçla karşılaştırın ve kötüleşme ölçütüne ulaşıldığında son doğrulanmış yapılandırmaya dönün.
Bir iş döngüsünü ve planlı raporları kapsayan gözlem yapın; yalnız düşük trafik saati yeterli olmayabilir. Sürümü, değişen parametreyi, gerekçeyi ve ölçümü kaydedin. HTTPS ve ters proxy ayarlarını ele alan rehber diğer katmanlar için bağlam sağlayabilir; bu katmanlardaki değişiklikleri aynı performans denemesinde karıştırmayın.
Son karar, beklenen yük altında RAM sınırları içinde kalmak, kabul edilen gecikmeyi sağlamak ve veri kalıcılığı koşullarını korumaktır. Bir sonraki değişikliğe geçmeden önce etkin değerleri, uygulama davranışını ve geri alma yolunu doğrulayın.
