Monitoring Database Activity:监控数据库活动

数据库管理员经常想知道:“系统现在在做什么?”本章讨论如何找到答案。

有几种工具可用于监视数据库活动和分析性能。本章的大部分内容都在描述PostgreSQL的累积统计系统，但也不应忽视常规的Unix监控程序，如ps、top、iostat和vmstat。此外，一旦确定了一个性能较差的查询，可能需要使用PostgreSQL的EXPLAIN命令进行进一步的调查。第14.1节讨论了EXPLAIN和理解单个查询行为的其他方法。

1. Standard Unix Tools 标准Unix工具

在大多数Unix平台上，PostgreSQL根据ps报告修改其命令标题，以便能够很容易地识别各个服务器进程。示例显示如下

$ ps auxww | grep ^postgres
postgres 15551 0.0 0.1 57536 7132 pts/0 S 18:02 0:00 postgres -i
postgres 15554 0.0 0.0 57536 1184 ? Ss 18:02 0:00 postgres: background writer
postgres 15555 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: checkpointer
postgres 15556 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: walwriter
postgres 15557 0.0 0.0 58504 2244 ? Ss 18:02 0:00 postgres: autovacuum launcher
postgres 15582 0.0 0.0 58772 3080 ? Ss 18:04 0:00 postgres: joe runbug 127.0.0.1 idle
postgres 15606 0.0 0.0 58772 3052 ? Ss 18:07 0:00 postgres: tgl regression [local] SELECT waiting
postgres 15610 0.0 0.0 58772 3056 ? Ss 18:07 0:00 postgres: tgl regression [local] idle in transaction

(ps的适当调用在不同的平台上是不同的，正如所示内容的细节一样。这个例子来自最近的一个Linux系统。)这里列出的第一个进程是主服务器进程。为它显示的命令参数与启动它时使用的相同。接下来的四个进程是由主进程自动启动的后台工作进程。(如果你设置系统不运行自动真空，“自动真空启动器”进程将不会出现。)其余的每个进程都是处理一个客户机连接的服务器进程。每个这样的进程在表单中设置其命令行显示。

postgres: user database host activity

在客户端连接的生命周期内，用户、数据库和(客户端)主机项保持不变，但活动指示器会发生变化。活动可以是空闲的(例如，等待客户端命令)，在事务中空闲的(在BEGIN块中等待客户端)，或者是一个命令类型名称，如SELECT。此外，如果服务器进程当前正在等待另一个会话持有的锁，则会追加等待。在上面的例子中，我们可以推断，进程15606正在等待进程15610完成它的事务，从而释放一些锁。进程15610必须是阻塞器，因为没有其他活动会话。在更复杂的情况下，有必要查看pg_locks系统视图，以确定谁在阻塞谁。)

如果已经配置了cluster_name，集群名称也会显示在ps的输出中:

如果你关闭了update_process_title，那么活动指示器就不会更新;当启动新流程时，流程标题只设置一次。在某些平台上，这可以节省大量的每个命令开销;对另一些人来说，它是微不足道的。

Solaris需要特殊处理。您必须使用/usr/ucb/ps，而不是/bin/ps你还必须使用两个w标志，而不是一个。此外，您对postgres命令的原始调用必须具有比每个服务器进程提供的更短的ps状态显示。如果这三件事都做不到，那么每个服务器进程的ps输出将是原始的postgres命令行。

2. The Cumulative Statistics System 累积统计系统

PostgreSQL的累积统计系统支持收集和报告关于服务器活动的信息。目前，对磁盘块和单行术语中的表和索引的访问都进行了统计。还会计算每个表中的行总数，以及关于每个表的抽真空和分析操作的信息。如果启用，对用户定义函数的调用以及在每个函数中花费的总时间也会被计算在内。

PostgreSQL还支持报告关于当前系统中正在发生什么的动态信息，例如其他服务器进程正在执行的确切命令，以及系统中存在哪些其他连接。这个工具独立于累积统计系统。

2.1. Statistics Collection Configuration 统计配置

由于统计信息的收集增加了查询执行的一些开销，因此可以配置系统收集或不收集信息。这是由通常在postgresql.conf中设置的配置参数控制的。(配置参数设置请参见第20章。)

