Chinaunix首页 | 论坛 | 博客

风-对酒当歌 人生几何 譬如朝暮 去日苦多

首页　| 　博文目录　| 　关于我

zhouyf605

  • 博客访问： 352423
  • 博文数量： 86
  • 博客积分： 1460
  • 博客等级： 上尉
  • 技术积分： 848
  • 用 户 组： 普通用户
  • 注册时间： 2009-07-12 14:07
文章分类

全部博文（86）

文章存档

2017年（36）

2016年（17）

2015年（1）

2013年（12）

2012年（2）

2011年（1）

2010年（1）

2009年（16）

我的朋友
最近访客
推荐博文
相关博文
PostgreSQL的监控二(pgwatch & pg_statsinfo)

分类： Mysql/postgreSQL

2015-09-19 23:19:39

原文地址：PostgreSQL的监控二(pgwatch & pg_statsinfo) 作者：skykiker

2.3 pgwatch


主要特性：
- 配置简单
- 大量的监控图表
- 快速系统检查面板
- 自动收集统计信息
- 交互式的 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 )
           - 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次，还是一样的错误，也懒得再折腾了。不过下面这位兄弟配成功了。


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）安装

  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. ---------------------------------------------

  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

  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

  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]


  5. ;-------------------------------------
  6. ; database connection
  7. ;-------------------------------------


  10. host = localhost
  11. port = 5432
  12. dbname = postgres
  13. username = postgres
  14. password = postgres

4)通过浏览器访问



详细参考：
pg_stats_reporter.html

阅读(1747) | 评论(0) | 转发(0) |
0

上一篇：python脚本自动生成ORACLE AWR报告

下一篇：OGG-01028 Non-Standard Redo Detected in 10g Compatible Format

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6