Uygulama veri büyüdükçe neden yavaşlıyor? Veritabanı darboğazları, indeks ve sorgu planı

Uygulamanız ilk aylarda hızlıydı, veri büyüdü ve şimdi aynı ekran saniyelerce açılıyorsa sorun neredeyse hiçbir zaman "sunucu zayıf" değildir. Sorun, maliyeti kayıt sayısıyla birlikte büyüyen sorgulardır. Beş yüz satırlık geliştirme veritabanında hepsi anlık görünür, beş milyon satırda aynı sorgu tabloyu baştan sona tarar. Tekrar eden dört sebep var: eksik ya da yanlış indeks, ORM'in ürettiği N+1 sorgu, sayfa numarasıyla yapılan OFFSET sayfalama, ve tükenmiş bağlantı havuzu. Dördü de ölçümle bulunur, tahminle değil. Donanım büyütmek bunları örtebilir ama çözmez, üstelik faturayı kalıcı hâle getirir.

Ortalama süre size sorunu göstermez

İlk hata yanlış sayıya bakmakla başlıyor. Ortalama yanıt süresi 180 milisaniye görünürken kullanıcıların yüzde beşi altı saniye bekliyor olabilir; şikayet o yüzde beşten gelir, panelde ise her şey yeşildir. Uç nokta bazında p95 ve p99 değerlerine bakın.

Yanında ikinci bir sayı tutun: istek başına çalışan sorgu adedi. Bu tek başına en ucuz teşhis aracıdır. Bir uç noktanın sorgu sayısı listedeki kayıt sayısıyla orantılı büyüyorsa cevabı zaten bulmuşsunuzdur. Ölçümü uygulama tarafına yerleştirmenin ve eşik belirlemenin nasıl kurulduğunu izlenebilirlik, SLO ve hata bütçesi yazımızda anlattık.

Önce hangi sorgu olduğunu bulun

PostgreSQL'de doğru araç pg_stat_statements. Sunucunun çalıştırdığı tüm SQL ifadelerinin planlama ve yürütme istatistiklerini tutar, ama ek paylaşımlı bellek gerektirdiği için postgresql.conf içinde shared_preload_libraries listesine eklenmesi ve sunucunun yeniden başlatılması şart. Varsayılan olarak 5000 farklı ifade izlenir. Buradaki asıl bakılacak sütun tek çağrının süresi değil, toplam süredir: günde 40 bin kez çalışan 30 milisaniyelik bir sorgu, günde iki kez çalışan iki saniyelik sorgudan çok daha pahalıdır.

MySQL'de yavaş sorgu günlüğü varsayılan olarak kapalıdır ve long_query_time varsayılanı 10 saniyedir. Bu eşik pratikte hiçbir işe yaramaz, çünkü sizi yavaşlatan sorgular 200 ile 800 milisaniye arasındadır. Değeri 0,2 gibi bir seviyeye çekin. log_queries_not_using_indexes değişkenini de bir süreliğine açın; indeks kullanmayan sorguları ayrıca işaretler.

EXPLAIN ANALYZE okumayı öğrenin, maliyet sayısına takılmayın

EXPLAIN planı gösterir, EXPLAIN ANALYZE ise sorguyu gerçekten çalıştırıp gerçek süreleri ekler. Bu ayrımı atlamayın: ANALYZE bir UPDATE ya da DELETE üzerinde çalıştırıldığında veriyi gerçekten değiştirir. Yazma sorgularını incelerken işlemi BEGIN ile açıp sonunda ROLLBACK yapın.

Planda bakılacak şey plan düğümlerinin yanındaki soyut maliyet tahmini değil, tahmin edilen satır sayısı ile gerçekte dönen satır sayısı arasındaki farktır. Planlayıcı 50 satır bekleyip 400 bin satır geldiyse yanlış birleştirme yöntemini seçmiş demektir ve asıl sorun istatistiklerdedir. PostgreSQL 18'den itibaren BUFFERS bilgisi ANALYZE ile otomatik geliyor; oradaki okunan blok sayısı, sorgunun diske mi gittiğini yoksa önbellekten mi döndüğünü söyler. Aynı sorgunun bazen hızlı bazen yavaş olmasının açıklaması genelde burada saklıdır.

Yabancı anahtarlar indekslenmiyor, PostgreSQL bunu sizin yerinize yapmıyor

Sahada en sık gördüğümüz somut hata bu. PostgreSQL belgeleri açıkça yazıyor: referans veren sütunları indekslemek çoğu zaman iyi bir fikirdir, çünkü referans verilen tablodan bir satır silmek ya da güncellemek referans veren tabloda tarama gerektirir; ancak bu her zaman gerekli olmadığı ve indeksleme biçimi değişebildiği için yabancı anahtar kısıtı tanımlamak otomatik olarak indeks oluşturmaz.

