希罗卡

postgre中的统计收集器

2019/08/22 数据库

postgre中的统计收集器

1、简介

​ PostgreSQL的统计收集器是一个支持收集和报告服务器活动信息的子系统。 目前，这个收集器可以对表和索引的访问计数，计数可以按磁盘块和个体行来进行。它还跟踪每个表中的总行数、每个表的清理和分析动作的信息。它也统计调用用户定义函数的次数以及在每次调用中花费的总时间。

​ 因为统计收集给查询执行增加了一些负荷，系统可以被配置为收集或不收集信息。这由配置参数控制，它们通常在postgresql.conf中设置。

2、统计视图

2.1、动态统计视图

视图名称 描述
pg_stat_activity 每个服务器进程一行，显示与那个进程的当前活动相关的信息，例如状态和当前查询。
pg_stat_replication 每一个 WAL 发送进程一行，显示有关到该发送进程 连接的后备服务器的复制的统计信息。
pg_stat_wal_receiver 只有一行，显示来自 WAL 接收器所连接服务器的有关该接收器的统计信息。
pg_stat_subscription 每个订阅至少一行，显示订阅工作者的相关信息。
pg_stat_ssl 每个连接（常规连接和复制连接）一行， 显示有关在此连接上使用的 SSL 的信息。
pg_stat_progress_vacuum 每个运行VACUUM的后端(包括 autovacuum 工作者进程)一行， 显示当前的进度。

2.2、已收集统计信息视图

视图名称 描述
pg_stat_archiver 只有一行，显示有关 WAL 归档进程活动的统计信息。
pg_stat_bgwriter 只有一行，显示有关后台写进程的活动的统计信息。
pg_stat_database 每个数据库一行，显示数据库范围的统计信息。
pg_stat_database_conflicts 每个数据库一行，显示数据库范围的统计信息， 这些信息的内容是关于由于与后备服务器的恢复过程 发生冲突而被取消的查询。
pg_stat_all_tables 当前数据库中每个表一行，显示有关访问指定表的统计信息。
pg_stat_sys_tables 和pg_stat_all_tables一样，但只显示系统表。
pg_stat_user_tables 和pg_stat_all_tables一样，但只显示用户表。
pg_stat_xact_all_tables 和pg_stat_all_tables相似，但计数动作只在当前事务内发生（还没有被包括在pg_stat_all_tables和相关视图中）。用于生存和死亡行数量的列以及清理和分析动作在此视图中不出现。
pg_stat_xact_sys_tables 和pg_stat_xact_all_tables一样，但只显示系统表。
pg_stat_xact_user_tables 和pg_stat_xact_all_tables一样，但只显示用户表。
pg_stat_all_indexes 当前数据库中的每个索引一行，显示：表OID、索引OID、模式名、表名、索引名、 使用了该索引的索引扫描总数、索引扫描返回的索引记录数、使用该索引的简 单索引扫描抓取的活表(livetable)中数据行数。 当前数据库中的每个索引一行，显示与访问指定索引有关的统计信息。
pg_stat_sys_indexes 和pg_stat_all_indexes一样，但只显示系统表上的索引。
pg_stat_user_indexes 和pg_stat_all_indexes一样，但只显示用户表上的索引。
pg_statio_all_tables 当前数据库中每个表一行(包括TOAST表)，显示：表OID、模式名、表名、 从该表中读取的磁盘块总数、缓冲区命中次数、该表上所有索引的磁盘块读取总数、 该表上所有索引的缓冲区命中总数、在该表的辅助TOAST表(如果存在)上的磁盘块读取总数、 在该表的辅助TOAST表(如果存在)上的缓冲区命中总数、TOAST表的索引的磁盘块读 取总数、TOAST表的索引的缓冲区命中总数。 当前数据库中的每个表一行，显示有关在指定表上 I/O 的统计信息。
pg_statio_sys_tables 和pg_statio_all_tables一样，但只显示系统表。
pg_statio_user_tables 和pg_statio_all_tables一样，但只显示用户表。
pg_statio_all_indexes 当前数据库中每个索引一行，显示：表OID、索引OID、模式名、 表名、索引名、该索引的磁盘块读取总数、该索引的缓冲区命中总数。 当前数据库中的每个索引一行，显示与指定索引上的 I/O 有关的统计信息。
pg_statio_sys_indexes 和pg_statio_all_indexes一样，但只显示系统表上的索引。
pg_statio_user_indexes 和pg_statio_all_indexes一样，但只显示用户表上的索引。
pg_statio_all_sequences 当前数据库中每个序列对象一行，显示：序列OID、模式名、序列名、序列的磁盘读取总数、序列的缓冲区命中总数。 当前数据库中的每个序列一行，显示与指定序列上的 I/O 有关的统计信息。
pg_statio_sys_sequences 和pg_statio_all_sequences一样，但只显示系统序列（目前没有定义系统序列，因此这个视图总是为空）。
pg_statio_user_sequences 和pg_statio_all_sequences一样，但只显示用户序列。
pg_stat_user_functions 对于所有跟踪功能，函数的OID，模式，名称，数量 通话总时间，和自我的时间。自我时间是 在函数本身所花费的时间量，总时间包括 它调用函数所花费的时间。时间值以毫秒为单位。 每一个被跟踪的函数一行，显示与执行该函数有关的统计信息。
pg_stat_xact_user_functions 和pg_stat_user_functions相似，但是只统计在当前事务期间的调用（还没有被包括在pg_stat_user_functions中）。

