PostgreSQL数据库学习手册之监控数据库的活动

　　 
　　Chapter 10. 监控数据库的活动

Table of Contents
10.1. 标准 Unix 工具
10.2. 统计收集器

10.2.1. 统计收集器配置
10.2.2. 查看收集到的统计信息

10.3. 查看锁

一个数据库管理员常常想知道，"现在系统正在干什么呢？"． 本章讨论如何回答这个问题．

有一些工具可以用来监控数据库的活动以及分析性能． 本章大部分内容是用于描述 PostgreSQL 的 统计收集器(statistics collector)， 但我们也不能忽视普通的 Unix 监控程序，比如 ps 和 top．同样，一旦我们找出了一个性能恶劣的查询，那么 我们可能还要用 PostgreSQL 的 EXPLAIN 命令进一步分析． PostgreSQL 7.3 用户手册 里讨论了 EXPLAIN 和其它用于理解独立查询的行为的方法．
10.1. 标准 Unix 工具

在大多数平台上，PostgreSQL 修改 ps 输出的命令标题，这样我们就很容易找出某个 服务器进程．一个简单的显示如下

$ ps auxww | grep ^postgres
postgres 960 0.0 1.1 6104 1480 pts/1 SN 13:17 0:00 postmaster -i
postgres 963 0.0 1.1 7084 1472 pts/1 SN 13:17 0:00 postgres: stats buffer process
postgres 965 0.0 1.1 6152 1512 pts/1 SN 13:17 0:00 postgres: stats collector process
postgres 998 0.0 2.3 6532 2992 pts/1 SN 13:18 0:00 postgres: tgl runbug 127.0.0.1 idle
postgres 1003 0.0 2.4 6532 3128 pts/1 SN 13:19 0:00 postgres: tgl regression [local] SELECT waiting
postgres 1016 0.1 2.4 6532 3080 pts/1 SN 13:19 0:00 postgres: tgl regression [local] idle in transaction

(调用 ps 的方法因不同的平台而略有不同， 显示出来的细节也有一些区别．这个例子来自一个最近的 Linux 系统．) 这里显示出来的第一个进程时 postmaster，主服务进程． 给它显示的命令参数和运行它的时候给它的是一样的． 下面两个进程实现统计收集器，我们将在下一节里详细描述它． (如果你设置了系统不启动统计收集器，那么它们不会出现．) 剩下的进程每个都是一个服务器进程，每个处理一个客户的联接． 每个这样的进程都用下面的形式设置其命令行显示∶

postgres: user database host activity

在该客户端联接的生命期中，用户，数据库，和联接源主机项都保持不变， 胆识活跃性指示符会变化．活跃性可以是 idle (也就是说， 等待客户端的命令)，idle in transaction (在一个BEGIN 块里等待用户)，或者一个命令类型名，比如 SELECT． 同样，如果服务器目前正在等待一个其它服务器进程持有的锁的时候， 会在信息后面附加 waiting．在上面的例子中，我们可以 推出∶进程 1003 正在等待 1016完成其事务，这样才能施放一些锁或者其它 什么东西．

小知识: Solaris 需要特别的处理． 你必需使用 /usr/ucb/ps，而不是 /bin/ps．你还必需使用两个 w 标志，而不是一个．另外，你最初调用 postmaster 是用到的命令行在 ps 状态显示中必需比每个后端显示的短．如果没做到这三件事， 那么 ps 为每个后端输出的将是最初的 postmaster 的命令行．

10.2. 统计收集器

PostgreSQL 的 统计收集器是一个支持收集和汇报服务器活跃性信息的 子系统．目前，这个收集器可以给对表和索引的访问计数，包括磁盘块的 数量和独立行的项．它还可以判断当前其它服务器进程在执行的查询是什么．
10.2.1. 统计收集器配置

因为统计收集给查询处理增加了一些过热，所以你可以把系统配置为收集 信息，也可以配置为不收集信息．这个是由配置参数控制的，这些配置参数 通常在 postgresql.conf 里设置 (参阅 Section 3.4 获取有关设置配置变量的细节)．

