摘要
之前曾介绍过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;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
查看安装版本
[root@localhost pgbadger-9.2]# which pgbadger
/usr/local/bin/pgbadger
[root@localhost pgbadger-9.2]# pgbadger -V
pgBadger version 9.2
- 1.
- 2.
- 3.
- 4.
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 '
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
安装httpd、php
yum install httpd
chkconfig httpd on
service httpd start
yum install php
- 1.
- 2.
- 3.
- 4.
查看/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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
日志分析
./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 ~]#
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
在浏览器打开/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 ~]#
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
这里创建一个具有年份的目录，链接所有报告的索引页面以及保存最后一个解析行的文件。
以增量模式创建报表时，会创建链接每个报表页面的索引页(index.html)。
总结
pgBadger是一个完美的分析工具，旨在提高数据库的性能，因为它具有丰富的统计结果。
当你不知道PostgreSQL性能坏的原因，如果你认为查询执行时间是可疑的，可以尝试使用它。