3、具体视图字段详解

3.1、pg_stat_activity 视图

pg_stat_activity视图将为每一个服务器进程有一行，显示与该进程的当前活动相关的信息。

列 类型 描述
datid oid 这个后端连接到的数据库的OID
datname name 这个后端连接到的数据库的名称
pid integer 这个后端的进程 ID
usesysid oid 登录到这个后端的用户的 OID
usename name 登录到这个后端的用户的名称
application_name text 连接到这个后端的应用的名称
client_addr inet 连接到这个后端的客户端的 IP 地址。如果这个域为空，它表示客户端通过服务器机器上的一个 Unix 套接字连接或者这是一个内部进程（如自动清理）。
client_hostname text 已连接的客户端的主机名，由client_addr的反向 DNS 查找报告。这个域将只对 IP 连接非空，并且只有log_hostname被启用时才会非空。
client_port integer 客户端用以和这个后端通信的 TCP 端口号，如果使用 Unix 套接字则为-1
backend_start timestamp with time zone 这个进程被启动的时间，对于客户端后端，这是客户端连接到服务器的时间。
xact_start timestamp with time zone 这个进程的当前事务被启动的时间，如果没有活动事务则为空。如果当前查询是它的第一个事务，这一列等于query_start。
query_start timestamp with time zone 当前活动查询被开始的时间，如果state不是active，这个域为上一个查询被开始的时间
state_change timestamp with time zone state上一次被改变的时间
wait_event_type text 后端正在等待的事件类型，如果不存在则为 NULL。可能的值有：LWLock：后端正在等待一个轻量级锁。 每一个这样的锁保护共享内存中的一个特定数据结构。 wait_event将包含一个名称，标识轻量级锁的用途。 （一些锁具有特定的名称；另一些锁则是一组锁的一部分，每个都有类似的用途。）Lock：后端正在等待一个重量级锁。重量级锁，也称为锁管理器锁或者简单锁，主要保护 SQL 可见的对象，例如表。不过，它们也被用于确保特定内部操作的互斥，例如关系扩展。wait_event将标识等待的锁的类型。BufferPin：服务器进程正在等待访问一个数据缓冲区，而此时没有其他进程正在检查该缓冲区。如果另一个进程持有一个最终从要访问的缓冲区中读取数据的打开的游标，缓冲区 pin 等待可能会被拖延。Activity:服务器进程空闲。这由系统进程在其主要处理循环中等待活动使用。 wait_event将识别特定的等待点。Extension: 服务器进程正在等待扩展模块中的活动。此类别对于跟踪自定义等待点的模块很有用。Client:服务器进程正在等待来自用户应用程序的套接字上的某些活动， 并且服务器期望发生与其内部进程无关的事情。 wait_event将识别特定的等待点。IPC: 服务器进程正在等待来自服务器中另一个进程的某些活动。 wait_event将识别特定的等待点。Timeout: 服务器进程正在等待超时到期。wait_event将识别特定的等待点。IO: 服务器进程正在等待IO完成。wait_event将识别特定的等待点。
wait_event text 如果后端当前正在等待，则是等待事件的名称，否则为 NULL。详见表 28.4。
state text 这个后端的当前总体状态。可能的值是：active：后端正在执行一个查询。idle：后端正在等待一个新的客户端命令。idle in transaction：后端在一个事务中，但是当前没有正在执行一个查询。idle in transaction (aborted)：这个状态与idle in transaction相似，不过在该事务中的一个语句导致了一个错误。fastpath function call：后端正在执行一个 fast-path 函数。disabled：如果在这个后端中track_activities被禁用，则报告这个状态。
backend_xid xid 这个后端的顶层事务标识符（如果存在）。
backend_xmin xid 当前后端的xmin范围。
query text 这个后端最近查询的文本。如果state为active，这个域显示当前正在执行的查询。在所有其他状态下，它显示上一个被执行的查询。 默认情况下，查询文本被截断为1024个字符；可以通过参数 track_activity_query_size更改此值。
backend_type text 当前后端的类型。可能的类型有 autovacuum launcher、autovacuum worker、 background worker、background writer、 client backend、checkpointer、 startup、walreceiver、 walsender和walwriter。

