{"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":"2025-11-08T12:25:59","modified_gmt":"2025-11-08T09:25:59","slug":"vpste-postgresqli-ucurmak-shared_buffers-work_mem-wal-ve-pgbounceri-ne-zaman-nasil-ayarlariz","status":"publish","type":"post","link":"https:\/\/www.dchost.com\/blog\/vpste-postgresqli-ucurmak-shared_buffers-work_mem-wal-ve-pgbounceri-ne-zaman-nasil-ayarlariz\/","title":{"rendered":"VPS\u2019te PostgreSQL\u2019i U\u00e7urmak: shared_buffers, work_mem, WAL ve PgBouncer\u2019\u0131 Ne Zaman, Nas\u0131l Ayarlar\u0131z?"},"content":{"rendered":"

Bir G\u00fcn, Tek Bir Sorgu Her \u015eeyi Durdurdu: Giri\u015f<\/h2>\n

Hi\u00e7 k\u00fc\u00e7\u00fck bir VPS<\/a>\u2019te her \u015fey yolundayken bir anda uygulaman\u0131n a\u011f\u0131r \u00e7ekime ge\u00e7ti\u011fini g\u00f6rd\u00fcn\u00fcz m\u00fc? Ben g\u00f6rd\u00fcm. Bir \u00f6\u011fleden sonra, hi\u00e7 akl\u0131ma gelmeyen bir rapor sorgusu, b\u00fct\u00fcn ekibin \u00e7ay molas\u0131n\u0131 uzatan bir mini felakete d\u00f6n\u00fc\u015ft\u00fc. CPU tavan, disk \u0131\u015f\u0131\u011f\u0131 yan\u0131p s\u00f6n\u00fcyor, web taraf\u0131 yava\u015flam\u0131\u015f\u2026 Derken \u015funu fark ettim: veritaban\u0131 ayarlar\u0131 kurulum g\u00fcn\u00fc neyse h\u00e2l\u00e2 oydu. Varsay\u0131lanlar iyidir ama her zaman de\u011fil. Hele ki VPS \u00fczerinde, RAM ve disk gibi kaynaklar s\u0131n\u0131rl\u0131yken, k\u00fc\u00e7\u00fck dokunu\u015flar bile oyunu de\u011fi\u015ftiriyor.<\/p>\n

Bu yaz\u0131da, PostgreSQL\u2019i VPS \u00fczerinde daha ak\u0131c\u0131 hale getirmek i\u00e7in d\u00f6rt temel ta\u015f\u0131 konu\u015faca\u011f\u0131z: shared_buffers<\/strong>, work_mem<\/strong>, WAL ve checkpoint<\/strong> ayarlar\u0131, bir de PgBouncer ile ba\u011flant\u0131 havuzu<\/strong>. Her birini g\u00fcnl\u00fck hayattan \u00f6rneklerle, \u201cneden\u201d ve \u201cne zaman\u201d sorular\u0131na cevap arayarak ele alaca\u011f\u0131z. Arada ufak config par\u00e7alar\u0131 payla\u015faca\u011f\u0131m, ama as\u0131l amac\u0131m kafan\u0131zda sade bir yol haritas\u0131 \u00e7izmek. Mesela \u015f\u00f6yle d\u00fc\u015f\u00fcn\u00fcn: RAM bir mutfak tezg\u00e2h\u0131, disk bir kiler, ba\u011flant\u0131lar da mutfa\u011fa giren \u00e7\u0131k\u0131p duran insanlar. Ne kadar d\u00fczenli olursa, o kadar h\u0131zl\u0131 haz\u0131rlars\u0131n\u0131z yeme\u011fi.<\/p>\n

shared_buffers: Mutfakta Tezg\u00e2h Gibi, Elinizin Alt\u0131 Ne Kadar Geni\u015f?<\/h2>\n

PostgreSQL\u2019in kendi belle\u011finde tuttu\u011fu sayfa \u00f6nbelle\u011fi diyebilece\u011fimiz shared_buffers, en kritik ayarlardan biri. \u00c7ok k\u00fc\u00e7\u00fck olursa her ad\u0131mda diske bakmak zorunda kal\u0131rs\u0131n\u0131z, \u00e7ok b\u00fcy\u00fck olursa bu kez sistemin genel dengesi bozulur ve i\u015fletim sistemi \u00f6nbelle\u011fini k\u00f6reltirsiniz. VPS\u2019teki RAM\u2019iniz s\u0131n\u0131rl\u0131yken, buraya dengeli yakla\u015fmak \u015fart. Benim pratik yakla\u015f\u0131m\u0131m, \u00f6nce uygulaman\u0131n davran\u0131\u015f\u0131n\u0131 izlemek ve okuma a\u011f\u0131rl\u0131kl\u0131 m\u0131, yazma a\u011f\u0131rl\u0131kl\u0131 m\u0131 anlamak. Sonra k\u00fc\u00e7\u00fck ad\u0131mlarla b\u00fcy\u00fctmek.<\/p>\n

Mesela \u015f\u00f6yle d\u00fc\u015f\u00fcn\u00fcn: g\u00fcn i\u00e7inde hep ayn\u0131 \u00fcr\u00fcn listesine bak\u0131l\u0131yor, ayn\u0131 kullan\u0131c\u0131 profilleri \u00e7ekiliyor, yani s\u0131cak veri bir \u015fekilde tekrar tekrar kullan\u0131l\u0131yor. Bu durumda shared_buffers\u2019\u0131 art\u0131rmak y\u00fcz g\u00fcld\u00fcr\u00fcr. Ama veri s\u00fcrekli de\u011fi\u015fiyor, yo\u011fun yazma yap\u0131l\u0131yor, bir de raporlar diske aban\u0131yorsa, \u00e7ok agresif bir de\u011fer bazen geri teper. Birka\u00e7 deneme, g\u00f6zlem ve ufak dokunu\u015f burada en iyi \u00f6\u011fretmen.<\/p>\n

Ba\u015flang\u0131\u00e7 i\u00e7in sade bir \u00f6rnek:<\/p>\n

# \/etc\/postgresql\/XX\/main\/postgresql.conf veya da\u011f\u0131t\u0131m\u0131n\u0131za g\u00f6re ilgili dosya\nshared_buffers = 1GB    # VPS RAM durumunuza g\u00f6re temkinli ba\u015flay\u0131n\neffective_cache_size = 3GB  # OS \u00f6nbelle\u011fini kabaca tahmin etmek i\u00e7in rehber de\u011fer\n<\/code><\/pre>\n
Buradaki effective_cache_size do\u011frudan bellek ay\u0131rmaz, ama sorgu planlay\u0131c\u0131s\u0131na \u201cd\u0131\u015far\u0131da \u015fu kadar \u00f6nbellek var\u201d diye f\u0131s\u0131ldar. Plans\u0131z i\u015f kalmas\u0131n diye bu t\u00fcr de\u011ferleri, ger\u00e7ek kullan\u0131m\u0131 izledikten sonra ayarlamak iyi olur. \u201cKa\u00e7 GB yapay\u0131m?\u201d derseniz, b\u00fcy\u00fct\u00fcp k\u00fc\u00e7\u00fclterek, diskten okuma oran\u0131n\u0131z\u0131<\/strong> ve sorgu s\u00fcrelerinizi izleyin. Bir noktadan sonra art\u0131\u015f\u0131n faydas\u0131 azal\u0131r, bunu hissetti\u011finiz anda durmak en sa\u011fl\u0131kl\u0131s\u0131.<\/p>\n
Resmi belgelere g\u00f6z atmak isterseniz, resmi PostgreSQL belgelerindeki bellek ayarlar\u0131n\u0131n a\u00e7\u0131klamalar\u0131<\/a> sade bir referans olarak elinizin alt\u0131nda dursun. Orada \u00e7ok teknik detaya girmeden, hangi ayar\u0131n neye dokundu\u011funu g\u00f6rmek moral veriyor.<\/p>\n
work_mem: S\u0131ray\u0131 Bekleyen Sorgulara K\u00fc\u00e7\u00fck Bir Masa Ay\u0131rmak<\/h2>\n
work_mem, s\u0131ralama ve hash gibi i\u015flemler i\u00e7in sorgu ba\u015f\u0131na ayr\u0131lan ge\u00e7ici \u00e7al\u0131\u015fma alan\u0131 gibi d\u00fc\u015f\u00fcnebilirsiniz. K\u00fc\u00e7\u00fcc\u00fck oldu\u011funda PostgreSQL diske d\u00f6k\u00fcl\u00fcr, yani s\u0131ralama i\u00e7in dosyalar olu\u015fturur ve yava\u015flar. \u00c7ok b\u00fcy\u00fck yapt\u0131\u011f\u0131n\u0131zda ise e\u015fzamanl\u0131 sorgular, her biri ayr\u0131 ayr\u0131 bu alan\u0131 kullanabildi\u011fi i\u00e7in belle\u011fi bir anda eritebilir. \u0130\u015fte denge tam burada ba\u015fl\u0131yor.<\/p>\n
Bir keresinde tek bir rapor sorgusunun, alt\u0131ndaki iki \u00fc\u00e7 alt-sorguyla birlikte belle\u011fi nas\u0131l \u015fi\u015firdi\u011fini g\u00f6rm\u00fc\u015ft\u00fcm. Sorgular\u0131n say\u0131s\u0131 artt\u0131k\u00e7a \u201cbir anda niye swap\u2019e ge\u00e7tik?\u201d sorusunun cevab\u0131 me\u011ferse work_mem\u2019in c\u00f6mertli\u011findeymi\u015f. \u00c7\u00f6z\u00fcm, tek bir devasa de\u011fer yerine, bilin\u00e7li bir orta yoldu. \u00d6nce e\u015fzamanl\u0131 ba\u011flant\u0131 say\u0131s\u0131n\u0131 ve tahmini a\u011f\u0131r sorgu say\u0131s\u0131n\u0131 d\u00fc\u015f\u00fcnd\u00fcm, sonra work_mem\u2019i makul bir seviyeye \u00e7ektim. G\u00fcn bitti\u011finde, herkes daha h\u0131zl\u0131 rapor ald\u0131\u011f\u0131n\u0131 s\u00f6yledi.<\/p>\n
Ayarlarken \u015f\u00f6yle bir ak\u0131l y\u00fcr\u00fctme i\u015f g\u00f6r\u00fcyor: ayn\u0131 anda ka\u00e7 b\u00fcy\u00fck s\u0131ralama bekliyorsunuz? Uygulama yo\u011funlu\u011funuz ne zaman art\u0131yor? \u00d6rnek bir ba\u015flang\u0131\u00e7:<\/p>\n
work_mem = 32MB           # Yava\u015f s\u0131ralamalar varsa yava\u015f yava\u015f art\u0131r\u0131n\nmaintenance_work_mem = 256MB  # VACUUM\/CREATE INDEX gibi i\u015fler i\u00e7in, bo\u015fta iken biraz c\u00f6mert davran\u0131labilir\n<\/code><\/pre>\n
\u0130\u015fin g\u00fczel yan\u0131, work_mem\u2019i oturum baz\u0131nda da de\u011fi\u015ftirebilirsiniz. Diyelim ki sadece bir bak\u0131m i\u015fi yapacaks\u0131n\u0131z, o oturum i\u00e7in ge\u00e7ici art\u0131r\u0131p i\u015f bitince eski haline b\u0131rakabilirsiniz:<\/p>\n
-- psql i\u00e7inde\nSET work_mem = '128MB';\n-- a\u011f\u0131r bir raporu \u00e7al\u0131\u015ft\u0131r\u0131n, i\u015f bitti\u011finde kapat\u0131n\n<\/code><\/pre>\n
Burada ama\u00e7; s\u0131rf bir kez h\u0131zlans\u0131n diye her oturuma l\u00fcks tan\u0131mak de\u011fil, bilin\u00e7li jestler yapmak. Sorgular\u0131n\u0131z\u0131n s\u0131kl\u0131\u011f\u0131n\u0131, boyutunu ve e\u015fzamanl\u0131l\u0131\u011f\u0131n\u0131 akl\u0131n\u0131zda tutarsan\u0131z, work_mem sizi tatl\u0131 bir dengeye ta\u015f\u0131r.<\/p>\n
WAL, Checkpoint ve Disk Yaz\u0131m\u0131: Sessizce \u0130\u015fleyen Bir Sigorta<\/h2>\n
WAL (write-ahead log) kula\u011fa karma\u015f\u0131k gelebilir ama basit\u00e7e \u015funu yapar: veritaban\u0131na bir de\u011fi\u015fiklik yapmadan \u00f6nce o de\u011fi\u015fikli\u011fi g\u00fcvenle bir g\u00fcnl\u00fck dosyas\u0131na yazar. B\u00f6ylece elektrikler gidip geldi\u011finde veri tutarl\u0131l\u0131\u011f\u0131 korunur. VPS\u2019te disk genelde en yava\u015f k\u0131s\u0131md\u0131r; o y\u00fczden WAL ayarlar\u0131, performans\u0131n nabz\u0131n\u0131 tutar. \u00c7ok s\u0131k checkpoint olursa disk yorulur; \u00e7ok seyrek olursa da crash sonras\u0131 toparlanma uzar. Burada kilit, sizin i\u015f y\u00fck\u00fcn\u00fcz ve risk i\u015ftah\u0131n\u0131z.<\/p>\n
Ben genelde \u015fu yoldan giderim: \u00f6nce checkpoint aral\u0131klar\u0131n\u0131 biraz ferahlat\u0131r, WAL dosyalar\u0131n\u0131n \u00e7ok h\u0131zl\u0131 \u015fi\u015fmemesine de dikkat ederim. Sonra disk grafikleriyle, y\u00fck alt\u0131nda davran\u0131\u015f\u0131 izlerim. Sanki bir makinenin ritmini dinlemek gibi. Bir s\u00fcre sonra, \u201ctamam, burada tatl\u0131 bir ak\u0131\u015f yakalad\u0131k\u201d diyebiliyorsunuz.<\/p>\n
Uygulanabilir bir ba\u015flang\u0131\u00e7 seti \u015f\u00f6yle olabilir:<\/p>\n
wal_level = replica            # Mant\u0131ksal \u00e7o\u011faltma yoksa replica genelde yeter\nmax_wal_size = 2GB             # Uygulamaya g\u00f6re art\u0131r\u0131labilir\nmin_wal_size = 512MB\ncheckpoint_timeout = 15min     # \u00c7ok s\u0131k checkpoint yapmas\u0131n\ncheckpoint_completion_target = 0.9  # Kontrol noktalar\u0131n\u0131 zamana yayarak disk tepe noktalar\u0131n\u0131 azalt\u0131r\nsynchronous_commit = on        # Veri g\u00fcvenli\u011fi \u00f6ncelikli, baz\u0131 i\u015flerde off\/remote_write d\u00fc\u015f\u00fcn\u00fclebilir\nwal_compression = on           # WAL boyutunu azaltabilir, CPU bedeli olabilir\n<\/code><\/pre>\n
Bir de \u015fu var: g\u00fcvenlik ve h\u0131z aras\u0131nda bir ip var. synchronous_commit gibi ayarlar, \u201c\u00f6nce g\u00fcven mi, \u00f6nce h\u0131z m\u0131?\u201d sorusunu f\u0131s\u0131ldar. Kritik veride kural bellidir; ama baz\u0131 g\u00fcnl\u00fck log benzeri tablolarda tolerans varsa se\u00e7ici davran\u0131labilir. Bunu rastgele de\u011fil, uygulaman\u0131n veri ak\u0131\u015f\u0131n\u0131 ve kay\u0131p tolerans\u0131n\u0131 bilerek yapmak en do\u011frusu.<\/p>\n
Merak ederseniz WAL ve ilgili ayarlar\u0131n resmi d\u00f6k\u00fcm\u00fcn\u00fc<\/a> h\u0131zl\u0131ca kar\u0131\u015ft\u0131rmak, her bir parametrenin mizac\u0131n\u0131 g\u00f6rmenize yard\u0131m eder. Ben bazen bir parametreye dokunmadan \u00f6nce buradaki k\u0131sa a\u00e7\u0131klamalara bakar\u0131m; fazla s\u00fcrpriz istemiyorsan\u0131z i\u015fe yar\u0131yor.<\/p>\n
PgBouncer: \u0130nce Kap\u0131dan Ge\u00e7en Kalabal\u0131klar \u0130\u00e7in Sessiz Bir D\u00fczenleyici<\/h2>\n
Uygulama sunucular\u0131n\u0131n her istekte yeni bir veritaban\u0131 ba\u011flant\u0131s\u0131 a\u00e7\u0131p kapatt\u0131\u011f\u0131 d\u00fcnyada, veritaban\u0131n\u0131n i\u015fi bitmiyor. \u0130\u015fte PgBouncer burada devreye giriyor ve ba\u011flant\u0131 havuzu y\u00f6netimiyle veritaban\u0131n\u0131 nefeslendiriyor. VPS\u2019te CPU ve RAM zaten s\u0131n\u0131rl\u0131; her ba\u011flant\u0131 a\u00e7\u0131l\u0131p kapand\u0131k\u00e7a kaynaklar par\u00e7a par\u00e7a harcan\u0131yor. Havuz, bu gidi\u015f geli\u015fleri azaltarak daha ak\u0131c\u0131 bir trafik sa\u011flar.<\/p>\n
Benim en \u00e7ok fayda g\u00f6rd\u00fc\u011f\u00fcm an, web uygulamas\u0131nda anl\u0131k trafik s\u0131\u00e7ramalar\u0131 ya\u015fand\u0131\u011f\u0131nda oldu. Ba\u011flant\u0131 havuzu, PostgreSQL\u2019e bir anda y\u00fczlerce kap\u0131 a\u00e7mak yerine, s\u0131ray\u0131 kibarca d\u00fczenledi. Bir yandan sorgular bitiyor, bir yandan yeni gelenler bekliyor ama ortal\u0131k y\u0131k\u0131lm\u0131yor. \u00d6zellikle bekleyen sorgular k\u0131sa s\u00fcr\u00fcyorsa, bu d\u00fczen mucizeler yarat\u0131yor.<\/p>\n
Basit bir PgBouncer yap\u0131land\u0131rmas\u0131 \u015fu kafay\u0131 anlat\u0131r:<\/p>\n
; \/etc\/pgbouncer\/pgbouncer.ini\n[databases]\nappdb = host=127.0.0.1 port=5432 dbname=appdb user=appuser password=secret\n\n[pgbouncer]\nlisten_addr = 0.0.0.0\nlisten_port = 6432\nauth_type = md5\nauth_file = \/etc\/pgbouncer\/userlist.txt\npool_mode = transaction    ; transaction genelde web i\u015f y\u00fcklerinde iyi sonu\u00e7 verir\nmax_client_conn = 500      ; uygulama taraf\u0131ndaki ba\u011flant\u0131 \u00fcst s\u0131n\u0131r\u0131\ndefault_pool_size = 50     ; veritaban\u0131na a\u00e7\u0131lacak ger\u00e7ek ba\u011flant\u0131 say\u0131s\u0131\nreserve_pool_size = 10     ; ani s\u0131\u00e7ramalar i\u00e7in k\u00fc\u00e7\u00fck bir tampon\nserver_idle_timeout = 300\n<\/code><\/pre>\n
Uygulama taraf\u0131nda veritaban\u0131 adresini PgBouncer\u2019\u0131n portuna \u00e7evirince, \u00e7o\u011fu durumda sihir ba\u015flar. Yine de \u015funu unutmay\u0131n: pool_mode se\u00e7iminiz \u00e7ok \u00f6nemlidir. transaction modu, her i\u015flem bitti\u011finde ba\u011flant\u0131y\u0131 havuza iade eder ve web uygulamalar\u0131 genelde bunu sever. Session modu, ba\u011flant\u0131y\u0131 oturum boyunca tutar; baz\u0131 i\u015flerde bu da gerekebilir. Her i\u015f y\u00fck\u00fcn\u00fcn huyu farkl\u0131d\u0131r, o y\u00fczden ufak testler iyi fikir.<\/p>\n
Resmi ayar referans\u0131 i\u00e7in PgBouncer konfig\u00fcrasyon sayfas\u0131na<\/a> g\u00f6z atmak, \u00f6zellikle time-out ve havuz boyutu gibi parametreleri tan\u0131rken yol g\u00f6sterir. Ben bazen bu sayfay\u0131 a\u00e7\u0131k tutup loglarla birlikte, \u201ckim nerede bekliyor\u201d sorusunu izleyerek ufak r\u00f6tu\u015flar yapar\u0131m.<\/p>\n
Ayarlad\u0131k, Peki Nas\u0131l \u00d6l\u00e7eriz? \u0130zleme, Loglar ve K\u00fc\u00e7\u00fck Rit\u00fceller<\/h2>\n
T\u00fcm bu ayarlar, \u00f6l\u00e7meyince havada kal\u0131yor. Benim i\u015f rutinimde, \u00f6nce birka\u00e7 metrikle bar\u0131\u015fmak var: ortalama sorgu s\u00fcresi, disk bekleme s\u00fcreleri, ba\u011flant\u0131 say\u0131lar\u0131, checkpoint zamanlamas\u0131 ve autovacuum davran\u0131\u015f\u0131. Bunu kurman\u0131n yollar\u0131 bol; \u00f6nemli olan, bir kez bak\u0131p b\u0131rakmamak. Zamana yay\u0131lm\u0131\u015f grafikler, \u00f6zellikle VPS\u2019te \u201cbu aral\u0131kta ne olmu\u015f?\u201d sorusunu \u00e7ok net yan\u0131tl\u0131yor.<\/p>\n
\u0130\u015fin g\u00f6rsel taraf\u0131n\u0131 seviyorsan\u0131z, daha \u00f6nce payla\u015ft\u0131\u011f\u0131m\u0131z VPS izleme ve alarm kurulumunu Prometheus ve Grafana ile aya\u011fa kald\u0131rmak<\/a> yaz\u0131s\u0131na g\u00f6z atabilirsiniz. PostgreSQL metriklerini kat\u0131nca, hangi ayar\u0131n nas\u0131l bir dalga yaratt\u0131\u011f\u0131n\u0131 anlamak \u00e7ok kolayla\u015f\u0131yor. Hatta loglar\u0131n\u0131z\u0131 derli toplu tutmak i\u00e7in Loki ve Promtail ile merkezi loglama rehberi<\/a> de i\u015finizi hafifletir; bir hata an\u0131nda, ba\u011flant\u0131 havuzuyla veritaban\u0131 loglar\u0131n\u0131 yan yana g\u00f6rmek bazen hayat kurtar\u0131yor.<\/p>\n
Bir di\u011fer k\u00fc\u00e7\u00fck rit\u00fcelim, konfig\u00fcrasyon de\u011fi\u015fikliklerini sakin zamanlarda ve m\u00fcmk\u00fcnse k\u0131sa bir bak\u0131m penceresinde uygulamak. \u00d6rne\u011fin, s\u0131f\u0131r kesintiye yak\u0131n deneyimler i\u00e7in VPS\u2019e s\u0131f\u0131r kesinti CI\/CD ak\u0131\u015f\u0131<\/a> kurmak, konfig\u00fcrasyon dosyalar\u0131n\u0131 versiyonlay\u0131p kontroll\u00fc da\u011f\u0131t\u0131m yapmay\u0131 kolayla\u015ft\u0131r\u0131yor. Sadece uygulama de\u011fil; veritaban\u0131 ayar dosyalar\u0131 da bu ritme dahil olunca, s\u00fcrprizler azal\u0131yor.<\/p>\n
\u00d6l\u00e7mek demi\u015fken, PostgreSQL i\u00e7inde anl\u0131k bir bak\u0131\u015f i\u00e7in \u015fu ufak komutlara g\u00f6z atmak keyifli olabilir:<\/p>\n
-- psql i\u00e7inde\nSHOW shared_buffers;\nSHOW work_mem;\nSHOW checkpoint_timeout;\nSELECT * FROM pg_stat_activity LIMIT 5;  -- \u00f6rnek bir durum turu\n<\/code><\/pre>\n
Bir de temizlik i\u015flerini unutmayal\u0131m. Autovacuum ayarlar\u0131 ihmal edildi\u011finde, tablolar \u015fi\u015fer ve sorgular hantalla\u015f\u0131r. Yo\u011fun yazma yapan tablolarda autovacuum daha s\u0131k ve h\u0131zl\u0131 \u00e7al\u0131\u015fs\u0131n isteyebilirsiniz; daha sakin tablolarda ise \u201cyerini bilsin, sessizce\u201d davranmas\u0131 yeter. Burada da yine k\u00fc\u00e7\u00fck ayarlar ve \u00f6l\u00e7\u00fcm size yol g\u00f6sterir.<\/p>\n
E\u011fer veritaban\u0131 ayarlar\u0131n\u0131 canl\u0131ya ta\u015f\u0131rken uygulama taraf\u0131nda korkular ba\u015f g\u00f6steriyorsa, da\u011f\u0131t\u0131m ad\u0131mlar\u0131n\u0131 planlarken geli\u015ftirme, staging ve canl\u0131 ortam aras\u0131nda s\u0131f\u0131r kesintiye yak\u0131n bir yolculuk<\/a> yaz\u0131s\u0131ndan ilham alabilirsiniz. Oradaki yakla\u015f\u0131m, veritaban\u0131 taraf\u0131ndaki k\u00fc\u00e7\u00fck ama etkili de\u011fi\u015fiklikleri de yumu\u015fat\u0131yor.<\/p>\n
Birlikte Tutan Har\u00e7: Uygulama Kal\u0131plar\u0131, \u0130ndeksler ve Sorgu Kokusu<\/h2>\n
\u015eimdi bir ad\u0131m geri \u00e7ekilelim. shared_buffers ve work_mem\u2019le bellek ak\u0131\u015f\u0131n\u0131, WAL\u2019la disk ritmini, PgBouncer\u2019la ba\u011flant\u0131 trafi\u011fini ele ald\u0131k. Peki ya sorgular? \u00c7o\u011fu zaman as\u0131l fark\u0131, iyi kokan sorgular ve yerinde indeksler yarat\u0131yor. Birka\u00e7 kere \u201cneden h\u00e2l\u00e2 yava\u015f?\u201d diye sordu\u011fumda cevap, eksik bir indeks ya da gere\u011finden fazla veri d\u00f6nd\u00fcren masum bir sorgu \u00e7\u0131kt\u0131. Burada ara\u00e7 basit: \u00f6nce yava\u015f sorgular\u0131 yakalay\u0131n, sonra EXPLAIN ile plan\u0131 g\u00f6r\u00fcn, ard\u0131ndan k\u00fc\u00e7\u00fck dokunu\u015flarla iyile\u015ftirin.<\/p>\n
Mesela \u015f\u00f6yle d\u00fc\u015f\u00fcn\u00fcn: \u00fcr\u00fcnler tablonuzda en s\u0131k arama kategoriye ve yay\u0131nda olma durumuna g\u00f6re yap\u0131l\u0131yor. Buna uygun bile\u015fik bir indeks, mucize gibi gelir. Sorgu plan\u0131, diskte dola\u015fmak yerine do\u011frudan hedefe gider. Sonra payla\u015f\u0131lan \u00f6nbellekler devreye girer, ayn\u0131 sorgular daha da h\u0131zlan\u0131r. Yani ayarlar\u0131n verdi\u011fi fayda, iyi sorgularla katlanarak art\u0131yor.<\/p>\n
Burada detaylara dalmak isteyenler i\u00e7in, referans okumas\u0131n\u0131 sakince yapmak g\u00fczel: WAL mant\u0131\u011f\u0131n\u0131n k\u0131sa bir tan\u0131t\u0131m\u0131<\/a> ve bellek ayarlar\u0131n\u0131n neye dokundu\u011funu anlatan sayfalar, terimleri insanile\u015ftiriyor. Teknik gibi g\u00f6r\u00fcnen her \u015fey, k\u00fc\u00e7\u00fck k\u00fc\u00e7\u00fck okuyunca anlaml\u0131 bir hik\u00e2yeye d\u00f6n\u00fc\u015f\u00fcyor.<\/p>\n
Kapan\u0131\u015f: K\u00fc\u00e7\u00fck Ayarlar, B\u00fcy\u00fck Nefes<\/h2>\n
Toparlayal\u0131m. VPS \u00fczerinde PostgreSQL\u2019i h\u0131zland\u0131rmak i\u00e7in harikalar yaratmaya gerek yok. shared_buffers ile elinizin alt\u0131ndaki s\u0131cak veriyi ak\u0131ll\u0131ca b\u00fcy\u00fct\u00fcn. work_mem ile sorgular\u0131n\u0131za k\u00fc\u00e7\u00fck ama isabetli \u00e7al\u0131\u015fma alanlar\u0131 verin. WAL ve checkpoint ayarlar\u0131yla diske yazma ritmini sakinle\u015ftirin, ani tepe noktalar\u0131n\u0131 da\u011f\u0131t\u0131n. PgBouncer ile ba\u011flant\u0131lar\u0131 usulca s\u0131raya sokun, veritaban\u0131n\u0131 gereksiz kap\u0131 a\u00e7malardan koruyun. Sonra da b\u00fct\u00fcn bu hik\u00e2yeyi izleyin, \u00f6l\u00e7\u00fcn, k\u00fc\u00e7\u00fck ad\u0131mlarla d\u00fczeltin.<\/p>\n
Pratik bir tavsiye: ayarlar\u0131 bir anda de\u011fil, ad\u0131m ad\u0131m de\u011fi\u015ftirin. Her ad\u0131mda \u00f6nce g\u00f6zlem yap\u0131n, sonra bir sonrakine ge\u00e7in. Gerekirse bir ak\u015fam\u00fcst\u00fc trafi\u011fin az oldu\u011fu saatleri se\u00e7in, de\u011fi\u015fikli\u011fi uygulay\u0131p loglara ve grafiklere bak\u0131n. E\u011fer t\u00fcm bu ad\u0131mlar\u0131n yan\u0131nda a\u011f, TLS ve ters proxy gibi katmanlar\u0131 da ele almak isterseniz, h\u0131zl\u0131 ve g\u00fcvenli HTTPS\u2019nin s\u0131cak rehberi<\/a> gibi yaz\u0131lar da yakla\u015f\u0131m\u0131n\u0131za omuz verir.<\/p>\n
Umar\u0131m bu rehber akl\u0131n\u0131zdaki d\u00fc\u011f\u00fcmleri biraz gev\u015fetmi\u015ftir. Sorular\u0131n\u0131z olursa not al\u0131n, ilk f\u0131rsatta test ederek cevaplay\u0131n. K\u00fc\u00e7\u00fck dokunu\u015flar, d\u00fczenli \u00f6l\u00e7\u00fcmler ve sab\u0131rl\u0131 bir yakla\u015f\u0131m; genelde en g\u00fczel performans\u0131 getiriyor. Bir sonraki yaz\u0131da yeniden g\u00f6r\u00fc\u015fmek \u00fczere; veritaban\u0131n\u0131z h\u0131zl\u0131, loglar\u0131n\u0131z berrak, geceleriniz huzurlu olsun.<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"
Bir G\u00fcn, Tek Bir Sorgu Her \u015eeyi Durdurdu: Giri\u015f Hi\u00e7 k\u00fc\u00e7\u00fck bir VPS\u2019te her \u015fey yolundayken bir anda uygulaman\u0131n a\u011f\u0131r \u00e7ekime ge\u00e7ti\u011fini g\u00f6rd\u00fcn\u00fcz m\u00fc? Ben g\u00f6rd\u00fcm. Bir \u00f6\u011fleden sonra, hi\u00e7 akl\u0131ma gelmeyen bir rapor sorgusu, b\u00fct\u00fcn ekibin \u00e7ay molas\u0131n\u0131 uzatan bir mini felakete d\u00f6n\u00fc\u015ft\u00fc. CPU tavan, disk \u0131\u015f\u0131\u011f\u0131 yan\u0131p s\u00f6n\u00fcyor, web taraf\u0131 yava\u015flam\u0131\u015f\u2026 Derken \u015funu […]<\/p>\n","protected":false},"author":1,"featured_media":1535,"comment_status":"","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":0,"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/posts\/1534\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/wp-json\/wp\/v2\/media\/1535"}],"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}]}}