PostgreSQL的监控二(pgwatch & pg_statsinfo)

2.3 pgwatch

http://www.cybertec.at/postgresql_produkte/pgwatch-cybertec-enterprise-postgresql-monitor/
主要特性：
- 配置简单
- 大量的监控图表
- 快速系统检查面板
- 自动收集统计信息
- 交互式的 Flash 图表
- 集成 SQL worksheet

pgwatch的监控画面


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

1. Requirements:
   -------------
   
   You will need a handful of components to make this work:
   
          - Apache (webserver)
          - PHP 5  (scripting language)
          - pgsql extension for PHP (see http://www.php.net/manual/en/book.pgsql.php)
          - 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.
   

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

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

重试了2次，还是一样的错误，也懒得再折腾了。不过下面这位兄弟配成功了。
http://www.linuxidc.com/Linux/2012-11/74515.htm

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

2.4 pg_statsinfo

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

http://pgstatsinfo.projects.pgfoundry.org/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展现数据方式有两种。
简易的文本报告：
http://pgstatsinfo.projects.pgfoundry.org/files/report_sample.txt

或者pg_stats_reporter产生的HTML报告：
http://pgstatsinfo.projects.pgfoundry.org/files/report_sample.html

使用例
1）安装

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

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

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

安装pg_stat_statements

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

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

1. pg_statsinfo.snapshot_interval = 30min

还可以手动采集

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

4）生成报告

1. [root@zabbix ~]#pg_statsinfo -r All -h localhost -d postgres -p 5432 -U postgres
   
2. ---------------------------------------------
   
3. STATSINFO Report (host: zabbix, port: 5432)
   
4. ---------------------------------------------
   
5. 
   
6. ----------------------------------------
   
7. /* Summary */
   
8. ----------------------------------------
   
9. Database System ID : 6057121524883617775
   
10. Host : zabbix
    
11. Port : 5432
    
12. PostgreSQL Version : 9.3.4
    
13. Snapshot Begin : 2014-09-25 07:20:00
    
14. Snapshot End : 2014-09-25 07:21:43
    
15. Snapshot Duration : 00:01:43
    
16. Total Database Size : 168 MiB
    
17. Total Commits : 283
    
18. Total Rollbacks : 2
    
19. 
    
20. ----------------------------------------
    
21. /* Database Statistics */
    
22. ----------------------------------------
    
23. Database Name : postgres
    
24. Database Size : 8 MiB
    
25. Database Size Increase : 1 MiB
    
26. Commit/s : 0.788
    
27. Rollback/s : 0.019
    
28. Cache Hit Ratio : 99.100 %
    
29. Block Read/s (disk+cache) : 803.700
    
30. Block Read/s (disk) : 7.045
    
31. Rows Read/s : 824.670
    
32. Temporary Files : 0
    
33. Temporary Bytes : 0 MiB
    
34. Deadlocks : 0
    
35. Block Read Time : 0.000 ms
    
36. Block Write Time : 0.000 ms
    
37. 
    
38. Database Name : zabbix
    
39. Database Size : 150 MiB
    
40. Database Size Increase : 0 MiB
    
41. Commit/s : 1.868
    
42. ...(太长了，以下略)

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


2）安装pg_stats_reporter

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

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

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

1. [root@zabbix ~]# vi /etc/pg_stats_reporter.ini
   
2. [sample]
   
3. 
   
4. 
   
5. ;-------------------------------------
   
6. ; database connection
   
7. ;-------------------------------------
   
8. 
   
9. 
   
10. host = localhost
    
11. port = 5432
    
12. dbname = postgres
    
13. username = postgres
    
14. password = postgres

4)通过浏览器访问
http://127.0.0.1/pg_stats_reporter/pg_stats_reporter.php


详细参考：

http://pgstatsinfo.projects.pgfoundry.org/pg_stats_reporter.html

转载：http://blog.chinaunix.net/uid-20726500-id-4505027.html