PgBouncer ile Bağlantı Havuzu Kurulumu

PostgreSQL, her bağlantı için ayrı bir işletim sistemi süreci başlatır. Bu tasarım kararı kararlılık açısından mükemmeldir ama bir bedeli vardır: her bağlantı birkaç megabayt bellek tüketir ve süreç oluşturmak milisaniyeler alır. PHP gibi istek başına bağlantı açan bir yığında ya da 40 kopya çalışan bir mikroservis mimarisinde bu maliyet hızla tavana vurur ve FATAL: sorry, too many clients already hatasını görürsünüz.

Bu noktada max_connections değerini yükseltmek en yaygın ama en yanlış tepkidir; sorunu bellek tükenmesine dönüştürür. Doğru çözüm bir bağlantı havuzudur ve PostgreSQL dünyasında bunun standart aracı PgBouncer'dır. Bu rehberde PgBouncer kurulumunu adım adım yapacağız: havuz modlarının farkını, kimlik doğrulamayı, boyutlandırmayı, izleme komutlarını ve hangi PostgreSQL özelliklerinin transaction modda çalışmadığını anlatacağım.

Bağlantı Havuzu Neden Gerekli#

Bir web uygulamasının veritabanı bağlantısı, ömrünün büyük kısmını boşta geçirir. Uygulama bağlantıyı açar, birkaç milisaniyelik sorgu çalıştırır, sonra HTTP yanıtını üretirken bağlantı hiçbir şey yapmadan bekler. 500 uygulama işçiniz varsa 500 açık bağlantınız olur ama aynı anda gerçekten çalışan belki 20 tanesidir.

PgBouncer, istemcilerle veritabanı arasına girer ve az sayıda gerçek bağlantıyı çok sayıda istemci arasında paylaştırır. 500 istemci bağlantısını 25 gerçek PostgreSQL bağlantısına indirmek tipik bir sonuçtur. Kazanç üç yerdedir:

Dördüncü satır sezgiye aykırı gelir ama önemlidir: CPU çekirdek sayınızın çok üzerinde eşzamanlı sorgu çalıştırmak toplam verimi düşürür. Çekirdekler zaman paylaşımına girer, önbellekler sürekli tazelenir. Havuz boyutunu sınırlamak, sunucuyu boğulmaktan kurtarır.

Kurulum#

Ubuntu ve Debian'da PgBouncer resmi depolarda bulunur:

sudo apt update
sudo apt install -y pgbouncer
# Sürümü doğrula
pgbouncer --version

Yapılandırma dosyaları /etc/pgbouncer/ altındadır. Ana dosya pgbouncer.ini, kullanıcı listesi ise userlist.txt. Başlangıç olarak şu yapılandırmayı kullanabilirsiniz:

; /etc/pgbouncer/pgbouncer.ini
[databases]
; İstemcinin göreceği ad = gerçek hedef
uygulama = host=127.0.0.1 port=5432 dbname=uygulama
rapor    = host=185.12.34.57 port=5432 dbname=uygulama

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

server_idle_timeout = 600
server_lifetime = 3600
query_wait_timeout = 120

admin_users = pgbouncer_admin
stats_users = pgbouncer_stat

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
ignore_startup_parameters = extra_float_digits

listen_addr = 127.0.0.1 satırı bilinçli bir seçimdir: PgBouncer uygulama sunucusuyla aynı makinedeyse dışarı açılmasına gerek yoktur. Ayrı bir makinedeyse iç ağ adresini yazın, asla * bırakıp güvenlik duvarını unutmayın.

ignore_startup_parameters = extra_float_digits satırı olmadan bazı sürücüler (özellikle bazı Java ve Node kütüphaneleri) bağlantıda hata alır; bu satır PgBouncer kurulumlarında neredeyse her zaman gereklidir.

Kimlik Doğrulama#

userlist.txt dosyası, kullanıcı adlarını ve parola özetlerini tutar. Parolaları düz metin yazmak yerine PostgreSQL'in kendi sakladığı SCRAM özetini kopyalayın:

sudo -u postgres psql -Atq -c \
  "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolpassword IS NOT NULL;" \
  | sudo tee /etc/pgbouncer/userlist.txt

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt

Dosya şuna benzer görünür:

"uygulama" "SCRAM-SHA-256$4096:...uzun bir dize..."
"rapor" "SCRAM-SHA-256$4096:...uzun bir dize..."

Kullanıcı sayısı çoksa her parola değişikliğinde bu dosyayı güncellemek zahmetlidir. Alternatif olarak auth_query kullanabilirsiniz; PgBouncer parolayı doğrudan veritabanından sorgular:

auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename = $1

