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，再在配置页面里填上监控目标机器的连接信息就可以了。
- 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.
然而实际配置时发现，收集数据的地方出错。
[root@zabbix util]#/usr/bin/php getraw_d.php
256:pgwatch_xml:given parameter isnota 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 bythe 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）安装
- [root@zabbix ~#rpm-ihv http://pgfoundryorg/frs/download.php/3545/pg_statsinfo-2.5.0-1.pg93.rhel6.x86_64.rpm
- Retrieving http.rpm
- Preparing.###########################################[100%]
- 1:pg_statsinfo ###########################################]
2）配置
这里只做最简单的配置
修改postgresql.conf
shared_preload_libraries='pg_statsinfo'#(change requires restart)
log_filename'postgresql-%Y-%m-%d_%H%M%S.log'# log filenamepattern,
安装pg_stat_statements
#psql-U postgres-c"CREATE EXTENSION pg_stat_statements"
3）采集snapshot
默认是10分钟采集一次，也可以通过postgresql.conf中的pg_statsinfo.snapshot_interval参数修改
pg_statsinfo.snapshot_interval=30min
还可以手动采集
"SELECT statsinfo.snapshot('comment')"
4）生成报告
#pg_statsinfo-r All-h localhost-d postgres-p 5432-U postgres
--
STATSINFO Report(host:zabbix: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:21:43
Snapshot Duration:00:01:43
Total DatabaseSize:168 MiB
Total Commits:283
Total Rollbacks:2
*Database Statistics-
DatabaseName:postgres
Database:8 MiB
DatabaseSizeIncrease:1 MiB
Commit/s:0.788
Rollback/s.019
Cache Hit Ratio:99.100 %
Block Read/s(disk+cache:803.700
Block Read/s:7.045
Rows Read/s:824.670
Temporary Files:0
Temporary Bytes:0 MiB
Deadlocks:0
Block Read Time.000 ms
Block Write Time.000 ms
Database:zabbix
Database:150 MiB
Database:0 MiB
Commit/s:1.868
(太长了，以下略)
生成HTML报告的例子
1）安装启动appche，php等相关组件
略
2）安装pg_stats_reporter
.php/3542/pg_stats_reporter.el6.noarch.rpm
Retrieving http.rpm
Preparing]
1:pg_stats_reporter ###########################################]
会自动把pg_stats_reporter安装到/var/www/html/
pg_stats_reporter
位置
3)修改到仓库的连接配置
#vi /etc/pg_stats_reporter.ini
[sample]
;-
;database connection
-
host=localhost
port=5432
dbname=postgres
username=postgres
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 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
