PG statspack 的安装和使用

今天学习了下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

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%