频道导航

• ▸ PHP
• ▸ Java
• ▸ Java SE
• ▸ Python
• ▸ C#
• ▸ C&C++
• ▸ Ruby
• ▸ VB
• ▸ asp.Net
• ▸ Go
• ▸ Perl
• ▸ netty
• ▸ Django
• ▸ Delphi
• ▸ Jsp
• ▸ .NET Core
• ▸ Spring
• ▸ Flask
• ▸ Springboot
• ▸ SpringMVC
• ▸ Lua
• ▸ Laravel
• ▸ Mybatis
• ▸ Asp
• ▸ Groovy
• ▸ ThinkPHP
• ▸ Yii
• ▸ swoole
• ▸ HTML
• ▸ HTML5
• ▸ JavaScript
• ▸ CSS
• ▸ jQuery
• ▸ Bootstrap
• ▸ Angularjs
• ▸ TypeScript
• ▸ Vue
• ▸ Dojo
• ▸ Json
• ▸ Electron
• ▸ Node.js
• ▸ extjs
• ▸ Express
• ▸ XML
• ▸ ES6
• ▸ Ajax
• ▸ Flash
• ▸ Unity
• ▸ React
• ▸ Flex
• ▸ Ant Design
• ▸ Web前端
• ▸ 微信小程序
• ▸ 微信公众号
• ▸ iOS
• ▸ Android
• ▸ Swift
• ▸ Hybrid
• ▸ Cocos2d-x
• ▸ Flutter
• ▸ Xcode
• ▸ Silverlight
• ▸ cocoa
• ▸ Cordova

• 前端之家
• Postgre SQL
• PostgreSQL的监控二(pgwatch & pg_statsinfo)

重试了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
------------------------------------------------------------------------------------------------------

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.

@H_404_181@ 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

#psql-U postgres-c"CREATE EXTENSION pg_stat_statements"