参数track_activities允许监视任何服务器进程正在执行的当前命令。

参数track_counts控制是否收集关于表和索引访问的累积统计信息。

参数track_functions支持跟踪用户定义函数的使用情况。

参数track_io_timing用于监视块的读和写时间。

参数track_wal_io_timing可以监视WAL写时间。

通常在postgresql.conf中设置这些参数，以便它们适用于所有服务器进程，但也可以使用set命令在单独的会话中打开或关闭它们。(为了防止普通用户向管理员隐藏他们的活动，只有超级用户被允许使用SET更改这些参数。)

累积统计信息在共享内存中收集。每个PostgreSQL进程在本地收集统计信息，然后在适当的时间间隔更新共享数据。当服务器(包括物理副本)完全关闭时，统计数据的永久副本存储在pg_stat子目录中，以便在服务器重新启动时保留统计数据。相反，当从不干净的关机开始时(例如，在立即关机、服务器崩溃、从基本备份和时间点恢复开始)，所有统计计数器都将重置。

2.2. Viewing Statistics 查看统计数据

表28.1中列出的几个预定义视图可用来显示系统的当前状态。还有表28.2中列出的其他几个视图可用于显示累积的统计信息。或者，也可以使用基础的累积统计函数构建自定义视图，如第2.24节所述。

在使用累积统计视图和函数监视收集的数据时，务必认识到信息不会立即更新。每个单独的服务器进程在空闲之前将累积的统计信息刷新到共享内存中，但每PGSTAT_MIN_INTERVAL毫秒(1秒，除非在构建服务器时更改)的频率不会超过一次;因此，仍然在进行的查询或事务不会影响显示的总数，显示的信息滞后于实际活动。但是，由track_activities收集的当前查询信息总是最新的。

另一个要点是，当请求服务器进程显示任何累积的统计信息时，所访问的值将被缓存，直到默认配置中的当前事务结束。因此，只要您继续当前事务，统计信息就会显示静态信息。类似地，当事务中第一次请求任何此类信息时，将收集所有会话的当前查询信息，并且在整个事务中显示相同的信息。这是一个特性，而不是一个bug，因为它允许您对统计数据执行多个查询，并将结果关联起来，而不必担心下面的数字会发生变化。当以交互方式分析统计信息，或使用昂贵的查询时，访问单个统计信息之间的时间增量可能导致缓存统计信息的严重倾斜。为了最小化倾斜，可以将stats_fetch_consistency设置为snapshot，代价是缓存不需要的统计数据会增加内存使用量。相反，如果已知统计信息只被访问一次，那么缓存已访问的统计信息是不必要的，可以通过将stats_fetch_consistency设置为none来避免。您可以调用pg_stat_clear_snapshot()来丢弃当前事务的统计快照或缓存值(如果有的话)。下一次对统计信息的使用(在快照模式下)将导致构建一个新的快照或(在缓存模式下)缓存已访问的统计信息

事务还可以在中查看自己的统计信息(尚未刷新到共享内存统计信息)

视图pg_stat_xact_all_tables、pg_stat_xact_sys_tables、pg_stat_xac d_t_user_tables和pg_stat_xact_user_functions。这些数字的作用并不如所述
以上;相反，它们在整个事务中不断更新。

表28.1所示的动态统计视图中的一些信息受到安全限制。普通用户只能看到关于他们自己的会话(属于他们所属角色的会话)的所有信息。在关于其他会话的行中，许多列将为空。但是请注意，会话的存在及其一般属性(如会话用户和数据库)对所有用户都是可见的。超级用户和具有内置角色pg_read_all_stats(参见第22.5节)特权的角色可以查看所有会话的所有信息。

1. Dynamic Statistics Views 动态统计视图

2. Collected Statistics Views 收集的统计视图

每个索引的统计信息对于确定正在使用哪些索引以及它们的有效性特别有用。

pg_statio_view主要用于确定缓冲区缓存的有效性。当实际磁盘读取的数量远远小于缓冲区命中的数量时，则缓存可以满足大多数读请求，而无需调用内核调用。然而，这些统计数据并不能说明全部情况:由于PostgreSQL处理磁盘I/O的方式，不在PostgreSQL缓冲区缓存中的数据可能仍然驻留在内核的I/O缓存中，因此可能不需要物理读取就可以获取数据。有兴趣获得更多关于PostgreSQL I/O行为的详细信息的用户，建议将PostgreSQL统计视图与操作系统实用工具结合使用，以便深入了解内核对I/O的处理。

