有一些同學覺得監控無非是針對CPU、內存 、磁盤進行一些簡單的監控，其實不僅僅如此，監控涵蓋了眾多知識的融合，能融會貫通PostgreSQL監控的人，大概率是PostgreSQL高手。

POSTGRESQL的監控除了對系統的CPU內存磁盤等項目的監控，更多是對數據庫的監控，因此需要對數據庫的原理有一定的理解，或者對數據庫所操作的業務邏輯有一定的了解,才能將相關工作做好.

監控的主要目的

1、在預訂的問題(閾值)發生時或某個預訂時間發生時，進行報警

2、針對一段系統運行歷史時期的某項值進行跟蹤，對系統的未來進行評估

3、通過監控的值或收集到的信息，解決系統在運行中發生的問題。

監控的性價比問題，也就是監控成本考量

1、監控的參數不一定追求百分之百的精準，需要與監控目標，占用資源等情況一起綜合考量

2、監控和性能之間有著密切的聯系

3、獲得監控參數的難度和復雜度，也決定了監控的成本

以下是詳細說明：

1、提取數據是有間隔的,即使在間隔中提取到的數據是準確的,但間隔的跨度，將影響整體數據的準確性。過密影或者提取數據的方式復雜，將影響系統性能，間隔跨度過大又影響分析的準確性。

2、獲取同一個數據庫性能的參數, 可以選擇不同方法，難易程度、數據準確性、系統耗能等因素都需要考量。有容易但不準確的方法、也有難度大很準確又十分消耗系統性能的方法。

3、獲取信息的目的各有不同，目標不同，對監控方式的選擇不同。是要形成一個系統的性能曲線圖,還是要進行報警觸發，顯然對信息獲取的要求是完全不同的。

舉一個例子，想要獲得當前的用戶連接數，方法有三：

三種方法都可以從某種角度獲得當前的POSTGRESQL和用戶之間的連接數, 哪種最適合？

如果要獲得最準確的當前與POSTGRESQL 的連接用戶數，應該用方法3。可使用方法3，就需要獲得數據庫系統的用戶名密碼, 要建立和PG數據庫之間的連接, 還要考慮到如果其他的系統也在頻繁查詢pg_stat_activity，是否會影響PG系統的性能的問題?

方法1 雖然最不準確, 但消耗資源最小、系統侵入性最小！ 如果僅僅是統計系統的連接數, 1號的方式基本可以達到需求了。

實際上大家可以看到真正的用戶的連接只有5個。

監控中信息獲得方式與目的通常有三種

1、通過日志進行分析。對于系統的優化和性能調優, 大部分的信息會來自于日志系統來進行分析, 通過日志獲取是對系統侵入性最小,性能影響最小的方式, 缺點是不及時或者分析上比較困難。

2、通過查詢數據庫進行相關數據的獲取。多來自需要準確指標的獲取,或與某些報警的參數閥值設定有關. 通過查詢數據庫來進行數據的獲取,對系統的侵入性大, 缺點是很可能會影響性能。

3、通過操作系統獲取。基本上在比較粗淺的系統性能參數,并繪制出相關較底層的性能曲線。此方式對于系統的侵入性不大。

接下來我們就分別說說這三種方式。。。

1、通過日志進行分析：

如果需要日志記錄信息，配置信息主要分為以下幾種

1、日志的格式

2、日志的輸出信息的標準

3、日志的位置,及日志的名字

4、廢棄日志的處理

POSTGRESQL本身日志提供的數據比較集中,并且相對的配置項也比較多

例如信息輸出的目的地, 收集是否打開還是關閉, 日志的存儲的目錄,日志數據的文件名格式,以及數據是否要進行rotation等等。還有日志內部的格式是什么, 這都與后面要如何分析日志有關,有些日志分析軟件是要指定日志的格式.

5、日志需要記錄的信息

Checkpoints信息

Connection信息

Disconnection信息

Lock信息

臨時表在系統中的產生的信息

例如我們收集信息的錯誤類型, 慢查詢日志, checkpoint connection的一些信息,主機名,鎖信息 等等。