MySQL'de durum tersidir. InnoDB yabancı anahtar kontrollerinin tablo taraması gerektirmemesi için indeks zorunlu tutar ve yoksa referans veren tabloda otomatik oluşturur. Yani MySQL'den PostgreSQL'e taşınan bir şemada, kimsenin dokunmadığı hâlde tüm birleştirmelerin ve silmelerin birden yavaşlaması normaldir. Kontrolü bir kez yapın: yabancı anahtar sütunlarını dolaşıp karşılığında indeks olup olmadığına bakan tek bir sorgu yeter.

İndeks yazdığınız her yerde çalışmaz

Bir indeksin varlığı kullanılacağı anlamına gelmiyor. Üç durum bunu bozar. Birincisi bileşik indekste sütun sırası: (musteri_id, tarih) indeksi yalnızca musteri_id ile filtreleyen sorguya yarar, yalnızca tarih ile filtreleyene yaramaz. İkincisi sütunun üzerine uygulanan fonksiyon: WHERE lower(eposta) = ... yazdığınızda eposta üzerindeki normal indeks devre dışı kalır, çünkü indekste saklanan değer dönüştürülmüş hâli değildir. Çözüm ya sorguyu düzeltmek ya da ifade indeksi tanımlamaktır. Üçüncüsü baştan joker karakterli arama: LIKE '%anahtar%' bir B-tree indeksini kullanamaz, bunun için tam metin araması ya da trigram indeksi gerekir.

Ters yönü de var. Kullanılmayan indeks bedavaya gelmiyor: her INSERT, UPDATE ve DELETE işleminde güncellenmesi gerekiyor ve disk yer kaplıyor. PostgreSQL'de pg_stat_user_indexes görünümündeki tarama sayacı sıfırda kalan indeksleri düzenli olarak gözden geçirin. "Ne olur ne olmaz" diye eklenmiş beş indeks, yazma yükü ağır bir tabloda ölçülebilir bir yavaşlama demektir.

N+1: ORM'in sessizce yaptığı iş

Yüz siparişi listeleyen bir sayfa yazıyorsunuz, her siparişin müşteri adını da gösteriyorsunuz. ORM önce siparişleri çeken bir sorgu çalıştırıyor, sonra döngü içinde her sipariş için ayrı bir müşteri sorgusu daha. Toplam 101 sorgu. Geliştirme ortamında on kayıt olduğu için kimse fark etmiyor, üretimde her biri bir milisaniyelik ağ turu olan yüz ekstra sorgu birikip yarım saniyeye çıkıyor.

Sebep neredeyse tüm ORM'lerin varsayılan olarak tembel yükleme yapmasıdır. Çözüm ilişkiyi baştan istemektir: Laravel'de with(), Django'da select_related() ve prefetch_related(), Rails'te includes, Hibernate'te join fetch ya da entity graph. GraphQL API'lerinde aynı iş DataLoader benzeri toplu okuma katmanıyla yapılır. Hepsinin ortak yanı, geliştiricinin bunu bilerek yazması gerektiğidir; hiçbiri kendiliğinden devreye girmez.

Bu yüzden düzeltmenin yanına bir de koruma koyun. Kritik uç noktalar için sorgu sayısını doğrulayan test yazın: "bu uç nokta en fazla 5 sorgu çalıştırmalı" diyen bir test, N+1'in ilerideki bir düzenlemeyle geri gelmesini yayına çıkmadan yakalar. Bu tür korumaların test stratejisinde nereye oturduğunu test otomasyonu ve test piramidi yazımızda ele aldık.

Sayfalama: OFFSET derinleştikçe pahalılaşır

Klasik LIMIT 20 OFFSET 10000 yazımının iki ayrı problemi var. PostgreSQL belgelerinin ifadesiyle, OFFSET ile atlanan satırların yine de sunucu içinde hesaplanması gerekir; dolayısıyla büyük bir OFFSET verimsiz olabilir. Yani 500. sayfayı isterken veritabanı önündeki 10 bin satırı üretip çöpe atar. Kullanıcı sayfa numaralarında ilerledikçe her sayfa bir öncekinden yavaşlar.