Bunun için sınırlı yetkili bir yardımcı kullanıcı oluşturun ve userlist.txt içinde yalnızca onun özetini tutun. Böylece uygulama kullanıcılarının parolası değiştiğinde PgBouncer tarafında hiçbir şey yapmanız gerekmez.

Servisi başlatıp bağlantıyı test edin:

sudo systemctl enable --now pgbouncer
sudo systemctl status pgbouncer --no-pager

# 6432 portundan bağlan
psql -h 127.0.0.1 -p 6432 -U uygulama -d uygulama -c "SELECT now();"

Havuz Modları: En Kritik Karar#

PgBouncer üç havuz modu sunar ve yanlış seçim, sessiz ve çok tehlikeli hatalara yol açar.

Doğru cevap neredeyse her zaman transaction'dır. Verimin büyük kısmını sağlar ve kısıtları yönetilebilirdir. statement modu yalnızca otomatik onaylı, tek sorguluk iş yükleri içindir ve işlem kullanan hiçbir uygulama onunla çalışmaz.

Transaction modunun kısıtı şudur: bir bağlantı işlem bittiğinde başka bir istemciye geçtiği için, oturuma bağlı hiçbir durum korunmaz. Şunlar çalışmaz ya da beklenmedik davranır:

1. PREPARE ile hazırlanmış ifadeler (sürücünüz sunucu tarafı hazırlama yapıyorsa).
2. SET ile yapılan oturum ayarları — SET search_path bir sonraki işlemde kaybolur.
3. LISTEN / NOTIFY — dinleyici bağlantısı sürekli aynı kalmaz.
4. Danışman kilitler (pg_advisory_lock) — kilidi alan bağlantı başkasına geçebilir.
5. Geçici tablolar (CREATE TEMP TABLE) — işlem sonunda erişilemez hâle gelir.
6. WITH HOLD imleçleri.

Bunlar gerçek problemlerdir ve genelde üretimde, aylar sonra, açıklanamayan bir hata olarak ortaya çıkar. Çözüm iki yönlüdür. Birincisi, SET yerine SET LOCAL kullanmak; ayar işlem sınırları içinde kalır ve güvenlidir. İkincisi, oturum durumu gerektiren bileşenleri (kuyruk dinleyicileri, danışman kilit kullanan işçiler) session modunda ayrı bir havuza yönlendirmektir:

[databases]
uygulama       = host=127.0.0.1 port=5432 dbname=uygulama pool_mode=transaction
uygulama_islem = host=127.0.0.1 port=5432 dbname=uygulama pool_mode=session pool_size=10

Aynı veritabanına iki farklı isimle iki havuz tanımladık; uygulamanın hangi bileşeninin hangi ismi kullanacağına siz karar verirsiniz. Sürücünüz sunucu tarafı hazırlanmış ifade kullanıyorsa, PgBouncer 1.21 ve sonrasında max_prepared_statements ayarıyla bu desteklenir:

max_prepared_statements = 200

Bu ayar sıfırsa (varsayılan) ve sürücünüz hazırlanmış ifade kullanıyorsa prepared statement ... does not exist hataları alırsınız — bu, PgBouncer'a geçen ekiplerin gördüğü bir numaralı hatadır.

Havuz Boyutlandırma#

default_pool_size, veritabanı ve kullanıcı çifti başına açılacak gerçek bağlantı sayısıdır. En sık yapılan hata bu değeri gereğinden büyük vermektir; havuz, max_connections ile aynı sayıya ayarlanırsa hiçbir şey kazanmazsınız.

Pratik bir başlangıç formülü:

default_pool_size ≈ (CPU çekirdek sayısı × 2) + etkin disk mili sayısı

SSD kullanıyorsanız son terim küçüktür. 8 çekirdekli bir sunucuda 20-25 makul bir başlangıçtır. Bu sayı düşük gelebilir ama unutmayın: 25 eşzamanlı çalışan sorgu, 8 çekirdekli bir makine için zaten doyma noktasıdır.

PostgreSQL tarafındaki max_connections değeri, tüm havuzların toplamının üzerinde olmalıdır:

max_connections ≥ (havuz sayısı × default_pool_size) + reserve_pool + yönetim bağlantıları

max_client_conn ise PgBouncer'a bağlanabilecek istemci sayısıdır ve cömert olabilir; bunlar hafif bağlantılardır. 1000 tipik bir değerdir. İstemciler havuzda yer açılana kadar bekler, hata almaz — asıl kazanç budur.

İşletim sistemi tarafında dosya tanıtıcı sınırını da yükseltmeyi unutmayın:

; pgbouncer.ini içinde
; max_client_conn + default_pool_size toplamından fazla olmalı

