PostgreSQL技術大講堂 - 第34講：調優工具pgBagder部署

PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色許可權、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

第34講：調優工具pgBagder部署

第34講：11月18日(週六)19:30-20:30，往期文件及影片，聯絡CUUG

內容1 : 日誌分析器pgBadger簡介

內容2 : pgBadger部署

內容3 : 如何產生類似於AWR報告

內容4 : 基於：一小時、一天、一週、一月的報告

內容5 :  如何分析pgBadger

第34講：調優工具pgBagder部署

pgBadger簡介

pgBadger是一個PostgreSQL日誌分析器，它是為提高速度而構建的，具有來自PostgreSQL日誌檔案的完整報告。它是一個小型的Perl指令碼，效能優於任何其他PostgreSQL日誌分析器。

pgBadger可以自動檢測日誌檔案格式(syslog、stderr、csvlog或jsonlog)。它被設計用來解析巨大的日誌檔案和壓縮檔案。支援的壓縮格式有gzip、bzip2、lz4、xz、zip和zstd。

還可以使用命令列選項將pgBadger限制為僅報告錯誤或刪除報告的任何部分。

pgBadger支援在postgresql.conf檔案中透過log_line_prefix 自定義的任何格式，只要它至少指定%t和%p模式。

pgBadger允許透過使用指定CPU數量的-j選項並行處理單個日誌檔案或多個檔案。

如果要儲存系統效能，也可以使用log_duration 替代log_min_duration_statement來僅報告持續時間和查詢數。

pgBadger特性

· pgBadger報告有關SQL查詢的所有資訊：

總體統計資料。

等待最頻繁的查詢。

等待時間最長的查詢。

生成最多臨時檔案的查詢。

生成最大臨時檔案的查詢。

最慢的查詢。

佔用時間最多的查詢。

最頻繁的查詢。

最常見的錯誤。

查詢時間直方圖。

會話時間柱狀圖。

參與熱門查詢的使用者。

頂-級查詢中涉及的應用程式。

產生最多取消的查詢。

查詢大部分被取消。

最耗時的準備/繫結查詢。

· 還提供分為五分鐘的小時圖表

SQL查詢統計資訊。

臨時檔案統計。

檢查點統計。

自動真空和自動分析統計資料。

取消的查詢。

錯誤事件(當機、致命、錯誤和警告)。

錯誤類分佈。

· 一些關於分佈的餅圖：

鎖定統計資訊。

按型別查詢(選擇/插入/更新/刪除)。

每個資料庫/應用程式的查詢型別分佈。

每個資料庫/使用者/客戶端/應用程式的會話數。

每個資料庫/使用者/客戶端/應用程式的連線數。

根據表格自動真空和自動分析。

每個使用者的查詢數和每個使用者的總持續時間。

所有圖表都是可縮放的，可以儲存為PNG影像。報告的SQL查詢將自動突出顯示和美化。

· 可以解析PgBouncer日誌檔案並建立以下報告

請求吞吐量

位元組I/O吞吐量

查詢平均持續時間

同時舉行的會議

會話時間柱狀圖

每個資料庫的會話數

每個使用者的會話數

每個主機的會話數

已建立的聯絡

每個資料庫的連線數

每個使用者的連線數

每個主機的連線數

使用最多的保留池

最常見錯誤/事件

pgBadger報告模式

· pgBadger報告模式

一小時一個報告

每天一個報告

每週一個累積報告的增量報告

每月一個報告

多個程式處理一個日誌

多個程式處理多個日誌

pgBadger部署

· 官方下載地址

· 編譯與安裝(root使用者)

1、解壓後進入該安裝目錄

# unzip pgbadger-master.zip

# cd pgbadger-master

2、編譯並安裝：

# perl Makefile.PL

# make && make install

3、預設安裝目錄：

/usr/local/bin/pgbadger

postgresql.conf配置

logging_collector = on

log_directory = 'pg_log‘

log_checkpoints = on

log_connections = on

log_disconnections = on

log_lock_waits = on

log_temp_files = 0

log_autovacuum_min_duration = 0

log_rotation_size=10240

--例如，對於“stderr”日誌格式，日誌行字首必須至少為：

log_line_prefix = '%t [%p]: '

--日誌行字首可以新增使用者、資料庫名稱、應用程式名稱和客戶端ip地址，如下所示：

log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '

--或者對於syslog日誌檔案格式：

log_line_prefix = 'user=%u,db=%d,app=%a,client=%h '

--日誌訊息支援英文，不支援中文：

lc_messages='en_US.UTF-8'

lc_messages='C‘

--記錄的日誌語句

假如：log_statement=all

則：不會使用(log_min_duration_statement)記錄任何內容

所以：log_min_duration_statement=0 #記錄所有的統計資訊包含實際的查詢字串

警告：不要同時啟用log_min_duration_statement、log_duration and log_statement ，這將導致錯誤的計數器值。注意，這也會大大增加日誌的大小。log_min_duration_statement應始終首-選。

如何產生報告

當有許多小的日誌檔案和許多CPU時，一次將一個核心專用於一個日誌檔案會更快。要啟用此行為，必須改用-J N選項。對於每個10MB的200個日誌檔案，-J選項的使用開始變得非常有效，有8個核心。使用此方法，您將確保不會丟失報表中的任何查詢。

一個在伺服器上完成的基準測試，有8個CPU和9.5GB的單個檔案。

Option | 1 CPU | 2 CPU | 4 CPU | 8 CPU

--------+---------+-------+-------+------

-j | 1h41m18 | 50m25 | 25m39 | 15m58

-J | 1h41m18 | 54m28 | 41m16 | 34m45

200個日誌檔案，每個10MB，總共2GB，結果略有不同：

Option | 1 CPU | 2 CPU | 4 CPU | 8 CPU

--------+-------+-------+-------+------

-j | 20m15 | 9m56 | 5m20 | 4m20

-J | 20m15 | 9m49 | 5m00 | 2m40

--產生一個小時的日誌報告：

$ pgbadger -q /usr/local/pg12.2/data/pg_log/postgresql-2020-06-19_00*.log \

-o /home/postgres/www/pg_reports/day-06-19-00.html

--產生每日和每週的日誌報告：

$ pgbadger -I -q /usr/local/pg12.2/data/pg_log/* \

-O /home/postgres/www/pg_reports/ \

-f stderr

在這種模式下，pgBadger將在輸出目錄中建立一個自動增量檔案。這意味著可以在每週旋轉的日誌檔案上每天以這種模式執行pgBadger，並且它不會對日誌條目計數兩次，可以使用crontab進行定時執行。

--重建報告：

pgbadger -X -I -O /home/postgres/www/pg_reports/ --rebuild

此時在reports目錄下產生建立一個目錄，裡面包含js檔案，同時在另外一個目錄下建立一個目錄week-25，存放index檔案。

它還將更新所有資原始檔(JS和CSS)。如果報表是使用此選項生成的，請使用-E或--explode。

--預設情況下，增量模式下的pgBadger只計算每日和每週報告。如果需要每月累積報告，則必須使用單獨的命令指定要生成的報告。例如，為2019年8月編制一份報告：

pgbadger --month-report 2919-08 /path/to/incremantal/reports/

JSON FORMAT

JSON格式有利於與其他語言共享資料，這使得pgBadger結果很容易整合到其他監控工具(如Cacti或Graphite)中。

報告介面一

報告介面二

CUUG PostgreSQL技術大講堂系列公開課，往期影片及文件，請聯絡CUUG客服。