一、简介

pgsi is the Postgres System Impact report,a script that analyzes Postgres log files and reports on which queries have thehighest overall impact on the server. The impact is computed by looking at notonly how long each query takes to complete,but how many times it is run,andthe time period between subsequent runs. The report show the queries with thehighest system impact,grouped by the type of query (SELECT,UPDATE,etc.)

二、配置数据库

[postgres@pgsi ~]$ vi /opt/pg93/data/postgresql.conf

logging_collector = on

log_filename = 'postgresql.log'

log_duration = on

log_statement = 'all'

若使用的是默认的日志模式，还需要设置下面参数：

log_line_prefix = '%t %p '---至少设置这两个参数，也可添加%h %u %d等参数

启动数据库：

[postgres@pgsi ~]$ pg_ctl start

三、安装

[root@pgsi ~]# wgethttp://bucardo.org/downloads/pgsi-1.7.1.tar.gz

[root@pgsi ~]# tar -zxvf pgsi-1.7.1.tar.gz

[root@pgsi ~]# cd pgsi-1.7.1

[root@pgsi pgsi-1.7.1]# perl Makefile.PL

Checking if your kit is complete...

Looks good

Writing Makefile for pgsi

[root@pgsi pgsi-1.7.1]# make

cp pgsi.pl blib/script/pgsi.pl

/usr/bin/perl -MExtUtils::MY -e'MY->fixin(shift)' -- blib/script/pgsi.pl

Manifying blib/man1/pgsi.pl.1

Created pgsi.html

[root@pgsi pgsi-1.7.1]# make install

Created pgsi.html

Installing/usr/local/share/man/man1/pgsi.pl.1

Installing /usr/local/bin/pgsi.pl

Appending installation info to/usr/lib64/perl5/perllocal.pod

四、使用

4.1 生成一些日志内容

[postgres@pgsi ~]$ createdb pgbench

[postgres@pgsi ~]$ pgbench -i -s 30 pgbench

[postgres@pgsi ~]$ pgbench -c 5 -T 360pgbench

4.2 生成报告

命令参数如下：

--version

Returns the version number

--verbose

Increases the level of verbosity

--help

Gives some basic help

--file=x

The log file to parse. If not given,willread stdin. This can be used more than once to read in multiple files at onetime.

--format=X

Indicates the format of the output file.Current options are html and mediawiki

--mode=X

Indicates the log file mode. Currentoptions are pid,csv,bare,and syslog

--color

Adds Syntax highlighting to the queries inhtml format. On by default,can be turned off with --no-color

生成报告：

[postgres@pgsi ~]$ pgsi.pl--file=/opt/pg93/data/pg_log/postgresql.log --format=html --color >pg-log.html

Items processed: 144259

浏览器打开：

五、参考文档

http://bucardo.org/wiki/Pgsi

https://github.com/bucardo/pgsi