用pg_statsinfo监控PostgreSQL v8.3,v8.4,v9.0

pg_statsinfo的架构如下：

分为三个组件:

1. pg_statsinfo

部署在被监控的数据库端,用于采集数据库瞬间状态,过滤数据库csv日志,需要与repository DB通信.

2. pg_reporter

部署在HTML报告服务器上,需要与repository DB通信，与被监控的数据库通信(可选).

3. repository DB

用于存放pg_statsinfo发送过来的snapshot报告。被pg_reporter调用，生产HTML报告。

另外,非常强的一点是可以自己编写模板。

架构如图:

报告分为两类：

第一类是pg_statsinfo,需要在repo数据库安装pg_statsinfo支持.

第二类是schema,需要有连接到被监控数据库的配置。

下面是statsinfo报告的介绍：

1. Summary

name	5480307906522906617
hostname	db-172-16-3-33.sky-mobi.com.hz
port	1921
pg_version	9.0beta2
snapshot begin	2010-06-08 18:04:52
snapshot end	2010-06-09 13:30:00
snapshot duration	19:25:09
total database size	5073 kB
total commits	18698
total rollbacks	2

2.Database Statistics

ID	database	MB	+MB	commit/s	rollback/s	hit%	gets/s	reads/s	rows/s
1	postgres	4	0	0.267	0.000	99.900	17.772	0.016	95.099
2	test	26	26	0.047	0.000	99.800	23.219	0.043	82.867

Disk Usage

Disk Usage per Tablespace

ID	tablespace	location	device	used (MB)	avail (MB)	remain%
1	<pg_xlog>	/database/pgdata/tbs2/pg_xlog	104:33	187	137594	99.864
2	pg_default	/database/pgdata/tbs1/pg_root	104:17	74	137707	99.946
3	pg_global	/database/pgdata/tbs1/pg_root	104:17	74	137707	99.946
4	tbs_test	/database/pgdata/tbs4/tbs_test	104:65	86	137695	99.937

Long Transactions

ID	pid	client address	when to start	duration (sec)	query

Notable Tables

Heavily Updated Tables

ID	database	schema	table	INSERT	UPDATE	DELETE	total	HOT%
1	test	test	tbl_test	620075	0	0	620075
2	test	pg_toast	pg_toast_2619	12	0	6	18
3	test	pg_catalog	pg_attribute	7	0	0	7
4	test	pg_catalog	pg_shdepend	4	0	0	4
5	postgres	pg_catalog	pg_shdepend	4	0	0	4
6	test	pg_catalog	pg_depend	3	0	0	3
7	test	pg_catalog	pg_statistic	1	2	0	3	50.000
8	test	pg_catalog	pg_type	2	0	0	2
9	test	pg_catalog	pg_namespace	1	0	0	1
10	postgres	pg_catalog	pg_tablespace	1	0	0	1
11	postgres	pg_catalog	pg_database	1	0	0	1
12	test	pg_catalog	pg_authid	1	0	0	1
13	test	pg_catalog	pg_database	1	0	0	1
14	postgres	pg_catalog	pg_authid	1	0	0	1
15	test	pg_catalog	pg_tablespace	1	0	0	1
16	test	pg_catalog	pg_class	1	0	0	1
17	postgres	pg_catalog	pg_shdescription	0	0	0	0
18	postgres	pg_catalog	pg_foreign_data_wrapper	0	0	0	0
19	postgres	pg_catalog	pg_proc	0	0	0	0
20	postgres	pg_catalog	pg_user_mapping	0	0	0	0

Heavily Accessed Tables

ID	database	schema	table	seq_scan	seq_tup_read	tup_per_seq	hit%

Low Density Tables

ID	database	schema	table	rows	dead rows	pages	rows per page
1	test	test	tbl_test	620075	0	2744	225.975

Fragmented Tables

ID	database	schema	table	column	correlation

Checkpoint Activity

total checkpoints	66
checkpoints by time	64
checkpoints by xlog	0
avg written buffers	42.500
max written buffers	2336.000
avg duration (sec)	3.721
max duration (sec)	149.940

Autovacuum Activity