2.3. pg_stat_activity

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

wait_event和state列是独立的。如果一个后端处于活动状态，那么它可能正在等待某个事件，也可能没有。如果状态为活动且wait_event为非空，则意味着正在执行查询，但在系统的某个位置被阻塞。

扩展可以将LWLock类型添加到表28.12所示的列表中。在某些情况下，扩展分配的名称在所有服务器进程中都不可用;因此，LWLock等待事件可能只是报告为“extension”，而不是扩展分配的名称。

下面是一个如何查看等待事件的例子:

SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;

2.4. pg_stat_replication

pg_stat_replication视图将包含每个WAL发送方进程一行，显示有关复制到该发送方连接的备用服务器的统计信息。只列出了直接连接的备用;没有关于下行备用服务器的可用信息。

pg_stat_replication视图中报告的滞后时间是最近的WAL被写入、刷新和重放以及发送方了解它所花费的时间的度量值。如果将远程服务器配置为同步备用服务器，这些时间表示每个同步提交级别所引入的(或将引入的)提交延迟。对于异步备用，replay_lag列近似于查询可见最近事务之前的延迟。如果备用服务器完全赶上了发送服务器，并且没有更多的WAL活动，那么最近测量的滞后时间将继续显示一段时间，然后显示NULL。

延迟时间可以自动用于物理复制。逻辑解码插件可以选择性地发出跟踪消息;如果没有，跟踪机制将简单地显示NULL滞后。

报告的延迟时间并不是预测备用服务器需要多长时间才能赶上发送服务器，假设当前重放速度。这样的系统在生成新的WAL时将显示类似的时间，但在发送方变为空闲时将显示不同的时间。特别是，当备用进程完全完成时，pg_stat_replication将显示写入、刷新和重放最近报告的WAL位置所花费的时间，而不是像一些用户可能期望的那样为零。这与测量最近写事务的同步提交和事务可见性延迟的目标一致。为了减少用户对不同延迟模型的困惑，在完全重放的空闲系统上，延迟列在短时间后恢复为NULL。监视系统应该选择是将其表示为缺失数据、零还是继续显示最后的已知值。

2.5. pg_stat_replication_slots

pg_stat_replication_slots视图将包含每个逻辑复制槽位一行，显示关于其使用情况的统计信息。

2.6. pg_stat_wal_receiver

pg_stat_wal_receiver视图将只包含一行，显示来自该接收器所连接的服务器的有关WAL接收器的统计信息。

2.7. pg_stat_recovery_prefetch

pg_stat_recovery_prefetch视图将只包含一行。wal_dis dantame、block_distance和io_depth列显示当前值，其他列显示可以用pg_stat_reset_shared函数重置的cu @ mulative计数器。

2.8. pg_stat_subscription

2.9. pg_stat_subscription_stats

pg_stat_subscription_stats视图将为每个订阅包含一行。

2.10. pg_stat_ssl

pg_stat_ssl视图将为每个后端或WAL发送方进程包含一行，显示关于此连接上SSL使用情况的信息。可以将它与pid列上的pg_stat_activity或pg_s tatt_replication连接，以获得有关连接的更多详细信息。

2.11. pg_stat_gssapi

pg_stat_gssapi视图将为每个后端包含一行，显示关于此连接上GSSAPI使用情况的信息。可以将它与pid列上的pg_stat_activity或pg_stat_replication连接，以获得有关连接的更多详细信息。

2.12. pg_stat_archiver

pg_stat_archiver视图总是只有一行，包含关于集群的存档进程的数据。

通常，WAL文件是按从最老到最新的顺序归档的，但这是不能保证的，而且在特殊情况下，如促进待机或崩溃恢复后不保存。因此，假设所有比last_archived_wal更老的文件也已成功归档是不安全的。

2.13. pg_stat_bgwriter

pg_stat_bgwriter视图始终只有一行，其中包含集群的全局数据

2.14. pg_stat_wal

