PG监控项

原创杨凯同学 2023-10-05

273

服务器配置

服务器配置的文件

在数据库集群中，有3个配置文件，分别是：

postgresql.conf

，

pg_hba.conf

和

pg_ident.conf

。其中postgresql.conf为服务器主要的配置文件，pg_hba.conf是客户端认证配置文件，pg_ident.conf用来配置哪些操作系统用户可以映射为数据库用户。

连接与认证

参考文章：

资源消耗

服务器的运行会消耗一定的资源，通过设置服务器的参数，可以提示服务器的性能。包括内存、自由空间映射、内核资源使用、基于开销的清理延迟和后端写进程。

自由空间映射用于跟踪数据库中未使用空间的位置。不在映射表里面的自由空间是不能重复使用的，通过合理地设置，可以提高磁盘的利用率。

在VACUUM和ANALYZE命令执行过程中，系统维护一个内部的记数器，跟踪所执行的各种I/O操作的开销。

在PostgreSQL中，有一个独立的服务器进程，叫做后端写进程，它唯一的功能就是发出写“脏”共享缓冲区的命令。这么做的目的是让持有用户查询的服务器进程应该很少或者几乎不等待写动作的发生，因为后端写进程会做这件事情。这样的安排同样也减少了检查点造成的性能下降。

预写式日志

预写式日志的设置主要包括对预写式日志的基本设置、检查点设置和归档设置等。

查询规划

在PostgreSQL中，查询优化器选择查询规划时，有时候并不是最优的方法。数据库管理员可以通过设置配置参数，强制优化器选择一个更好的查询规划。

错误报告和日志

数据库管理员也许想知道错误报告和日志记录在什么地方，什么时间开始记录的和记录了什么等等。

运行时统计

在PostgreSQL中，如果启用了统计搜集，那么生成的数据可以通过pg_stat和pg_statio系统视图查看服务器的统计信息。

自动清理

数据库管理员可以通过设置自动清理的缺省行为，从而提高工作效率。

客户端连接缺省

数据库管理员可以设置客户端连接时的语句行为、区域和格式化等。

锁管理

在数据库系统运行的过程中，会产生各种各样的锁。管理员可以通过设置锁管理的相关参数，从而提高服务器的高可用性。

版本和平台兼容性

PostgreSQL有很多版本，管理员可以设置各个版本之间兼容性。另外，PostgreSQL可以在不同的平台上安装，管理员可以设置各个平台之间兼容性更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

监控数据库的活动

配置统计收集器

PostgreSQL的统计收集器是一个支持收集和汇报服务器活跃性信息的子系统。目前，这个收集器可以给出对表和索引的访问计数，包括磁盘块的数量和独立行的项。PostgreSQL 还可以判断当前其它服务器进程正在执行的命令是什么。这个特性独立于统计收集器子系统，可以单独地被启用或禁用。

因为统计收集给查询处理增加了一些开销，所以可以启用或禁用统计收集。这是由配置参数控制的，通常在postgresql.conf 里设置。

查看收集到的统计信息

PostgreSQL提供了预定义的视图用于显示统计收集的结果。

在使用统计观察当前系统活跃性的时候，必须意识到这些信息并不是实时更新的。每个独立的服务器进程只是在准备进入空闲状态的时候才向收集器传送新的块和行访问计数；因此正在处理的查询或者事务并不影响显示出来的总数。

另外一个需要着重指出的是，在请求服务器进程显示任何这些统计信息的时候，它首先抓取收集器进程发出的最新报告，然后就拿这些数据作为所有统计视图和函数的快照，直到它当前的事务结束。因此统计信息在当前事务的持续期间内不会改变。

另外，可以使用底层的统计函数制作自定义的视图。这些底层统计访问函数和标准视图里使用的是一样的。

监控磁盘的使用

监控磁盘的使用量

使用磁盘空间函数
使用VACUUM信息

