PostgreSQL怎么用系統(tǒng)表來分析postgresql的問題

這篇文章將為大家詳細(xì)講解有關(guān)PostgreSQL怎么用系統(tǒng)表來分析postgresql的問題，小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

數(shù)據(jù)庫中本身的系統(tǒng)表提供了對外展示當(dāng)前數(shù)據(jù)庫狀態(tài)的作用，其中這些系統(tǒng)表可以監(jiān)控系統(tǒng)的狀態(tài)，查詢執(zhí)行計(jì)劃的狀態(tài)，以及作為服務(wù)器管理狀態(tài)顯示的一部分。

對于任何的數(shù)據(jù)庫理解和巧妙的使用這些系統(tǒng)表都很重要。

一般來說如果客戶開始抱怨你的應(yīng)用使用的postgresql 反映緩慢，或者你自己發(fā)現(xiàn)部分查詢反饋的時(shí)間已經(jīng)很慢，已經(jīng)肉眼可查的時(shí)候，該怎么做。

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

不同的在于每種數(shù)據(jù)庫對于查詢的方便些和便捷性，從我掌握的數(shù)據(jù)庫來說，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;

其實(shí)研究一下 pg_statio_uer_tables 這張表，可以很容易發(fā)現(xiàn)通過pg_statio_user_tables 這張表可以變化出多種系統(tǒng)的指標(biāo)參數(shù)。

而實(shí)際上這個(gè)pg_statio_user_tables 是一個(gè)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;

而什么會(huì)引起 cache hit ratio 比較低的問題

1 設(shè)計(jì)的表中存儲(chǔ)了比較大的字段或者存儲(chǔ)其他方式的不適合存儲(chǔ)在傳統(tǒng)數(shù)據(jù)庫的數(shù)據(jù)，例如大型的圖片，或者大量的文字，并且經(jīng)常調(diào)用

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

3 查詢并未被優(yōu)化，大量的走了 sequential scans 的方式

4 你缺乏足夠的內(nèi)存來進(jìn)行目前面對的查詢活動(dòng)

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

2  pg_stat_database 這個(gè)系統(tǒng)表，這樣表可以很清楚的給出如下信息

1  單獨(dú)每個(gè)數(shù)據(jù)庫產(chǎn)生的事務(wù)多少

2  回滾事務(wù)有多少，（從這點(diǎn)就可以看出某些問題）

3  整體數(shù)據(jù)庫的讀寫比 , tup_fetched 與 tup_inserted, tup_updated, tup_deleted 和的比率

4   查詢數(shù)據(jù)回饋與實(shí)際數(shù)據(jù)的搜索的比率，也就是查找多少數(shù)據(jù)返回的行數(shù)與對應(yīng)到底數(shù)據(jù)庫檢索了多少行 tup_fetched / tup_returned

5  是否數(shù)據(jù)庫有死鎖

等等以上信息。應(yīng)該可以確認(rèn)至少那個(gè)數(shù)據(jù)庫是 熱的，或者對比歷史同期數(shù)據(jù)指標(biāo)，指標(biāo)不大對，那就可以繼續(xù)針對這個(gè)數(shù)據(jù)庫進(jìn)行問題的查找.

在確認(rèn)了數(shù)據(jù)庫后，下一步就可以開始針對這個(gè)數(shù)據(jù)庫的表進(jìn)行問題的確認(rèn)了。

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 可以將當(dāng)前數(shù)據(jù)庫中的表進(jìn)行一個(gè)梳理，例如某個(gè)表的數(shù)據(jù)的 insert ,update del ,以及查詢中使用的到的,以及查詢的比率，還有了解到一個(gè)表最后一次 autovacuum的時(shí)間，等等有用的信息，尤其可以通過n_dead_tup     這個(gè)參數(shù)的跟蹤，得到某個(gè)表是否有事務(wù)沒有commit 制造了大量的 dead_tup 或者長事務(wù)，造成某個(gè)時(shí)間段的 dead_tup急劇上升等等，問題。

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

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