PostgreSQL的监控二(pgwatch & pg_statsinfo)-skykiker-ChinaUnix博客

2.3 pgwatch

主要特性：
- 配置简单
- 大量的监控图表
- 快速系统检查面板
- 自动收集统计信息
- 交互式的 Flash 图表
- 集成 SQL worksheet

pgwatch的监控画面

pgwatch是个php做的web管理控制台，后台存储使用PostgreSQL数据库，其工作的原理是利用cron定期经由dblink采集远程数据库性能数据，并以图形的形式显示。使用方法也很简单，只要把pgwatch配到Apache上，第一次访问的时候进入setup.php,引导设置pgwatch使用的后台数据库。然后再配置定期调用采集数据脚本的daemon，再在配置页面里填上监控目标机器的连接信息就可以了。

Requirements:
-------------

You will need a handful of components to make this work:

- Apache (webserver)
- PHP 5 (scripting language)
- pgsql extension for PHP (see )
- PostgreSQL 9 (to store the data we collect)
- dblink (contribution module for PostgreSQL 9)

- For now we only support PostgreSQL >= 9.0 databases.
Older systems cannot be monitored, however, we will
add support for future database releases.

How to set it up:
-----------------

- Extract your package to your Apache directory

- Adjust ownership of your application if www-data is improper

- Open in browser the setup.php. It will lead you through the setup process.

- You can choose setup.sh in linux and setup.bat in windows as alternatives.

- Start the data collection daemon by adding the proper row to cron, something like:
0 * * * * cd /var/www/pgwatch/util && /usr/bin/php5 getraw_d.php
(But setup.sh will also remind you of it)

- Log into the website and click "configure" to add new database servers
/ databases to make sure that your daemon will pick up the statistics.

然而实际配置时发现，收集数据的地方出错。

[root@zabbix util]# /usr/bin/php getraw_d.php
256: pgwatch_xml: given parameter is not a valid xml. in /var/www/html/pgwatch/classes/pgwatch_xml.php (18)<br/>

重试了2次，还是一样的错误，也懒得再折腾了。不过下面这位兄弟配成功了。

pgwatch的优点是可以监控多个数据库，并且不需要在被监控数据库上作任何设置。但pgwatch的界面有点丑，监控项目不多，更新也慢（pgwatch最新一次更新是2011年）。

2.4 pg_statsinfo

pg_statsinfo的功能比较全面。pg_statsinfo在被监控DB上以代理的形式存在，pg_statsinfo定期采集snaoshot信息并存入仓库。仓库可以在相同的数据库也可以在远程，在远程的话可以使用同一个仓库存放多个被监控数据库的信息。pg_statsinfo不仅采集数据库中系统表，还采集OS(CPU,MEM,IO)信息.甚至还可以从PostgreSQL日志中抽取性能数据。pg_statsinfo还可以报警，当某个监控项超出预定义的阈值时，pg_statsinfo会在日志中产生ALERT消息，配合其它可以监视日志的监控软件(比如zabbix)就可以实现告警。

pg_statsinfo.html
------------------------------------------------------------------------------------------------------

pic1: Example of system installed with pg_statsinfo

pic2: Image of Functionary in pg_statsinfo

Statistics Snapshot

pg_statsinfo gathers statistics periodically and stores them as snapshots into a repository database. The repository can be in the same database with the monitored instance or in another instance. Also, one repository can store snapshots from multiple monitored instances.

Snapshots are gathered periodically (default: every 10 minutes) or by manual.
An event handler function is called after every snapshots taken. The function can be defined by users. A new snapshot can be compared with previous snapshots and raise alert messages in the function.

Snapshot holds the following statistics information:

All of the information collected by the statistics collector. For example, numbers of INSERT/UPDATE/DELETE and buffer access counters.
Disk usages of each tablespace, pg_xlog, and archive log directory.
Long transactions and their query strings.
Process status in running, waiting for locks, idles in transaction, and idle.
Amount of WAL output size.
Number of CHECKPOINT ,VACUUM execution time and access of buffers.
SQLs and functions that take long time.
PostgreSQL configuration parameters.
OS resource information(Usage of CPU, Memory, Disk I/O, Load Average)
Lock information
Number of canceled queries which conflicts recovery
State fo replication

Size of a snapshot depends on the numbers of objects in DB. There are about 600 - 800kB per snapshot. In case of pg_statsinfo default settings, snapshots for each monitored DB requires 90 - 120MB per day.

Note that pg_statsinfo doesn't delete old snapshots. Please delete them manually.

Server Log Filter