介紹個相關工具Pgbadger,Pgbadger是一個開源的分析POSTGRESQL日志的工具,通過這個工具可以對POSTGRESQL 日志進行分析,Ppgbadger是通過perl語言撰寫的根據固定格式日志,來產生WEB 分析報告的一個開源的軟件。其中主要對連接, checkpoint 臨時文件, vacuum 以及鎖慢查詢等等進行一個頁面展示,并進行一些分析.

Postgresql 如何分析日志 -- Pgbadger

Postgresql 如何分析日志 -- Pgbadger

上圖的相關展示還是比較詳細的。還可以進行二次開發將信息通過網站發布,方便查看。

通過日志可以分析更多的信息,這里就不再展開了, 另外我們其實是可以通過數據庫系統本身來獲取信息, 數據庫本身的提供的信息也分兩種。

1與數據庫底層有關的信息 ,也就是數據庫與系統有關的信息2與數據庫本身有關的信息, 這里PG中有一部分是pg_catalog schema 信息,其中包含了大量 與PG有關的信息。

2、通過語句獲取postgresql 信息：

SELECT'index hit rate' AS name,(sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratioFROM pg_statio_user_indexesUNION ALLSELECT'table hit rate' AS name,sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratioFROM pg_statio_user_tables;

這條語句獲取的信息, 有兩個點 ：

1系統的內存是否有短缺的可能。

2是否缺少索引。

pg_statio_user_indexes是一個視圖，其中包含了數據庫中的表中的index的讀取和命中的數字, 將這兩個數字進行加工就可以得到一個比率,通過這個比率就可以, 下邊的是pg_statio_user_tables這里也是展示在內存中獲取到信息和整體讀取數據的數字, 這兩個的比率也是可以展示表數據讀取 在內存中HIT 的情況.

BLOAT膨脹這個詞在postgresql中是一個比較敏感的詞, 你的數據庫中的表的是否膨脹你是要清楚了,如果POSTGRESQL 中一個表任意膨脹.

1、會占據大量的數據庫存儲的空間

2、會影響對此表的數據查詢性能

所以表膨脹一直是對POSTGRESQL 的監控中的一個要點

在執行完腳本后,我們就可以觀察到bloat的比率 和膨脹占用的空間, 如果我們的可以將這些數據,例如將一些關鍵表的數據進行歷史留存,并且使用一些通過一些前端程序展示某些曲線, 就很容易發現潛在的問題,

例如經常有大型的SQL 占用某些核心表, 導致無法進行有效的 dead tuple 回收,造成某個表的 waste 空間一漲再漲

例如我們可以擴展CREATE EXTENSION pgstattuple; 對 dead_tuple_count /tuple_count*100 來看一下當前POSTGRESQL的 dead_typle_count的一個百分比, 也可以對這些關鍵的表設定一些警告,當超過多少百分比后

我們就進行相關的報警或觸發一些操作.

與其他的數據庫比較, POSTGRESQL 在buffer利用上的統計和展示是比較明確的,也是比較方便的, 上面的腳本我們使用POSTGRESQL的擴展 pg_buffercache , 通過這個插件配合系統表,我們可以實時的查看postgresql在buffer hit 方面的狀態, buffer hit 大致的意思就是在數據處理時，數據庫中的處理的數據在內存中是否都能被命中, 如果這個命中比較低的情況下,說明我們的內存短缺,或者我們有一些系統的實際SQL不合理的.就需要我們更深層次的分析了.

同時通過延伸, 對整體的buffer_percent進行一個累計,后就可以得到我們的內存和數據之間的BUFFER HIT 的比率。

3、通過系統獲得監控數據:

通過postgresql的命令pg_isready來判斷是否可以和POSTGRESQL數據庫進行連接,并通過返回的數字來判斷釋放可以連接 還是不可以連接 0 可以連接 1 拒絕連接2 無響應

大家可以注意到,與系統的狀態, 簡單的信息的獲取可以通過 系統的命令 + 簡單的過濾 就可以了而詳細需要分析的以及歷史數據分析等等 大多是要通過其他的方式來進行

圖中是通過PSQL 命令執行簡單的SQL 語句獲得當前PG的連接占總的運行連接的比率, 所以大多數簡單的信息大部分都是要提供給圖形化或監控報警的.

監控誤區

誤區1、人家監控哪里我就監控哪里。例如某保險公司的監控參數, 我直接拿來, 可能部分常規的監控參數是可以通用的,但與特性有關的監控指標照搬就有點多此一舉了。業務量及業務內容，業務需求都不同，照搬來的監控，有些內容即耗費你的系統的性能,來提取無用的性能點, 又耗費你的精力,導致后期監控疲勞.

誤區2、監控的內容要全。一個數據庫監控的指標可能有上百,甚至上千個。都要監控,毫無重點,最終出了事情,不知道哪個監控點應該被響應.

誤區3、監控的閾值要低。越早報警越好。如果你的系統中你負責的數據庫只有幾個,十幾個還好說,實際上如果你有上百個數據庫要負責,這樣的做法,只能是狼來了,最終導致監控沒人看，出了事情再后悔莫及。

誤區4、監控軟件越新越好。監控本身就是獲取監控端的數據為基礎的, 新的監控軟件是否在這方面有更改革新, 如果僅僅是展示方式或者其他附屬功能上的提升,應考慮升級的花費以及相關精力的付出。

監控原理

1復制的服務是否持續的進行 2復制是否有延遲

一個問題： 如果邏輯復制停止了, 我們要不要當做一個緊急的任務來報警？如果我們不考慮業務,或者說如果復制停止了, 業務在一定時間是可以承受的,或不是很在乎這里就要介入到PG的數據庫的原理, 如果邏輯復制停止了, 則會最終導致主庫的wal無法被清除, 占滿磁盤空間, 最終導致主庫停庫的問題, 說到這里如果此時有邏輯復制的PG ,我們并未監控邏輯復制是否中斷后立即報警, 但這臺機器的WALLOG 磁盤空間報警了, 可能第一就會想看邏輯復制是否還正常那么就會繼續這個問題問, 如果是standby的庫不穩定, 經常DOWN 掉, 那針對邏輯復制, 如果我設置了報警, 怎么辦, 經常性的報警那就需要

1 增大WAL LOG 的空間, 設置相關的邏輯復制停止后的 多長時間進行報警 比如 5分鐘以后報警還是 1分鐘以后報警 這都要看 standby經常多長時間內恢復,并正常工作.

在知道監控什么, 并且知道一些如果logical replication 停止后會觸發什么的情況下, 你可能會選擇 ,當邏輯復制停止后,選擇報警,并開始關注磁盤空間尤其是涉及 wal log 的那部分,但事情并沒有到此為止, 如果你的客戶告訴你, 經常獲取的的數據和主庫有不同的時候,怎么來解決,通過pg_stat_replication對你所在的通道中的sent_lsn write_lsn flush_lsn replay_lsn 這四個參數進行比對

通過對比這四個參數的的diff 就可以得出幾種情況

1sent_lsn和write_lsn之間有延遲

2write_lsn和flush_lsn之間有延遲

3replya_lsn和flush_lsn有延遲

4sent_lsn和replay_lsn之間有沒有延遲

Sent_lsn和write_lsn之間有延遲是不是網絡方面有問題, 可以著重關注

Write_lsn和flush_lsn之間有延遲查看I/O 方面的壓力大不大

Replay_lsn和flush之間有延遲,可以關注是否經常有批操作或大事務的存在

Sent_lsn和 replay之間沒有延遲說明復制正常性能OK

總結一個相關的PG 數據庫或者說是數據庫監控方面的一個思維導圖:

分別從監控的模式,監控的目的,監控的方式以及監控與性能之間的關系進行了一個初步的總結.

最后，介紹幾種PG 監控的工具：

?PG_ADMIN

?Solarwinds

?Pganalyze

?PGWATCH

?PMM2

?PGHERO

?PGCLUU

?PGBADGER

?PGTOP

以上內容有對應視頻授課內容，請近期關注，我剪輯完就上傳。

以上內容由東方瑞通資深講師 Austin原創，Austin老師13年專業DBA經驗，曾任互聯網金融公司Senior DBA、500強制藥企業Senior DBA，精通Mysql、PostgreSQL、Mongo DB、SQLServer

#PostgreSQL#

總結

以上是生活随笔為你收集整理的postgresql参数化查询_一个能融会贯通PostgreSQL监控的人，大概率是高手的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。