pg_stat_statements ile PostgreSQL’de Yavaş Sorguları Bulma

PostgreSQL’de yavaş olan sorguları bulabilmek için postgresql.conf dosyasındaki log_min_duration_statement parametresini belirli bir eşik değerine ayarlayarak bu eşiği geçen sorguların log dosyasına yazılmasını sağlayabiliriz. Bu konfigürasyon bize uzun süren sorguları verse de belirttiğimiz eşikten daha kısa süren ve veritabanına yük oluşturan sorgular bizim için görünmezdir.

pg_stat_statements extension’ı, veritabanında çalıştırılan tüm SQL sorgularının istatistiklerini kaydeder. Performans sorunlarını tespit etmek için kullanılabilecek en önemli extension’lardan biridir. Oluşturulduğu veritabanında çalıştırılan her sorguya ait toplam çalışma süresi, sorguyla getirilen satır sayısı, sorgunun kaç kez çağırıldığı gibi bir çok bilgiyi elde etmemizi sağlar.

pg_stat_statements Kurulumu

Mevcut PostgreSQL Cluster’ımızın versiyonundaki contrib paketini yüklüyoruz:

CentOS/Redhat dağıtımları için:

sudo yum install postgresql12-contrib

Debian tabanlı dağıtımlar için:

sudo apt-get install postgresql-contrib-12

postgresql.conf dosyası içindeki shared_preload_libraries parametresini pg_stat_statements modülünü içerecek şekilde aşağıdaki gibi düzenliyoruz:

vim /var/lib/pgsql/12/data/postgresql.conf
>
shared_preload_libraries = 'pg_stat_statements'

Ya da PostgreSQL’e bağlanarak aşağıdaki gibi ALTER SYSTEM SET komutunu kullanarak ayarlayabiliriz:

ALTER SYSTEM SET shared_preload_libraries TO 'pg_stat_statements';

shared_preload_libraries parametresinde yapılan değişikliğin etkin olabilmesi için PostgreSQL servisinin yeniden başlatılmasını gereklidir, aşağıdaki gibi servisi yeniden başlatıyoruz:

CentOS/Redhat dağıtımları için:

sudo systemctl restart postgresql-12

Debian tabanlı dağıtımlar için:

sudo systemctl restart postgresql@main-12

Sorgu istatistikleri izlemek istediğimiz veritabanına, superuser haklarına sahip bir kullanıcı ile bağlanarak extension’ı oluşturuyoruz:

CREATE EXTENSION pg_stat_statements;

Oluşturduğumuz pg_stat_statements extension’ını \dx ile aşağıdaki gibi görüntüleyebiliriz:

Veritabanında ortalama çalışma zamanı en yüksek olan sorguyu aşağıdaki gibi görebiliriz:

SELECT total_time, min_time, max_time, mean_time, calls, rows, query
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 1;

Örnek komut çıktısı aşağıdaki gibidir:

total_time: Sorgu çalıştırıldığında harcanan toplam süreyi milisaniye cinsinden verir.
min_time & max_time : Sorgunun minimum ve maksimum çalışma zamanlarını verirler.
mean_time: total_time/calls ile sorgunun ortalama çalışma süresini milisaniye cinsinden verir.
calls: Sorgunun toplam kaç kez çalıştırıldığını verir.
rows: Sorgu sonucunda alınan veya etkilenen toplam satır sayısını verir.
query: Çalıştırılan sorguyu verir. Varsayılan olarak sorgunun en fazla 1024 byte karakteri görüntüler. Bu değeri track_activity_query_size parametresi ile değiştirebilirsiniz.

Varsayılan olarak, pg_stat_statements extension’ı karşılaştığı ilk 5.000 sorguya ait istatistikleri saklar. pg_stat_statements.max, parametresini düzenleyerek bu sayıyı değiştirebiliriz. Parametrede yapılan değişikliğin etkin olabilmesi için PostgreSQL servisinin yeniden başlatılması gereklidir.

Örnek olarak aşağıdaki gibi 10000 olarak ayarlayabiliriz:

ALTER SYSTEM SET pg_stat_statements.max TO 10000;

İstatistikleri Sıfırlama

pg_stat_statements tarafından elde edilen istatistikler, sıfırlanana kadar birikir. İstatistik verilerini sıfırlamak için superuser haklarına sahip bir kullanıcı ile veritabanına bağlanarak aşağıdaki fonksiyonu çalıştırabilirsiniz:

SELECT pg_stat_statements_reset();

Bu yazımda temel olarak pg_stat_statements kullanımından bahsettim. Daha ayrıntılı bilgi için kendi sayfasına bakabilirsiniz.