PostgreSQL 101: Sorgularım Neden Yavaşlıyor?

Yavaş sorgulardan bahsetmiyoruz, birden bire, durup dururken, düne kadar normal çalışırken yavaşladığını farkettiğimiz sorgulardan bahsedelim. Milisaniyeler sürerken saniyeler sürmeye başlayan, birkaç saniye sürerken dakikalar süren sorgular bunlar.

İlk bakışta dikkat edeceğimiz bir kaç noktayı ele alıp eleyelim öncelikle:

Düne göre ne değişti?

Matrix‘te bir değişiklik yapıldığunda oluşan, tekrarlayan görüntülere diyoruz: déjà vu! İlk bakacağımız kontrol noktaları, son yapılan değişiklikler olacaktır. Genellikle de bu yavaşlamaların olası şüphelileri bu değişikliklerdir.

  • Yeni bir veri mi içeriye aldık, ani bir büyüme mi söz konusu oldu? Sorguların düne göre dikkate değer şekilde daha fazla veri getirip getirmediği kontrol edilmelidir.
  • Bir indeks ile mi oynadık? Değişim günlüğü tutuyorsanız buna bakmalısınız. Bu tür durumlarda sorumlulun yan koltukta oturuyor olması karşılaşılmadık bir durum değildir :)
  • Bir monitör/izleme aracı mı ekledik? Cacti, Munin gibi izleme araçları sürekli olarak CPU, bellek, disk kullanımı gibi bilgileri fazla sık şekilde toplamaya başlamış olabilir mi?

Daha önceden kurulu bir izleme aracımız varsa bu sefer de işlemci, bellek, disk ve ağ kullanımı gibi değerlerin değişikliğine bakmak iyi bir başlangıç noktası olabilir. Örneğin, RAID sistemindeki disklerden birisi devre dışı kalmış ve o an yedek disk sisteme ekleniyor dolayısı ile çok yüksek I/O değerleri görünüyor olabilir.

Veritabanı Analizi

Olağan şüphelilerin sorgusundan birşey çıkmadıysa veritabanımıza bir bakalım:

db_01=# analyse;
ANALYZE
Time: 6331.113 ms

Ucuz ve çok kullanılan bir yöntem olmakla beraber, en basit, genellikle bir derde çare olmayan, Türk usulü tabir ettiğimiz, kafaya iki pat pat yapmak anlamına gelir. Ama eğer dikkate değer bir düzelme gözlemlerseniz o zaman bilin ki autovacuum işlevini doğru şekilde yerine getiremiyor demektir. Bu durumda dikkatinizi bu konuya verebilirsiniz.

Bazen, dışarıdan veri ekleme vs sebeplerle belirli bazı tabloların veri boyutu ani şekilde artırılmış ancak tablo istatistikleri güncellenmemiş olabilir. Böyle bir durumda bahsi geçen sorgular için istatistik tabanlı optimizasyon motoru kötü bir plan seçmiş olabilir. Bu durumda ANALYZE komutu elle çalıştırıldığında tablo istatistiklerini günceller ve sorgular doğru planlar ile çalışmaya başlar.

Yava;layan sorgulari izole ederek çalıştırarak deneyebilirsiniz. Development, test vb sistemleriniz varsa veya read-only replikanız üzerinde de aynı performansı mı sergiliyorlar? Aynı anda birlikte çalışan sorgular, birbirleri ile bellek için rekabet ederler. Bu rekabette sizin sorgunuz kaybediyor olabilir.

Aynı sorgunun ikinci defa çalıştırılması durumunda da performans kaybı yaşanıyor mu? İşte bu durum, yukarıda açıkladığımız gibi bellek ile ilgili bir durumu işaret eder. Sonuçlar belleğe sığmıyor veya diğer sorgular tarafından bellek dışına atılıyor olabilir.

Tablo ve Dizin Şişmesi (Index Bloat)

Zaman içerisinde oluşabilen sorunlardan birisi de bakım işlemlerinin doğru şekilde çalıştırılmaması sonucu oluşabilen tablo şişmesi sorunudur. MVCC‘nin çalışma prensipleri gereği, tablolar bir çok eski sürüm veriyi de saklamaya devam eder. Bu eski sürümler bir şekilde silinmezler ise tablo şişmesi sorunu ortaya çıkar.

Bu problemin oluşmasına birkaç farklı sebep neden olabilir ki bunların hemen hepsi INSERT, UPDATE ve DELETE cümlecikleri ile alakalıdır. Bu işlemler esnasında oto vakum (autovacuum) eski verinin kaldırılması işlemini yerine getiremezse ve hatta bu işlevi yerine getirmiş ve eski sürümleri silmiş olsa dahi tablo eski sürümleri (tuples) de içeren veri boyutu ile kalır. Öyle ki sadece tek satır veri içeren tablonun birkaç gigabyte boyutunda gözüktüğü görülmemiş şey değildir.

Böyle durumlar ile karşılaşıldığında aşağıdaki sorgu yardımcı olacaktır:

SELECT
   pg_relation_size(relid) as tablesize, 
   schemaname, relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname = ;

Tablo boyutunu gösteren tablesize ile n_live_tup değeri arasındaki ilişkiyi gözlemlemelisiniz. Öyle ki, eğer tablo boyutu tablo yapısına göre görece büyük, ama satır sayısı çok az ise muhtemelen bir tablo şişmesi sorunu söz konusudur. Böyle bir durumda tablodaki dizinlerin (indexes) de şişmiş olması ve sorguların yavaşlamasına sebep olması kaçınılmazdır.

Dikkat: Tablo yapısına göre görece büyüklükten kasıt, tablo içerisinde blob, büyük text alanların olup olmamasına ilişkin bir göreceliliktir, dikkat ediniz, tabloda hali hazırda büyük boyutlu veri de tutuluyor olabilir. Tutulan verinin boyutuna göre bir büyüklük kıyaslaması yapınız.