Veritabanı Performans Ayarlama: PgBouncer Prepared Statement Tuzakları

Veritabanı optimizasyonu için önce protokol seviyesindeki riski tanımlayın

PgBouncer transaction modunda istemci bir transaction tamamladığında aynı PostgreSQL backend oturumunu korumaz. Buna karşılık PostgreSQL prepared statement'ları backend oturumuna bağlıdır. Uygulama SQL metniyle PREPARE üretip sonraki transaction içinde EXECUTE çağırırsa, istek başka bir backende düştüğünde prepared statement bulunamadı hatası alabilir. İlk teşhis için uygulama bağlantısından ve doğrudan PostgreSQL bağlantısından aşağıdaki sorguyu çalıştırın; PgBouncer arkasında sonuçların istekler arasında değişmesi beklenen bir sinyaldir.

SELECT name, statement, generic_plans, custom_plans
FROM pg_prepared_statements
ORDER BY name;

Önemli ayrım şudur: sürücülerin extended query protocol ile gönderdiği named prepared statement'lar ile SQL metni içindeki PREPARE aynı mekanizma değildir. Güncel PgBouncer yapılandırmalarında max_prepared_statements, protocol-level named statement'ları backendler arasında izleyip gerektiğinde yeniden hazırlayabilir; SQL ile yazılmış PREPARE ve EXECUTE kullanımını transaction pooling altında güvenli hale getirmez. Kod tabanında bu anti-pattern'i bulmak için migration ve repository dosyalarında somut bir tarama yapın.

rg -n --glob '*.sql' --glob '*.java' --glob '*.ts'   'PREPARE\s+|EXECUTE\s+|DEALLOCATE\s+' .

Bu ayrım veritabanı eğitimi içinde çoğu zaman atlanır: sorun sadece bağlantı sayısı değildir, PostgreSQL Parse mesajında verilen statement adının backend oturumunda yaşamasıdır. Hata oranını PgBouncer yönetim veritabanından izleyin. cl_active yükselirken sv_active sabit kalıyorsa havuz kuyruk oluşturuyordur; prepared statement hatasını yalnızca uygulama exception sayısı ile değil, bu kuyruk metriğiyle birlikte yorumlayın.

psql 'dbname=pgbouncer' -c 'SHOW POOLS;'
psql 'dbname=pgbouncer' -c 'SHOW STATS;'

Veritabanı performans ayarlama: önce-sonra ölçümünü planlama maliyetiyle kurun

Bağlantı havuzu değişikliğini yalnızca ortalama sorgu süresiyle değerlendirmeyin. PostgreSQL'de pg_stat_statements için planlama istatistiklerini açın, ardından kontrollü yük öncesinde istatistiği sıfırlayın. plans / calls oranı, parse ve planlama tekrarını; total_plan_time / calls ise istek başına planlama maliyetini verir. Bu iki değer, prepared statement kapatmanın parse maliyetini gerçekten ne kadar artırdığını gösterir.

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.track_planning = on;
SELECT pg_reload_conf();
SELECT pg_stat_statements_reset();

Aynı veri seti, aynı eşzamanlılık ve en az iki ısınma turu ile pgbench çalıştırın. Birinci koşulda sürücüde server-side prepare kapalı, ikinci koşulda PgBouncer prepared statement takibi açık olmalıdır. -l her transaction için gecikme günlüğü üretir; sonuçta ortalama yerine p95 ve p99 hesaplayın. Havuz doygunken ortalama değer düşük kalabilir, ancak kuyrukta bekleyen az sayıdaki isteğin p99'u kullanıcı tarafındaki gecikmeyi belirler.

pgbench -n -c 80 -j 8 -T 180 -P 10 -l   -f workload.sql 'postgresql://app@127.0.0.1:6432/appdb'
awk '{print $3}' pgbench_log.* | sort -n |   awk '{a[NR]=$1} END {print "p95_ms=" a[int(NR*0.95)], "p99_ms=" a[int(NR*0.99)]}'

Yükten sonra sorguları planlama maliyetine göre sıralayın. Bir sorguda calls yüksek, mean_plan_ms anlamlı ve mean_exec_ms düşükse, server-side prepare veya SQL metninin sadeleştirilmesi önceliklidir. Tersine, yürütme süresi baskınsa sorun prepared statement değil; örneğin eksik database indexleme, disk I/O veya kilit beklemesi olabilir. Bu ayrım, yanlış katmanda yapılan veritabanı performans ayarlama değişikliklerini engeller.