要想让统计收集器运行起来， 变量 STATS_START_COLLECTOR 必须设置为真． 这个设置是缺省设置，也是建议设置，但是如果你对统计不感兴趣 并且想把所有过荷都挤出去．(不过，省下来的东西并不多．) 请注意这个选项在服务器运行的时候并不能改变．

变量 STATS_COMMAND_STRING， STATS_BLOCK_LEVEL，和 STATS_ROW_LEVEL 控制实际发送给收集器的数量， 因此也决定了会产生多少运行时过热．这些选项分别决定一个服务器进程 是否发送它的当前命令字串，磁盘块层次的访问统计，以及行层次的访问统计 给收集器．通常这些变量在 postgresql.conf 中设置， 因此它们适用于所有服务器进程，但是我们也可以在独立的服务器 进程里用 SET 命令把它们打开或者关闭． (为避免普通用户把它们的活跃性隐藏不给管理员看，只有超级用户 允许用 SET 命令修改这些参数．)

Caution

因为变量 STATS_COMMAND_STRING， STATS_BLOCK_LEVEL，和 STATS_ROW_LEVEL 缺省时false， 索引实际上缺省配置中不收集任何统计信息。你必须打开其中一个或者 多个才能从统计显示函数中获取有用的结果．
10.2.2. 查看收集到的统计信息

有一些预定义的视图可以用于显示统计收集的结果，在 Table 10-1 里列出．另外， 我们可以使用下层的统计函数制作自己的客户化视图．

在使用统计观察当前活跃性的时候，你必须意识到这些信息 并不是实时更新的．每个独立的服务器进程只是在等待另外一条客户端命令的 时候才向收集器传送新的访问计数；因此正在处理的查询并不影响 显示出来的总数．同样，收集器本身也时最多每 pgstat_stat_interval毫秒 (缺省是 500)发送一次新的总数．因此显示的总数总是落后于实际活动．

另外一个需要着重指出的问题是，在请求服务器进程显示任何这些统计信息的 时候，它首先抓取收集器进程发出的最新的总计．然后它就接着拿这些数据 作为所有统计视图和函数的快照，直到它当前的事务的结束． 因此统计在你当前事务的持续期间内时不会改变的．这是一个特性， 而不是一个毛病，因为这样就允许你在统计上执行几个查询并且 对结果进行相关性的检查而又不用担心这些数字会背着你变化． 但是如果你想看每个查询的新的结果，那么就要记住在任何事务块外面 处理这些查询．

Table 10-1. 标准统计视图
视图名字 描述
pg_stat_activity 每个服务器进程一行，显示进程ID，数据库，用户，和当前查询． 只有超级用户看得到当前查询字段；对于其它用户，它显示为 NULL． (请注意因为收集器的报告延迟，当前查询只是对长时间运行的查询 及时更新．)
pg_stat_database 每个数据库一行，显示激活的后端的数量，提交的事务总数以及在该 数据库中回卷数目的总数，读取的磁盘块的总数，以及缓冲区命中的总数( 也就是中所需要的块已经在缓冲区中找到，从而避免了读取块的动作)．
pg_stat_all_tables 当前数据库中每个表一行，里面有顺序扫描和索引扫描的总数， 每种类型的扫描返回的元组的总数以及元组插入，更新，和删除的总数．
pg_stat_sys_tables 和pg_stat_all_tables一样，只不过只显示系统表．
pg_stat_user_tables 和pg_stat_all_tables一样，只不过只显示用户表．
pg_stat_all_indexes 当前数据库的每个索引一行，包括使用了该索引的索引扫描总数， 索引元组读取的总数以及成功抓取的堆元组的数目(如果有些索引记录指向过期 的堆元组，那么这个数目可能少一些．)
pg_stat_sys_indexes 和pg_stat_all_indexes一样，只不过只包含那些显示为系统表上的索引．
pg_stat_user_indexes 和pg_stat_all_indexes一样，只不过只包含那些显示为用户表上的索引．
pg_statio_all_tables 当前数据库中每个表一行，包含从该表中读取的磁盘块总数， 缓冲区命中的总数，在该表上所有索引的磁盘块读取和缓冲区命中总数， 在该表的辅助 TOAST 表(如果存在)上的磁盘块读取和缓冲区命中总数， 以及 TOAST 表的索引的磁盘块读取和缓冲区命中总数．
pg_statio_sys_tables 和pg_statio_all_tables一样，只不过只显示系统表．
pg_statio_user_tables 和pg_statio_all_tables一样，只不过只显示用户表．
pg_statio_all_indexes 当前数据库中每个索引一行，包含该索引的磁盘块读取和缓冲区命中的 数目．
pg_statio_sys_indexes 和pg_statio_all_indexes一样，只不过只显示系统表．
pg_statio_user_indexes 和pg_statio_all_indexes一样，只不过只显示用户表．
pg_statio_all_sequences 当前数据库中每个序列对象一个，包含该序列磁盘读取和缓冲区命中的 数目．
pg_statio_sys_sequences 和pg_statio_all_sequences一样，只不过只显示系统序列． (准确地说，我们没有定义系统序列，所以这个视图总是空的．)
pg_statio_user_sequences 和pg_statio_all_sequences一样，只不过只显示用户序列．