3.2、pg_stat_replication 视图

pg_stat_replication视图中将为每一个 WAL 发送进程包含一行，用来显示与该发送进程连接的后备服务器的复制统计信息。 这个视图中只会列出直接连接的后备机，下游后备服务器的信息不包含在此。

列 类型 描述
pid integer 一个 WAL 发送进程的进程 ID
usesysid oid 登录到这个 WAL 发送进程的用户的 OID
usename name 登录到这个 WAL 发送进程的用户的名称
application_name text 连接到这个 WAL 发送进程的应用的名称
client_addr inet 连接到这个 WAL 发送进程的客户端的 IP 地址。 如果这个域为空，它表示该客户端通过服务器机器上的一个 Unix 套接字连接。
client_hostname text 连接上的客户端的主机名，由一次对client_addr 的逆向 DNS 查找报告。这个域将只对 IP 连接非空，并且只有在 log_hostname被启用时非空
client_port integer 客户端用来与这个 WAL 发送进程通讯的 TCP 端口号， 如果使用 Unix 套接字则为-1
backend_start timestamp with time zone 这个进程开始的时间，即客户端是何时连接到这个 WAL 发送进程的
backend_xmin xid 由hot_standby_feedback报告 的这个后备机的xmin水平线。
state text 当前的 WAL 发送进程状态 可能的值是：startup: 这个 WAL 发送进程正在启动。catchup: 这个 WAL 发送进程连接的备用服务器正在追赶主服务器。streaming: 这个 WAL 发送进程在其连接的备用服务器追赶上主服务器之后传输更改。backup: 这个 WAL 发送进程正在发送一个备份。stopping: 这个 WAL 发送进程正在停止。
sent_lsn pg_lsn 在这个连接上发送的最后一个预写日志的位置
write_lsn pg_lsn 被这个后备服务器写入到磁盘的最后一个预写日志的位置
flush_lsn pg_lsn 被这个后备服务器刷入到磁盘的最后一个预写日志的位置
replay_lsn pg_lsn 被重放到这个后备服务器上的数据库中的最后一个预写日志的位置
write_lag interval 从本地刷新最近的WAL到接收到该备用服务器已经写入它（但尚未刷新或应用它） 的通知之间所经过的时间。如果将此服务器配置为同步备用服务器， 则可以用它来测量在提交时发生的synchronous_commit 级别的remote_write的延迟。
flush_lag interval 从本地刷新最近的WAL到接收到该备用服务器写入并刷新它 （但尚未应用它）的通知之间的时间间隔。如果将此服务器配置为同步备用服务器， 则可以用它来测量在提交时发生的synchronous_commit 级别的remote_flush的延迟。
replay_lag interval 从本地刷新最近的WAL到接收到该备用服务器写入、 刷新并应用它的通知之间的时间间隔。如果将此服务器配置为同步备用服务器， 则可以用它来测量在提交时发生的synchronous_commit 级别的remote_apply的延迟。
sync_priority integer 此备用服务器的优先级被选为基于优先级的同步复制中的同步备用服务器。 这在基于定量的同步复制中不起作用。
sync_state text 这个后备服务器的同步状态 可能的值是：async: 该备用服务器是异步的。potential: 此备用服务器现在是异步的，但如果当前同步服务器中的一个出现故障，可能会变为同步服务器。sync: 该备用服务器是同步的。quorum: 此备用服务器被视为仲裁备用服务器的候选人。

3.3、pg_stat_wal_receiver 视图

pg_stat_wal_receiver事务只包含一行，它显示了从 WAL 接收器所连接的服务器得到的有关该接收器的统计信息。