VACUUM 命令回收已删除行占据的存储空间。在 PostgreSQL 的操作中，那些已经被删除或者更新过的行，并没有从它们所属的表中物理删除，这些数据在完成 VACUUM 之前它们仍然存在。因此有必要周期地运行 VACUUM，特别是在经常更新的表上。VACUUM 命令可以选择分析一个特定的数据表，如果没有指定数据表，VACUUM处理当前数据库里每个表。具体语法格式如下。

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]

磁盘满导致的失效

一个数据库管理员最重要的磁盘监控任务就是确保磁盘不会写满。磁盘写满可能不会导致数据的丢失，但它肯定会导致系统进一步使用的问题。如果WAL文件也在同一个磁盘上(缺省配置就是这样)，则会发生数据库服务器恐慌，并且停止运行。如果不能通过删除其它东西来释放磁盘空间，那么可以通过使用表空间把一些数据库文件移动到其它文件系统上去。

PostgreSQL 里的表空间允许数据库管理员在文件系统里定义那些代表数据库对象的文件存放位置。一旦创建了表空间，那么就可以在创建数据库对象的时候引用它。

通过使用表空间，管理员可以控制一个 PostgreSQL 安装的磁盘布局。更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

下面介绍一下常用的监控指标。最关键的一些数据库健康指标，趋势监测。

总连接数

主要看趋势，直接与业务量挂钩，如果连接数接近

max_connection

水位，需要注意。同时连接数应与数据库主机可用内存挂钩，每个连接保守估计10MB内存开销（这里还未计算SYSCACHE,RELCACHE）。

select count(*) from pg_stat_activity ;

演示，打印每秒的总连接数。

psql

select count(*) from pg_stat_activity ; watch 1

N秒内新建的连接数

主要看趋势，直接与业务量挂钩，如果突发大量连接，可能是新增了业务服务器，或者是性能抖动过导致业务大量新建连接满足并发的请求。突然连接数下降，可能原因是业务服务器突然释放连接，或者业务服务器挂了。

select count(*) from pg_stat_activity where now()-backend_start > '? second';

演示，打印每秒的5秒内新建连接数。

psql

select count(*) from pg_stat_activity where now()-backend_start > '5 second'; watch 1

SQL活跃统计

1、需要加载pg_stat_statements，如果需要跟踪IO时间，需要开启

track_io_timing

。

同时需要注意，由于

pg_stat_statements

跟踪的SQL有限，最近未访问过的SQL的跟踪信息可能被抛弃。所以统计并不是非常的精准。

postgres=# d pg_stat_statements

QPS

QPS指标来自pg_stat_statements，由于这个插件有一个STATEMENT采集上限，可配置，例如最多采集1000条SQL，如果有新的SQL被采集到时，并且1000已用完，则会踢掉最老的SQL。所以我们这里统计的QPS并不是完全精确，不过还好PG内部会自动合并SQL，把一些条件替换成变量，这样即使不使用绑定变量，也能追踪到很多SQL。

对于业务SQL非常繁多并且大多数都是活跃SQL的场景，可以适当调大

pg_stat_statements

的track数，提高精准度。

除此之外，可以改进pg_stat_statements的功能，直接统计精准的QPS。主要看趋势，直接与业务量挂钩。

with

a as (select sum(calls) s, sum(case when ltrim(query,' ') ~* '^select' then calls else 0 end) q from pg_stat_statements), b as (select sum(calls) s, sum(case when ltrim(query,' ') ~* '^select' then calls else 0 end) q from pg_stat_statements , pg_sleep(1)) select b.s-a.s, -- QPS b.q-a.q, -- 读QPS b.s-b.q-a.s+a.q -- 写QPS from a,b;

如果只想看QPS，使用

with

a as (select sum(calls) s from pg_stat_statements), b as (select sum(calls) s from pg_stat_statements , pg_sleep(1)) select b.s-a.s -- QPS from a,b;

演示，打印每秒的QPS。

psql

with a as (select sum(calls) s from pg_stat_statements), b as (select sum(calls) s from pg_stat_statements , pg_sleep(1)) select b.s-a.s -- QPS from a,b; watch 0.000001