# systemd birimine limit ekle
sudo systemctl edit pgbouncer
# Açılan dosyaya:
# [Service]
# LimitNOFILE=8192
sudo systemctl daemon-reload && sudo systemctl restart pgbouncer

PostgreSQL tarafındaki bellek ayarlarını da havuz boyutuna göre yeniden düşünün; work_mem hesabı artık 1000 değil 25 bağlantı üzerinden yapılabilir ve bu size ciddi bir bellek serbestliği kazandırır. Ayrıntı için postgresql.conf optimizasyon rehberi yazısına bakın.

İzleme ve Yönetim Konsolu#

PgBouncer'ın kendi sanal veritabanı vardır: pgbouncer. Buraya bağlanıp SQL benzeri komutlarla durumu izlersiniz.

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

En sık kullanacağınız komutlar:

SHOW POOLS;      -- havuz başına bekleyen ve aktif istemci sayısı
SHOW CLIENTS;    -- bağlı istemciler ve durumları
SHOW SERVERS;    -- gerçek PostgreSQL bağlantıları
SHOW STATS;      -- veritabanı başına sorgu ve trafik sayaçları
SHOW CONFIG;     -- yürürlükteki ayarlar

SHOW POOLS çıktısındaki cl_waiting sütunu en önemli göstergedir: sıfırdan büyükse istemciler havuzda yer bekliyor demektir. Sürekli yüksekse ya havuzu büyütmeniz ya da sorgularınızı hızlandırmanız gerekir. maxwait sütunu, bir istemcinin en uzun ne kadar beklediğini saniye cinsinden verir; bu değerin birkaç saniyeyi geçmesi kullanıcı tarafında donma olarak hissedilir.

Yönetim komutları da aynı konsoldan çalışır:

RELOAD;              -- yapılandırmayı yeniden oku (kesintisiz)
PAUSE uygulama;      -- yeni sorguları beklet, mevcutları bitir
RESUME uygulama;     -- devam et
RECONNECT uygulama;  -- sunucu bağlantılarını tazele

PAUSE komutu, veritabanı bakımı veya devir alma sırasında altın değerindedir: istemciler hata almaz, sadece bekler. Bakım bitince RESUME dersiniz ve kimse kopmuş bir bağlantı görmez. Replikasyonlu bir yapıda devir alma sırasında bu akışı kullanmak için PostgreSQL streaming replikasyon yazısındaki failover adımlarına bakabilirsiniz.

Sık Yapılan Hatalar#

max_connections artırarak sorunu çözmeye çalışmak. Bu, bağlantı hatasını bellek tükenmesine dönüştürür; her yeni bağlantı ek süreç ve ek work_mem riski demektir. Aynı hatanın MySQL'deki karşılığını MySQL too many connections hatası yazısında görebilirsiniz; mantık birebir aynıdır.

Havuzu gereğinden büyük vermek. default_pool_size = 200 yazmak PgBouncer'ı süslü bir vekil sunucuya indirger. Havuzun küçük olması bir kusur değil, çalışma prensibidir.

Transaction modda oturum durumuna güvenmek. SET search_path ile başlayan uygulamalar, PgBouncer'a geçtikten sonra rastgele şemalarda sorgu çalıştırmaya başlar. SET LOCAL kullanın ya da bağlantı dizesine options=-c search_path=uygulama ekleyin.

Hazırlanmış ifade ayarını atlamak. Sürücünüz varsayılan olarak sunucu tarafı hazırlama yapıyorsa ya max_prepared_statements ayarını açın ya da sürücüde bu özelliği kapatın. İkisini de yapmazsanız yük altında beliren, testte hiç görünmeyen hatalar alırsınız.