每索引的统计对于判断哪个索引得到使用以及它们的效果非常有用．

pg_statio_ 系列视图在判断缓冲的效果的时候特别有用． 在实际磁盘读取远表缓冲命中小的时候，我们就知道这个缓冲基本满足所有 读要求，因此不需要进行系统调用．

其它查看统计的方法可以通过书写使用下层统计访问函数的查询 来设置，这些下层统计访问函数和标准视图里使用的是一样的． 这些函数在Table 10-2 中列出。 就某数据库进行访问的函数接受一个数据库 OID 以标识需要报告哪个数据库． 就某表或者某索引进行访问的函数接受一个表或者索引的 OID (请注意这些函数 只能看到在当前数据库里的表和索引)． 就某后端进行访问的函数接受一个后端 ID 号，其范围从一到当前活跃后端的 数目．

Table 10-2. 统计访问函数
函数 返回类型 描述
pg_stat_get_db_numbackends(oid) integer 数据库中活跃的后端数目．
pg_stat_get_db_xact_commit(oid) bigint 数据库中已提交事务数量．
pg_stat_get_db_xact_rollback(oid) bigint 数据库中回卷的事务数量
pg_stat_get_db_blocks_fetched(oid) bigint 数据库中磁盘块抓取请求总数
pg_stat_get_db_blocks_hit(oid) bigint 为数据库在缓冲区中找到的磁盘块请求总数
pg_stat_get_numscans(oid) bigint 如果参数是一个表，那么就是进行的顺序扫描的数目， 如果是一个索引，那么就是索引扫描的数目．
pg_stat_get_tuples_returned(oid) bigint 如果参数是一个表，那么就是顺序扫描读取的元组数目， 如果是一个索引，那么就是索引元组的数目
pg_stat_get_tuples_fetched(oid) bigint 如果参数是一个表，那么就是顺序扫描抓取的有效(未过期)的表元组数目， 如果是一个索引，那么就是用这个索引抓取的有效表元组数目
pg_stat_get_tuples_inserted(oid) bigint 插入表中的元组数量
pg_stat_get_tuples_updated(oid) bigint 在表中已更新的元组数量
pg_stat_get_tuples_deleted(oid) bigint 从表中删除的元组数量
pg_stat_get_blocks_fetched(oid) bigint 表或者索引的磁盘块抓取请求的数量
pg_stat_get_blocks_hit(oid) bigint 在缓冲区中找到的表或者索引的磁盘块请求数目
pg_stat_get_backend_idset() set of integer 当前活跃后端 ID 的集合 (从 1 到 N，N 时活跃后端的数目)． 参阅下面的使用样例．
pg_backend_pid() integer 附着的进程的进程 ID
pg_stat_get_backend_pid(integer) integer 所有后端进程的 PID
pg_stat_get_backend_dbid(integer) oid 后端进程的数据库 ID
pg_stat_get_backend_userid(integer) oid 后端进程的用户 ID
pg_stat_get_backend_activity(integer) text 后端进程的当前查询(如果调用者不是超级用户则为 NULL)
pg_stat_reset() boolean 重置所有当前收集的统计。

