{"id":1961,"date":"2015-02-06T22:48:06","date_gmt":"2015-02-06T20:48:06","guid":{"rendered":"http:\/\/www.profelis.com.tr\/tr\/blog\/?p=1961"},"modified":"2023-02-01T10:58:13","modified_gmt":"2023-02-01T07:58:13","slug":"postgresql-101-aktivite-izleme","status":"publish","type":"post","link":"https:\/\/profelis.com.tr\/en\/2015\/02\/06\/postgresql-101-aktivite-izleme\/","title":{"rendered":"PostgreSQL 101: Aktivite \u0130zleme"},"content":{"rendered":"

PostgreSQL 101: Aktivite \u0130zleme<\/h2>\n

track_activities = on<\/strong> olarak d\u00fczenlendi\u011finde, PostgreSQL \u00e7al\u0131\u015ft\u0131r\u0131lan t\u00fcm sorgular i\u00e7in veri toplamaya ba\u015flar. Uygun haklara sahip kullan\u0131c\u0131lar bu \u00f6zellik sayesinde bir sistem g\u00f6r\u00fcn\u00fcm\u00fc olan view pg_stat_ activities<\/strong>\u00a0g\u00f6r\u00fcn\u00fcm\u00fc ile kullan\u0131c\u0131 aktivitesini izleyebilirler.<\/p>\n

pg_stat_activities<\/strong> bir sistem fonksiyonu olan pg_stat_get_activity<\/strong> (procpid int<\/em>) s\u00fcre\u00e7 numaras\u0131n\u0131 arg\u00fcman olarak vererek ilgili s\u00fcre\u00e7 i\u00e7erisindeki aktiviteyi izlemenizi sa\u011flar. Arg\u00fcman olarak NULL<\/em> verirseniz t\u00fcm \u00e7al\u0131\u015fan s\u00fcre\u00e7ler ile ilgili aktivite sonu\u00e7 olarak getirilir.<\/p>\n

SELECT dbname, usename, current_query FROM pg_stat_activity ;<\/pre>\n
Bir \u00e7ok kullan\u0131c\u0131n\u0131n ayn\u0131 anda \u00e7al\u0131\u015ft\u0131\u011f\u0131 sistemlerde bir \u00e7ok arka plan i\u015fleminin tan\u0131ml\u0131 sorgular ile listelendi\u011fini g\u00f6r\u00fcrs\u00fcn\u00fcz. Bu s\u00fcrecin durum bilgisini g\u00f6sterir ve asl\u0131nda aktif olarak \u00e7al\u0131\u015fan bir sorgunun bulunmad\u0131\u011f\u0131n\u0131, ve PostgreSQL’in ilgili kullan\u0131c\u0131dan yeni komut\/sorgular bekledi\u011fini anlars\u0131n\u0131z.<\/p>\n
Sadece aktif sorgu \u00e7al\u0131\u015ft\u0131ran kullan\u0131c\u0131 s\u00fcre\u00e7lerinin bilgisini getirmek i\u00e7in a\u015fa\u011f\u0131daki \u015fekilde sorgu filtrelenebilir:<\/p>\n
SELECT dbname, usename, current_query FROM pg_stat_activity WHERE current_query != '' ;<\/pre>\n
K\u0131sa S\u00fcren Sorgular\u0131 Yakalama<\/h2>\n
Modern OLTP (Online Transaction Processing<\/em>) sistemlerinde \u00e7al\u0131\u015fan sorgular ancak birka\u00e7 milisaniye s\u00fcrmektedir. Bu t\u00fcr sorgular\u0131\u00a0pg_stat_activity<\/strong> yard\u0131m\u0131 ile izlemek bir yana, yakalamak dahi meseledir. Bu t\u00fcr sorgular\u0131 izlemenin en kaba yolu sorguyu yava\u015flatmakt\u0131r ki bunu yapmay\u0131 kimse istemez!<\/p>\n
PostgreSQL 9.0 dan itibaren gelen bir \u00f6zellik olan pg_stat_statements<\/strong> sorgu i\u015fleme istatistiklerini ger\u00e7ek zamanl\u0131 kaydeden bir mod\u00fcld\u00fcr. (Bkz:\u00a0https:\/\/www.postgresql.org\/docs\/9.6\/static\/pgstatstatements.html<\/a>)<\/p>\n
Sorgular aras\u0131na pg_sleep yard\u0131m\u0131 ile belirli bir bo\u015f zaman dilimi koyup, ilgili sorgu kay\u0131tlar\u0131n\u0131 farkl\u0131 bir dosyaya y\u00f6nlendirerek incelemek \u00fczere daha k\u00fc\u00e7\u00fck boyutlu bir kay\u0131t dosyas\u0131 olu\u015fturabilirsiniz:<\/p>\n
SELECT CURRENT_TIMESTAMP; SELECT pg_sleep(60); SELECT CURRENT_TIMESTAMP;<\/pre>\n
Bu esnada farkl\u0131 bir terminal penceresinde tail -f \/var\/log\/ postgresql\/postgresql-X.X-main.log > ozel_sorgu.log komutu ile \u00e7al\u0131san sorguya ait ufak bir kay\u0131t dosyas\u0131 olu\u015fturmu\u015f olursunuz.<\/p>\n
Uzun S\u00fcren Sorgular\u0131 \u0130zleme<\/h2>\n
Uzun s\u00fcren sorgular\u0131 izlemek farkl\u0131 bir konudur. Sorgular\u0131n listesini al\u0131p bunlar\u0131n ne kadar s\u00fcreden beri \u00e7al\u0131\u015ft\u0131\u011f\u0131na dair s\u0131ralamak i\u00e7in:<\/p>\n
select \n  current_timestamp - query_start as runtime, \n  datname, \n  usename, \n  current_query \nfrom \n  pg_stat_activity \nwhere\n  current_query != '' \norder by 1 desc;<\/pre>\n
Bu sorgu en \u00fcstte en uzun zamandan beri \u00e7al\u0131\u015fan sorgu olmak \u00fczere a\u015fa\u011f\u0131ya do\u011fru azalarak s\u0131ral\u0131 \u00e7al\u0131\u015fan sorgu listesini getirecektir. Y\u00fckl\u00fc sistemlerde bu sorguyu \u00e7al\u0131\u015ft\u0131r\u0131rken limit kullanarak getirilecek olan sat\u0131r say\u0131s\u0131n\u0131 (LIMIT 10) s\u0131n\u0131rlaman\u0131z tavsiye edilir. Bunu \u00e7al\u0131\u015fma zaman\u0131n\u0131 limitleyerek de yapabilirsiniz: WHERE c\u00fcmlesine\u00a0‘current_timestamp \u2013 query_start 1’ min eklerseniz bir dakikadan uzun sorgular sadece listelenecektir.<\/p>","protected":false},"excerpt":{"rendered":"
track_activities = on olarak d\u00fczenlendi\u011finde, PostgreSQL \u00e7al\u0131\u015ft\u0131r\u0131lan t\u00fcm sorgular i\u00e7in veri toplamaya ba\u015flar. Uygun haklara sahip kullan\u0131c\u0131lar bu \u00f6zellik sayesinde bir sistem g\u00f6r\u00fcn\u00fcm\u00fc olan view pg_stat_ activities\u00a0g\u00f6r\u00fcn\u00fcm\u00fc ile kullan\u0131c\u0131 aktivitesini izleyebilirler.<\/p>","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,45],"tags":[48,60],"class_list":["post-1961","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-yazi","tag-enterprisedb","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1961","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/comments?post=1961"}],"version-history":[{"count":0,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1961\/revisions"}],"wp:attachment":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media?parent=1961"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/categories?post=1961"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/tags?post=1961"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}