PostgreSQL — Maliyetli Sorguları Belirlemek ve Analiz Etmek

Merhabalar, bu çalışmamda PostgreSQL üzerinde veritabanı yöneticilieri ve aslında geliştiriciler için önemli bir konu olan maliyetli sorguların belirlenmesi konusuna değinmeye çalışacağım. PostgreSQL’de aslında tüm veritabanı sistemlerinde maliyetli sorguların hesaplanması her zaman önemli bir konu olmuştur. Çünkü sorguların sistemin kaynak tüketimi ve performansına doğrudan etkisi mevcuttur. Bu nedenle de sorguların sürekli takip edilmesi,analiz edilmesi ve optimize edilmesi önem arz etmektedir.

PostgreSQL üzerinde sorguların analiz edilmesi ve tespit edilmesi için kullanılabilecek en iyi modüllerden bir tanesi (çoğu kaynak için belkide en iyisi) pg_stat_statements olarak bilinmektedir.

Detaylı bilgi için bu aşağıdaki bağlantıyı kullanabilirsiniz.

https://www.postgresql.org/docs/12/pgstatstatements.html

Bu çalışma kapsamında ve içerikte aşağıdaki konulara değinmeye gayret edeceğiz.

1. PostgreSQL üzerinde pg_stat_statements yüklenmesi ve açılması
2. PostgreSQL maliyetli sorguların sınıflandırılması
3. PostgreSQL üzerinde pg_stat_statements ile maliyetli sorguların analiz edilmesi

PostgreSQL pg_stat_statements Yüklenmesi ve Açılması

pg_stat_statements aslında PostgreSQL için yazılmış bir extension’dır. Bu nedenle de PostgreSQL’in shared_preload_librariries parametresine bu extension eklenmeli ve veritabanı üzerinde bu extension create edilmelidir.

Öncelikle PostgreSQL’in shared_preload_libraries değerine pg_stat_statement extension’ı eklenmelidir.

postgres=# ALTER SYSTEM SET shared_preload_libraries TO 'pg_stat_statements';
ALTER SYSTEM

Daha sonrasında ise PostgreSQL sistemi tekrardan başlatılmalıdır.

pg_ctlcluster 12 main start
pg_ctlcluster 12 main stop

Bu işlemler başarılı tamamlandıktan sonra, hangi veritabanında isteniyorsa extension create edilmelidir.

query_db=# create extension pg_stat_statements; 
CREATE EXTENSION 
query_db=#

PostgreSQL Maliyetli Sorguların Sınıflandırılması

PostgreSQL üzerinde maliyetli sorguları analiz ederken ve tespit ederken dikkat etmemiz gereken birkaç konu vardır. Sorgular maliyetlerine göre kendi arasında sınıflandırılmalıdır. Çünkü sistemin darboğaz yaşadığı katmana göre sorgular değerlendirilmelidir. Çünkü sistemde yaşanabilecek darboğazlar CPU,memory yada storage performansı olarak başgöterebilir. Bu nedenle de sorguları yaptıkları maliyet görünen alanlara göre sınıflandırmak daha da önem kazanmaktadır.

Ancak yinede, sistemde optimize edilmemiş her sorgu sistemde darboğaz oluşturulan (oluşturmaya çok meyilli de diyebiliriz) bir süreç haline gelmektedir. Bu nedenle analizlerimizi yaparken aşağıdaki maddelere dikkat etmemiz gerekiyor

• I/O maliyeti en yüksek sorgular
• CPU maliyeti en yüksek sorgular

Bu maddeler daha da arttırılabilir ama temelde bu şekilde bir kırılımla başlamak yeterli gelecektir.

PostgreSQL Maliyetli Sorgu Analizi

