Using pg_statsinfo monitor 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

name5480307906522906617hostnamedb-172-16-3-33.sky-mobi.com.hzport1921pg_version9.0beta2snapshot begin2010-06-08 18:04:52snapshot end2010-06-09 13:30:00snapshot duration19:25:09total database size5073 kBtotal commits18698total rollbacks2

2.Database Statistics

IDdatabaseMB+MBcommit/srollback/shit%gets/sreads/srows/s1postgres400.2670.00099.90017.7720.01695.0992test26260.0470.00099.80023.2190.04382.867

 

 

 

 

Disk Usage

Disk Usage per Tablespace

IDtablespacelocationdeviceused (MB)avail (MB)remain%1<pg_xlog>/database/pgdata/tbs2/pg_xlog104:3318713759499.8642pg_default/database/pgdata/tbs1/pg_root104:177413770799.9463pg_global/database/pgdata/tbs1/pg_root104:177413770799.9464tbs_test/database/pgdata/tbs4/tbs_test104:658613769599.937

Long Transactions

IDpidclient addresswhen to startduration (sec)query

Notable Tables

Heavily Updated Tables

IDdatabaseschematableINSERTUPDATEDELETEtotalHOT%1testtesttbl_test62007500620075 2testpg_toastpg_toast_2619120618 3testpg_catalogpg_attribute7007 4testpg_catalogpg_shdepend4004 5postgrespg_catalogpg_shdepend4004 6testpg_catalogpg_depend3003 7testpg_catalogpg_statistic120350.0008testpg_catalogpg_type2002 9testpg_catalogpg_namespace1001 10postgrespg_catalogpg_tablespace1001 11postgrespg_catalogpg_database1001 12testpg_catalogpg_authid1001 13testpg_catalogpg_database1001 14postgrespg_catalogpg_authid1001 15testpg_catalogpg_tablespace1001 16testpg_catalogpg_class1001 17postgrespg_catalogpg_shdescription0000 18postgrespg_catalogpg_foreign_data_wrapper0000 19postgrespg_catalogpg_proc0000 20postgrespg_catalogpg_user_mapping0000

Heavily Accessed Tables

IDdatabaseschematableseq_scanseq_tup_readtup_per_seqhit%

Low Density Tables

IDdatabaseschematablerowsdead rowspagesrows per page1testtesttbl_test62007502744225.975

Fragmented Tables

IDdatabaseschematablecolumncorrelation

Checkpoint Activity

total checkpoints66checkpoints by time64checkpoints by xlog0avg written buffers42.500max written buffers2336.000avg duration (sec)3.721max duration (sec)149.940

Autovacuum Activity

IDdatabaseschematablecountavg index scansavg removed rowsavg remain rowsavg duration (sec)max duration (sec)

Query Activity

Functions

IDfuncidnamenamefuncnamecallstotal time (ms)self time (ms)time/call (ms)

Statements

IDuserdatabasequerycallstotal time (sec)time/call (sec)10postgrespostgresSELECT statsinfo.sample()140340.3160.00016postgrespostgresSELECT * FROM statsinfo.tablespaces2830.0820.00019postgrespostgresSELECT * FROM statsinfo.activity()2830.0190.0001postgrespostgresSELECT 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 12542.6880.011

Setting Parameters

IDnamesettingsource1TimeZonePRCcommand line2checkpoint_segments32configuration file3default_statistics_target1000configuration file4default_text_search_configpg_catalog.englishconfiguration file5effective_cache_size1024000configuration file6lc_messagesCconfiguration file7listen_addresses*configuration file8log_autovacuum_min_duration60000configuration file9log_checkpointsonconfiguration file10log_destinationcsvlogoverride11log_directory/var/applog/pg_logconfiguration file12log_lock_waitsonconfiguration file13log_statementddlconfiguration file14log_timezonePRCcommand line15log_truncate_on_rotationonconfiguration file16logging_collectoronoverride17max_connections1500configuration file18max_stack_depth8192configuration file19pg_statsinfo.excluded_dbnamestemplate0,template1configuration file20pg_statsinfo.repository_serverhostaddr=172.16.3.39 port=1921 database=repo user=statsrepo → dbname=repo host=172.16.3.39 port=1921 user=statsrepoconfiguration file21random_page_cost2configuration file22server_encodingUTF8override23shared_buffers192000configuration file24shared_preload_librariespg_statsinfo,pg_stat_statementsconfiguration file25stats_temp_directory/database/pgdata/tbs3/pg_stat_tmpconfiguration file26superuser_reserved_connections13configuration file27timezone_abbreviationsDefaultcommand line28track_functionsplconfiguration file29wal_buffers256configuration file30wal_sync_methodopen_syncconfiguration file

Schema Information

Tables

IDdatabaseschematablecolumnsrow widthMB+MBtable scansindex scans1testtesttbl_test14212100

Indexes

IDdatabaseschemaindextableMB+MBscansrows/scanreadshitskeys

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

安装需求:

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');"

官方链接http://pgfoundry.org/projects/pgstatsinfo/