每秒处理了多少行

每秒处理了多少行，包括写入，读取，更新，删除等操作。

两次快照相减除以时间间隔

sum(pg_stat_statements.rows)

共享缓冲区：每秒缓存命中、未命中读

shared_blks_hit | bigint | | |

shared_blks_read | bigint | | |

共享缓冲区：每秒产生多少脏页

shared_blks_dirtied | bigint | | |

共享缓冲区：每秒异步write多少脏页

shared_blks_written | bigint | | |

进程本地缓冲区：每秒缓存命中、未命中读

local_blks_hit | bigint | | |

local_blks_read | bigint | | |

进程本地缓冲区：每秒产生多少脏页

local_blks_dirtied | bigint | | |

进程本地缓冲区：每秒异步write多少脏页

local_blks_written | bigint | | |

临时文件每秒读

temp_blks_read | bigint | | |

临时文件每秒写

temp_blks_written | bigint | | |

两次快照之间的读数据块耗时

blk_read_time | double precision | | |

两次快照之间的写数据块耗时

blk_write_time | double precision | | |

active session

主要看趋势，直接与业务量挂钩，如果活跃会话数长时间超过CPU核数时，说明数据库响应变慢了，需要深刻关注。

select count(*) from pg_stat_activity where state='active';

演示，打印每秒的活跃会话数。

psql

select count(*) from pg_stat_activity where state='active'; watch 1

更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

平均RT

活跃会话/qps = RT(秒)

long query

当前系统中执行时间超过N秒的SQL有多少条，LONG QUERY与活跃会话的比例说明当前LONG SQL的占比。占比越高，说明该系统可能偏向OLAP，占比越低，说明该系统偏向OLTP业务。

select count(*) from pg_stat_activity where state='active' and now()-query_start > interval '? second';

演示，打印每秒系统中执行时间超过5秒的SQL有多少条。

psql

select count(*) from pg_stat_activity where state='active' and now()-query_start > interval '5 second'; watch 1

long transaction

当前系统中N秒未结束的事务有多少条

select count(*) from pg_stat_activity where now()-xact_start > interval '? second';

演示，打印每秒系统中5秒未结束的事务有多少条

psql

select count(*) from pg_stat_activity where now()-xact_start > interval '5 second'; watch 1

idle in transaction

当前系统中在事务中并且处于空闲状态的会话有多少，很多，说明业务端的处理可能比较慢，如果结合锁等待发现有大量锁等待，并且活跃会话数有突增，可能需要关注并排查业务逻辑的问题。

select count(*) from pg_stat_activity where state='idle in transaction';

演示，打印每秒系统中在事务中并且处于空闲状态的会话有多少

psql

select count(*) from pg_stat_activity where state='idle in transaction'; watch 1

long idle in transaction

当前系统中，有多少长期（超过N秒）处于空闲的事务。如果有较多这样的事务，说明业务端的处理时间超过N秒的情况非常普遍，应该尽快排查业务。

比如前端开启了游标，等待用户的翻页动作，用户可能开小差了。又比如业务上使用了一些交互模式，等用户的一些输入等。

这种情况应该尽量避免，否则长时间占用连接资源。

select count(*) from pg_stat_activity where state='idle in transaction' and now()-state_change > interval '? second';

演示，打印每秒系统中在事务中并且处于空闲状态(超过5秒)的会话有多少

psql

select count(*) from pg_stat_activity where state='idle in transaction' and now()-state_change > interval '5 second'; watch 1

waiting

当前系统中，处于等待中的会话有多少。如果很多，说明出现了大量的锁等待，使用末尾文章进行排查。

select count(*) from pg_stat_activity where wait_event_type is not null;

演示，打印每秒系统中处于等待中的会话有多少。

psql

select count(*) from pg_stat_activity where wait_event_type is not null; watch 1

long waiting

当前系统中，等待超过N秒的会话有多少。