pg_stat_wal视图总是只有一行，包含关于集群WAL活动的数据。

2.15. pg_stat_database

pg_stat_database视图将包含集群中每个数据库的一行，以及共享对象的一行，显示数据库范围的统计信息。

2.16. pg_stat_database_conflicts

pg_stat_database_conflicts视图将为每个数据库包含一行，显示关于因备用服务器上的恢复冲突而发生的查询取消的base级统计数据。这个视图将只包含备用服务器上的信息，因为主服务器上不会发生冲突。

2.17. pg_stat_all_tables

pg_stat_all_tables视图将为当前数据库(包括TOAST表)中的每个表包含一行，显示对该特定表的访问统计信息。视图pg_s 17:tat_user_tables和视图pg_stat_sys_tables包含相同的信息，但是分别仅显示ser表和system表。

2.18. pg_stat_all_indexes

pg_stat_all_indexes视图将为当前数据库中的每个索引包含一行，显示对该特定索引的访问的统计信息。pg_stat_user_indexes和pg_s d_tat_sys_indexes视图包含相同的信息，但分别过滤为只显示用户和系统索引。

索引可以由简单的索引扫描、“位图”索引扫描和优化器使用。在位图扫描中，几个索引的输出可以通过AND或or规则组合，因此在使用位图扫描时，很难将单个堆行获取与特定索引关联起来。因此，位图扫描会增加pg_stat_all_indexes。它使用的索引的Idx_tup_read计数(s)，并增加pg_stat_all_tables。表的Idx_tup_fetch计数，但它不影响pg_stat_all_indexes.idx_tup_fetch。优化器还访问索引以检查提供的常量，这些常量的值在优化器统计信息的记录范围之外，因为优化器统计信息可能已经过时。

即使不使用位图扫描，idx_tup_read和idx_tup_fetch计数也可能不同，因为idx_tup_read统计从索引检索的索引项，而idx_tup_fetch统计从表中获取的活动行。如果使用索引获取任何死行或尚未提交的行，或者通过仅索引扫描避免任何堆获取，则后者将减少。

2.19. pg_statio_all_tables

pg_statio_all_tables视图将为当前数据库(包括TOAST表)中的每个表包含一行，显示关于该特定表上I/O的统计信息。视图pg_sta _2tio_user_tables和视图pg_statio_sys_tables包含相同的信息，但是分别显示用户表和系统表。

2.20. pg_statio_all_indexes

pg_statio_all_indexes视图将为当前数据库中的每个索引包含一行，显示关于该特定索引上的I/O的统计信息。pg_statio_user_indexes和pg_statio_sys_indexes视图包含相同的信息，但分别过滤为只显示用户和系统索引

2.21. pg_statio_all_sequences

pg_statio_all_sequences视图将为当前数据库中的每个序列包含一行，显示关于该特定序列上的I/O的统计信息。

2.22. pg_stat_user_functions

pg_stat_user_functions视图将为每个跟踪的函数包含一行，显示关于该函数执行的统计信息。track_functions参数控制跟踪哪些函数。

2.23. pg_stat_slru

PostgreSQL通过SLRU(最近最少使用的简单缓存)访问某些磁盘上的信息。pg_stat_slru视图将为每个跟踪的SLRU缓存包含一行，显示对缓存页面的访问统计信息。

2.23. Statistics Functions 统计功能

查看统计信息的其他方法可以通过编写使用上面所示的标准视图所使用的相同底层统计信息访问函数的查询来设置。有关函数名称等详细信息，请参阅标准视图的定义。(例如，在psql中，你可以发出\d+pg_stat_activity。)每个数据库统计信息的访问函数采用数据库OID作为参数，以标识要报告的数据库。每个表和每个索引函数接受一个表或索引OID。用于每个函数统计的函数有一个函数OID。注意，只有当前数据库中的表、索引和函数可以通过这些函数看到。

与累积统计系统相关的附加功能如表28.34所示。

使用pg_stat_reset()还会重置autovacuum用来确定何时触发真空或分析的计数器。重置这些计数器可能导致自动真空不执行必要的工作，从而可能导致表膨胀或表统计信息过时等问题。建议在重置统计信息之后进行数据库范围的ANALYZE。