ID	database	schema	table	count	avg index scans	avg removed rows	avg remain rows	avg duration (sec)	max duration (sec)

Query Activity

Functions

ID	funcid	name	name	funcname	calls	total time (ms)	self time (ms)	time/call (ms)

Statements

ID	user	database	query	calls	total time (sec)	time/call (sec)
10	postgres	postgres	SELECT statsinfo.sample()	14034	0.316	0.000
16	postgres	postgres	SELECT * FROM statsinfo.tablespaces	283	0.082	0.000
19	postgres	postgres	SELECT * FROM statsinfo.activity()	283	0.019	0.000
1	postgres	postgres	SELECT d.oid AS dbid, d.datname, pg_database_size(d.oid), age(d.datfrozenxid), pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted FROM pg_database d WHERE datallowconn AND datname <> ALL (('{' || $1 || '}')::text[]) ORDER BY 1	254	2.688	0.011

Setting Parameters

ID	name	setting	source
1	TimeZone	PRC	command line
2	checkpoint_segments	32	configuration file
3	default_statistics_target	1000	configuration file
4	default_text_search_config	pg_catalog.english	configuration file
5	effective_cache_size	1024000	configuration file
6	lc_messages	C	configuration file
7	listen_addresses	*	configuration file
8	log_autovacuum_min_duration	60000	configuration file
9	log_checkpoints	on	configuration file
10	log_destination	csvlog	override
11	log_directory	/var/applog/pg_log	configuration file
12	log_lock_waits	on	configuration file
13	log_statement	ddl	configuration file
14	log_timezone	PRC	command line
15	log_truncate_on_rotation	on	configuration file
16	logging_collector	on	override
17	max_connections	1500	configuration file
18	max_stack_depth	8192	configuration file
19	pg_statsinfo.excluded_dbnames	template0,template1	configuration file
20	pg_statsinfo.repository_server	hostaddr=172.16.3.39 port=1921 database=repo user=statsrepo → dbname=repo host=172.16.3.39 port=1921 user=statsrepo	configuration file
21	random_page_cost	2	configuration file
22	server_encoding	UTF8	override
23	shared_buffers	192000	configuration file
24	shared_preload_libraries	pg_statsinfo,pg_stat_statements	configuration file
25	stats_temp_directory	/database/pgdata/tbs3/pg_stat_tmp	configuration file
26	superuser_reserved_connections	13	configuration file
27	timezone_abbreviations	Default	command line
28	track_functions	pl	configuration file
29	wal_buffers	256	configuration file
30	wal_sync_method	open_sync	configuration file

Schema Information

Tables

ID	database	schema	table	columns	row width	MB	+MB	table scans	index scans
1	test	test	tbl_test	1	4	21	21	0	0

Indexes

ID	database	schema	index	table	MB	+MB	scans	rows/scan	reads	hits	keys

配置非常简单，下面简单的介绍一下配置时的注意事项:

安装需求:

PostgreSQL 版本

PostgreSQL 8.3, 8.4, 9.0

操作系统

RHEL 5.3, CentOS 5.3, Windows XP

连接消耗

1 每个被监控的机器需要消耗1个repo DB连接.

限制:

1. 被监控系统的encoding and lc_messages必须相同

2. 被监控系统的pg_statsrepo.textlog_filename名字必须固定,建议所有监控系统一致.

3. log_timezone 参数必须设置为 unknown, gmt, or utc

4. 错误日志记录

fast或immediate关闭时，错误日志不被pg_statsinfo解析.

5. 不能采集到shutdown的checkpoint

如果repoDB与被监控的数据库是同一个集群，可能采集不到.

被监控数据库维护

1.-- 删除服务端日志pg_log

2.-- 手工生成snapshot

psql -d postgres -U postgres -c "SELECT statsinfo.snapshot('comment')"

3.-- 回旋日志文件

psql -d postgres -U postgres -c "SELECT pg_rotate_logfile()"

4.-- 重启异常进程(会造成僵死进程)

psql -d postgres -U postgres -c "SELECT statsinfo.restart()"

repo数据库维护

1. Delete Snapshots

psql -d <repository> -c "SELECT statsrepo.del_snapshot('2010-02-01 07:00:00');"