PostgreSQL Bağlantı Havuzu: PgBouncer'ın Üç Modu, Hazırlıklı Deyim Tuzağı ve Çok Kiracılı Boyutlandırma

PostgreSQL'de her istemci bağlantısı sunucu tarafında ayrı bir işletim sistemi sürecidir. Bağlantı açmak kimlik doğrulama, süreç çatallama ve önbellek ısıtma demektir; açık tutmak ise bağlantı başına birkaç megabayt bellek ve planlayıcı üzerinde yük demektir. Uygulama sunucusu sayısı arttıkça, her biri kendi havuzunda 20 bağlantı tutan 30 kopya veritabanına 600 bağlantı olarak yansır. Bunların büyük kısmı boşta bekler ama veritabanı yine de her birini taşır. Bağlantı havuzu bu farkı kapatır: istemciye bol bağlantı verir, veritabanına az bağlantı açar ve ikisini gerektiği anda eşler. Bu yazı PgBouncer'ın üç havuz modunu, hangisinin hangi iş yükünde çalıştığını, çok kiracılı yapıda boyutlandırmayı ve en çok can yakan tuzağı, hazırlıklı deyimleri ele alıyor.

Üç mod, tek karar

PgBouncer bir istemciye sunucu bağlantısını ne kadar süreyle tahsis edeceğine göre üç modda çalışır.

Oturum modu istemci bağlanınca bir sunucu bağlantısı verir ve istemci kopana kadar geri almaz. Sunucu tarafındaki her şey, oturum değişkenleri, hazırlıklı deyimler, geçici tablolar, kilitler, olduğu gibi çalışır. Kazanç yalnızca bağlantı açma maliyetinin önbelleklenmesidir; eşzamanlı istemci sayısı düşmez.

İşlem modu sunucu bağlantısını işlem başında verir, COMMIT ya da ROLLBACK ile geri alır. Ardışık iki işlem farklı sunucu bağlantılarında koşabilir. Bin istemcinin gerçekte aynı anda işlem yürüten 40 tanesi 40 sunucu bağlantısı ile idare eder. Havuzun asıl kazancı burada çıkar ve üretimde neredeyse her zaman istenen mod budur.

Deyim modu her deyimden sonra bağlantıyı geri alır; çok deyimli işlem yasaktır. Yalnızca otomatik commit ile çalışan, salt okuma trafiği gibi dar bir alanı vardır.

Karar şudur: işlem modunu hedefleyin, oturum durumuna dayanan her şeyi uygulamadan ayıklayın. Oturum modu geçiş dönemi için geçici bir çözümdür, kalıcı bir tercih değil.

İşlem modunda bozulan şeyler

İşlem modunda istemci, iki işlem arasında aynı sunucu bağlantısını göreceğini varsayamaz. Bu varsayıma dayanan her özellik ya hata verir ya da daha kötüsü sessizce yanlış çalışır:

• SET ile yapılan oturum ayarları (SET search_path, SET timezone) bir sonraki işlemde kaybolur. Daha tehlikelisi, o ayar sunucu bağlantısında kalır ve o bağlantıyı alan başka bir istemciye sızar. İşlem içinde SET LOCAL kullanın; ayar commit ile birlikte söner.
• LISTEN ve NOTIFY çalışmaz; bildirim, istemcinin artık elinde olmayan bir bağlantıya gelir.
• Oturum seviyesi danışmanlık kilitleri (pg_advisory_lock) yanlış bağlantıda kalır. İşlem seviyesi olanları (pg_advisory_xact_lock) tercih edin.
• Geçici tablolar ve WITH HOLD imleçler işlem bitince erişilmez olur.
• Adlandırılmış hazırlıklı deyimler: aşağıda ayrı başlık.

Bu listeyi kod tabanında aramak, işlem moduna geçişin gerçek işidir. PgBouncer yapılandırması beş satırdır; kodun temizlenmesi haftalar sürebilir.

Hazırlıklı deyim tuzağı

Sürücülerin çoğu performans için sunucu tarafı hazırlıklı deyim kullanır: sorgu planı bir kez hazırlanır, bir adla saklanır, sonraki çağrılar yalnızca adı ve parametreleri gönderir. Bu ad sunucu bağlantısına aittir. İşlem modunda istemci bir sonraki işlemde başka bir sunucu bağlantısına düşer ve "prepared statement does not exist" hatası alır. Sinsi tarafı, hatanın yük altında ve rastgele çıkmasıdır; tek istemciyle yapılan testte havuz hep aynı bağlantıyı verdiği için görünmez.

İki çözüm yolu vardır. Birincisi sürücü tarafında sunucu hazırlıklı deyimlerini kapatmaktır:

• Go pgx: bağlantı dizesine default_query_exec_mode=simple_protocol
• Python psycopg 3: prepare_threshold=None
• JDBC: prepareThreshold=0
• Rails: prepared_statements: false