Pg_stat_get_activity是pg_stat_activity视图的基础函数，它返回一组记录，其中包含关于每个后端进程的所有可用信息。有时，只获取这些信息的一个子集可能更方便。在这种情况下，可以使用一组较老的后端统计数据访问函数;表28.35所示。这些访问功能使用后端ID号，其范围从1到当前活动的后端数量。函数pg_stat_get_backend_idset提供了一种方便的方法，为每个活动后端生成一行，以调用这些函数。例如，查询所有后端的pid和当前查询。

SELECT pg_stat_get_backend_pid(s.backendid) AS pid, pg_stat_get_backend_activity(s.backendid) AS query  FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

3. Viewing Locks

监视数据库活动的另一个有用工具是pg_locks系统表。它允许数据库管理员在锁管理器中查看关于未执行锁的信息。例如，这种能力可以用于:

• 查看当前所有未执行的锁、特定数据库中关系上的所有锁、特定关系上的所有锁或特定PostgreSQL会话持有的所有锁。

• 确定当前数据库中与最多未授予锁的关系(这可能是数据库客户机之间的争用源)。

• 确定锁争用对总体数据库性能的影响，以及争用随总体数据库流量的变化程度。

关于pg_locks视图的详细信息见54.12节。关于使用PostgreSQL进行锁和管理并发老化的更多信息，请参阅第13章。

4. Progress Reporting 进度报告

PostgreSQL能够在命令执行过程中报告某些命令的进度。目前，支持进度报告的命令只有ANALYZE、CLUSTER、CREATE INDEX、VACUUM、COPY和BASE_BACKUP(即pg_basebackup发出的用于进行基本备份的复制命令)。这在未来可能会扩大。

4.1. ANALYZE Progress Reporting

当ANALYZE运行时，pg_stat_progress_analyze视图将包含当前运行该命令的每个后端的一行。下表描述了将要报告的信息，并提供了关于如何解释这些信息的信息。

注意，当在分区表上运行ANALYZE时，它的所有分区也会被递归分析。在这种情况下，首先为父表报告ANALYZE进度，然后收集它的继承统计信息，然后为每个分区报告。

4.2. CREATE INDEX Progress Reporting

当CREATE INDEX或REINDEX运行时，pg_stat_progress_create_index视图将包含当前正在创建索引的每个后端的一行。下面的表格记录了将被报告的信息，并提供了如何解释这些信息的信息。

4.3. VACUUM Progress Reporting

每当VACUUM正在运行时，pg_stat_progress_vacuum视图将为当前正在抽真空的每个后端(包括autovacuum工作进程)包含一行。这些表描述了将要报告的信息，并提供了如何解释这些信息的信息。VACUUM FULL命令的进度通过pg_stat_progress_cluster报告，因为VACUUM FULL和CLUSTER都重写表，而常规的VACUUM只在适当的位置修改表。参见4.4节。

4.4. CLUSTER Progress Reporting

当CLUSTER或VACUUM FULL正在运行时，pg_stat_progress_cluster视图将包含当前正在运行这两个命令的每个后端的一行。下表描述了将要报告的信息，并提供了关于如何解释这些信息的信息。

4.5. Base Backup Progress Reporting

当像pg_basebackup这样的应用程序执行基本备份时，pg_s 17:tat_progress_basebackup视图将包含当前正在运行ASE_BACKUP复制命令并流化备份的每个WAL发送方进程的一行。下表描述了将要报告的信息，并提供了关于如何解释这些信息的信息。

4.6. COPY Progress Reporting

当COPY正在运行时，pg_stat_progress_copy视图将包含当前正在运行COPY命令的每个后端的一行。下表描述了要报告的信息，并提供了关于如何解释这些信息的信息。

5. Dynamic Tracing

PostgreSQL提供了支持动态跟踪数据库服务器的功能。这允许在代码的特定点调用外部实用程序，从而跟踪执行情况。

许多探测或跟踪点已经插入到源代码中。这些探测旨在供数据库开发人员和管理员使用。默认情况下，探测不会编译到PostgreSQL中;用户需要显式地告诉配置脚本使探测可用。

