postgreSQL troubleshooting 故障分析

1: 产品介绍

来自于

https://github.com/lesovsky/pgcenter

完全开源，两板斧就可以安装入库

帮助DBA提供分析问题的手段，然后定位问题解决问题

Postgres provides various activity statistics that include detailed information about its behaviour: 

connections, statements, database operations, replication, resources usage and more. 

General purpose of the statistics is to help DBAs to monitor and troubleshoot Postgres. 

However, these statistics provided in textual form retrieved from SQL functions and views, 

and Postgres doesn't provide any tools for working with them. 

pgCenter's main goal is to help Postgres DBA manage statistics that theу have in their databases 

and see all the necessary data in convenient format based on builtin stats views and functions.

2：特色

Top-like interface that allows you to monitor stats changes as you go.  

When troubleshooting Postgres it's always important to keep an eye not only on Postgres metrics, 

but also system metrics, 

since Postgres utilizes system resources, such as cpu, memory, storage and network when working. 

pgCenter allows you to see both kinds of statistics related to Postgres and your system.

3：统计了哪些内容

pgCenter supports majority of statistics views available in Postgres, 

and at the same time, uses additional SQL functions applied to statistics to show these in a more convenient way. 

The following stats are available:

current summary activity - a compilation/selection of metrics from different sources - postgres uptime, version, recovery status, number of clients grouped by their states, number of (auto)vacuums, statements per second, age of the longest transaction and the longest vacuum;

pg_stat_activity - information related to the current activity of connected clients and Postgres background processes.

pg_stat_database - database-wide statistics, such as number of commits/rollbacks, handled tuples, deadlocks, temporary files, etc.

pg_stat_replication - statistics on replication, connected standby hosts and their activity.

pg_stat_user_tables, pg_statio_user_tables - statistics on accesses (including IO) to tables.

pg_stat_user_indexes, pg_statio_user_indexes - statistics on accesses (including IO) to indexes.

pg_stat_user_functions - statistics on execution of functions.

pg_stat_statements - statistics on SQL statements executed including time and resources usage.

statistics on tables sizes based on pg_relation_size() and pg_total_relation_size() functions;

pg_stat_progress_vacuum - information about (auto)vacuums status.

System statistics

pgcenter top also provides system usage information based on statistics from procfs filesystem:

load average and CPU usage time (user, system, nice, idle, iowait, software and hardware interrupts, steal);

memory and swap usage, amount of cached and dirty memory, writeback activity;

storage devices statistics: iops, throughput, latencies, average queue and requests size, devices utilization;

network interfaces statistics: throughput in bytes and packets, different kind of errors, saturation and utilization.

In case of connecting to remote Postgres there is possibility to use additional SQL functions used for

 retrieving /proc statistics from remote host.

4：其他类似产品，没有用过，大家可以验证一下，欢迎分享经验

pg_top

https://github.com/markwkm/pg_top

99：多练习，多实践，就可以找到各种问题，尤其性能故障，总可以找到蛛丝马迹，只要你够仔细认真。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29497382/viewspace-2287035/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29497382/viewspace-2287035/