İkincisi PgBouncer'ın protokol seviyesi hazırlıklı deyimleri kendisinin izlemesidir. 1.21 sürümünden itibaren max_prepared_statements sıfırdan büyük bir değere ayarlanınca PgBouncer istemcinin hazırladığı deyimi hangi sunucu bağlantısına düşerse orada yeniden hazırlar. Bu yalnızca protokol seviyesi (sürücünün gönderdiği Parse mesajı) için geçerlidir; SQL ile yazılan PREPARE deyimini kapsamaz. Yeni kurulumda ikinci yolu seçin, sürücü tarafındaki optimizasyonu kaybetmezsiniz. Eski PgBouncer'a mahkûmsanız birinci yol tek seçenektir.

Temel yapılandırma

Aşağıdaki dosya işlem modunda çalışan, hazırlıklı deyimleri izleyen bir başlangıç noktasıdır:

[databases]
app = host=pg-primary port=5432 dbname=app pool_size=30

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

pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 80

max_prepared_statements = 200
server_idle_timeout = 300
query_wait_timeout = 60
ignore_startup_parameters = extra_float_digits

admin_users = pgbouncer_admin

Değerlerin anlamı: max_client_conn istemci tarafında kabul edilecek toplam bağlantı, default_pool_size her veritabanı ve kullanıcı çifti için açılacak sunucu bağlantısı sayısı. reserve_pool_size bir istemci reserve_pool_timeout saniyeden uzun beklerse devreye giren ek bağlantılar. query_wait_timeout havuzda bekleyen istemcinin ne kadar sonra hata alacağı; bunu sonsuz bırakırsanız veritabanı tıkandığında uygulama katmanı da sessizce yığılır ve zaman aşımı yerine kilitlenme görürsünüz.

ignore_startup_parameters satırı JDBC gibi bazı sürücülerin bağlantı açılışında gönderdiği ve PgBouncer'ın tanımadığı parametreleri yutmak içindir; bunu eklemeden JDBC istemcileri bağlanamaz.

Boyutlandırma

Sunucu bağlantı sayısını istemci sayısından türetmeyin; veritabanının kaç eşzamanlı sorguyu verimli yürütebildiğinden türetin. Yaygın başlangıç kuralı çekirdek sayısının iki katı artı disk sayısıdır. 8 çekirdekli bir sunucuda 20 civarı aktif bağlantı, 200 aktif bağlantıdan daha yüksek iş hacmi verir çünkü bağlam değiştirme ve kilit çekişmesi azalır. Bunun üzerine SHOW POOLS çıktısındaki iki sütunla ayar yapın: cl_waiting sürekli sıfırın üstündeyse ve maxwait büyüyorsa havuz dar; sv_idle hep yüksekse havuz geniş.

PgBouncer'ın havuzu veritabanı ve kullanıcı çifti başınadır. Çok kiracılı yapıda kiracı başına ayrı veritabanı kullanıyorsanız bu çarpım hızla patlar: 200 kiracı ve 20'lik havuz, veritabanına 4000 bağlantı potansiyeli demektir. Bu durumda max_db_connections ve max_user_connections ile üst sınır koyun, min_pool_size değerini sıfırda bırakın ki boş kiracılar bağlantı tutmasın, ve [databases] bölümünde tek tek kiracı yazmak yerine joker girdi kullanın:

[databases]
* = host=pg-primary port=5432 pool_size=5

Toplam üst sınır her koşulda PostgreSQL'in max_connections değerinin altında ve superuser_reserved_connections payını dışarıda bırakacak şekilde kalmalıdır.

Nereye koymalı

Uygulamanın yanına yan konteyner olarak ya da merkezi bir servis olarak. Yan konteyner her uygulama kopyasına ayrı havuz demektir; kopya sayısıyla çarpılan bağlantılar sorununu çözmez, yalnızca öteler. Merkezi kurulum bağlantıları gerçekten birleştirir ve varsayılan tercih olmalıdır. Tek zaafı PgBouncer'ın tek iş parçacıklı olmasıdır; çok yüksek istek hacminde tek süreç doyar. Bunun için aynı portu paylaşan birden fazla PgBouncer süreci çalıştırılabilir ya da çok iş parçacıklı bir alternatif (PgCat gibi) değerlendirilebilir. PgCat ayrıca okuma ve yazma ayrımı yapabilir; bu ihtiyaç yoksa PgBouncer'ın olgunluğu ve sadeliği ağır basar.

Uygulama tarafındaki havuzu (HikariCP, pgx pool) kapatmayın ama küçültün. Uygulama havuzu bağlantı kurma maliyetini, PgBouncer ise veritabanı tarafındaki toplam sayıyı yönetir; ikisi farklı işleri yapar.

Ne zaman kullanmamalı

Tek bir uygulama kopyasıyla çalışıyorsanız ve toplam bağlantı sayısı veritabanının rahat taşıdığı sınırın altındaysa PgBouncer yalnızca bir arıza noktası ekler. LISTEN/NOTIFY üzerine kurulu bir mimariniz varsa işlem modu sizin için kapalıdır; oturum modu da havuzun asıl kazancını vermez, bu durumda uygulama havuzu yeterlidir. Son olarak PgBouncer'ın yeniden başlatılması tüm istemci bağlantılarını düşürür; yapılandırma değişikliği için RELOAD, bakım için PAUSE ve RESUME yönetim komutlarını öğrenmeden üretime almayın.