İkincisi tutarlılık. Siz sayfa 2'ye geçerken listeye yeni kayıt girdiyse sıra kayar: bazı kayıtlar iki kez görünür, bazıları hiç görünmez. Çözüm anahtar tabanlı sayfalama (keyset ya da seek yöntemi): "10 bin satır atla" demek yerine "en son gördüğüm kaydın anahtarından sonrasını getir" demek, yani WHERE (tarih, id) < (?, ?) ORDER BY tarih DESC, id DESC LIMIT 20. Doğru indeksle bu sorgunun süresi kaçıncı sayfada olduğunuzdan bağımsız kalır. Bedeli, rastgele bir sayfa numarasına atlayamamanızdır; sonsuz kaydırma ve "daha fazla yükle" akışlarına doğal olarak uyar, klasik sayfa numarası çubuğuna uymaz. Bir uyarı daha: toplam sayfa sayısı için attığınız COUNT(*) sorgusu çoğu zaman asıl sorgudan pahalıdır. Toplam sayıyı gerçekten gösteriyor musunuz, yoksa alışkanlıktan mı hesaplıyorsunuz?

Bağlantı havuzu: PostgreSQL her bağlantı için ayrı süreç açar

PostgreSQL mimarisi gereği her istemci bağlantısı için yeni bir süreç çatallar. Bu, bağlantının hafif bir nesne değil, bellek maliyeti olan bir işletim sistemi süreci olduğu anlamına gelir. Uygulamanızı yatayda ölçekleyip her biri 20 bağlantılık havuza sahip 15 kopya çalıştırdığınızda 300 bağlantı istemiş olursunuz ve veritabanı bunu kaldıramadığında hata mesajı size sorgular hakkında hiçbir şey söylemez.

Buradaki cevap havuz boyutunu büyütmek değil, araya bir havuzlayıcı koymaktır. PgBouncer gibi bir katman işlem seviyesinde havuzlama yaparak yüzlerce uygulama bağlantısını çok daha az sayıda gerçek sunucu bağlantısına indirger. Aynı yerde sık yapılan bir yapılandırma hatası daha var: work_mem varsayılanı 4MB'dir ve bu değer bağlantı başına değil, işlem başına geçerlidir. Belgelerin uyardığı gibi karmaşık bir sorgu aynı anda birkaç sıralama ve karma işlemi yapabilir, birden fazla oturum da bunu eşzamanlı yapabilir; toplam kullanım work_mem değerinin katları olabilir. Bu ayarı "biraz artıralım" diye 256MB'a çekmek, yoğun saatte sunucunun belleğini bitirmenin bilinen yoludur.

Performans için ham SQL'e inerken açtığınız kapı

Optimizasyon çalışmalarının doğal bir aşaması ORM'i bırakıp elle SQL yazmaktır ve bunda yanlış bir şey yok. Yanlış olan, dinamik filtreleri ve sıralama alanlarını dizge birleştirmesiyle sorgunun içine gömmek. ORM kullanırken sizi koruyan şey parametreli sorgulardı; ham SQL'e geçtiğiniz anda o koruma sizin sorumluluğunuza döner. Değerleri her zaman bağlı parametre olarak geçirin. Sütun ve tablo adları parametre olarak geçirilemediği için ORDER BY alanı gibi yerlerde tek güvenli yöntem, kullanıcıdan gelen değeri izin verilen sabit bir listeyle eşleştirmektir. Bu noktayı ayrıca OWASP Top 10 yazımızda örnekleriyle anlattık.

Bu hafta yapılabilecek beş iş

Bir: pg_stat_statements açın ya da MySQL yavaş sorgu günlüğünü 0,2 saniye eşiğiyle etkinleştirin, bir hafta veri toplayın ve toplam süreye göre sıralayın. İki: en pahalı beş sorguyu EXPLAIN ANALYZE ile inceleyin, tahmin edilen ve gerçek satır sayılarını karşılaştırın. Üç: indeksi olmayan yabancı anahtar sütunlarını listeleyin ve eksikleri tamamlayın. Dört: en çok trafik alan üç uç noktada istek başına sorgu sayısını sayın, sayı sonuç adediyle büyüyorsa eager loading uygulayın. Beş: derin sayfalama yapan ekranları anahtar tabanlı sayfalamaya çevirin.

Bu beş adım genellikle yavaşlığın büyük kısmını çözer. Çözmediği durumda sebep tek tek sorgular değil, veri modelinin ya da erişim düzeninin kendisidir: yanlış normalleştirme, tek tabloya yığılmış olay geçmişi, her yazma isteğinde senkron çalışan raporlama sorguları. O eşiğe geldiyseniz karar artık optimizasyon değil, tasarım kararıdır; nasıl önceliklendirileceğini teknik borç yazımızda, kademeli yenileme ile sıfırdan yazma arasındaki seçimi ise eski yazılımı yenilemek mi, sıfırdan yazmak mı yazımızda ele aldık.