SELECT queryid,
       calls,
       round(total_plan_time / NULLIF(calls, 0), 3) AS mean_plan_ms,
       round(total_exec_time / NULLIF(calls, 0), 3) AS mean_exec_ms,
       left(query, 180) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_plan_time DESC
LIMIT 20;

PgBouncer ve sürücü ayarını birlikte değiştirin

Transaction pooling seçildiyse PgBouncer'da havuz boyutunu PostgreSQL'in gerçek backend bütçesinden türetin. Örneğin PostgreSQL max_connections değeri 300 ise bunu tamamen uygulamaya vermeyin: bakım, izleme, migration ve yönetici bağlantıları için baştan pay ayırın. Aşağıdaki örnekte uygulama havuzu 160 backend ile sınırlandırılmıştır. max_client_conn istemci kabul sınırıdır, PostgreSQL backend sayısı değildir; ikisini eşitlemek yoğun trafikte bağlantı fırtınasına yol açar.

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 160
reserve_pool_size = 20
reserve_pool_timeout = 3
max_prepared_statements = 200
server_idle_timeout = 300
query_wait_timeout = 15

PgBouncer'ın prepared statement yeniden yazma desteğini kullanmayacak veya henüz doğrulamayacaksanız, PostgreSQL JDBC sürücüsünde server-side prepare'ı açıkça kapatın. Bu değişiklik her sorguda Parse ve planlama çalıştırır, bu nedenle bir önceki bölümdeki mean_plan_ms karşılaştırması zorunludur. Buna rağmen transaction pooling ile SQL-level prepared statement çakışmalarını ortadan kaldırır. JDBC URL'sine eklenen değer uygulama kodunda görünür ve sürüm yükseltmelerinde denetlenebilir olur.

jdbc:postgresql://pgbouncer.internal:6432/appdb?
sslmode=require&prepareThreshold=0&ApplicationName=orders-api

PgBouncer prepared statement takibini etkinleştirirken sürücü başına davranışı test edin. Örneğin node-postgres'te aynı bağlantıda tekrar kullanılan named query, statement adını backend üzerinde saklar. Uygulama sürümü, tenant veya sorgu varyantını ada katmadan sabit bir ad üretmek, farklı SQL metinlerinin aynı isimle çarpışmasına neden olur. Adı SQL şablonunun kararlı hash'iyle üretin; dinamik kullanıcı girdisini statement adına koymayın.

import crypto from 'node:crypto';

const text = 'select id, status from orders where tenant_id = $1 and id = $2';
const name = 'order_by_id_' + crypto.createHash('sha256')
  .update(text)
  .digest('hex')
  .slice(0, 12);

const result = await pool.query({ name, text, values: [tenantId, orderId] });

SQL eğitimi kapsamında generic plan ve index etkisini ayrı inceleyin

Prepared statement çalışıyorsa PostgreSQL belirli çağrılardan sonra custom plan ile generic plan maliyetlerini karşılaştırabilir. Tenant dağılımı dengesiz bir sistemde generic plan, küçük tenant için index scan yerine büyük tenant varsayımıyla daha pahalı bir yol seçebilir. Doğrudan PostgreSQL bağlantısında aşağıdaki deneyi yapın: önce normal modda, sonra yalnızca teşhis amacıyla force_custom_plan modunda aynı parametrelerle EXPLAIN ANALYZE alın. Kalıcı olarak zorlamak yerine gerçek veri dağılımını ve index tasarımını düzeltmek daha güvenlidir.

SET plan_cache_mode = auto;
PREPARE tenant_orders(uuid) AS
  SELECT id, created_at, total
  FROM orders
  WHERE tenant_id = $1
  ORDER BY created_at DESC
  LIMIT 50;

EXPLAIN (ANALYZE, BUFFERS) EXECUTE tenant_orders('11111111-1111-1111-1111-111111111111');
SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE tenant_orders('11111111-1111-1111-1111-111111111111');

Bu örnekte database indexleme kararı için orders(tenant_id, created_at DESC) INCLUDE (id, total) indeksi, sorgunun filtre ve sıralamasını tek B-tree üzerinden karşılayabilir. Ancak index-only scan ancak ilgili heap sayfaları visibility map üzerinde all-visible ise heap fetch yapmaz; yoğun yazılan tabloda Heap Fetches yüksek kalabilir. Bu nedenle yalnızca index eklemek yerine EXPLAIN (ANALYZE, BUFFERS) çıktısındaki shared hit/read ve heap fetch sayılarını önce-sonra kaydedin.

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE tenant_id = '11111111-1111-1111-1111-111111111111'
ORDER BY created_at DESC
LIMIT 50;