Veritabanı üzerinde maliyetli sorguları analiz etmek ya da görüş sahibi olmak için öncelikle sistemde çalışmış olan sorguların toplam çalışma sayısına göre bir analiz çıkarılabilir. Bu analiz ile sistemde en fazla çağrılan sorguların tespiti yapılabilir. Bu sorgular sistemde ayrı olarak incelenebilir. Ancak doğrudan kök sebep olarak asla görülmemelidir. Çünkü sistemdeki maliyetli sorgulara birçok farklı açıdan bakmak gerekiyor. Tek bir analiz tek başına kök sebebi vermeyebilir.

Sistemde en çok çağrılan sorguların listesini almak için aşağıdaki sorgu kullanılabilir.

SELECT 
  (total_time / 1000 / 60) as total, 
  (total_time/calls) as avg,
  calls,
  query 
FROM pg_stat_statements 
ORDER BY calls DESC 
LIMIT 100;

Buradaki listede, toplam ve ortalama süre bilgileri de buluınmaktadır. Bu bilgiler ışığında sistemde en çok çağrılan sorgu ya da ortalama çalışma süresi en yüksek olan sorgulara göre ORDER BY işlemi yapılabilir.

İkinci olarak ise, sistemdeki sorguların I/O maliyetlerine göre analiz yapılabilir. Aşağıdaki iki sorguda hem total I/O maliyetine göre hem de tek seferde en büyük I/O maliyeti oluşturan sorgular sıralanmıştır.

Bu analiz sonucu, sistemde I/O maliyeti oluşturan sorgular tespit edilebilir. Bu sayede de, I/O anlamında optimize edilmesi gereken sorgulara karar verilebilir.

Sistemde, I/O maliyeti en yoğun sorguların listesini aşağıdaki elde edebiliriz. Bu sorguda, çalışma başına ortalama olarak en yüksek I/O maliyetini gösteren sorgular elde edilmektedir.

SELECT userid::regrole, calls, query 
FROM pg_stat_statements 
ORDER BY (blk_read_time+blk_write_time)/calls DESC LIMIT 5;

most i/o consumption per call

İkinci sorguda ise, çağrılma başına en yüksek ortalamaya sahip sorgular yerine bütün halde ne kadar çağrıldığına bakılmaksızın I/O olarak en yüksek maliyeti gösteren sorgular elde edilmektedir. Burada bir önceki sorgu ile en temel fark şudur; birinci sorguda listenen query’ler tek başına diğer sorgulara göre daha fazla I/O yapmaktadır. İkinci sorguda elde edilenler ise, çağrılma sayısı ile birlikte toplam I/O maliyetinde en fazla maliyeti sergileyendir. Yani daha küçük I/O maliyetine sahip olsada çağrılma sayısı nedeniyle daha fazla I/O maliyeti ortaya çıkarabilir.

SELECT userid::regrole, calls, query 
FROM pg_stat_statements 
ORDER BY (blk_read_time+blk_write_time) DESC LIMIT 5;

top i/o consumption total

Buna ek olarak ise, sorguların toplam kaç satır ile işlem yaptığını ve shared buffer üzerinde ne kadar karşılanabildiğine de bakılabilir. Bu sayede, fazla veri ile işlem yapan ve buffer’ı efektif kullanamayan sorguları da tespit etmiş oluruz. Bu da bize önemli ipuçları vermektedir.

SELECT calls,
  total_time,
  rows,
  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,
  query
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Yukarıda yapılan işlemler ve çalışamalar tek başına bu konun tüm yönlerini kapsamamaktadır. Önemli olan nokta pg_stat_statement view’ından ihtiyacınıza yönelik şekilde sorgular üretmektir. Ancak yukarıdaki sorgular ve bakış açıları çoğu yerde kullanılabilir ve bu çıktılar birleştirildiğinde sistem hakkında önemli bilgileri iletebilir. PostgreSQL veritabanı üzerinde sorgu maliyet analizi için pg_stat_statement çok yetenekli ve önemli bir araçtır. Sistemlerde her zaman olması gereken bir extensiondır.

Bu konu ile ilgili öneri,görüş ve sorularınızı bana iletebilirsiniz.

Twitter — Linkedin — Email

Sevgiler,

Demir.