PostgreSQL怎么用系統表來分析postgresql的問題

這篇文章將為大家詳細講解有關PostgreSQL怎么用系統表來分析postgresql的問題，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

數據庫中本身的系統表提供了對外展示當前數據庫狀態的作用，其中這些系統表可以監控系統的狀態，查詢執行計劃的狀態，以及作為服務器管理狀態顯示的一部分。

對于任何的數據庫理解和巧妙的使用這些系統表都很重要。

一般來說如果客戶開始抱怨你的應用使用的postgresql 反映緩慢，或者你自己發現部分查詢反饋的時間已經很慢，已經肉眼可查的時候，該怎么做。

1  查看cache hit ratio  這個東西其實放到其他數據庫也是一樣，如果你的內存對于系統的緩沖支持不足，需要的數據無法駐留在內存，經常會產生 fault page (有些數據庫對于讀取的數據不在內存中的一種叫法)， 那就必須要要查看你的一個系統參數 cache hit ratio ，大部分建議最低不要低于95%，如果達到99% 才是一個令人滿意的數字。

不同的在于每種數據庫對于查詢的方便些和便捷性，從我掌握的數據庫來說，PG獲取 cache hit ratio的方法比較簡單。

select  sum(heap_blks_read) as heap_read,

        sum(heap_blks_hit) as heap_hit,

sum(heap_blks_hit) /(sum(heap_blks_hit) + sum(heap_blks_read)) as ratio 

from pg_statio_user_tables;

其實研究一下 pg_statio_uer_tables 這張表，可以很容易發現通過pg_statio_user_tables 這張表可以變化出多種系統的指標參數。

而實際上這個pg_statio_user_tables 是一個view 從 pg_statio_all_tables 中變化而成的

 SELECT pg_statio_all_tables.relid,

    pg_statio_all_tables.schemaname,

    pg_statio_all_tables.relname,

    pg_statio_all_tables.heap_blks_read,

    pg_statio_all_tables.heap_blks_hit,

    pg_statio_all_tables.idx_blks_read,

    pg_statio_all_tables.idx_blks_hit,

    pg_statio_all_tables.toast_blks_read,

    pg_statio_all_tables.toast_blks_hit,

    pg_statio_all_tables.tidx_blks_read,

    pg_statio_all_tables.tidx_blks_hit

   FROM pg_statio_all_tables

  WHERE (pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND pg_statio_all_tables.schemaname !~ '^pg_toast'::text;

而什么會引起 cache hit ratio 比較低的問題

1 設計的表中存儲了比較大的字段或者存儲其他方式的不適合存儲在傳統數據庫的數據，例如大型的圖片，或者大量的文字，并且經常調用

2 由于vacuum 的問題，dead tuple 沒有及時被清理，

3 查詢并未被優化，大量的走了 sequential scans 的方式

4 你缺乏足夠的內存來進行目前面對的查詢活動

那么接下來的問題如果從找尋到底哪個表可能會存在問題的角度入手，可以馬上先看一下

2  pg_stat_database 這個系統表，這樣表可以很清楚的給出如下信息

1  單獨每個數據庫產生的事務多少

2  回滾事務有多少，（從這點就可以看出某些問題）

3  整體數據庫的讀寫比 , tup_fetched 與 tup_inserted, tup_updated, tup_deleted 和的比率

4   查詢數據回饋與實際數據的搜索的比率，也就是查找多少數據返回的行數與對應到底數據庫檢索了多少行 tup_fetched / tup_returned

5  是否數據庫有死鎖

等等以上信息。應該可以確認至少那個數據庫是 熱的，或者對比歷史同期數據指標，指標不大對，那就可以繼續針對這個數據庫進行問題的查找.

在確認了數據庫后，下一步就可以開始針對這個數據庫的表進行問題的確認了。

3 pg_stat_all_tables

select * from pg_stat_all_tables where relname not like 'pg%' and relname not like 'sql%';

通過pg_stat_all_tables 可以將當前數據庫中的表進行一個梳理，例如某個表的數據的 insert ,update del ,以及查詢中使用的到的,以及查詢的比率，還有了解到一個表最后一次 autovacuum的時間，等等有用的信息，尤其可以通過n_dead_tup     這個參數的跟蹤，得到某個表是否有事務沒有commit 制造了大量的 dead_tup 或者長事務，造成某個時間段的 dead_tup急劇上升等等，問題。

然后我們在得到這些證據后，就可以將其report 給相關的開發人員，并且通過 POSTGRESQL 的慢查詢來進一步確認某些設計的問題，或者語句缺少索引的問題。

關于“PostgreSQL怎么用系統表來分析postgresql的問題”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。