摘要

之前曾介紹過PostgreSQL的日志審計，這會將執行的SQL輸出到服務日志(pg_log)，勢必會帶來性能問題，真正的問題是，這些記錄對我們有多少幫助?所以我們來看一下如何使用增加的日志記錄來做一些有用的事情。

pg_stat_statements 不僅輸出SQL語句，還可以輸出執行時間等，通過分析此服務器日志，可以知道執行什么樣的SQL，以及縮小范圍找到哪些SQL有可能出現性能問題。

SQL日志分析工具pgBadger

pgBadger 主頁：home 下載地址：download

pgBadger是在Perl中創建的一個腳本，與PHP腳本中開發的名為pgFouine的程序具有相同的功能。 在命令行上指定并執行服務器日志時，會生成一個將服務器日志分析為HTML文件的報告。

此外，它不僅收集SQL，還收集服務器日志中的錯誤日志等。

環境

CentOS 6.9、PostgreSQL9.5.9、pgBadger 9.2

安裝pgBadger

tar zxvf pgbadger-9.2.tar.gz 
cd pgbadger-9.2 
[root@localhost pgbadger-9.2]# ll 
total 1416 
drwxr-xr-x. 8 root root    4096 Sep 11 01:13 blib 
-rw-rw-r--. 1 root root   94609 Jul 28 07:45 ChangeLog 
-rw-rw-r--. 1 root root     347 Jul 28 07:45 CONTRIBUTING.md 
drwxrwxr-x. 2 root root    4096 Jul 28 07:45 doc 
-rw-rw-r--. 1 root root     903 Jul 28 07:45 LICENSE 
-rw-r--r--. 1 root root   24018 Sep 11 01:13 Makefile 
-rw-rw-r--. 1 root root    1400 Jul 28 07:45 Makefile.PL 
-rw-rw-r--. 1 root root      81 Jul 28 07:45 MANIFEST 
-rw-rw-r--. 1 root root     334 Jul 28 07:45 META.yml 
-rw-rw-r--. 1 root root 1256247 Jul 28 07:45 pgbadger 
-rw-r--r--. 1 root root       0 Sep 11 01:13 pm_to_blib 
-rw-rw-r--. 1 root root   30799 Jul 28 07:45 README 
drwxrwxr-x. 3 root root    4096 Jul 28 07:45 resources 
drwxrwxr-x. 2 root root    4096 Jul 28 07:45 tools 
 
perl Makefile.PL 
 
make; 
 
make install;  

查看安裝版本

[root@localhost pgbadger-9.2]# which pgbadger 
/usr/local/bin/pgbadger 
[root@localhost pgbadger-9.2]# pgbadger -V 
pgBadger version 9.2  

PostgreSQL配置

logging_collector = on 
log_filename = 'postgresql-%w.log' 
log_file_mode = 0640 
log_truncate_on_rotation = on 
log_rotation_age = 1d 
log_min_duration_statement = 0 
log_checkpoints = on 
log_connections = on 
log_disconnections = on 
log_duration = on 
log_lock_waits = on 
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '  

安裝httpd、php

yum install httpd 
chkconfig httpd on 
service httpd start 
yum install php  

查看/var/www

[root@localhost www]# pwd 
/var/www 
[root@localhost www]# ll 
total 20 
drwxr-xr-x. 2 root root 4096 Aug 15 15:45 cgi-bin 
drwxr-xr-x. 3 root root 4096 Sep 11 02:23 error 
drwxr-xr-x. 2 root root 4096 Sep 11 03:43 html 
drwxr-xr-x. 3 root root 4096 Sep 11 02:23 icons 
drwxr-xr-x. 3 root root 4096 Sep 11 04:33 pgbadger  

日志分析

./pgbench -i 
./pgbench -c 10 -t 1000 
 
[root@localhost ~]# pgbadger /opt/postgres/db/pgsql-9.5.9/data/pg_log/*.log -o /var/www/pgbadger/out.html -f stderr 
[========================>] Parsed 10485802 bytes of 10485802 (100.00%), queries: 65341, events: 2 
[========================>] Parsed 755640 bytes of 755640 (100.00%), queries: 70036, events: 2 
LOG: Ok, generating html report... 
[root@localhost ~]#   

在瀏覽器打開/var/www/pgbadger/out.html

連續日志分析(增量模式)

增量模式用于連續獲取分析結果。

以增量模式執行pgBadger會在第二天創建下一個報告(增量報告)。

[root@localhost ~]# pgbadger -I /opt/postgres/db/pgsql-9.5.9/data/pg_log/*.log -o /var/www/pgbadger/out.html -f stderr 
[========================>] Parsed 1499636 bytes of 1499636 (100.00%), queries: 0, events: 0 
[========================>] Parsed 10485846 bytes of 10485846 (100.00%), queries: 65379, events: 0 
[========================>] Parsed 743891 bytes of 743891 (100.00%), queries: 4625, events: 0 
LOG: Ok, generating HTML daily report into /var/www/pgbadger//2017/09/11/... 
LOG: Ok, generating HTML daily report into /var/www/pgbadger//2017/09/12/... 
LOG: Ok, generating HTML weekly report into /var/www/pgbadger//2017/week-38/... 
LOG: Ok, generating global index to access incremental reports... 
 
[root@localhost ~]# ll /var/www/pgbadger/ 
total 692 
drwxr-xr-x. 4 root root   4096 Sep 11 21:09 2017 
-rw-r--r--. 1 root root 695474 Sep 12 21:15 index.html 
-rw-r--r--. 1 root root    187 Sep 12 21:15 LAST_PARSED 
drwxrwxrwx. 6 1107 1107   4096 Aug 28 17:44 postgresql-9.5.9 
[root@localhost ~]# ll /var/www/pgbadger/2017/ 
total 8 
drwxr-xr-x. 4 root root 4096 Sep 12 21:15 09 
drwxr-xr-x. 2 root root 4096 Sep 11 21:09 week-38 
[root@localhost ~]#   

這里創建一個具有年份的目錄，鏈接所有報告的索引頁面以及保存最后一個解析行的文件。

以增量模式創建報表時，會創建鏈接每個報表頁面的索引頁(index.html)。

總結

pgBadger是一個完美的分析工具，旨在提高數據庫的性能，因為它具有豐富的統計結果。

當你不知道PostgreSQL性能壞的原因，如果你認為查詢執行時間是可疑的，可以嘗試使用它。