列 类型 描述
pid integer WAL 接收器进程的进程 ID
status text WAL 接收器进程的活动状态
receive_start_lsn pg_lsn WAL 接收器启动时使用的第一个预写日志位置
receive_start_tli integer WAL 接收器启动时使用的第一个时间线编号
received_lsn pg_lsn 已经接收到并且已经被杀入磁盘的最后一个预写日志的位置，这个域的初始值是 WAL 接收器启动时使用的第一个日志位置
received_tli integer 已经接收到并且已经被杀入磁盘的最后一个预写日志的时间线编号，这个域的初始值是 WAL 接收器启动时使用的第一个日志所在的时间线编号
last_msg_send_time timestamp with time zone 从源头 WAL 发送器接收到的最后一个消息的发送时间
last_msg_receipt_time timestamp with time zone 从源头 WAL 发送器接收到的最后一个消息的接收时间
latest_end_lsn pg_lsn 报告给源头 WAL 发送器的最后一个预写日志位置
latest_end_time timestamp with time zone 报告给源头 WAL 发送器最后一个预写日志位置的时间
slot_name text 这个 WAL 接收器使用的复制槽的名称
conninfo text 这个 WAL 接收器使用的连接串，安全相关的域会被隐去。

3.4、pg_stat_subscription

​ pg_stat_subscription 视图将包含主工作进程的每个订阅一行（如果工作进程未运行，则为null PID）， 以及处理订阅表的初始数据副本的工作进程的附加行。

字段 类型 描述
subid oid 订阅的 OID
subname text 订阅的名称
pid integer 订阅工作进程的进程ID
relid Oid 工作进程正在同步的关系的OID；主应用工作进程为null
received_lsn pg_lsn 最后一次接收到预写日志位置，此字段的初始值为0
last_msg_send_time timestamp with time zone 从原始WAL发件程序收到的最后一条消息的发送时间
last_msg_receipt_time timestamp with time zone 从原始WAL发件程序收到的最后一条消息的接收时间
latest_end_lsn pg_lsn 报告给原始WAL发送程序的最后预写日志位置
latest_end_time timestamp with time zone 给原始WAL发送程序报告最后预写日志位置的时间

3.5、pg_stat_ssl

​ pg_stat_ssl视图将为每一个后端或者 WAL 发送进程 包含一行，用来显示这个连接上的 SSL 使用情况。可以把它与 pg_stat_activity或者 pg_stat_replication通过 pid列连接来得到更多有关该连接的细节。

列 类型 描述
pid integer 一个后端或者 WAL 发送进程的进程 ID
ssl boolean 如果在这个连接上使用了 SSL 则为真
version text 在用的 SSL 版本，如果这个连接上没有使用 SSL 则为 NULL
cipher text 在用的 SSL 密码的名称，如果这个连接上没有使用 SSL 则为 NULL
bits integer 使用的加密算法中的位数，如果这个连接上没有使用 SSL 则为 NULL
compression boolean 如果使用了 SSL 压缩则为真，否则为假， 如果这个连接上没有使用 SSL 则为 NULL
clientdn text 来自所使用的客户端证书的识别名（DN）域， 如果没有提供客户端证书或者这个连接上没有使用 SSL 则为 NULL。如果 DN 域长度超过 NAMEDATALEN（标准编译 中是 64 个字符），则它会被截断。

3.6、pg_stat_archiver

The pg_stat_archiver视图将总是一个单一的行， 该行包含着有关集簇的归档进程的数据。

列 类型 描述
archived_count bigint 已被成功归档的 WAL 文件数量
last_archived_wal text 最后一个被成功归档的 WAL 文件名称
last_archived_time timestamp with time zone 最后一次成功归档操作的时间
failed_count bigint 失败的归档 WAL 文件尝试的数量
last_failed_wal text 最后一次失败的归档操作的 WAL 文件名称
last_failed_time timestamp with time zone 最后一次失败的归档操作的时间
stats_reset timestamp with time zone 这些统计信息最后一次被重置的时间

3.7、 pg_stat_database视图

​ pg_stat_database视图将为集簇中的每一个数据库包含有一行，每一行显示数据库范围的统计信息。

列 类型 描述
datid oid 一个数据库的 OID
datname name 这个数据库的名称
numbackends integer 当前连接到这个数据库的后端数量。这是在这个视图中唯一一个返回反映当前状态值的列。所有其他列返回从上次重置以来积累的值。
xact_commit bigint 在这个数据库中已经被提交的事务的数量
xact_rollback bigint 在这个数据库中已经被回滚的事务的数量
blks_read bigint 在这个数据库中被读取的磁盘块的数量
blks_hit bigint 磁盘块被发现已经在缓冲区中的次数，这样不需要一次读取（这只包括 PostgreSQL 缓冲区中的命中，而不包括在操作系统文件系统缓冲区中的命中）
tup_returned bigint 在这个数据库中被查询返回的行数
tup_fetched bigint 在这个数据库中被查询取出的行数
tup_inserted bigint 在这个数据库中被查询插入的行数
tup_updated bigint 在这个数据库中被查询更新的行数
tup_deleted bigint 在这个数据库中被查询删除的行数
conflicts bigint 由于与恢复冲突而在这个数据库中被取消的查询的数目（冲突只发生在后备服务器上，
temp_files bigint 在这个数据库中被查询创建的临时文件的数量。所有临时文件都被统计，不管为什么创建这些临时文件（如排序或哈希），并且不管log_temp_files设置。
temp_bytes bigint 在这个数据库中被查询写到临时文件中的数据总量。所有临时文件都被统计，不管为什么创建这些临时文件（如排序或哈希），并且不管log_temp_files设置。
deadlocks bigint 在这个数据库中被检测到的死锁数
blk_read_time double precision 在这个数据库中后端花费在读取数据文件块的时间，以毫秒计
blk_write_time double precision 在这个数据库中后端花费在写数据文件块的时间，以毫秒计
stats_reset timestamp with time zone 这些统计信息上次被重置的时间