select count(*) from pg_stat_activity where wait_event_type is not null and now()-state_change > interval '? second';

演示，打印每秒系统中等待超过5秒的会话有多少。

psql

select count(*) from pg_stat_activity where wait_event_type is not null and now()-state_change > interval '5 second'; watch 1

2pc

当前系统中，2PC的事务有多少。如果接近max_prepared_transactions，需要注意。建议调大max_prepared_transactions，或者排查业务是否未及时提交。

select count(*) from pg_prepared_xacts;

演示，打印每秒系统中未结束的2PC事务数。

psql

select count(*) from pg_prepared_xacts; watch 1

long 2pc

当前系统中，超过N秒未结束的2PC的事务有多少。如果很多，需要排查业务为什么未及时提交。

select count(*) from pg_prepared_xacts where now() - prepared > interval '? second';

演示，打印每秒系统中5秒仍未结束的2PC事务数。

psql

select count(*) from pg_prepared_xacts where now() - prepared > interval '5 second'; watch 1

膨胀点监测 - 多久以前的垃圾可以被回收

时间间隔越大，说明越容易导致膨胀。排查这几个方向，长事务，长SQL，2PC，持有SNAPSHOT的QUERY。必要时把不合理的老的会话干掉。

with a as

(select min(xact_start) m from pg_stat_activity where backend_xid is not null or backend_xmin is not null), b as (select min(prepared) m from pg_prepared_xacts) select now()-least(a.m,b.m) from a,b;

演示，打印每秒系统中多久以前的垃圾可以被回收

psql

with a as (select min(xact_start) m from pg_stat_activity where backend_xid is not null or backend_xmin is not null), b as (select min(prepared) m from pg_prepared_xacts) select now()-least(a.m,b.m) from a,b; watch 1

空间

看当前占用情况，打快照，看时间维度空间变化情况。按库划分：

postgres=# l+

按表空间划分

postgres=# db+

更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

数据空间

数据占用的空间。

日志空间

WAL日志占用的空间。

备库发送延迟

select application_name,client_addr,client_hostname,client_port,state,sync_priority,sync_state,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) from pg_stat_replication;

备库APPLY延迟

select application_name,client_addr,client_hostname,client_port,state,sync_priority,sync_state,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lag)) from pg_stat_replication;

SLOT 延迟

select slot_name, plugin, slot_type, temporary, active, active_pid, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) from pg_replication_slots;

归档延迟

最后一次归档失败时间减去最后一次归档成功的时间，求时间差。

select last_failed_time - last_archived_time from pg_stat_archiver;

数据库活动信息

以下都可以针对单个数据库输出，也可以输出整个实例的统计。

postgres=# d pg_stat_database

每秒事务提交数

多次查询计算

select sum(xact_commit) from pg_stat_database; -- pg_stat_get_db_xact_commit 为stable函数，一个事务中两次调用之间只执行一次，所以需要外部多次执行。

每秒事务回滚数

select sum(xact_rollback) from pg_stat_database;

每秒全表扫描记录数

select sum(tup_returned) from pg_stat_database;

每秒索引扫描回表记录数

select sum(tup_fetched) from pg_stat_database;

每秒插入记录数

select sum(tup_inserted) from pg_stat_database;

每秒更新记录数

select sum(tup_updated) from pg_stat_database;

每秒删除记录数

select sum(tup_deleted) from pg_stat_database;

备库查询冲突数

select sum(conflicts) from pg_stat_database;

死锁数

select sum(deadlocks) from pg_stat_database;

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

PG监控项

服务器配置

监控数据库的活动

监控磁盘的使用

总连接数

N秒内新建的连接数

SQL活跃统计

QPS

active session

平均RT

long query

long transaction

idle in transaction

long idle in transaction

waiting

long waiting

2pc

long 2pc

膨胀点监测 - 多久以前的垃圾可以被回收

空间

数据空间

日志空间

备库发送延迟

备库APPLY延迟

SLOT 延迟

归档延迟

数据库活动信息

评论