Uygulama tarafındaki havuzu unutmak. Uygulamanız kendi havuzunu kullanıyorsa (ORM'lerin çoğu kullanır) iki katmanlı havuz oluşur. Uygulama havuzunu küçük tutun; iki havuzun çarpımı PgBouncer'ın max_client_conn değerini aşmasın.

Zaman aşımlarını varsayılan bırakmak. query_wait_timeout çok yüksekse, veritabanı yavaşladığında istemciler dakikalarca bekler ve hata yerine donma yaşarsınız. server_lifetime ile bağlantıları düzenli tazelemek de uzun ömürlü bağlantılarda birikebilecek sorunları önler.

Kimlik dosyasının izinlerini gevşek bırakmak. userlist.txt parola özetlerini içerir; chmod 600 ve doğru sahiplik şarttır. Sunucuda çalışan başka bir servis bu dosyayı okuyabiliyorsa veritabanı erişiminiz risk altındadır.

Sıkça Sorulan Sorular#

PgBouncer hangi havuz modunda kullanılmalı#

Neredeyse her zaman transaction modunda. Bu mod, verimin büyük kısmını sağlarken uygulamaların çoğuyla uyumludur. session modu havuzdan pek kazanç sağlamaz ama hiçbir kısıtı yoktur; LISTEN/NOTIFY ya da danışman kilit kullanan bileşenler için ayrı bir session havuzu tanımlayın. statement modu yalnızca işlem kullanmayan çok özel iş yükleri içindir.

PgBouncer havuz boyutu kaç olmalı#

Başlangıç için CPU çekirdek sayısının iki katı civarında bir değer alın; 8 çekirdekli bir sunucuda 20-25 makuldür. Büyük görünmesi gereken sayı max_client_conn (istemci tarafı, 1000 gibi), küçük kalması gereken sayı default_pool_size'dır. Havuzu büyütmeden önce SHOW POOLS çıktısındaki cl_waiting ve maxwait değerlerine bakın; bekleme yoksa büyütmenin faydası yoktur.

PgBouncer kullanınca prepared statement hatası alıyorum#

Bu, transaction modunda sunucu tarafı hazırlanmış ifadelerin bağlantılar arasında taşınmamasından kaynaklanır. İki çözüm var: PgBouncer 1.21 ve sonrasında max_prepared_statements ayarını sıfırdan büyük bir değere getirin, ya da veritabanı sürücünüzde sunucu tarafı hazırlamayı kapatın. Sürücüye göre bu ayarın adı değişir; belgelerinde "prepared statement" veya "prepare threshold" ifadesini arayın.

PgBouncer yerine ne kullanabilirim#

PostgreSQL 14 ve sonrasında uygulama sunucusu tarafında havuz kullanmak birçok senaryoda yeterlidir; birçok çerçeve kendi havuzunu getirir. Daha gelişmiş yönlendirme, okuma-yazma ayrımı ve otomatik devir alma istiyorsanız Pgpool-II veya Odyssey gibi alternatifler vardır. Yalnızca bağlantı sayısını düşürmek istiyorsanız PgBouncer hâlâ en hafif ve en öngörülebilir seçenektir.

PgBouncer'ı hangi sunucuya kurmalıyım#

İki geçerli yaklaşım var. Uygulama sunucusuna kurarsanız ağ gecikmesi sıfıra iner ve tek bir PgBouncer arızası tüm sistemi etkilemez; ancak her uygulama sunucusu ayrı bir havuz açar ve toplam bağlantı sayısı kolayca büyür. Veritabanı sunucusuna kurarsanız toplam bağlantı kontrolü mükemmeldir ama tek bir hata noktası oluşur. Az sayıda uygulama sunucusu varsa uygulama tarafı, çok sayıda varsa veritabanı tarafı daha uygundur.

PgBouncer bakım sırasında kesintiyi nasıl azaltır#

Yönetim konsolundaki PAUSE komutu, yeni sorguları kuyruğa alır ve çalışanların bitmesini bekler. Bu sırada istemciler bağlantı hatası almaz, yalnızca yanıt gecikir. Veritabanını yeniden başlatıp RESUME dediğinizde her şey kaldığı yerden devam eder. Kısa süren bakımlarda ve devir alma senaryolarında kullanıcı tarafında hiç hata görünmemesini bu mekanizma sağlar.

Kapanış#

PgBouncer, PostgreSQL kurulumlarındaki en yüksek getirili tek eklemedir: birkaç yüz satırlık bir yapılandırmayla bağlantı hatalarını bitirir, bellek kullanımını düşürür ve sunucunun çekirdek sayısına uygun bir eşzamanlılıkta çalışmasını sağlar. Aklınızda tutmanız gereken dört alışkanlık: transaction modunu seçin ama oturum durumu gerektiren bileşenler için ayrı bir session havuzu tanımlayın, havuzu küçük tutup max_client_conn değerini büyük verin, SHOW POOLS çıktısındaki cl_waiting ve maxwait değerlerini izlemeye alın ve userlist.txt dosyasının izinlerini sıkı tutun.

Bağlantı havuzu, sunucunun kaynaklarını verimli kullanmanızı sağlar ama sınırsız kapasite yaratmaz; belli bir noktadan sonra CPU ve disk gerçekten yetmez. Kaynakları garantili VDS ve bulut sunucu paketlerimizde PostgreSQL ve PgBouncer'ı birlikte konumlandırabilir, daha yüksek yükler için dedicated sunucu seçeneğimize geçebilirsiniz. Kurulum, boyutlandırma ve izleme işini bize bırakmak isterseniz sunucu yönetimi hizmetimiz bu yükü üstlenir.