@endofPostgreSQL'de yavaş sorguyu bulmak: EXPLAIN ANALYZE çıktısını okumayı nasıl öğrendim
Geçen ay çalıştığım projede sipariş listesi sayfası bazen iki saniyede, bazen dokuz saniyede açılıyordu. İlk refleksim herkesinki gibi oldu: önbellek ekleyelim. Ekip arkadaşım durdurdu, önce sorgunun neden yavaş olduğunu görelim dedi. O hafta EXPLAIN çıktısını okumayı oturup öğrendim ve şimdi geriye bakınca önbellek ekleseydik sorunu sadece halının altına süpürmüş olacaktık.
İşe yarayan sıra şuydu. Önce pg_stat_statements eklentisini açıp hangi sorguların toplamda en çok zaman yediğine baktık. En yavaş tek sorgu ile toplamda en çok vakit harcayan sorgu çoğu zaman aynı şey değil; saniyede yüz kez çalışan 40 milisaniyelik bir sorgu, günde bir kez çalışan 5 saniyelik rapordan daha pahalı olabiliyor. Listeyi toplam süreye göre sıralayınca ilk üç sorgu bütün yükün büyük kısmını taşıyordu.
Sonra o sorguları EXPLAIN (ANALYZE, BUFFERS) ile çalıştırdık. Dokümantasyonun güncel sürümünde ANALYZE seçeneğinin BUFFERS bilgisini de kendiliğinden açtığı yazıyor, eski sürümlerde bunu ayrıca yazmak gerekiyordu. Çıktıda ilk baktığım iki şey oldu: planlayıcının tahmin ettiği satır sayısı ile gerçekte dönen satır sayısı arasındaki fark ve hangi düğümde zamanın gittiği. Bizim sorguda planlayıcı 50 satır bekliyordu, gerçekte 180 bin satır geliyordu. Tahmin bu kadar şaşınca planlayıcı yanlış birleştirme yöntemini seçiyor. Tabloda ANALYZE komutunu çalıştırıp istatistikleri tazeleyince plan kendiliğinden düzeldi.
İkinci sorgunun hikâyesi daha klasikti. Siparişler tablosunda müşteri kimliği yabancı anahtar olarak tanımlıydı ve ben yıllarca PostgreSQL'in buna otomatik indeks açtığını sanmışım. Açmıyor. Yabancı anahtarın işaret ettiği taraf birincil anahtar olduğu için orada indeks var ama referans veren sütunda yok. Seq Scan satırını görünce anladık, indeksi ekledik.
Üçüncüsü e-posta ile arama yapan sorguydu. Koşul lower(email) üzerinden yazılmıştı, indeks ise düz email sütunundaydı. Fonksiyon uygulanmış sütun düz indeksi kullanamıyor; ifade indeksi gerekiyor. Aynı şekilde başında yüzde işareti olan LIKE aramaları da normal B-tree indeksten yararlanamıyor, onun için pg_trgm eklentisiyle GIN indeks denemek lazım.
Yeni başlayana önerim şu: çıktının tamamını anlamaya çalışmayın. Önce en içteki ve en pahalı düğümü bulun, tahmin ile gerçek satır sayısını karşılaştırın, Seq Scan görüp görmediğinize bakın. Bu üç şey bizim vakaların çoğunu açıkladı. Yukarıdaki bağlantı resmi dokümantasyonun EXPLAIN bölümü, örneklerle okununca bir akşamda oturuyor.
Merak ettiğim: sizde üretim veritabanında bu analizi kim yapıyor? Bizde yetkiler sıkı olduğu için ANALYZE seçeneğiyle sorgu çalıştırmak bile ayrı bir izin süreci istiyor, çünkü ANALYZE sorguyu gerçekten çalıştırıyor. Yazma sorgularında bunu bir işlem içinde yapıp geri almak gerekiyor, ben bunu bir kez unutup test verisini değiştirmiştim.
İşe yarayan sıra şuydu. Önce pg_stat_statements eklentisini açıp hangi sorguların toplamda en çok zaman yediğine baktık. En yavaş tek sorgu ile toplamda en çok vakit harcayan sorgu çoğu zaman aynı şey değil; saniyede yüz kez çalışan 40 milisaniyelik bir sorgu, günde bir kez çalışan 5 saniyelik rapordan daha pahalı olabiliyor. Listeyi toplam süreye göre sıralayınca ilk üç sorgu bütün yükün büyük kısmını taşıyordu.
Sonra o sorguları EXPLAIN (ANALYZE, BUFFERS) ile çalıştırdık. Dokümantasyonun güncel sürümünde ANALYZE seçeneğinin BUFFERS bilgisini de kendiliğinden açtığı yazıyor, eski sürümlerde bunu ayrıca yazmak gerekiyordu. Çıktıda ilk baktığım iki şey oldu: planlayıcının tahmin ettiği satır sayısı ile gerçekte dönen satır sayısı arasındaki fark ve hangi düğümde zamanın gittiği. Bizim sorguda planlayıcı 50 satır bekliyordu, gerçekte 180 bin satır geliyordu. Tahmin bu kadar şaşınca planlayıcı yanlış birleştirme yöntemini seçiyor. Tabloda ANALYZE komutunu çalıştırıp istatistikleri tazeleyince plan kendiliğinden düzeldi.
İkinci sorgunun hikâyesi daha klasikti. Siparişler tablosunda müşteri kimliği yabancı anahtar olarak tanımlıydı ve ben yıllarca PostgreSQL'in buna otomatik indeks açtığını sanmışım. Açmıyor. Yabancı anahtarın işaret ettiği taraf birincil anahtar olduğu için orada indeks var ama referans veren sütunda yok. Seq Scan satırını görünce anladık, indeksi ekledik.
Üçüncüsü e-posta ile arama yapan sorguydu. Koşul lower(email) üzerinden yazılmıştı, indeks ise düz email sütunundaydı. Fonksiyon uygulanmış sütun düz indeksi kullanamıyor; ifade indeksi gerekiyor. Aynı şekilde başında yüzde işareti olan LIKE aramaları da normal B-tree indeksten yararlanamıyor, onun için pg_trgm eklentisiyle GIN indeks denemek lazım.
Yeni başlayana önerim şu: çıktının tamamını anlamaya çalışmayın. Önce en içteki ve en pahalı düğümü bulun, tahmin ile gerçek satır sayısını karşılaştırın, Seq Scan görüp görmediğinize bakın. Bu üç şey bizim vakaların çoğunu açıkladı. Yukarıdaki bağlantı resmi dokümantasyonun EXPLAIN bölümü, örneklerle okununca bir akşamda oturuyor.
Merak ettiğim: sizde üretim veritabanında bu analizi kim yapıyor? Bizde yetkiler sıkı olduğu için ANALYZE seçeneğiyle sorgu çalıştırmak bile ayrı bir izin süreci istiyor, çünkü ANALYZE sorguyu gerçekten çalıştırıyor. Yazma sorgularında bunu bir işlem içinde yapıp geri almak gerekiyor, ben bunu bir kez unutup test verisini değiştirmiştim.
3 2 yorum
