一、简介
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