Uygulamalı bir sql eğitimi için kritik edge case şudur: PgBouncer transaction modunda SET ile oturum parametresi değiştirmek, sonraki transaction'da aynı backendin gelmesini garanti etmez. Sorguya özel plan teşhisinde ayarı transaction içine SET LOCAL ile koyun. Böylece commit sonrası ayar geri alınır ve havuza iade edilen backend başka istemci için kirlenmez.

BEGIN;
SET LOCAL plan_cache_mode = force_custom_plan;
SELECT id FROM orders WHERE tenant_id = '11111111-1111-1111-1111-111111111111' LIMIT 10;
COMMIT;

SQL Server eğitimi ve NoSQL eğitimi için havuz karşılıkları

Bir sql server eğitimi modülünde PgBouncer ayarını SQL Server'a birebir taşımayın. SQL Server istemci havuzu genellikle sürücü tarafındadır; bağlantı sayısı, uyuyan oturumlar ve istek beklemeleri DMV'lerden izlenir. Aşağıdaki sorguda sleeping oturum sayısı artarken uygulama timeout üretiyorsa, önce connection string varyasyonları nedeniyle birden çok ayrı havuz oluşup oluşmadığını kontrol edin. Connection string içindeki farklı Application Name veya kimlik doğrulama seçeneği ayrı havuz anahtarı oluşturabilir.

SELECT s.program_name,
       s.status,
       COUNT(*) AS session_count
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
GROUP BY s.program_name, s.status
ORDER BY session_count DESC;

NoSQL eğitimi tarafında MongoDB sürücüsünün maxPoolSize değeri backend connection sınırıdır, ancak uzun süren cursor'lar bağlantıyı işgal edebilir. Node.js MongoDB driver ile command monitoring açıp duration değerini kaydedin ve maxPoolSize değişiminden önce-sonra p99 ölçün. Sadece havuzu büyütmek, yavaş aggregation pipeline'ını hızlandırmaz; kuyruktaki beklemeyi daha fazla eşzamanlı sunucu işine dönüştürür.

const client = new MongoClient(uri, {
  maxPoolSize: 80,
  waitQueueTimeoutMS: 5000,
  monitorCommands: true
});
client.on('commandSucceeded', event => {
  if (event.duration > 100) console.log(event.commandName, event.duration);
});

Sık Sorulan Sorular

Veritabanı performans ayarlama sırasında PgBouncer transaction pooling ile PREPARE kullanılabilir mi?

SQL metniyle gönderilen PREPARE ve EXECUTE'yi transaction pooling altında kullanmayın; statement backend oturumuna bağlıdır ve sonraki transaction başka backende gidebilir. Sürücünün protocol-level prepared statement desteğini max_prepared_statements ile doğrulayın veya JDBC için prepareThreshold=0 kullanın. Her iki seçeneği pg_stat_statements total_plan_time ve pgbench p99 ile karşılaştırın.

Database indexleme prepared statement kaynaklı yavaş sorguyu çözer mi?

Yalnızca yürütme planındaki erişim maliyeti baskınsa çözer. pg_stat_statements içinde total_plan_time yüksekse index parse-plan maliyetini azaltmaz. EXPLAIN ANALYZE BUFFERS ile actual time, shared read ve Heap Fetches değerlerini alın; ardından önerilen indeksi CREATE INDEX CONCURRENTLY ile ekleyip aynı parametre setinde tekrar ölçün.

SQL eğitimi için PostgreSQL generic plan sorunu nasıl yeniden üretilir?

Dengesiz tenant verisi oluşturun, tenant_id parametreli bir PREPARE tanımlayın ve pg_prepared_statements içindeki generic_plans ile custom_plans sayaçlarını izleyin. EXPLAIN ANALYZE çıktısını plan_cache_mode=auto ve force_custom_plan altında karşılaştırın. Testi PgBouncer transaction havuzu yerine doğrudan backend veya session pooling bağlantısında yapın.

SQL Server eğitimi ile PgBouncer havuz metrikleri nasıl ayrıştırılmalı?

PgBouncer için SHOW POOLS içindeki cl_waiting ve sv_active değerlerini, SQL Server için sys.dm_exec_sessions ve gerekirse sys.dm_exec_requests DMV'lerini kullanın. SQL Server'da ayrı connection string'ler ayrı istemci havuzları oluşturabilir; PgBouncer'da ise pool_mode ve default_pool_size backend tahsisini belirler. Aynı sayı hedefi yerine her platformda kuyruk gecikmesi ve p99 timeout oranını ölçün.