{"id":1534,"date":"2025-11-08T12:25:59","date_gmt":"2025-11-08T09:25:59","guid":{"rendered":"https:\/\/www.dchost.com\/blog\/vpste-postgresqli-ucurmak-shared_buffers-work_mem-wal-ve-pgbounceri-ne-zaman-nasil-ayarlariz\/"},"modified":"2026-11-04T11:14:55","modified_gmt":"2026-11-04T08:14:55","slug":"vps-postgresql-bellek-wal-pgbouncer","status":"publish","type":"post","link":"https:\/\/www.dchost.com\/blog\/vps-postgresql-bellek-wal-pgbouncer\/","title":{"rendered":"VPS\u2019te PostgreSQL: Bellek, WAL ve PgBouncer Ayarlar\u0131"},"content":{"rendered":"

VPS \u00dczerinde PostgreSQL \u0130\u00e7in \u00d6l\u00e7\u00fclebilir Bir Ba\u015flang\u0131\u00e7<\/h2>\n

Bir VPS<\/a> \u00fczerinde rapor sorgusunun web uygulamas\u0131n\u0131 yava\u015flatt\u0131\u011f\u0131n\u0131 d\u00fc\u015f\u00fcn\u00fcn. Neden bellek bask\u0131s\u0131, disk beklemesi, kilit, ba\u011flant\u0131 yo\u011funlu\u011fu veya sorgu plan\u0131 olabilir. Ayar de\u011fi\u015ftirmeden \u00f6nce ayn\u0131 zaman aral\u0131\u011f\u0131ndaki sorgu, i\u015fletim sistemi ve disk \u00f6l\u00e7\u00fcmlerini kar\u015f\u0131la\u015ft\u0131r\u0131n. Varsay\u0131lan ayarlar\u0131n bulunmas\u0131 tek ba\u015f\u0131na sorunun nedeni de\u011fildir.<\/p>\n

Bu rehber shared_buffers, work_mem, WAL\/checkpoint ve PgBouncer kararlar\u0131n\u0131 birlikte ele al\u0131r. PostgreSQL a\u00e7\u0131klamalar\u0131 s\u00fcr\u00fcm 17 belgelerine, PgBouncer \u00f6rnekleri g\u00fcncel 1.26 belgelerine dayan\u0131r; kurulu s\u00fcr\u00fcm\u00fcn\u00fczle kar\u015f\u0131la\u015ft\u0131r\u0131n. Say\u0131sal \u00f6rnekler 4 GiB RAM\u2019li, kaynaklar\u0131 a\u011f\u0131rl\u0131kl\u0131 olarak PostgreSQL\u2019e ayr\u0131lm\u0131\u015f bir VPS i\u00e7in de\u011ferlendirme adaylar\u0131d\u0131r. Ayn\u0131 makinedeki uygulama, i\u015fletim sistemi ve havuzlay\u0131c\u0131n\u0131n \u00f6l\u00e7\u00fclen ihtiyac\u0131 artt\u0131k\u00e7a veritaban\u0131na ayr\u0131labilecek b\u00fct\u00e7e azal\u0131r.<\/p>\n

shared_buffers ve effective_cache_size \u0130\u00e7in B\u00fct\u00e7e<\/h2>\n

shared_buffers, PostgreSQL s\u00fcre\u00e7lerinin payla\u015ft\u0131\u011f\u0131 veri ve indeks sayfas\u0131 \u00f6nbelle\u011fidir. Buradaki bir ka\u00e7\u0131rma fiziksel disk okumas\u0131n\u0131 zorunlu k\u0131lmaz; i\u015fletim sistemi \u00f6nbelle\u011fi de okumay\u0131 kar\u015f\u0131layabilir. PostgreSQL belgelerindeki yakla\u015f\u0131k y\u00fczde 25 ba\u015flang\u0131\u00e7 \u00f6nerisi, en az 1 GB RAM\u2019li ayr\u0131lm\u0131\u015f veritaban\u0131 sunucusu i\u00e7indir. Ayn\u0131 VPS\u2019te ba\u015fka hizmetler \u00e7al\u0131\u015f\u0131yorsa bu oran\u0131 otomatik uygulamay\u0131n.<\/p>\n

\u00d6rnek b\u00fct\u00e7ede 1 GiB shared_buffers ayr\u0131l\u0131r. Kalan 3 GiB; i\u015fletim sistemi, dosya \u00f6nbelle\u011fi, ba\u011flant\u0131 s\u00fcre\u00e7leri, sorgu \u00e7al\u0131\u015fma alanlar\u0131, bak\u0131m ve di\u011fer hizmetler aras\u0131nda payla\u015f\u0131l\u0131r. Tekrarlanan s\u0131cak veri mevcut \u00f6nbelle\u011fe s\u0131\u011fm\u0131yorsa art\u0131\u015f yararl\u0131 olabilir; bellek bask\u0131s\u0131 veya ba\u015fka bir darbo\u011faz varsa daha b\u00fcy\u00fck de\u011fer sonucu iyile\u015ftirmeyebilir. Y\u00fck alt\u0131nda sorgu gecikmesini ve toplam bellek kullan\u0131m\u0131n\u0131 kar\u015f\u0131la\u015ft\u0131r\u0131n.<\/p>\n

A\u015fa\u011f\u0131daki 2 GiB \u00f6nbellek tahmini, 1 GiB PostgreSQL \u00f6nbelle\u011fine ek olarak y\u00fck alt\u0131nda kullan\u0131labilir i\u015fletim sistemi \u00f6nbelle\u011fi bulundu\u011fu varsay\u0131m\u0131na dayan\u0131r; \u00e7ak\u0131\u015fan sayfalar\u0131 ve e\u015fzamanl\u0131 sorgular\u0131 hesaba kat\u0131n. Tahmini \u00f6l\u00e7\u00fcmlere g\u00f6re de\u011fi\u015ftirin. shared_buffers de\u011fi\u015fikli\u011fi PostgreSQL yeniden ba\u015flatmas\u0131 gerektirir.<\/p>\n

# Active postgresql.conf; locate it with SHOW config_file;\nshared_buffers = 1GB\n# Planner estimate including shared and usable OS cache; reserves no RAM\neffective_cache_size = 2GB\n<\/code><\/pre>\n
effective_cache_size bellek ay\u0131rmaz veya i\u015fletim sisteminde \u00f6nbellek olu\u015fturmaz; planlay\u0131c\u0131n\u0131n bir sorgu i\u00e7in kullan\u0131labilir disk \u00f6nbelle\u011fi varsay\u0131m\u0131d\u0131r. A\u015f\u0131r\u0131 iyimser ya da d\u00fc\u015f\u00fck bir tahmin plan se\u00e7imini etkileyebilir. PostgreSQL \u00f6nbellek isabet oran\u0131n\u0131 disk gecikmesi ve i\u015fletim sistemi \u00f6l\u00e7\u00fcmleriyle birlikte de\u011ferlendirin; tek bir oran\u0131 y\u00fckseltmek ba\u015fl\u0131 ba\u015f\u0131na ama\u00e7 de\u011fildir.<\/p>\n
PostgreSQL bellek ayarlar\u0131 referans\u0131n\u0131<\/a> kurulu s\u00fcr\u00fcm\u00fcn\u00fcz i\u00e7in okuyun. Yap\u0131land\u0131rma dosyas\u0131ndaki de\u011ferle etkin de\u011ferin ayn\u0131 oldu\u011funu, de\u011fi\u015fikli\u011fin yeniden ba\u015flatma m\u0131 yoksa yeniden y\u00fckleme mi gerektirdi\u011fini ayr\u0131ca do\u011frulay\u0131n.<\/p>\n
work_mem: \u0130\u015flem ve E\u015fzamanl\u0131l\u0131k \u00c7arpanlar\u0131<\/h2>\n
work_mem, sorgu ba\u015f\u0131na tek bir tahsis de\u011fil; s\u0131ralama veya hash gibi sorgu i\u015flemlerinin temel bellek s\u0131n\u0131r\u0131d\u0131r ve ba\u015ftan b\u00fct\u00fcn\u00fcyle ayr\u0131lmaz. Bir sorguda birden fazla i\u015flem ayn\u0131 anda \u00e7al\u0131\u015fabilir. Hash s\u0131n\u0131r\u0131 work_mem \u00d7 hash_mem_multiplier ile belirlenir; PostgreSQL 17 varsay\u0131lan \u00e7arpan\u0131 2\u2019dir. Paralel \u00e7al\u0131\u015fanlar\u0131n ayr\u0131 b\u00fct\u00e7eleri ve e\u015fzamanl\u0131 sorgular toplam t\u00fcketimi art\u0131r\u0131r.<\/p>\n
Yaln\u0131z \u00f6l\u00e7e\u011fi g\u00f6stermek i\u00e7in, paralellik olmadan ayn\u0131 anda 20 sorgunun iki\u015fer hash i\u015flemi y\u00fcr\u00fctt\u00fc\u011f\u00fcn\u00fc varsay\u0131n. work_mem 4 MiB ve hash \u00e7arpan\u0131 2 iken bu i\u015flemler i\u00e7in yakla\u015f\u0131k 320 MiB b\u00fct\u00e7e gerekir. Bu, sunucunun toplam bellek s\u0131n\u0131r\u0131 de\u011fildir: s\u00fcre\u00e7ler, ba\u015fka plan d\u00fc\u011f\u00fcmleri, bak\u0131m, \u00f6nbellek ve di\u011fer hizmetler ayr\u0131ca yer kullan\u0131r. Ger\u00e7ek plan ve e\u015fzamanl\u0131l\u0131k farkl\u0131ysa hesap de\u011fi\u015fir.<\/p>\n
Genel de\u011feri art\u0131rmadan \u00f6nce ge\u00e7ici dosyalara ta\u015fmay\u0131 ve ilgili sorgu plan\u0131n\u0131 do\u011frulay\u0131n. \u0130ndeks veya sorgu d\u00fczenlemesi sorunu daha az bellekle \u00e7\u00f6zebilir. A\u015fa\u011f\u0131daki \u00f6rnekte sorgu, elle bak\u0131m ve autovacuum b\u00fct\u00e7eleri ayr\u0131 g\u00f6sterilir; \u00fc\u00e7 autovacuum \u00e7al\u0131\u015fan\u0131 64 MiB s\u0131n\u0131rla yakla\u015f\u0131k 192 MiB \u00e7al\u0131\u015fma alan\u0131 kullanabilir. Bu s\u0131n\u0131rlara s\u00fcre\u00e7 ek y\u00fck\u00fc dahil de\u011fildir.<\/p>\n
# Illustrative inputs for the measured 4 GiB budget\nwork_mem = 4MB\nmaintenance_work_mem = 256MB\nautovacuum_work_mem = 64MB\n<\/code><\/pre>\n
Yaln\u0131z belirli bir rapor i\u00e7in art\u0131\u015f gerekiyorsa a\u00e7\u0131k i\u015flem i\u00e7inde SET LOCAL kullan\u0131n. D\u00fcz SET, i\u015flem bitti\u011finde kendili\u011finden geri al\u0131nmaz ve transaction havuzlamas\u0131na uygun oturum y\u00f6netimi say\u0131lmaz. A\u015fa\u011f\u0131daki iskelet rapor sorgusunu i\u00e7ermez; 128 MiB yaln\u0131z s\u0131nanacak \u00f6rnek de\u011ferdir, hash ve paralellik \u00e7arpanlar\u0131 yine ge\u00e7erlidir.<\/p>\n
BEGIN;\nSET LOCAL work_mem = '128MB';\n-- Run the reviewed report query here, within this transaction.\nCOMMIT;\n<\/code><\/pre>\n
SET LOCAL etkisi COMMIT veya ROLLBACK ile biter. \u0130\u015flem a\u00e7\u0131kken istemciyi bo\u015fta b\u0131rakmay\u0131n. Bak\u0131m komutlar\u0131 i\u00e7in work_mem yerine ilgili maintenance_work_mem davran\u0131\u015f\u0131n\u0131 de\u011ferlendirin; autovacuum_work_mem -1 ise her autovacuum \u00e7al\u0131\u015fan\u0131 bak\u0131m de\u011ferini miras al\u0131r. Rol varsay\u0131lanlar\u0131 yaln\u0131z yeni sunucu oturumlar\u0131na uygulan\u0131r; mevcut havuz ba\u011flant\u0131lar\u0131n\u0131 de\u011fi\u015ftirmi\u015f say\u0131lmazs\u0131n\u0131z.<\/p>\n
WAL ve Checkpoint: Disk Alan\u0131 ile Kal\u0131c\u0131l\u0131k<\/h2>\n
WAL, de\u011fi\u015fen veri sayfalar\u0131 kal\u0131c\u0131 depolamaya yaz\u0131lmadan \u00f6nce ilgili g\u00fcnl\u00fck kay\u0131tlar\u0131n\u0131n kal\u0131c\u0131 depolamaya yaz\u0131lmas\u0131n\u0131 sa\u011flar. Bellek i\u00e7indeki her de\u011fi\u015fiklikten \u00f6nce diske yaz\u0131m yap\u0131ld\u0131\u011f\u0131 anlam\u0131na gelmez. Kurtarma ve kal\u0131c\u0131l\u0131k, do\u011fru yap\u0131land\u0131rma ve depolaman\u0131n yaz\u0131m garantilerine ba\u011fl\u0131d\u0131r. Performans denemesi i\u00e7in fsync veya full_page_writes korumas\u0131n\u0131 kapatmay\u0131n.<\/p>\n
S\u0131k checkpoint, disk yaz\u0131m\u0131n\u0131 ve tam sayfa WAL kay\u0131tlar\u0131n\u0131 art\u0131rabilir. Daha b\u00fcy\u00fck max_wal_size veya daha uzun checkpoint_timeout baz\u0131 i\u015f y\u00fcklerinde s\u0131kl\u0131\u011f\u0131 azaltabilir; ancak bo\u015f disk alan\u0131 ve kurtarma s\u00fcresi b\u00fct\u00e7esi gerektirir. max_wal_size kat\u0131 disk kullan\u0131m s\u0131n\u0131r\u0131 de\u011fildir. Yo\u011fun WAL \u00fcretimi zaman a\u015f\u0131m\u0131ndan \u00f6nce checkpoint ba\u015flatabilir; ar\u015fivleme hatas\u0131 veya \u00e7o\u011faltma tutma gereksinimi WAL birikimine yol a\u00e7abilir.<\/p>\n
A\u015fa\u011f\u0131daki de\u011ferler yazma hacmi ve kurtarma hedefiyle s\u0131nanacak \u00f6rneklerdir. replica, fiziksel \u00e7o\u011faltma\/ar\u015fivleme i\u00e7in gereken bilgiyi \u00fcretir; tek ba\u015f\u0131na yedek veya PITR kurmaz. Mant\u0131ksal \u00e7\u00f6z\u00fcmleme kullanan sistemde logical gereksinimini koruyun. wal_level de\u011fi\u015fikli\u011fi yeniden ba\u015flatma gerektirir.<\/p>\n
# postgresql.conf: workload-dependent candidates\nwal_level = replica\nmax_wal_size = 2GB\nmin_wal_size = 256MB\ncheckpoint_timeout = 15min\ncheckpoint_completion_target = 0.9\nsynchronous_commit = on\n# Compresses full-page WAL images; compare CPU and WAL volume\nwal_compression = on\n<\/code><\/pre>\n
wal_compression = on, PostgreSQL 17\u2019de tam sayfa g\u00f6r\u00fcnt\u00fclerini pglz ile s\u0131k\u0131\u015ft\u0131r\u0131r; t\u00fcm WAL kay\u0131tlar\u0131n\u0131 s\u0131k\u0131\u015ft\u0131rmaz ve CPU maliyeti vard\u0131r. synchronous_commit = on yerel WAL kal\u0131c\u0131l\u0131\u011f\u0131n\u0131 bekler; senkron standby yap\u0131land\u0131r\u0131lm\u0131\u015fsa onun onay\u0131n\u0131 da etkiler. off, yak\u0131n zamanda onaylanm\u0131\u015f i\u015flemlerin \u00e7\u00f6kmede kayb\u0131na yol a\u00e7abilir; kay\u0131p aral\u0131\u011f\u0131 evrensel olarak birka\u00e7 milisaniye de\u011fildir. remote_write yerel kal\u0131c\u0131l\u0131\u011f\u0131 kald\u0131rmaz, standby\u2019\u0131n dosya sistemine yazmas\u0131n\u0131 bekler; standby i\u015fletim sistemi \u00e7\u00f6kmesine kar\u015f\u0131 ayn\u0131 kal\u0131c\u0131l\u0131\u011f\u0131 sa\u011flamaz. Bu ayar tablo bazl\u0131 de\u011fil, commit s\u0131ras\u0131ndaki i\u015flem\/oturum kapsam\u0131ndad\u0131r.<\/p>\n
WAL ayarlar\u0131 referans\u0131ndaki<\/a> kal\u0131c\u0131l\u0131k ve yeniden ba\u015flatma ko\u015fullar\u0131n\u0131 kurulu s\u00fcr\u00fcmle kar\u015f\u0131la\u015ft\u0131r\u0131n. De\u011fi\u015fikli\u011fi de\u011ferlendirirken yaln\u0131z gecikmeyi de\u011fil; WAL birikimini, ar\u015fivleme\/\u00e7o\u011faltma durumunu, disk bo\u015flu\u011funu ve kurtarma hedefini de izleyin.<\/p>\n
PgBouncer: Havuz, Kimlik Do\u011frulama ve Kuyruk<\/h2>\n
PgBouncer istemciler ile PostgreSQL aras\u0131nda sunucu ba\u011flant\u0131lar\u0131n\u0131 yeniden kullan\u0131r. K\u0131sa \u00f6m\u00fcrl\u00fc ba\u011flant\u0131lar\u0131n kurulum maliyetini ve a\u00e7\u0131k sunucu ba\u011flant\u0131s\u0131 say\u0131s\u0131n\u0131 azaltabilir; yava\u015f sorguyu veya kilidi kendi ba\u015f\u0131na \u00e7\u00f6zmez. Havuz doluysa istekler bekler. Uygulama zaman a\u015f\u0131m\u0131, kuyruk s\u00fcresi ve ger\u00e7ek i\u015flem h\u0131z\u0131n\u0131 birlikte \u00f6l\u00e7\u00fcn.<\/p>\n
default_pool_size kullan\u0131c\u0131\/veritaban\u0131 \u00e7ifti ba\u015f\u0131nad\u0131r; reserve_pool_size ek kapasite sa\u011flar. \u00d6rnekte tek appdb\/appuser havuzu 20 normal ve 5 yedek ba\u011flant\u0131 kullanabilir; max_db_connections ayn\u0131 PgBouncer veritaban\u0131 takma ad\u0131 i\u00e7in 25 s\u0131n\u0131r\u0131 koyar. max_client_conn = 100 istemci kabul s\u0131n\u0131r\u0131d\u0131r, 100 etkin PostgreSQL sorgusu de\u011fildir. T\u00fcm havuzlay\u0131c\u0131lar, takma adlar, do\u011frudan ba\u011flant\u0131lar ve y\u00f6netim rezervleri PostgreSQL max_connections b\u00fct\u00e7esine dahil edilmelidir.<\/p>\n
\u00d6rnek, uygulama, PgBouncer ve PostgreSQL\u2019in ayn\u0131 makinede IPv4 loopback \u00fczerinden konu\u015ftu\u011fu mevcut bir servis kurulumunun par\u00e7as\u0131d\u0131r. PostgreSQL\u2019de appdb ve yaln\u0131z gerekli yetkileri olan appuser \u00f6nceden olu\u015fturulmu\u015f olmal\u0131d\u0131r. \u0130stemci SCRAM desteklemeli; PostgreSQL\u2019in etkin pg_hba.conf kural\u0131 appdb\/appuser i\u00e7in 127.0.0.1\/32 adresinden SCRAM do\u011frulamas\u0131 istemelidir. \u0130lk e\u015fle\u015fen HBA kayd\u0131n\u0131 kontrol edin; dosyan\u0131n tamam\u0131n\u0131 bu \u00f6rnekle de\u011fi\u015ftirmeyin.<\/p>\n
; Existing service configuration excerpt; local clients only\n[databases]\nappdb = host=127.0.0.1 port=5432 dbname=appdb\n\n[pgbouncer]\nlisten_addr = 127.0.0.1\nlisten_port = 6432\nunix_socket_dir =\nauth_type = scram-sha-256\nauth_file = \/etc\/pgbouncer\/userlist.txt\npool_mode = transaction\nmax_client_conn = 100\ndefault_pool_size = 20\nreserve_pool_size = 5\nreserve_pool_timeout = 5\nmax_db_connections = 25\nserver_idle_timeout = 300\nmax_prepared_statements = 100\n<\/code><\/pre>\n
auth_file i\u00e7inde \u00e7ift t\u0131rnakl\u0131 kullan\u0131c\u0131 ad\u0131 ve tam SCRAM do\u011frulay\u0131c\u0131s\u0131 bulunur; bi\u00e7im \u015f\u00f6yledir. K\u00f6\u015feli yer tutucular ger\u00e7ek bir parola veya \u00e7al\u0131\u015f\u0131r do\u011frulay\u0131c\u0131 de\u011fildir:<\/p>\n
"appuser" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"\n<\/code><\/pre>\n
Yetkili y\u00f6netici PostgreSQL\u2019deki ayn\u0131 do\u011frulay\u0131c\u0131y\u0131, salt ve iterasyonlar dahil birebir e\u015fit olacak \u015fekilde g\u00fcvenli haz\u0131rlama s\u00fcreciyle bu dosyaya aktarmal\u0131d\u0131r. Ayn\u0131 paroladan ayr\u0131 \u00fcretilmi\u015f iki do\u011frulay\u0131c\u0131 yeterli de\u011fildir. Dosyay\u0131 yaln\u0131z PgBouncer servis hesab\u0131 ve yetkili y\u00f6netici okuyabilsin; ger\u00e7ek de\u011ferleri g\u00fcnl\u00fck, sohbet veya komut ge\u00e7mi\u015fine ta\u015f\u0131may\u0131n. Veritaban\u0131 sat\u0131r\u0131nda zorunlu user\/password bulunmamas\u0131 SCRAM aktar\u0131m\u0131 i\u00e7in bilin\u00e7lidir. Servisin mevcut ba\u015flatma, g\u00fcnl\u00fck ve izin ayarlar\u0131n\u0131 koruyun; yeni bir istemciyle u\u00e7tan uca ba\u011flant\u0131 testini tamamlamadan trafi\u011fi ta\u015f\u0131may\u0131n.<\/p>\n
transaction modu sunucu ba\u011flant\u0131s\u0131n\u0131 i\u015flem sonunda havuza verir; sonraki i\u015flem ba\u015fka ba\u011flant\u0131ya gidebilir. Oturum d\u00fczeyindeki SET\/RESET, LISTEN, oturum dan\u0131\u015fma kilitleri ve korunan ge\u00e7ici tablolar gibi ba\u011f\u0131ml\u0131l\u0131klar\u0131 test edin; gerekiyorsa session havuzlamas\u0131 veya ayr\u0131 do\u011frudan yol kullan\u0131n. Protokol d\u00fczeyindeki adland\u0131r\u0131lm\u0131\u015f prepared statement deste\u011fi, uyumlu s\u00fcr\u00fcm ve pozitif max_prepared_statements gerektirir; \u00f6rnekteki 100 s\u0131n\u0131r\u0131 i\u015f y\u00fck\u00fcyle ayarlanmal\u0131d\u0131r. SQL PREPARE ayn\u0131 destek de\u011fildir. server_reset_query transaction modunda \u00e7al\u0131\u015fmaz; DISCARD ALL eklemek uyumsuz oturum durumunu d\u00fczeltmez.<\/p>\n
PgBouncer yap\u0131land\u0131rma referans\u0131ndaki<\/a> havuz, zaman a\u015f\u0131m\u0131 ve dosya tan\u0131t\u0131c\u0131s\u0131 s\u0131n\u0131rlar\u0131n\u0131 kontrol edin. \u00d6rnekte Unix soketi kapal\u0131d\u0131r; yaln\u0131z 127.0.0.1:6432 dinlenir. Ba\u015fka makinelerdeki uygulamalar i\u00e7in a\u011f eri\u015fimi, TLS kimlik do\u011frulamas\u0131 ve iki ba\u011flant\u0131 aya\u011f\u0131 ayr\u0131ca tasarlanmal\u0131d\u0131r. INI yorumlar\u0131n\u0131 ayr\u0131 sat\u0131rda tutun; sat\u0131r i\u00e7indeki noktal\u0131 virg\u00fcl yorum say\u0131lmaz.<\/p>\n
Etkin Ayarlar, \u0130zleme ve Uygulama S\u0131ras\u0131<\/h2>\n
\u00d6nce normal ve yo\u011fun d\u00f6nemleri kapsayan bir ba\u015flang\u0131\u00e7 \u00f6l\u00e7\u00fcm\u00fc al\u0131n: sorgu gecikmeleri, disk beklemesi, etkin ba\u011flant\u0131lar, havuz kuyru\u011fu, ge\u00e7ici dosyalar, WAL hacmi ve autovacuum ilerlemesi. Birikimli saya\u00e7larda zaman aral\u0131\u011f\u0131 fark\u0131n\u0131 ve s\u0131f\u0131rlanma zaman\u0131n\u0131 kullan\u0131n. PostgreSQL 17\u2019de checkpoint \u00f6l\u00e7\u00fcmlerini pg_stat_checkpointer \u00fczerinden inceleyin; s\u00fcr\u00fcmler aras\u0131nda g\u00f6r\u00fcn\u00fcm alanlar\u0131 de\u011fi\u015febilir.<\/p>\n
Prometheus ve Grafana ile VPS izleme rehberi<\/a> altyap\u0131 \u00f6l\u00e7\u00fcmlerini toplamaya yard\u0131mc\u0131 olur. PostgreSQL ve PgBouncer \u00f6l\u00e7\u00fcmlerini ayn\u0131 zaman \u00e7izelgesine ekleyin. Merkezi loglama rehberindeki<\/a> yakla\u015f\u0131m\u0131 de\u011ferlendirirken kurulu toplay\u0131c\u0131n\u0131n destek durumunu kontrol edin; sorgu metinleri ve kimlik bilgileri i\u00e7in eri\u015fim ve saklama s\u0131n\u0131rlar\u0131 belirleyin.<\/p>\n
Yeniden ba\u015flatma gerektiren de\u011fi\u015fiklikleri uygun bak\u0131m plan\u0131yla uygulay\u0131n; dosyay\u0131 yeniden y\u00fcklemek her parametreyi etkinle\u015ftirmez. VPS CI\/CD da\u011f\u0131t\u0131m rehberi<\/a> dosya s\u00fcr\u00fcmleme i\u00e7in yararl\u0131 olabilir, ancak uygulama symlink de\u011fi\u015fimi PostgreSQL yeniden ba\u015flatmas\u0131n\u0131 kesintisiz h\u00e2le getirmez.<\/p>\n
A\u015fa\u011f\u0131daki sorgular yap\u0131land\u0131rma ve anl\u0131k durum okumalar\u0131d\u0131r; bir de\u011fi\u015fikli\u011fin performans\u0131 iyile\u015ftirdi\u011fini kan\u0131tlamaz. pg_settings i\u00e7indeki context ve pending_restart de\u011ferlerini, ayar\u0131n bekledi\u011finiz kapsamda etkin oldu\u011funu do\u011frulamak i\u00e7in kullan\u0131n.<\/p>\n
SHOW config_file;\nSHOW shared_buffers;\nSHOW work_mem;\nSHOW checkpoint_timeout;\nSELECT name, setting, unit, context, pending_restart\nFROM pg_settings\nWHERE name IN ('shared_buffers', 'max_connections', 'work_mem',\n               'wal_level', 'checkpoint_timeout');\nSELECT pid, state, wait_event_type, wait_event\nFROM pg_stat_activity\nWHERE datname = current_database()\nORDER BY pid\nLIMIT 5;\n<\/code><\/pre>\n
Autovacuum yaln\u0131z sakin saatlerde \u00e7al\u0131\u015fan bak\u0131m de\u011fildir. \u00d6l\u00fc sat\u0131rlar, uzun i\u015flemler, temizleme ilerlemesi ve i\u015flem kimli\u011fi ya\u015f\u0131 izlenmelidir. Yo\u011fun tablolarda e\u015fik veya maliyet ayar\u0131 de\u011fi\u015ftirirken t\u00fcm \u00e7al\u0131\u015fanlar\u0131n toplam bellek ve I\/O b\u00fct\u00e7esini de\u011ferlendirin. Bak\u0131m\u0131 durdurmak bellek sorununu \u00e7\u00f6zm\u00fc\u015f say\u0131lmaz; geciken i\u015f daha sonra b\u00fcy\u00fcyebilir.<\/p>\n
Geli\u015ftirme, staging ve canl\u0131 ortam da\u011f\u0131t\u0131m rehberindeki<\/a> a\u015famal\u0131 yakla\u015f\u0131m\u0131 veritaban\u0131na uyarlarken uygulama uyumlulu\u011funu, ba\u011flant\u0131lar\u0131n yeniden kurulmas\u0131n\u0131 ve veri kurtarmas\u0131n\u0131 ayr\u0131ca s\u0131nay\u0131n. Yap\u0131land\u0131rma geri alma plan\u0131, tutarl\u0131 yedek ve do\u011frulanm\u0131\u015f geri y\u00fckleme s\u00fcrecinin yerine ge\u00e7mez.<\/p>\n
Sorgu Planlar\u0131 ve \u0130ndeksleri Birlikte \u0130nceleyin<\/h2>\n
Y\u00fck\u00fcn \u00e7o\u011funu olu\u015fturan sorgular\u0131 belirleyin; eksik indeks, fazla sat\u0131r okuma veya uzun kilit bekleme yaln\u0131z bellek ayar\u0131yla \u00e7\u00f6z\u00fclmeyebilir. \u00d6nce EXPLAIN ile plan\u0131 inceleyin. EXPLAIN ANALYZE sorguyu ger\u00e7ekten \u00e7al\u0131\u015ft\u0131r\u0131r; de\u011fi\u015fiklik yapan veya pahal\u0131 sorgularda yan etkileri ve y\u00fck\u00fc kontroll\u00fc test ortam\u0131nda de\u011ferlendirin. Yeni indeksi ger\u00e7ek plan ve yazma maliyetiyle kar\u015f\u0131la\u015ft\u0131r\u0131n.<\/p>\n
\u00d6rne\u011fin \u00fcr\u00fcnleri kategori ve yay\u0131nda olma durumuna g\u00f6re s\u00fczen bir sorguda bile\u015fik ya da k\u0131smi indeks de\u011ferlendirilebilir. Do\u011fru se\u00e7im; veri da\u011f\u0131l\u0131m\u0131na, sorgunun ko\u015fullar\u0131na, s\u0131ralamas\u0131na ve d\u00f6nd\u00fcrd\u00fc\u011f\u00fc sat\u0131r say\u0131s\u0131na ba\u011fl\u0131d\u0131r. \u0130ndeksin varl\u0131\u011f\u0131 kullan\u0131laca\u011f\u0131n\u0131 garanti etmez; \u00f6l\u00e7\u00fcmde plan, okunan bloklar ve i\u015flem s\u00fcresini birlikte inceleyin.<\/p>\n
WAL yakla\u015f\u0131m\u0131n\u0131n resmi a\u00e7\u0131klamas\u0131<\/a>, veri sayfas\u0131 yaz\u0131m\u0131 ile g\u00fcnl\u00fck kal\u0131c\u0131l\u0131\u011f\u0131n\u0131 ay\u0131r\u0131r. Sorgu iyile\u015ftirmesi, bellek b\u00fct\u00e7esi ve kal\u0131c\u0131l\u0131k farkl\u0131 karar alanlar\u0131d\u0131r; birindeki h\u0131zlanmay\u0131 di\u011ferinin g\u00fcvencesi olarak de\u011ferlendirmeyin.<\/p>\n
Tek Bir \u00d6l\u00e7\u00fclebilir De\u011fi\u015fiklikle \u0130lerleyin<\/h2>\n
\u00d6nce darbo\u011faz\u0131 belirleyin, sonra ilgili ayar\u0131 se\u00e7in. Bellek de\u011fi\u015fikli\u011finde i\u015flem say\u0131s\u0131 ve e\u015fzamanl\u0131l\u0131\u011f\u0131; WAL de\u011fi\u015fikli\u011finde disk alan\u0131 ve kurtarmay\u0131; havuz de\u011fi\u015fikli\u011finde uygulama uyumlulu\u011fu ve kuyruk s\u00fcresini s\u0131nay\u0131n. Ayn\u0131 y\u00fck alt\u0131nda \u00f6nceki sonu\u00e7la kar\u015f\u0131la\u015ft\u0131r\u0131n ve k\u00f6t\u00fcle\u015fme \u00f6l\u00e7\u00fct\u00fcne ula\u015f\u0131ld\u0131\u011f\u0131nda son do\u011frulanm\u0131\u015f yap\u0131land\u0131rmaya d\u00f6n\u00fcn.<\/p>\n
Bir i\u015f d\u00f6ng\u00fcs\u00fcn\u00fc ve planl\u0131 raporlar\u0131 kapsayan g\u00f6zlem yap\u0131n; yaln\u0131z d\u00fc\u015f\u00fck trafik saati yeterli olmayabilir. S\u00fcr\u00fcm\u00fc, de\u011fi\u015fen parametreyi, gerek\u00e7eyi ve \u00f6l\u00e7\u00fcm\u00fc kaydedin. HTTPS ve ters proxy ayarlar\u0131n\u0131 ele alan rehber<\/a> di\u011fer katmanlar i\u00e7in ba\u011flam sa\u011flayabilir; bu katmanlardaki de\u011fi\u015fiklikleri ayn\u0131 performans denemesinde kar\u0131\u015ft\u0131rmay\u0131n.<\/p>\n
Son karar, beklenen y\u00fck alt\u0131nda RAM s\u0131n\u0131rlar\u0131 i\u00e7inde kalmak, kabul edilen gecikmeyi sa\u011flamak ve veri kal\u0131c\u0131l\u0131\u011f\u0131 ko\u015fullar\u0131n\u0131 korumakt\u0131r. Bir sonraki de\u011fi\u015fikli\u011fe ge\u00e7meden \u00f6nce etkin de\u011ferleri, uygulama davran\u0131\u015f\u0131n\u0131 ve geri alma yolunu do\u011frulay\u0131n.<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"
PostgreSQL i\u00e7in RAM ve ba\u011flant\u0131 b\u00fct\u00e7esini birlikte kurun. shared_buffers, work_mem, WAL ve yerel PgBouncer \u00f6rneklerini s\u00fcr\u00fcm, kal\u0131c\u0131l\u0131k ve uygulama uyumlulu\u011fuyla de\u011ferlendirin.<\/p>\n","protected":false},"author":1,"featured_media":7512,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-1534","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-teknoloji"],"_links":{"self":[{"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/posts\/1534","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/comments?post=1534"}],"version-history":[{"count":5,"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/posts\/1534\/revisions"}],"predecessor-version":[{"id":11236,"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/posts\/1534\/revisions\/11236"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/media\/7512"}],"wp:attachment":[{"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/media?parent=1534"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/categories?post=1534"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/tags?post=1534"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}