pg 监控工具,分析pg的性能问题
pg_stat_statements
http://www.postgres.cn/docs/10/pgstatstatements.html
PostgreSQL CPU占用100%性能分析及慢查询优化 pg_stat_statements,查看连接数pg_stat_activity视图,(Table Scan)
pg_top
https://github.com/markwkm/pg_top
pg_systat
https://github.com/pg-systat/pg_systat
pg_proctab
https://github.com/markwkm/pg_proctab
pgdash
https://github.com/rapidloop/pgdash
pgmetrics
https://github.com/rapidloop/pgmetrics
pgbadger
https://github.com/darold/pgbadger
pgcluu
https://github.com/darold/pgcluu
pg_buffercache
https://www.postgresql.org/docs/10/pgbuffercache.html
.
.
.
.
pg_timeout
PG长时间空闲连接可以自动回收并释放资源
pg_trgm
模糊查询优化
其他说明
statement_timeout
在 PostgreSQL 被用来控制语句执行时长,单位是ms。
$ vi postgresql.conf
#statement_timeout = 0 # in milliseconds, 0 is disabled
# /*
# 默认是0,表示语句可以一直执行下去。
#
# 如果设置为1440000,则表示语句最多可以执行 1440000ms = 1440s = 24min。
# 可以设置为0,禁用该参数。
# */
pg_stat_statements
: 监控收集各类sql执行情况
system_stats
:用于监控OS层信息
pg_stat_kcache
:监控单条SQL花费cpu等
pg_stat_monitor
:pg_stat_statements
和pg_stat_kcache
的结合体
pgpro_stats
:等待事件统计、采样配置、自动化监控
pgsentinel
、pgsampler
:类Oracle ASH
pg_top
:类top工具
pgcenter
:全能监控工具
pg_activity
:命令行top工具
查询pg全部设置信息
select
"name",
"setting",
"unit",
"category",
"short_desc",
"extra_desc",
"context",
"vartype",
"source",
"min_val",
"max_val",
"enumvals",
"boot_val",
"reset_val",
"sourcefile",
"sourceline",
"pending_restart"
from pg_settings ps
-- where ps.name like '%statement_timeout%'