Y\u0131llar s\u00fcren geli\u015ftirme ve kullan\u0131m sonras\u0131, de\u011fi\u015fen geli\u015ftiriciler ve veritaban\u0131 y\u00f6neticileri, eklenen\/silinen tablolar, alanlar paketler: Bir de bakm\u0131\u015fs\u0131n\u0131z \u015feman\u0131z\u0131n i\u00e7inde art\u0131k kimin kulland\u0131\u011f\u0131, hatta kullan\u0131l\u0131p kullan\u0131lmad\u0131\u011f\u0131 bilinmeyen tablolarla dolu bir veritaban\u0131 ile kar\u015f\u0131 kar\u015f\u0131yas\u0131n\u0131z.<\/p>\n
Pop\u00fcler ve tehlikeli bir mottodur, \u00f6zellikle IT sekt\u00f6r\u00fcnde: \u00c7al\u0131\u015f\u0131yorsa b\u0131rak \u00e7al\u0131\u015fs\u0131n!<\/em> Buna kar\u015f\u0131n sorumluluk sahibi bir veritaban\u0131 y\u00f6neticisi veya uygulama geli\u015ftiricisi olarak inisiyatifi ele alal\u0131m ve bakal\u0131m \u015fu tablolar\u0131 kimler kullan\u0131yor, hangi tablolar art\u0131k hi\u00e7 kullan\u0131lm\u0131yor.<\/p>\n Belirli bir s\u00fcre \u00e7al\u0131\u015f\u0131r \u015fekilde b\u0131rakal\u0131m, sonras\u0131nda gidip kontrol edelim bakal\u0131m olu\u015fturdu\u011fumuz\u00a0tmp_stat_user_tables<\/strong>\u00a0tablosunda neler var?<\/p>\n pg_stat_user_tables<\/strong>\u00a0tablo kullan\u0131m istatistiklerini tutan \u00f6zel bir g\u00f6r\u00fcn\u00fcmd\u00fcr. Kullan\u0131m istatistiklerinde tablolar\u0131n kullan\u0131m say\u0131s\u0131n\u0131 (count) kontrol etti\u011fimizde kullan\u0131lan tablolar\u0131n say\u0131s\u0131n\u0131n de\u011fi\u015fmi\u015f oldu\u011funu g\u00f6rebiliriz. Ge\u00e7ici tabloyu daha uzun s\u00fcre \u00e7al\u0131\u015f\u0131r vaziyette b\u0131rak\u0131rsak uzun d\u00f6nemde tablo kullan\u0131mlar\u0131n\u0131 g\u00f6rm\u00fc\u015f ve art\u0131k hi\u00e7 kullan\u0131lmayan tablolar i\u00e7in iyi bir aday listesi \u00e7\u0131kartm\u0131\u015f oluruz. Aday diyozum zira, bu listedeki tablolar genel ge\u00e7er uygulama veya kullan\u0131c\u0131 i\u015flemlerinde de\u011fil de raporlarda kullan\u0131l\u0131yor olabilir bir kontrol daha yapman\u0131z gerekecektir.<\/p>\n Alternatif olarak, PostgreSQL’e t\u00fcm tablo istatistiklerini resetlemesini s\u00f6ylemek de bir y\u00f6ntem olabilir:<\/p>\n<\/div>\n<\/div>\n<\/div>\n Bu durumda t\u00fcm tablo kullan\u0131m\u0131na dair istatistikler s\u0131f\u0131rlanacakt\u0131r. Dolay\u0131s\u0131 ile kullan\u0131lan tablolar\u0131 kullan\u0131 0 olmayanlar diyerek bulabilirsiniz.<\/p>\n Ge\u00e7mi\u015fe dair tablo kullan\u0131m istatistikleri sahibi olmak iyi bir \u015feydir. Zaman i\u00e7erisinde kullan\u0131m de\u011fi\u015fikliklerine bakarak \u015fema hakk\u0131nda karar verebilir, farkl\u0131 optimizasyonlar uygulayabilirsiniz. G\u00fcnl\u00fck, ayl\u0131k, y\u0131ll\u0131k veya y\u00fck durumuna g\u00f6re kullan\u0131m\u0131 bulmak i\u00e7in saatlik raporlar \u00fcretmek m\u00fcmk\u00fcnd\u00fcr. Bunun i\u00e7in Linux i\u015fletim sisteminin zamanlay\u0131c\u0131 g\u00f6rev arac\u0131 CRON<\/strong> veya PostgreSQL’in zamanlay\u0131c\u0131s\u0131 pg_agent<\/strong> kullan\u0131labilir.<\/p>\n \u00d6nce bir yedek kopya istatistik tablosu olu\u015ftural\u0131m:<\/p>\n Daha \u00f6nceden olu\u015fturmu\u015f oldu\u011fumuz bu backup_stat_user_tables<\/strong> tablosuna sistemin zaman damgal\u0131 (timestamped<\/em>) bir\u00a0anl\u0131k bir g\u00f6r\u00fcnt\u00fcs\u00fcn\u00fc ekleyelim:<\/p>\n Bu eklemeyi yukar\u0131da bahsetti\u011fimiz \u015fekilde istedi\u011finiz s\u0131kl\u0131kla, g\u00fcnl\u00fck, ayl\u0131k veya saatlik olarak bu tabloya eklerseniz, ge\u00e7mi\u015fe y\u00f6nelik bir tablo kullan\u0131m istatistikleri tablosu olu\u015fturmu\u015f olursunuz.<\/p>","protected":false},"excerpt":{"rendered":" Pop\u00fcler ve tehlikeli bir mottodur, \u00f6zellikle IT sekt\u00f6r\u00fcnde: \u00c7al\u0131\u015f\u0131yorsa b\u0131rak \u00e7al\u0131\u015fs\u0131n! Buna kar\u015f\u0131n sorumluluk sahibi bir veritaban\u0131 y\u00f6neticisi veya uygulama geli\u015ftiricisi olarak inisiyatifi ele alal\u0131m ve bakal\u0131m \u015fu tablolar\u0131 kimler kullan\u0131yor, hangi tablolar art\u0131k hi\u00e7 kullan\u0131lm\u0131yor.<\/p>","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[76,75,45],"tags":[48,61,60],"class_list":["post-1967","post","type-post","status-publish","format-standard","hentry","category-linux","category-postgresql","category-yazi","tag-enterprisedb","tag-linux","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1967","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=1967"}],"version-history":[{"count":0,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1967\/revisions"}],"wp:attachment":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media?parent=1967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/categories?post=1967"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/tags?post=1967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}create temp table tmp_stat_user_tables as select * from pg_stat_user_\n tables;\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n
select * from pg_stat_user_tables n\n join tmp_stat_user_tables t\n on n.relid=t.relid\n and (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) <>\n (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n
select pg_stat_reset()\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n
G\u00fcnl\u00fck Tablo Kullan\u0131m Raporu \u00dcretmek<\/h2>\n
create table backup_stat_user_tables as\n select current_timestamp as snaptime, *\n from pg_stat_user_tables;\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n
INSERT into backup_stat_user_tables\n select current_timestamp as snaptime, *\n from pg_stat_user_tables;\n<\/pre>\n<\/div>\n<\/div>\n<\/div>\n