今天学习了下postgres 的statpack 功能，设置PG的statpack功能需要下载一个包,并安装;PG的statspack功能和Oracle 的Statspack功能相似，用来统计一个时间段内数据库的运行指标只是PG 的statpack报告内容相比Oracle的没有那么详尽,下面介绍下PG statspack安装和使用过程。

安装 pgstatspack

1.1 下载 pgstatspack tar包 ，下载地址
http://pgfoundry.org/frs/?group_id=1000375&release_id=1321
我下载的是pgstatspack_version_2.1.tar.gz, 是目前的最新版本;

1.2 创建一个超级用户，如果使用postgres用户，则不用新建；

1.3 开始安装,执行脚本 install_pgstats.sh (以postgres OS用户执行脚本)

1
2

cd pgstatspack的解压目录  
./install_pgstats.sh

这个脚本将在各个数据库(postgres.templates库除外)上安装统计信息表和函数;

1.4 创建 snapshot,有两种方式:

方式一： 手工方式创建 snapshot

1
2

Snapshot with comment:  
select pgstatspack_snap('my comment');

方式二：crontab 方式自动创建 snapshots

以下是我本机的crontab,每15分钟创建一个snapshot

1
2

#Automated snapshots every 15 mins  
*/15 * * * * /home/postgres/pgstatspack/bin/snapshot.sh 1 > /home/postgres/pgstatspack/log/1.log 2>&1

生成报表

1
2
3

cd pgstatspack的解压目录/bin  
./pgstatspack_report.sh <username> <database>  
pgstatspack_report.sh -u postgres -d mydb

执行这个脚本后，就会提示输入begin snapid,end snapid,这里和生成Oracle 的statspack报告内似; 这个脚本生成的报告默认是在/tmp目录下，可以修改下这个脚本，将目录生成在指定目录

1
2
3
4

vi pgstatspack_report.sh,修改FILENAME变量即为报告存放目录。  
..  
FILENAME=/home/postgres/pgstatspack/report/pgstatreport_${CUR_DATE}_${STARTSNAP}_${STOPSNAP}.txt  
..

报告内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

napshot information  
Begin snapshot :  
snapid | ts | description  
--------+----------------------------+---------------------  
 4 | 2010-08-07 04:36:38.303123 | cron based snapshot  
(1 行)
End snapshot :  
snapid | ts | description  
--------+----------------------------+---------------------  
 5 | 2010-08-07 04:51:33.874608 | cron based snapshot  
(1 行)
Seconds in snapshot: 895.571485
  
Database version  
 version  
---------------------------------------------------------------------------------------------------------------  
PostgreSQL 9.0beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 32-bit  
(1 行)
current_database | dbsize  
------------------+--------  
mydb | 29 MB  
(1 行)
Database statistics  
 database | tps | hitrate | lio_ps | pio_ps | rollbk_ps  
---------------+-------+---------+----------+--------+-----------  
mydb | 75.10 | 99.00 | 11420.30 | 0.35 | 0.00  
template1 | 0.03 | 99.00 | 2.03 | 0.00 | 0.00  
postgres | 0.03 | 99.00 | 2.03 | 0.00 | 0.00  
template0 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00  
(4 行)

注意： 以下是报告的部分内容, 详细内容可以参照报告模板，解压包的目录下有份报告模板 pgstatspack_sample_report.txt。

报告字段解释

tps – transactions per second
Number of committed transactions between snapshots, devided by the number of seconds between the two snapshots.
pg_stat_database.xact_commit – number of committed transactions
hitrate : cache命中率
Number of cache reads as a percentage of the total number of reads (cache and physical) between the two snapshots.
pg_stat_database.blks_hit – cache reads
pg_stat_database.blks_read – physical reads
lio_ps: 逻辑读
Number of logical reads between snapshots, devided by the number of seconds between the two snapshots.
Logical reads is in postgresql the number of physical reads + the number of cache reads.
pg_stat_database.blks_hit – cache reads
pg_stat_database.blks_read – physical reads
pio_ps: 物理读
Number of physical reads between snapshots, devided by the number of seconds between the two snapshots.
pg_stat_database.blks_read – physical reads
rollbk_ps
Number of rollbacks between snapshots, devided by the number of seconds between the two snapshots.
pg_stat_database.xact_rollback

Pgstatspack 相关表

1
2
3
4
5
6
7

pgstatspack_snap  
pgstatspack_database  
pgstatspack_tables  
pgstatspack_indexes  
pgstatspack_sequences  
pgstatspack_settings  
pgstatspackid