Split server log lines according to the message levels. You can set different message threshold for csvlog, textlog, and syslog.
Fix filename of the textlog. The default is $PGDATA/pg_log/pg_statsinfo.log. You can always read the latest log in the same filename and setup of log monitoring tools would be easier.
Set arbitrary access permission for each textlog files. You can control the default permission not only 600.
Can change log level which was outputted Text log or syslog freely. For example, it can change log message ERROR level to INFO level. It is useful in missed operations.
We can set not to output log message which is specified user which is like database admin.

Alert Function

If database statistics is over threshold which was set by user, pg_statsinfo detect and write alert log in postgresql-log(message level is 'ALERT').

Alert function can set following alert parameter:

rollback / seconds
commit / seconds
garbage data size (MB)
garbage data size ratio(%)
garbage data size ratio in each tables (%)
average response time of queries (sec)
longest response time of queries (sec)
correlations of each tables (%)
maximum number of backends
empty disk space in table space (%)
load average
usage of disk swap (KB)
amount of replication delay (MB)

(*1) Correlation of table is judged by only clustered table which is in cluster index.

------------------------------------------------------------------------------------------------------
pg_statsinfo展现数据方式有两种。
简易的文本报告：
files/report_sample.txt

或者pg_stats_reporter产生的HTML报告：
files/report_sample.html

使用例
1）安装

[root@zabbix ~]# rpm -ihv http://pgfoundry.org/frs/download.php/3545/pg_statsinfo-2.5.0-1.pg93.rhel6.x86_64.rpm
Retrieving http://pgfoundry.org/frs/download.php/3545/pg_statsinfo-2.5.0-1.pg93.rhel6.x86_64.rpm
Preparing... ########################################### [100%]
1:pg_statsinfo ########################################### [100%]

2）配置
这里只做最简单的配置
修改postgresql.conf

shared_preload_libraries = 'pg_statsinfo' # (change requires restart)
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,

安装pg_stat_statements

[root@zabbix ~]# psql -U postgres -c "CREATE EXTENSION pg_stat_statements"

3）采集snapshot
默认是10分钟采集一次，也可以通过postgresql.conf中的pg_statsinfo.snapshot_interval参数修改

pg_statsinfo.snapshot_interval = 30min

还可以手动采集

[root@zabbix ~]#psql -U postgres -c "SELECT statsinfo.snapshot('comment')"

4）生成报告

[root@zabbix ~]#pg_statsinfo -r All -h localhost -d postgres -p 5432 -U postgres
---------------------------------------------
STATSINFO Report (host: zabbix, port: 5432)
---------------------------------------------
----------------------------------------
/* Summary */
----------------------------------------
Database System ID : 6057121524883617775
Host : zabbix
Port : 5432
PostgreSQL Version : 9.3.4
Snapshot Begin : 2014-09-25 07:20:00
Snapshot End : 2014-09-25 07:21:43
Snapshot Duration : 00:01:43
Total Database Size : 168 MiB
Total Commits : 283
Total Rollbacks : 2
----------------------------------------
/* Database Statistics */
----------------------------------------
Database Name : postgres
Database Size : 8 MiB
Database Size Increase : 1 MiB
Commit/s : 0.788
Rollback/s : 0.019
Cache Hit Ratio : 99.100 %
Block Read/s (disk+cache) : 803.700
Block Read/s (disk) : 7.045
Rows Read/s : 824.670
Temporary Files : 0
Temporary Bytes : 0 MiB
Deadlocks : 0
Block Read Time : 0.000 ms
Block Write Time : 0.000 ms
Database Name : zabbix
Database Size : 150 MiB
Database Size Increase : 0 MiB
Commit/s : 1.868
...(太长了，以下略)

生成HTML报告的例子
1）安装启动appche，php等相关组件
略

2）安装pg_stats_reporter

[root@zabbix ~]# rpm -ihv http://pgfoundry.org/frs/download.php/3542/pg_stats_reporter-2.0.0-1.el6.noarch.rpm
Retrieving http://pgfoundry.org/frs/download.php/3542/pg_stats_reporter-2.0.0-1.el6.noarch.rpm
Preparing... ########################################### [100%]
1:pg_stats_reporter ########################################### [100%]

会自动把pg_stats_reporter安装到/var/www/html/pg_stats_reporter位置

3)修改到仓库的连接配置

[root@zabbix ~]# vi /etc/pg_stats_reporter.ini
[sample]
;-------------------------------------
; database connection
;-------------------------------------
host = localhost
port = 5432
dbname = postgres
username = postgres
password = postgres

4)通过浏览器访问

详细参考：
pg_stats_reporter.html