注意: blocks_fetched 减去 blocks_hit 就是为该表，索引或者数据库 发出的 read() 内核调用的数目；不过实际的物理读取的数目通常比较低， 因为还有内核级的缓冲．

函数 pg_stat_get_backend_idset 提供了 一个为每个活跃后端生成一行的变量的方法．比如，要显示所有后端 的PID和它们的当前查询∶

SELECT pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S;

10.3. 查看锁

监控数据库活动的另外一个有用的工具是系统表 pg_locks。 这样就允许数据库管理员查看在锁管理器里面的未决的锁的信息。比如， 这个功能可以用于：

*

查看当前所有未决锁，所有在某一特定数据库里的关系上的锁， 所有在特定关系上的锁，或者某一 PostgreSQL 会话持有的所有锁。
*

查看当前数据库里带有最多未批准锁的关系（它很可能是数据库客户端 的竞争源）。
*

判断锁竞争给数据库性能带来的影响，以及锁竞争随着整个数据库流量 的变化所产生的变化。

有关更多 PostgreSQL 的锁和管理并发性的 信息，请参考 PostgreSQL 7.3 用户手册。

注意: 在访问 pg_locks 视图的时候，锁管理器的内部 数据结构被瞬间锁住，然后复制一份拷贝给视图呈现。这样就保证了 这个视图提供给我们一套一致地结果集。但是如果我们过于频繁地查看 这个视图，那么肯定会对数据库性能有一点影响。

Table 10-3 显示了 pg_locks 的字段定义。 pg_locks 里对每个可以锁定和可以请求锁模式的对象 都包含一行。因此，如果多个事务都持有或者等待一个对象的锁，那么同一 个可锁定对象可能会出现多次。一个可锁定对象要么是一个关系，要么是一个 事务 ID。（请注意这个视图只包含表级别的锁，没有行级别的锁。如果一个 事务在等待一个行级别的锁，那么在这个视图里会表现成在等待一个持有该锁的 当前事务的 ID。）

Table 10-3. 锁状态系统视图
字段名 类型 描述
relation oid 被锁定的关系的 OID，如果可锁定对象是一个事务 ID，那么这个字段是 NULL。 我们可以用这个字段和 pg_class 系统表进行连接，获取 有关被锁定关系的更多信息。不过，请注意这个功能只是对当前数据库的关系 有效（就是那些 database 字段要么是当前数据库 的 OID，要么是零的关系。）
database oid 被锁定关系所存在的数据库的 OID，如果可锁定对象是事务 ID，则为 NULL。 如果这个锁是在一个全局共享表上面的，那么这个字段会是零。我们可以用 这个字段和 pg_database 系统表进行连接，获取有关 被锁定对象的数据库的更多信息。
transaction xid 一个事务的 ID，如果可锁定对象是一个关系则为 NULL。每个事务在其整个生命期 中都在它的事务 ID 上持有一个排他锁。如果一个事务觉得有必要等待另外一个特定 的事务，它会象企图请求在那个事务 ID 上的共享锁那样进行处理。只有在那个事务 终止并释放它的锁之后，这个请求才可能成功。
pid int4 请求了或者是企图请求该锁的 PostgreSQL 后端的进程 ID。 这个进程属于这个会话。 如果你打开了统计收机器，那么这个字段可以和 pg_stat_activity 视图连接起来获取有关持有或者等待持有该锁的后端的信息。
mode text 对可锁定对象请求的或者持有的锁模式。有关 PostgreSQL 里面可用的不同锁模式的更多信息，请参考PostgreSQL 7.3 用户手册。
isgranted bool 如果这个锁已经批准，则为真（也就是说被这个会话持有）。假则表明这个 后端目前正在等待获取这个锁，也就意味着其它某个后端在同一个可锁定对象 上持有一个冲突的锁模式。这个后端将休眠到其它锁释放（或者是等到侦测出 死锁条件)。单个后端在任意时刻只能等到批准最多一个锁。

原文转自：http://www.ltesting.net