目前，DTrace1实用程序是受支持的，在撰写本文时，它可用于Solaris、macOS、FreeBSD、NetBSD和Oracle Linux。Linux的SystemTap2项目提供了等效的DTrace，也可以使用。通过更改src/include/utils/probes.h中的宏的定义，理论上可以支持其他动态跟踪实用程序。

5.1. Compiling for Dynamic Tracing

默认情况下，探测是不可用的，因此您需要显式地告诉配置脚本使探测在PostgreSQL中可用。要包含DTrace支持，请指定——enable-dtrace命令来配置DTrace。更多信息请参见17.4节。

5.2. Built-in Probes

源代码中提供了许多标准探针，如表28.47所示;表28.48显示了探测中使用的类型。当然可以添加更多的探测来增强PostgreSQL的可观察性。

5.3. Using Probes

下面的示例显示了一个用于分析系统中事务计数的DTrace脚本，它是在性能测试前后快照pg_stat_database的一个alter & native

#!/usr/sbin/dtrace -qs
postgresql$1:::transaction-start
{@start["Start"] = count();self->ts = timestamp;
}
postgresql$1:::transaction-abort
{@abort["Abort"] = count();
}
postgresql$1:::transaction-commit
/self->ts/
{@commit["Commit"] = count();@time["Total time (ns)"] = sum(timestamp - self->ts);self->ts=0;
}
When executed, the example D script gives output such as:
# ./txn_count.d `pgrep -n postgres` or ./txn_count.d 
^C
Start 71
Commit 70
Total time (ns) 2312105013

SystemTap对跟踪脚本使用与DTrace不同的表法，尽管un * *基础跟踪点是兼容的。有一点值得注意，在编写本文时，SystemTap脚本引用探测名称时必须使用双下划线而不是连字符。这预计将在未来的SystemTap版本中得到修复

您应该记住，需要仔细编写和调试DTrace脚本，否则收集的跟踪信息可能没有意义。在大多数情况下，问题出在设备上，而不是底层系统。在讨论使用动态跟踪找到的信息时，一定要附上用于检查和讨论该信息的脚本。发现它是

5.4. Defining New Probes

可以在开发人员希望的任何地方在代码中定义新的探测，尽管这将需要重新编译。下面是插入新探针的步骤:

1. 决定探测名称和要通过探测提供的数据

2. 将探测定义添加到src/backend/utils/probes.d

3. 如果包含探测点的模块中没有pg_trace.h，则包含pg_trace.h，并在源代码中所需的位置插入TRACE_POSTGRESQL探测宏

4. 重新编译并验证新的探测是否可用

下面是一个示例，说明如何按事务ID添加探测来跟踪所有新事务。

1. 决定将探针命名为transaction-start，并需要一个类型为LocalTransactionId的参数

2. 将探针定义添加到src/backend/utils/probes.d:

probe transaction__start(LocalTransactionId);注意在探测名称中使用了双下划线。在使用探测的DTrace脚本中，双下划线需要替换为连字符，因此transaction-start是为用户编写文档的名称。

3. 在编译时，transaction__start被转换为一个名为TRACE_POST的宏(注意这里的下划线是单一的)，它可以通过包含pg_trace.h来获得。将宏调用添加到源代码中的适当位置。在本例中，它看起来如下所示:

TRACE_POSTGRESQL_TRANSACTION_START(vxid.localTransactionId);

4. 在重新编译并运行新的二进制文件之后，检查新添加的探测是否可用通过执行下面的DTrace命令。你应该会看到类似的输出:

在向C代码中添加跟踪宏时，有几件事需要注意:

• 您应该注意为探测的参数指定的数据类型与宏中使用的变量的数据类型相匹配。否则，您将得到编译错误。

• 在大多数平台上，如果PostgreSQL是用——enable-dtrace构建的，那么跟踪宏的参数将在控制通过宏时被计算，即使没有进行跟踪。如果只是报告几个局部变量的值，这通常不值得担心。但是要注意在参数中调用昂贵的函数。如果需要这样做，请考虑通过检查是否实际启用了跟踪来保护宏

if (TRACE_POSTGRESQL_TRANSACTION_START_ENABLED())TRACE_POSTGRESQL_TRANSACTION_START(some_function(...));

每个跟踪宏都有一个相应的ENABLED宏。