PostgreSQL的监控二(pgwatch & pg_statsinfo)

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