3.8、 pg_stat_database_conflicts

pg_stat_database_conflicts视图为每一个 数据库包含一行，用来显示数据库范围内由于与后备服务器上的恢复过程 冲突而被取消的查询的统计信息。 这个视图将只包含后备服务器上的信息， 因为冲突会不发生在主服务器上。

列 类型 描述
datid oid 一个数据库的 OID
datname name 这个数据库的名称
confl_tablespace bigint 这个数据库中由于表空间被删掉而取消的查询数量
confl_lock bigint 这个数据库中由于锁超时而取消的查询数量
confl_snapshot bigint 这个数据库中由于旧快照而取消的查询数量
confl_bufferpin bigint 这个数据库中由于被占用的缓冲区而取消的查询数量
confl_deadlock bigint 这个数据库中由于死锁而取消的查询数量

3.9、pg_stat_all_tables

pg_stat_all_tables视图将为当前数据库中的每一个表（包括 TOAST 表）包含一行，该行显示与对该表的访问相关的统计信息，每一行就是一个表的相关信息。

列 类型 描述
relid oid 一个表的 OID
schemaname name 这个表所在的模式的名称
relname name 这个表的名称
seq_scan bigint 在这个表上发起的顺序扫描的次数
seq_tup_read bigint 被顺序扫描取得的活着的行的数量
idx_scan bigint 在这个表上发起的索引扫描的次数
idx_tup_fetch bigint 被索引扫描取得的活着的行的数量
n_tup_ins bigint 被插入的行数
n_tup_upd bigint 被更新的行数（包括 HOT 更新的行）
n_tup_del bigint 被删除的行数
n_tup_hot_upd bigint 被更新的 HOT 行数（即不要求独立索引更新的行更新）
n_live_tup bigint 活着的行的估计数量
n_dead_tup bigint 死亡行的估计数量
n_mod_since_analyze bigint 从这个表最后一次被分析后备修改的行的估计数量
last_vacuum timestamp with time zone 上次这个表被手动清理的时间（不统计VACUUM FULL）
last_autovacuum timestamp with time zone 上次这个表被自动清理守护进程清理的时间
last_analyze timestamp with time zone 上次这个表被手动分析的时间
last_autoanalyze timestamp with time zone 上次这个表被自动清理守护进程分析的时间
vacuum_count bigint 这个表已被手工清理的次数（不统计VACUUM FULL）
autovacuum_count bigint 这个表已被自动清理守护进程清理的次数
analyze_count bigint 这个表已被手工分析的次数
autoanalyze_count bigint 这个表已被自动清理守护进程分析的次数

3.10、pg_stat_all_indexes

pg_stat_all_indexes视图将为当前数据库中的每个索引包含一行，该行显示关于对该索引访问的统计信息

列 类型 描述
relid oid 这个索引的基表的 OID
indexrelid oid 这个索引的 OID
schemaname name 这个索引所在的模式的名称
relname name 这个索引的基表的名称
indexrelname name 这个索引的名称
idx_scan bigint 在这个索引上发起的索引扫描次数
idx_tup_read bigint 在这个索引上由扫描返回的索引项数量
idx_tup_fetch bigint 被使用这个索引的简单索引扫描取得的活着的表行数量

4、常用数据库指标统计

  1. 库占用内存

    select pg_size_pretty(pg_database_size(current_database())

  2. 整个数据库中schema数量

    select count(*) from information_schema.schemata

  3. 连接数

    select count(*) from pg_stat_activity

  4. 当前存在所有库的死锁数量

    select sum(deadlocks) from pg_stat_database

  5. 查询超过几秒的查询数量

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

  6. 查询事务超过几秒的个数

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

  7. 全表扫描次数

    select sum(tup_returned) from pg_stat_database

  8. 活跃的会话数

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

Search

    欢迎添加我的个人微信号

    个人微信哦

    Table of Contents