PostgreSQL日志分析工具——pgBadger

摘要

之前曾介绍过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

tarzxvfpgbadger-9.2.tar.gz
cdpgbadger-9.2
[root@localhostpgbadger-9.2]#ll
total1416
drwxr-xr-x.8rootroot4096Sep1101:13blib
-rw-rw-r--.1rootroot94609Jul2807:45ChangeLog
-rw-rw-r--.1rootroot347Jul2807:45CONTRIBUTING.md
drwxrwxr-x.2rootroot4096Jul2807:45doc
-rw-rw-r--.1rootroot903Jul2807:45LICENSE
-rw-r--r--.1rootroot24018Sep1101:13Makefile
-rw-rw-r--.1rootroot1400Jul2807:45Makefile.PL
-rw-rw-r--.1rootroot81Jul2807:45MANIFEST
-rw-rw-r--.1rootroot334Jul2807:45META.yml
-rw-rw-r--.1rootroot1256247Jul2807:45pgbadger
-rw-r--r--.1rootroot0Sep1101:13pm_to_blib
-rw-rw-r--.1rootroot30799Jul2807:45README
drwxrwxr-x.3rootroot4096Jul2807:45resources
drwxrwxr-x.2rootroot4096Jul2807:45tools

perlMakefile.PL

make;

makeinstall;

查看安装版本

[root@localhostpgbadger-9.2]#whichpgbadger
/usr/local/bin/pgbadger
[root@localhostpgbadger-9.2]#pgbadger-V
pgBadgerversion9.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

yuminstallhttpd
chkconfighttpdon
servicehttpdstart
yuminstallphp

查看/var/www

[root@localhostwww]#pwd
/var/www
[root@localhostwww]#ll
total20
drwxr-xr-x.2rootroot4096Aug1515:45cgi-bin
drwxr-xr-x.3rootroot4096Sep1102:23error
drwxr-xr-x.2rootroot4096Sep1103:43html
drwxr-xr-x.3rootroot4096Sep1102:23icons
drwxr-xr-x.3rootroot4096Sep1104:33pgbadger

日志分析

./pgbench-i
./pgbench-c10-t1000

[root@localhost~]#pgbadger/opt/postgres/db/pgsql-9.5.9/data/pg_log/*.log-o/var/www/pgbadger/out.html-fstderr
[========================>]Parsed10485802bytesof10485802(100.00%),queries:65341,events:2
[========================>]Parsed755640bytesof755640(100.00%),queries:70036,events:2
LOG:Ok,generatinghtmlreport...
[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-fstderr
[========================>]Parsed1499636bytesof1499636(100.00%),queries:0,events:0
[========================>]Parsed10485846bytesof10485846(100.00%),queries:65379,events:0
[========================>]Parsed743891bytesof743891(100.00%),queries:4625,events:0
LOG:Ok,generatingHTMLdailyreportinto/var/www/pgbadger//2017/09/11/...
LOG:Ok,generatingHTMLdailyreportinto/var/www/pgbadger//2017/09/12/...
LOG:Ok,generatingHTMLweeklyreportinto/var/www/pgbadger//2017/week-38/...
LOG:Ok,generatingglobalindextoaccessincrementalreports...

[root@localhost~]#ll/var/www/pgbadger/
total692
drwxr-xr-x.4rootroot4096Sep1121:092017
-rw-r--r--.1rootroot695474Sep1221:15index.html
-rw-r--r--.1rootroot187Sep1221:15LAST_PARSED
drwxrwxrwx.6110711074096Aug2817:44postgresql-9.5.9
[root@localhost~]#ll/var/www/pgbadger/2017/
total8
drwxr-xr-x.4rootroot4096Sep1221:1509
drwxr-xr-x.2rootroot4096Sep1121:09week-38
[root@localhost~]#

这里创建一个具有年份的目录，链接所有报告的索引页面以及保存最后一个解析行的文件。

以增量模式创建报表时，会创建链接每个报表页面的索引页(index.html)。

总结

pgBadger是一个完美的分析工具，旨在提高数据库的性能，因为它具有丰富的统计结果。

当你不知道PostgreSQL性能坏的原因，如果你认为查询执行时间是可疑的，可以尝试使用它。