5 共享内存与输入/输出

说明

本章将讨论：

如何分析共享内存的内容；
pg_buffercache 和 pg_shmem_allocations 视图；
共享缓存中的缓冲区使用情况；
如何分析后端进程的内存；
pg_stat_slru 视图；
如何评估数据库对象层面的输入/输出；
如何评估查询执行层面的输入/输出；
什么是临时文件，以及如何评估其使用情况；
如何评估后台写入进程和检查点进程执行的输入/输出；
pg_stat_bgwriter 视图；
检查点执行的特点；
如何评估数据库管理系统不同进程执行的缓冲区写入。

前几章介绍了查询和数据库管理系统的主要对象。查询决定工作负载，并作用于数据库管理系统对象——表和索引。数据库管理系统需要一块独立的运行内存，用于存放用户数据和系统数据并执行查询。内存速度很快，但具有易失性；为了可靠地保存数据，还需要使用其他存储设备，尽管它们的速度不如内存。这就产生了在“快速”运行内存与“慢速”主存储之间移动数据的需求。这种数据移动可以用一个相当笼统的术语来称呼：输入/输出（input/output）。从性能角度看，理解输入/输出的数量和速度非常重要，因为它们会直接影响性能：数据在内存区域与主存储之间移动得越快，查询执行得就越快，能够执行的查询也越多。本章将介绍数据库管理系统提供的工具，以及从数据库对象、后台进程和查询执行等角度评估输入/输出时可能需要的方法。

5.1. 分析共享内存

共享内存用于快速存放表、索引的数据以及大量不同的辅助结构。之所以称为共享，是因为数据库管理系统的所有进程都可以平等访问它，一个进程做出的修改也会对其他进程可见。通常，共享内存中最大的部分是缓冲区缓存，也称为共享缓存，用于存放用户数据。

只要缓冲区缓存能够容纳主存储中的全部数据（主数据目录和表空间），它几乎不会带来问题。但是，当存储数据量超过共享缓存的容量后，就需要有效利用已经分配的空间：哪些数据应该保留在缓存中，空间不足时又该替换掉哪些无用数据？这时可能会产生一系列问题：共享内存中有哪些系统结构，它们占用多少空间？缓冲区缓存中有哪些对象（表、索引）？这些对象的大小及其相互占比是多少？缓冲区缓存的使用效率如何？访问命中率是多少？数据找不到、必须访问主存储的情况有多频繁？数据库管理系统提供了几个可以回答这些问题的工具，既适合一次性分析，也适合定期监控：

pg_buffercache¹——一个独立扩展，提供基于函数的视图，用于显示共享缓存的缓冲区使用情况；
pg_shmem_allocations²——一个视图，提供共享内存中各辅助结构的信息。

借助这些视图，可以从表和索引以及内部辅助结构两个角度了解共享内存的内容。下面详细考察这两个工具及其使用场景。

pg_buffercache 视图

pg_buffercache 扩展可以从用户对象的角度分析缓冲区缓存，并帮助回答以下问题：

共享缓存中有哪些表和索引的页面；
表和索引使用了多少个缓冲区，以及它们占整个缓存的百分比；
当前缓存中有多少空闲缓冲区和已占用缓冲区（“干净”或“脏”缓冲区）。

使用该扩展只需通过 CREATE EXTENSION 命令安装。安装后会出现相应的函数和视图。测试环境中已经安装好该扩展，可以直接使用。

# SELECT * FROM pg_buffercache LIMIT 10;
 bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount | pinn...
----------+-------------+---------------+-------------+---------------+----------------+---------+------------+-----...
        1 |        1262 |          1664 |           0 |             0 |              0 | f       |          5 |                0
        2 |        1260 |          1664 |           0 |             0 |              0 | f       |          5 |                0
        3 |        1259 |          1663 |           5 |             0 |              0 | f       |          5 |                0
        4 |        1259 |          1663 |           5 |             0 |              1 | f       |          5 |                0
        5 |        1259 |          1663 |           5 |             0 |              2 | f       |          5 |                0
        6 |        1259 |          1663 |           5 |             0 |              3 | f       |          5 |                0
        7 |        1249 |          1663 |           5 |             0 |              0 | f       |          5 |                0
        8 |        1249 |          1663 |           5 |             0 |              1 | f       |          5 |                0
        9 |        1249 |          1663 |           5 |             0 |              2 | f       |          5 |                0
       10 |        1249 |          1663 |           5 |             0 |              3 | f       |          5 |                0

每一行描述一个独立的缓冲区；因此，缓冲区缓存越大，视图中的行就越多。根据实践经验，我不记得有过需要单独某个缓冲区信息的情况：通常需要的是按对象、数据库和表分组的统计信息。为 pg_buffercache 编写查询时，需要注意扩展安装在某个具体数据库中，但统计信息描述的是所有缓冲区，包括与其他数据库中的对象关联的缓冲区。可以与 pg_class 连接，把数值标识符转换为表名和其他关系名，但这种转换只适用于当前数据库中的对象。遇到这种情况，建议增加条件，只输出当前数据库的缓冲区，而不是全部数据库的缓冲区。

该视图包含以下字段：

bufferid——具体缓冲区的标识符。对于空闲（未使用）的缓冲区，除 bufferid 外所有字段都没有值（NULL）；
relfilenode——关系文件编号（引用 pg_class.relfilenode）；可以根据这个编号在主数据目录中找到对象；
reltablespace——关系所属表空间的标识符（引用 pg_tablespace.oid 字段）；
reldatabase——数据库的标识符（引用 pg_database.oid）。属于系统目录的共享对象，其值为零；
relforknumber——指示关系分支的标识符（main、visibility map、free space map、init）；
relblocknumber——关系内部的块编号；

isdirty——指示缓冲区是否为脏缓冲区的标志，即其中块的内容是否不同于主存储中对应块的内容；
usagecount——缓冲区访问计数器，Clock Sweep 算法使用它来淘汰共享缓存中未使用的页面³；
pinning_backends——当前固定该缓冲区的后端进程数。

说明

缓冲区固定

进程读取页面时，必须阻止其他进程修改该缓冲区，但长时间锁定缓冲区会对并发工作产生负面影响。根据行可见性规则，缓冲区只需在读取页面头部的过程中保持锁定；不过，仍然必须确保页面不会从缓冲区中淘汰，且页面内容不会发生根本变化。为了限制对缓冲区可以执行的操作范围，又不锁定缓冲区，可以使用所谓的缓冲区固定（pin）：它允许其他进程读取和修改数据，但不允许淘汰页面或清理缓冲区。

利用这些数值标识符，可以准确确定缓冲区缓存中的对象（表和索引）、它们所属的具体数据库，甚至可以使用 pg_relation_filepath 函数了解它们在文件系统中的位置。

根据 isdirty、usagecount 和 pinning_backends 字段，可以区分缓冲区可能处于的几种状态，从而判断缓冲区缓存的使用程度。数据库管理系统启动、缓冲区缓存初始化后，大多数缓冲区都未被使用，也没有关联任何块（除 bufferid 外没有其他值）。查询执行时，数据库管理系统会访问主存储，将表和索引的数据填入缓冲区缓存；缓冲区就会从空闲状态转为已占用状态。此时，缓冲区与表或索引的具体块关联。reltablespace、reldatabase 和 relfilenode 字段共同决定了与缓冲区关联的对象。

还可以关注 usagecount 的值，它表示当前访问缓冲区的强度。每次访问都会使计数器加一（最大值为 5），每次执行淘汰算法都会使它减一。如果执行递减时计数器已经为零，块就会被淘汰并替换为另一个块。因此，根据 usagecount 的值（0 到 5），可以判断缓冲区的使用活跃程度。

还可以进一步细分已占用缓冲区的状态。例如，后端进程可能正在访问缓冲区，读取或修改其中块的数据；这由非零的 pinning_backends 值表示。如果缓冲区内容被修改，它就会变成脏缓冲区，必须把这些修改写入主存储中与该缓冲区关联的块。脏状态由 isdirty 标志表示。

因此，至少可以从两个维度考察缓冲区缓存的使用情况：

在较高层面，按空闲、已使用、已固定和脏缓冲区划分。
在更细的层面，根据访问计数器（usagecount）划分，其取值范围为 0 到 5。

在定期监控中使用 pg_buffercache 时，需要注意该视图只显示轮询时刻的缓冲区状态；两次轮询之间缓冲区发生了什么是未知的。这与 pg_locks 和 pg_stat_activity 的工作方式类似。

说明

对性能的影响

在 PostgreSQL 10 之前，pg_buffercache 的内部机制会使用锁来获取所有缓冲区的一致状态。在工作负载繁重的系统中，这些锁会降低性能。从版本 10 开始，缓冲区管理器不再设置这些锁。因此，访问 pg_buffercache 对缓冲区正常活动的影响变小了，但针对所有缓冲区得到的结果整体上可能不一致（通常这并不严重）。对于每个单独的缓冲区，信息的一致性仍然有保障。

回到前面查询 pg_buffercache 的 SQL。它的输出几乎全部是数值标识符；要得到更易理解的信息，需要把结果与其他信息源连接起来并进行一些转换。

# SELECT
    n.nspname,
    c.relname,
    count(*) AS buffers,
    pg_size_pretty(count(*) * 8192) AS bytes
  FROM pg_buffercache b
  JOIN pg_class c
    ON b.relfilenode = pg_relation_filenode(c.oid) AND
       b.reldatabase IN (
         0,
         (SELECT oid FROM pg_database WHERE datname = current_database())
       )
  JOIN pg_namespace n ON n.oid = c.relnamespace
  GROUP BY n.nspname, c.relname
  ORDER BY 3 DESC
  LIMIT 10;

  nspname   |            relname             | buffers |  bytes
------------+--------------------------------+---------+---------
 public     | pgbench_accounts               |    9902 | 77 MB
 public     | pgbench_accounts_pkey          |    5234 | 41 MB
 public     | pgbench_tellers                |     290 | 2320 kB
 public     | pgbench_history                |     198 | 1584 kB
 pg_catalog | pg_proc                        |      99 | 792 kB
 pg_catalog | pg_attribute                   |      38 | 304 kB
 pg_catalog | pg_class                       |      15 | 120 kB
 pg_catalog | pg_proc_proname_args_nsp_index |      14 | 112 kB
 pg_catalog | pg_operator                    |      14 | 112 kB
 pg_catalog | pg_statistic                   |      13 | 104 kB

得到的结果更容易理解了：对象不再以数值标识符显示，统计信息按对象分组，块数也被汇总，并额外换算成了字节数。现在可以看出，在测试环境中，缓冲区缓存的大部分空间（shared_buffers 参数设置为 128 MB）由 pgbench_accounts 表及其索引占用。

说明

换算为字节

在测试环境中，我预先知道块大小，因此查询示例使用常量 8192。不过，在实践中，块大小可以在从源代码编译时重新定义，这种情况下换算结果就会不正确。可以使用更通用的方案，通过调用 current_setting('block_size') 函数从数据库管理系统配置中获取实际块大小。需要将块大小表示为字节、但又不知道确切块大小时，优先使用这种方法。

下面的查询可以获取缓冲区缓存的使用情况，并了解处于不同状态的缓冲区容量之间的关系。

# SELECT
    pg_size_pretty(count(*) FILTER (
        WHERE reldatabase IS NULL) * 8192) AS free,
    pg_size_pretty(count(*) FILTER (
        WHERE pinning_backends = 0 AND isdirty = 'f') * 8192) AS clean,
    pg_size_pretty(count(*) FILTER (
        WHERE pinning_backends > 0 AND isdirty = 'f') * 8192) AS "clean/pinned",
    pg_size_pretty(count(*) FILTER (
        WHERE pinning_backends = 0 AND isdirty = 't') * 8192) AS dirty,
    pg_size_pretty(count(*) FILTER (
        WHERE pinning_backends > 0 AND isdirty = 't') * 8192) AS "dirty/pinned"
  FROM pg_buffercache;

  free   | clean | clean/pinned | dirty  | dirty/pinned
---------+-------+--------------+--------+--------------
 0 bytes | 26 MB | 8192 bytes   | 102 MB | 40 kB

从查询输出可以看出，大部分缓冲区都是脏的。这说明系统确实发生了大量修改：前面介绍查询的章节已经说明，测试工作负载中占主导的是更新 pgbench_accounts 中记录的查询。在测试环境中，监控代理使用类似的查询获取 postgres_shared_buffers_all_usage_bytes 指标，并据此绘制下面的图表（图 5.1）：

图中记录了数据库管理系统容器重启的时刻，此时缓冲区缓存被清空。一段时间内，缓冲区缓存中存在空闲缓冲区，但它们很快就会耗尽。

还可以用另一种图表展示数据：按数据库名称分组统计。这样可以跟踪特定数据库页面对缓冲区缓存的填充情况；当一个集簇中使用了许多数据库时，这种方式可能很有用。

`pg_shmem_allocations` 视图

pg_buffercache 扩展只显示用于缓存表和索引的缓冲区信息。不过，除了缓冲区缓存外，共享内存中还放置着各种辅助结构，数据库管理系统的所有进程（包括后端进程和后台进程）都可以访问这些结构。可以使用 pg_shmem_allocations 视图对共享内存进行结构分析。

# SELECT *
  FROM pg_shmem_allocations
  ORDER BY size DESC LIMIT 10;
         name         |    off    |   size    | allocated_size
----------------------+-----------+-----------+----------------
 Buffer Blocks        |   6843520 | 134217728 |      134217728
 <anonymous>          |           |   6710784 |        6710784
 XLOG Ctl             |     54144 |   4208200 |        4208256
                      | 149586304 |   1900160 |        1900160
 Buffer Descriptors   |   5794944 |   1048576 |        1048576
 CommitTs             |   4792192 |    533920 |         534016
 Xact                 |   4263040 |    529152 |         529152
 Checkpointer Data    | 146862208 |    393280 |         393344
 Checkpoint BufferIds | 141323392 |    327680 |         327680
 Subtrans             |   5326336 |    267008 |         267008

每一行描述为某个特定辅助结构分配的一段内存。列表中最大的结构是 Buffer Blocks 区域，用户对象的数据缓冲区缓存就存放在其中，可以使用 pg_buffercache 对其进行详细研究。查看该视图的完整输出可以发现，共享内存中放置了大量结构。例如，在版本 15 中大约有六十个，具体数量可能因配置、所使用的模块等因素而异。视图本身的字段并不多：

name——段的名称，可以据此大致判断其用途。没有名称（NULL）的段表示未使用的内存；名称为 <anonymous> 的段是匿名段，可以用于存放锁信息；
off——段在共享内存区域中的起始偏移量。匿名段没有偏移量值（NULL）；
size——段的大小，单位为字节；
allocated_size——考虑对齐（padding）后的段大小。对于空闲内存和匿名段，size 与 allocated_size 的值始终相等。

在日常监控中，pg_shmem_allocations 提供的信息并不十分有用。它主要适用于少数故障排查场景，或者在开发、调试数据库管理系统的新功能时检查内存使用效率。

5.2. 分析后端进程的内存

另外两个同样可能对调试和排查问题有用的工具是 pg_backend_memory_contexts 视图和 pg_log_backend_memory_contexts 函数。这两个工具都用于显示某个具体进程所使用的内存段信息，但视图和函数的行为有所不同。视图只显示与当前会话关联的进程的内存分布，无法用来查看其他进程（例如正在执行长查询的进程）的内存布局。遇到这种情况，应使用 pg_log_backend_memory_contexts 函数；它接收进程标识符作为参数，并将内存布局输出到数据库服务器日志中。

# SELECT * FROM pg_backend_memory_contexts
  ORDER BY used_bytes DESC LIMIT 10;
          name           | ident |      parent      | level | total_bytes | total_nblocks | free_bytes | free_chunks
-------------------------+-------+------------------+-------+-------------+---------------+------------+------------...
 CacheMemoryContext      |       | TopMemoryContext |     1 |     1048576 |             8 |     499872 |           2
 Timezones               |       | TopMemoryContext |     1 |      104120 |             2 |       2616 |           0
 TopMemoryContext        |       |                  |     0 |       97680 |             5 |      12232 |           7
 ExecutorState           |       | PortalContext    |     3 |       49208 |             4 |       4280 |           3
 WAL record construction |       | TopMemoryContext |     1 |       50208 |             2 |       6360 |           0
 MessageContext          |       | TopMemoryContext |     1 |       73728 |             4 |      30856 |           4
 TupleSort main          |       | ExecutorState    |     4 |       32824 |             2 |       6808 |           7
 Type information cache  |       | TopMemoryContext |     1 |       24376 |             2 |       2616 |           0
 smgr relation table     |       | TopMemoryContext |     1 |       32768 |             3 |      12712 |           8
 Operator lookup cache   |       | TopMemoryContext |     1 |       24576 |             2 |      10752 |           3

每一行包含某个具体内存区域（即所谓的上下文）的信息、使用指标以及其他数据：父上下文、嵌套层级、块数（total_nblocks）和空闲片段数（free_chunks）。

这些工具更适合调试和排查问题，而不是常规监控。不过，截至目前（版本 15），它们是唯一能够详细分析数据库管理系统进程内存构成和使用情况的工具。

5.3. 评估 SLRU 缓存的使用情况

还需要考察另一组缓存，即 SLRU 缓存（Simple Least-Recently-Used，简单最近最少使用）。这些独立缓存用于加快访问存放在主数据目录磁盘上的某些辅助结构。此类辅助结构包括：

事务提交状态日志结构（clog，commit log）；
支持嵌套事务（subtransactions）的结构；
跟踪事务提交时间的结构（参见 track_commit_timestamp）；

与通知子系统相关的结构（LISTEN 和 NOTIFY 命令）；
其他使用基于 SLRU 算法的缓存的结构。

可以使用 pg_stat_slru 视图跟踪 SLRU 缓存的使用情况：

# SELECT * FROM pg_stat_slru;
      name       | blks_zeroed | blks_hit | blks_read | blks_written | blks_exists | flushes | truncates |          ...
-----------------+-------------+----------+-----------+--------------+-------------+---------+-----------+----------...
 CommitTs        |           0 |        0 |         0 |            0 |           0 |    1424 |         0 | 2022-11-0...
 MultiXactMember |           1 |        0 |         0 |            1 |           0 |    1424 |         0 | 2022-11-0...
 MultiXactOffset |           1 |        1 |         4 |            5 |           0 |    1424 |         0 | 2022-11-0...
 Notify          |           0 |        0 |         0 |            0 |           0 |       0 |         0 | 2022-11-0...
 Serial          |           0 |        0 |         0 |            0 |           0 |       0 |         0 | 2022-11-0...
 Subtrans        |        7522 |        0 |         0 |         7462 |           0 |    1424 |         0 | 2022-11-0...
 Xact            |         471 | 79554357 |        48 |         1889 |           0 |    1424 |         0 | 2022-11-0...
 other           |           0 |        0 |         0 |            0 |           0 |       0 |         0 | 2022-11-0...

每一行包含某个具体 SLRU 缓存的累计使用统计（缓存列表是固定的）：

name——SLRU 缓存的名称；
blks_zeroed——初始化期间填零的块数；
blks_hit——在缓存中找到的块数；
blks_read——必须从磁盘上的主存储读取的块数；
blks_written——必须从缓存写入磁盘的脏块数；
blks_exists——在磁盘上成功找到的块数；
flushes——已执行的脏块同步请求数；
truncates——因块已不再需要而执行的删除块请求数（在维护操作期间）；
stats_reset——上次重置统计信息的时间戳。

目前，SLRU 缓存使用统计只能用于监控，因为数据库管理系统的配置中没有可以调节其大小的参数（不过，未来不排除会出现这样的参数）。统计信息的主要用途是评估缓存效率。

5.4. 从数据库管理系统对象角度评估输入/输出

前面介绍的 pg_buffercache 统计信息只能分析缓冲区缓存使用的总体情况。使用该视图监控缓冲区缓存无法完整反映输入/输出量，因为它只包含当前位于缓存中的对象信息，许多在两次统计快照之间被访问的其他数据库对象可能会从视野中消失。要更完整地统计输入/输出，需要针对所有数据库对象的累计统计信息。这些信息分布在多个视图中，其中一个我们已经遇到过：

pg_stat_database 包含各个数据库的部分输入/输出统计信息；
pg_statio_all_tables——表的输入/输出统计信息（包括 TOAST 表和临时表）；
pg_statio_all_indexes——索引的输入/输出统计信息；
pg_statio_all_sequences——序列的输入/输出统计信息。

数据库

前面几章已经介绍过 pg_stat_database 视图。现在重新讨论它，是因为其中有几个字段提供了按数据库划分的输入/输出信息：

blks_hit——在共享缓存和本地缓存中找到的块数；
blks_read——从磁盘读取的块数（包括在操作系统页缓存中找到的块）；
blk_read_time——读取磁盘所耗费的时间，单位为毫秒；
blk_write_time——写入磁盘所耗费的时间，单位为毫秒。

只有启用 track_io_timing 参数后，才会统计耗时。要完整了解情况，还缺少已写入块和脏块的统计信息。利用这些数据可以大致评估缓存使用效率，但需要注意，统计信息同时包含共享缓存和本地缓存的数据；当临时表被广泛使用时，这一点尤其重要。

可以使用下面的查询评估缓存效率并计算缓存命中率：

# SELECT sum(blks_hit) / sum(blks_hit + blks_read) AS hit_ratio
  FROM pg_stat_database;
       hit_ratio
------------------------
 0.98473387049692560384

得到的指标反映了缓存的使用效率。接近 1 的值表示所需数据能够在缓存中成功找到。相反，值越接近 0，所需数据就越不容易在缓存中找到，数据库管理系统也就越频繁地需要访问主存储，读取数据，再将其加载到缓存中，同时把其他数据从缓存中淘汰。缓存效率持续偏低时，大量时间会耗费在磁盘输入/输出上，从而损害整体性能。遗憾的是，这里没有简单或通用的解决方案，提高缓存效率可以采用多种方式。最直接的办法是增大共享缓存，但这种方法并不总能奏效。更合理的办法是找出执行了大部分输入/输出或在此过程中耗费大量时间的查询，并尝试优化这些查询（例如重构查询或添加索引）。通过这种优化，查询性能可能提高几十倍甚至几百倍，同时缓存使用效率也会提高，而无需修改配置。

还可以利用这些统计信息，通过 blk_read_time 和 blk_write_time 跟踪输入/输出耗时（图 5.2）：

这种图表适合整体评估数据库管理系统的状态，能够反映数据库管理系统被迫耗费时间读取和写入数据的时刻；这些时刻可能导致查询执行出现性能下降。

表、索引和序列

要获取数据库管理系统各个对象层面更详细的输入/输出信息，可以使用下面的视图：

pg_statio_all_tables 包含每张表的统计信息：

— heap_blks_read——从磁盘读取的块数；

— heap_blks_hit——在缓存中找到的块数；

— idx_blks_read——从磁盘读取的、属于该表的所有索引的块数；— idx_blks_hit——在缓存中找到的、属于该表的所有索引的块数；

— toast_blks_read——从磁盘读取的 TOAST 表块数；

— toast_blks_hit——在缓存中找到的 TOAST 表块数；

— tidx_blks_read——从磁盘读取的 TOAST 表索引块数；

— tidx_blks_hit——在缓存中找到的 TOAST 表索引块数。

pg_statio_all_indexes 包含每个索引的统计信息：

— idx_blks_read——从磁盘读取的块数；

— idx_blks_hit——在缓存中找到的块数。

pg_statio_all_sequences 包含每个序列的统计信息：

— blks_read——从磁盘读取的块数；

— blks_hit——在缓存中找到的块数。

上述视图包含所有对象的统计信息，包括用户对象和属于系统目录的系统对象。以 pg_statio_sys_ 和 pg_statio_user_ 为前缀的视图分别提供系统对象和用户对象的统计信息。

还需要特别注意缓存命中统计（字段名带有后缀 _hit）。当在缓冲区缓存中找到某个块时，不会发生输入/输出：该块已经位于数据库管理系统拥有的内存中，因此只需读取缓冲区中的所需行，甚至其中一部分即可。需要理解的是，缓存命中统计显示的是在缓存中找到的数据量，但实际需要的数据量可能更少。组织监控时，应尽量避免直接将缓存命中量与读取量进行比较，尤其不要把两个值都表示成字节。评估缓存效率时，最好计算缓存命中量占数据访问总量（缓存命中和从存储读取）的比例。缓冲区脏化和页面写入统计（pg_stat_statements 视图中的 blks_dirtied 和 blks_written 字段）同样如此：插入、更新或删除单独的行时，块的脏化只是对缓冲区的部分修改；与将整个块直接写入主存储不同，它不会导致输入/输出。在把指标合并到一张图中、计算输入/输出量、编写性能报告以及选择通用计量单位（块或字节）时，都要考虑这一细节。

这项统计还有一个重要特点：数据库是表、索引及其他对象的某种隔离容器。因此，这些视图只包含属于当前数据库的对象的统计信息。要收集数据库集簇中所有对象的统计信息，必须依次连接到每个数据库。

单个对象的统计信息能够提供详细的输入/输出数据，但通常只有在优化或排查性能瓶颈等特殊情况下才需要这些数据。以我多年的实践经验来看，需要如此详细信息的情况一只手就数得过来。在许多生产环境中，数据库、表和索引的数量可能很大。监控会生成大量指标，因此在采集和存储之前，应先评估这些指标的实际价值。

下面的查询可以得到数据库各表的读取量（图 5.3 和 5.4）：

# sum by (table)
    (rate(postgres_table_io_blocks_total{service_id="primary",access="read"}[1m]))

pgbench\_history 表的读取量 — 图 5.4 pgbench_history 表的读取量

第一张图（图 5.3）中的输入/输出量大致稳定，但从生产运行的角度看，仍然可能需要关注与 pgbench_accounts 表相关的周期性峰值，以及 pgbench_history 表读取量的增加。后者没有峰值那么明显，但在关闭其他指标后就能看出来（图 5.4）。

下面的查询显示 pgbench_accounts 表在缓存中命中的块数以及从磁盘读取的块数（这是测试环境中使用最频繁的表；图 5.5）：

# sum by (access) (rate(postgres_table_io_blocks_total{
        service_id="primary", table="pgbench_accounts"
  }[1m]))

pgbench\_accounts 表的输入/输出量 — 图 5.5 pgbench_accounts 表的输入/输出量

从图中可以看出，大部分块从缓存读取，只有相对较小的一部分从主存储（或操作系统页缓存）读取，这对性能总体有利。当然，还需要注意，计量单位使用的是未经任何换算的块。

5.5. 从查询执行角度评估输入/输出

可以使用 pg_stat_statements 扩展分析已执行的查询；前面几章已经介绍过这个扩展。该视图包含大量有用信息，其中也包括按查询类型划分的输入/输出统计。与 pg_stat_database 和 pg_statio_* 相比，查询级统计信息更加完整：

shared_blks_hit——在缓冲区缓存中找到的块数；

shared_blks_read——从磁盘或操作系统页缓存读取到共享缓冲区缓存中的块数；
shared_blks_dirtied——由于发生修改而变脏的共享缓冲区缓存块数；
shared_blks_written——内容已写入磁盘的共享缓冲区缓存块数。

统计信息还包括本地缓存的对应字段：local_blks_hit、local_blks_read、local_blks_dirtied 和 local_blks_written。在介绍查询的第 3 章中，我们已经讨论过 blk_read_time 和 blk_write_time 字段，它们表示输入/输出所耗费的时间。

这些统计信息可以用于以下场景：

确定具体查询产生的输入/输出量；
找出访问临时表的查询及其产生的输入/输出量（临时表使用本地缓存，其大小由 temp_buffers 参数决定）；
评估已实施优化的效果（执行具体查询时的输入/输出量发生了什么变化）；
在确定优化优先级时评估查询的输入/输出量（输入/输出量更大的查询经过优化后，在资源利用方面会有更明显、更有效的改善）。

在某些特殊的性能问题排查场景中，还可能有其他使用方式。

5.6. 临时文件

查询执行，尤其是复杂查询的执行，由与计划节点对应的多个步骤组成。在这些步骤中，通常会对数据执行某些操作（选择、过滤、排序、比较等）。排序数据、为连接和聚合构建哈希表、物化 CTE 等操作可能需要临时缓冲区。为此会分配一块大小为 work_mem 的工作内存，通常比较小，默认值为 4 MB。不过，查询处理的数据量可能大得多。工作内存不足时，系统会在磁盘上创建临时文件，并将一部分数据移入其中。查询完成后，执行查询所需的所有临时文件都会被删除。使用临时文件通常会降低查询性能，因为所需数据必须写入磁盘并从磁盘读取。从性能角度看，应跟踪所有使用临时文件的查询，并设法优化它们，消除输入/输出的必要性。

数据库管理系统的统计信息提供了多个跟踪临时文件的来源，可以用于不同场景：

pg_stat_database 包含按数据库划分的已创建临时文件统计信息；
pg_stat_statements 包含按查询执行划分的临时文件输入/输出统计信息；
数据库管理系统的服务器日志也可以记录临时文件的使用情况（参见 log_temp_files），其中还可能包含查询文本。

这三个来源都有一个共同特点：只有在查询结束时才会记录使用统计信息。对于执行时间很长、已经使用临时文件的查询来说，这很不方便：统计信息只有在查询成功结束后才会更新；如果查询因为超时（例如 statement_timeout）异常结束，或者被管理员通过 pg_terminate_backend 函数强制终止，则不会记录这些信息。

下面逐一介绍获取临时文件统计信息的各种方法。

数据库级别

pg_stat_database 视图包含 temp_files 和 temp_bytes 字段，分别表示临时文件的总数及其大小。我们知道，pg_stat_database 的每一行都包含某个数据库的累计统计信息，因此这两个字段也表示统计信息累计期间的总量（统计信息重置时间记录在 stats_reset 中）。

# SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size
  FROM pg_stat_database WHERE temp_files > 0
  ORDER BY temp_bytes DESC;
 datname | temp_files | temp_size
---------+------------+-----------
 pgbench |          4 | 38 MB
         |          1 | 178 kB

测试环境中的查询几乎不使用临时文件，但仍然可以看到一些统计信息。接下来进行实验时，将使用下面的查询；在 work_mem 取默认值的情况下，它一定会需要临时文件：

# SELECT a.*, b.* FROM pg_class a, pg_class b ORDER BY random();

查询执行需要几秒钟，并会创建一个约 60 MB 的临时文件。再次查询 pg_stat_database，可以发现 postgres 数据库记录了临时文件的使用情况：

# SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size
  FROM pg_stat_database
  WHERE temp_files > 0
  ORDER BY temp_bytes DESC;
 datname  | temp_files | temp_size
----------+------------+-----------
 postgres |          1 | 58 MB
 pgbench  |          4 | 38 MB
          |          1 | 178 kB

利用 postgres_database_temp_bytes_total 指标可以绘制图表（图 5.6）：

这种图表通常用于粗略跟踪临时文件：一旦发现临时文件使用量达到临界水平，就可以通过 pg_stat_statements 或服务器日志进行更详细的分析。

temp_bytes 计数器的一个重要特点是记录临时文件的最终大小。不要把这个大小与该文件产生的输入/输出量混淆：文件可能被反复读写，已执行的输入/输出量可能远远超过文件的最终大小。

查询执行期间的输入/输出

pg_stat_statements 扩展是跟踪临时文件的第二个来源。该视图中下面这些字段显示查询的输入/输出量，以及处理临时文件所耗费的时间：

temp_blks_read——从临时文件读取的块数；
temp_blks_written——写入临时文件的块数；
temp_blk_read_time——读取临时文件所耗费的时间，单位为毫秒；
temp_blk_write_time——写入临时文件所耗费的时间，单位为毫秒。

没有 _hits 和 _dirtied 计数器是正常的，因为所有输入/输出都直接针对文件进行，不经过缓存。此外，临时文件使用的处理方式也不同于缓冲区缓存（共享缓存或本地缓存）所采用的基于页面的方式：这里的输入/输出处理的是没有头部、紧密排列的行，而且只处理所需字段。视图中还有一个 dbid 字段，按该字段分组可以获得各个数据库的统计信息。这可能让人想到 pg_stat_database，但 pg_stat_database 统计临时文件的数量和大小，而 pg_stat_statements 统计输入/输出量。直接比较 pg_stat_database 和 pg_stat_statements 中的值没有什么意义。

# SELECT calls, total_exec_time,
    pg_size_pretty(temp_blks_read * 8192) AS temp_read_bytes,
    pg_size_pretty(temp_blks_written * 8192) AS temp_written_bytes,
    temp_blk_read_time, temp_blk_write_time,
    left(query, 32) as query
  FROM pg_stat_statements WHERE query ~ 'SELECT a.*, b.*';
-[ RECORD 1 ]-------+--------------------------------------------------------------
calls               | 1
total_exec_time     | 3262.312213999999
temp_blks_read      | 115 MB
temp_blks_written   | 115 MB
temp_blk_read_time  | 96.354933
temp_blk_write_time | 257.18505
query               | SELECT a.*, b.* FROM pg_class a, pg_class b ORDER BY random()

从查询监控的角度看，Top-K 图表也很有用，它们可以快速找出使用临时文件最多的查询。不过，测试环境中没有合适的查询，因此这种图表不会显示有意义的内容。

在服务器日志中跟踪

跟踪临时文件的下一种方法是使用数据库管理系统的服务器日志。默认情况下，临时文件消息记录是关闭的；要启用它，需要通过 log_temp_files 参数设置阈值。临时文件大小超过该值时，包含查询文本在内的相关信息会写入服务器日志。临时文件使用记录具有容易识别的特征：其中包含 temporary file 文本、文件路径和以字节表示的文件大小，下一行会显示查询文本：

LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp51374.0", size 60497920
STATEMENT: SELECT a.*, b.* FROM pg_class a, pg_class b ORDER BY random();

说明

关注服务器日志

实际工作中曾发生过这样的情况：监控系统发出磁盘可用空间不足的告警。很快就发现，原因是服务器日志占用了大量空间。不过，只有最近两天的日志明显变大，前几天的日志小了几十倍。分析日志内容后发现，工作负载中出现了大量使用临时文件的查询。但问题不在临时文件，而在查询文本。查询包含一个很长的 IN 列表，由应用程序自动生成，并且没有长度限制。结果，列表中包含数万个 UUID 值，查询文本达到数 MB。每记录一次临时文件使用情况，还会额外写入一条包含巨大查询文本的记录，最终导致日志体积增加。

跟踪活动临时文件

使用 pg_stat_database 和 pg_stat_statements 视图跟踪临时文件存在一个缺点：只有使用临时文件的查询结束后，统计信息才会记录，而此时临时文件本身已经被删除。对于执行很快的查询，这没有问题；但对于运行数分钟的长查询，这种行为可能无法接受。最糟糕的情况是，正在执行的查询所需的临时文件数量和大小不断增加，直到磁盘上的所有可用空间耗尽，导致数据库管理系统异常停止。此时只能看到磁盘空间耗尽，却不容易看出耗尽的原因。数据库、表和索引的大小都不会变化，而临时文件信息只有在查询成功结束后才会出现。

要有效跟踪临时文件，需要能够发现当前正在使用的活动临时文件。为此需要使用操作文件系统的函数，并了解数据库管理系统数据目录的文件层次结构。

先从文件系统的布局开始。数据库管理系统使用 temp_tablespaces 参数指定临时文件⁴的存放位置；该参数列出一组表空间，系统会从中随机选择一个，并在所选表空间的 pgsql\_tmp 子目录中创建临时文件。不过，默认情况下，以及大多数配置中，temp_tablespaces 通常为空（NULL）；此时使用当前数据库的默认表空间。通常这个表空间是 pg_default，临时文件会创建在 base/pgsql\_tmp 子目录中。临时文件名的格式为 pgsql_tmpPPP.NNN，其中 PPP 是文件所属后端进程的标识符，NNN 是文件序号，用于区分同一进程所属的文件。

知道临时文件所在的目录后，只需查看这些目录，并使用访问文件系统的函数获取临时文件统计信息。

为了进行实验，我们打开两个会话，在第一个会话中执行之前介绍过的、会创建临时文件的查询；不过这次对查询进行修改，再增加一次表连接，以提高处理量：

# SELECT a.*, b.*, c.* FROM pg_class a, pg_class b, pg_class c ORDER BY random();

用于输出所需统计信息的查询有几十行，因此这里不再列出，但可以在测试环境的 scripts 目录中找到它⁵。在第二个会话中，我们会定期执行包含该查询的 SQL 脚本；它不仅可以显示临时文件大小的增加，还可以显示新段的出现：

# \i /var/lib/postgresql/scripts/active_temp_files.sql
  pid  |    query_age    |            filename             |  size   | last_modification |
-------+-----------------+---------------------------------+---------+-------------------+--------------------------...
 68403 | 00:02:06.101641 | base/pgsql_tmp/pgsql_tmp68403.0 | 1024 MB | 00:01:01.995852   | SELECT a.*, b.*, c.* FROM...
 68403 | 00:02:06.101641 | base/pgsql_tmp/pgsql_tmp68403.1 | 1024 MB | 00:00:10.995852   | SELECT a.*, b.*, c.* FROM...
 68403 | 00:02:06.101641 | base/pgsql_tmp/pgsql_tmp68403.2 | 455 MB  | 00:00:01.004148   | SELECT a.*, b.*, c.* FROM...

还可以打开第三个会话，观察 pg_stat_database 和 pg_stat_statements 视图中的统计信息何时更新。

5.7. 后台进程的输入/输出

数据库管理系统除了后端进程外，还有同样会执行输入/输出的后台服务。本节介绍后台写入进程 background writer 和执行检查点的检查点进程 checkpointer。这两个进程都负责把缓冲区缓存中的变更写入主存储。由于数据量和所需的输入/输出操作数量可能很大，进程不会在变更发生时立即写入，而是在后台异步写入数据。

后台写入进程的任务是把缓冲区缓存中的脏缓冲区写入主存储。该进程交替执行工作和暂停。在开始下一轮迭代之前，它会评估上一轮迭代期间对缓冲区的访问次数：访问越多，本轮写入的缓冲区就越多（参见 bgwriter_lru_multiplier），但不会超过 bgwriter_lru_maxpages⁶ 指定的上限。在脏缓冲区中，进程会选择近期最有可能被淘汰的缓冲区；为此，它实际上会重复执行淘汰算法，但不会减少使用计数器。也就是说，后台写入进程采取主动方式，避免后端进程尝试淘汰脏页。

checkpointer 进程执行检查点，将变更可靠地写入主存储。检查点完成时，可以保证检查点开始之前发生的所有变更都已经与磁盘同步。检查点通过删除旧段、为新段释放空间来维护 WAL 日志。执行检查点时，进程会扫描缓冲区缓存，把所有脏缓冲区标记为需要同步，然后逐步将其中的内容写入主存储。这项工作同样会产生输入/输出，输入/输出量直接取决于脏缓冲区的数量。关于检查点进程工作的完整信息，可以参见文档⁷和书籍⁸。

pg_stat_bgwriter 视图。 可以使用 pg_stat_bgwriter 视图跟踪后台写入进程和检查点进程的工作。历史上，这两项任务由同一个进程执行；但在 9.2 版本中，与检查点相关的工作被拆分到独立进程中，而统计信息仍然保留在同一个视图中。该视图只有一行：

# TABLE pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------
checkpoints_timed     | 316
checkpoints_req       | 2
checkpoint_write_time | 84919068
checkpoint_sync_time  | 58509
buffers_checkpoint    | 1254815
buffers_clean         | 1801423
maxwritten_clean      | 0
buffers_backend       | 443469
buffers_backend_fsync | 0
buffers_alloc         | 2646195
stats_reset           | 2022-11-04 05:01:49.427562+00

这些统计信息不仅涉及后台写入进程和检查点进程，也涉及整体的后台写入。下面详细考察这个视图。

检查点的触发原因。 前两个字段描述已执行检查点的数量：

checkpoints_timed——按计划执行的检查点数量；
checkpoints_req——按需执行的检查点数量。

可以看出，检查点有两种启动方式：一种是按计划启动，即按照 checkpoint_timeout 参数指定的时间间隔执行；另一种是按需启动，即 WAL 写入量超过 max_wal_size 参数设定的限制时，在计划之外启动。此外，管理员通过 CHECKPOINT 命令启动的检查点，以及数据库服务器关闭时为将缓冲区缓存与主存储同步而执行的检查点，也属于后一类。

配置检查点有两种策略。第一种策略依赖按计划执行检查点：设置相对较长的 checkpoint_timeout 时间间隔，并让检查点执行过程延续整个间隔。这种策略适用于性能较低的硬件，目的是分散检查点带来的负载，使其对整体性能的影响尽可能小。在这种情况下，如果出现按需检查点，说明负载增加了，在设定的时间间隔结束之前就有更多数据写入 WAL 日志。这种策略的缺点是：如果增大 checkpoint_timeout 间隔和 max_wal_size 上限，由于 WAL 日志写入量更大、检查点更加稀疏，潜在故障后的恢复可能需要很长时间（因为故障恢复时必须重放 WAL 中记录的所有变更）。

第二种策略依赖根据 max_wal_size 的值按需执行检查点：WAL 日志写入量一达到指定值，就启动检查点。这种方法适合调节故障后的恢复时间：由于知道需要重放多少 WAL，恢复时间更容易预测，而且可以根据硬件的性能特点调整 max_wal_size 的值。不过，这种策略也有缺点。max_wal_size 只是一个参考值，实际写入 WAL 日志的变更可能更多，这同样会影响恢复时间。

WAL 日志写入有一个特点：执行检查点后，缓存中任意缓冲区的第一次修改都会执行整页写入（full page write，FPW），将该缓冲区中的整页写入日志（参见 full_page_writes）。之后对该缓冲区的所有修改都会单独记录。因此，检查点执行得越频繁（无论按时间间隔还是按需执行），写入整页镜像带来的额外负载就越大；负载大小取决于缓冲区缓存的大小以及其中发生的变更量（INSERT、UPDATE、DELETE 操作）。

可以注意到，在实践中第一种配置策略更常见，原因正是检查点通常被视为一种开销很大的操作，最好谨慎执行，尽量减少对其他进程和正在执行的查询性能的影响。在这种策略下，checkpoints_req 增加会被视为负面信号，需要评估实际负载是否增加及其对整体性能的影响，重新考虑配置，并进一步查找负载增加的原因。如今，磁盘子系统借助 SSD 和 NVMe 设备大幅提升了性能，这种方法的重要性已经受到质疑。无论采用哪种策略，都应根据存储子系统的性能指标以及查询执行时数据库管理系统的吞吐量来做选择。

监控时可以根据这些指标绘制图表（图 5.7），跟踪“非预期”检查点出现的峰值（具体取决于所选的配置策略）。

图中可以看出，大部分检查点都是按计划执行的，这说明 WAL 日志写入量保持在 max_wal_size 上限以内。也有一些按需执行的检查点：如果这是生产环境，可能意味着 WAL 写入出现峰值；但测试环境中的负载很稳定，因此 WAL 写入量也很平稳，没有明显突增。为了演示，这些检查点是通过 CHECKPOINT 命令启动的。

检查点执行时长。 检查点执行过程可以粗略分为两个阶段：把脏缓冲区的内容写入磁盘，以及调用同步操作将其与主存储同步（参见 fsync）。第一阶段的写入不能可靠保证数据已经保存，因此必须执行第二阶段的显式同步（但即使如此也可能出现问题⁹）。这种阶段划分是粗略的，因为检查点执行机制会把同步请求排成队列；如果队列已满，就会暂停写入，先同步已经写入的块，然后恢复写入。下面的字段分别显示这两个阶段所耗费的时间（两个值的单位都是毫秒）：

checkpoint_write_time——写入页面所耗费的时间；
checkpoint_sync_time——执行同步所耗费的时间。

第一个阶段的页面写入占检查点的大部分时间，而同步是最后阶段，通常要快得多。不过，同步速度取决于磁盘子系统的性能：在速度较慢的存储设备上，同步阶段可能积压输入/输出请求队列，从而增加延迟并降低性能。因此，同步缓慢说明磁盘子系统的性能不足。通过这些字段的值，可以跟踪同步所需的时间；如果同步变慢，就应评估检查点对查询性能的影响（检查点同步期间查询执行时间的变化）。

可以根据 postgres_checkpoints_seconds_total 指标绘制图表来跟踪执行时间（图 5.8）：

图中可以看出，检查点执行时间总体平稳，但在这段时间内有两个检查点的同步持续了约 10 秒。这与其他检查点相比很突出，足以引起关注并进一步查明为什么同步偶尔会持续这么长时间。

缓冲区写入。 下面的字段可以分析后台进程和后端进程写入的数据量：

buffers_checkpoint——检查点进程写入的缓冲区数量；
buffers_clean——后台写入进程写入的缓冲区数量；

buffers_backend——后端进程写入的缓冲区数量；
buffers_alloc——为存放数据页面而分配的缓冲区数量；
maxwritten_clean——后台写入进程因达到 bgwriter_lru_maxpages 参数指定的上限而暂停刷写脏页的次数。

可以把后台写入进程看作检查点进程的助手。它持续写入脏页，从而减少执行检查点所需的工作量。前两个指标显示这两个后台进程完成的工作量。第三个指标显示后端进程写入的缓冲区数量，其中也包括自动清理工作进程。回想一下，如果进程无法在缓存中找到所需的数据块，就必须从磁盘读取该块。为此，通常需要找到一个缓冲区（buffers_alloc），并淘汰其中已有的块。如果找到的缓冲区是脏的，还必须把缓冲区内容写入磁盘。后台写入进程会提前完成这项工作。

数据库管理系统的配置包含几个用于调节后台写入进程工作的参数¹⁰，因此这些参数会影响该进程在一次工作循环迭代中完成的工作量和产生的负载。通过 maxwritten_clean 指标，可以评估后台写入进程因达到 bgwriter_lru_maxpages 指定的上限而停止的频率。这样，结合 buffers_backend 和 maxwritten_clean 的值，可以评估后台写入进程的效率：两个值越小越好。配置时需要记住，后台写入进程会给输入/输出子系统带来负载。配置过于激进会让进程执行多余的输入/输出，从而可能损害其他进程的性能。

可以将上述指标绘制成图表（图 5.9）。图中可以看到，后台写入进程和检查点进程产生的负载比较稳定、平缓，而后端进程产生了两个写入请求峰值。需要进一步确认后台写入进程是否能够应对这种情况（参见 maxwritten_clean），或者这只是工作负载中的突发峰值。

还剩下两个字段需要考察：

buffers_backend_fsync——后端进程被迫自行执行同步的次数。同步操作通常交给检查点进程，但也可能由后端进程执行，这被视为不良信号。在我的数据库管理系统运维实践中，从未遇到过 buffers_backend_fsync 大于零的情况；
stats_reset——其他视图中已经熟悉的字段，表示统计信息重置的时间戳。通常，如果后台写入和检查点统计信息被收集到监控系统中，就不需要查看这个字段；但如果

使用该视图的统计信息生成汇总报告，就必须确保统计信息是在相对较长的时间段内累计的，例如 2–4 周。时间段过短的统计信息可能缺乏代表性，而时间段过长则会被过度平均，无法反映任何峰值和可能的异常。

最后还要指出，其他一些视图（例如 pg_stat_wal 或 pg_stat_replication_slots）也包含输入/输出统计信息。它们与用户数据相关的输入/输出关系较小，但可以作为数据库管理系统实例级输入/输出的信息来源。这些视图以及其他视图将在后面介绍 WAL 日志和复制的章节中讨论。

总结

pg_buffercache 视图可以从不同角度评估缓冲区缓存的使用情况。
可以使用 pg_shmem_allocations 视图分析共享内存的结构。
可以使用 pg_backend_memory_contexts 视图和 pg_log_backend_memory_contexts 函数分析后端进程内存的结构。
可以使用 pg_stat_slru 视图评估 SLRU 缓存的使用情况。
可以使用 pg_stat_database 评估总体输入/输出。
可以在相应的 pg_statio_* 视图中详细评估具体数据库对象的输入/输出。

可以使用 pg_stat_statements 视图评估查询执行期间的输入/输出。
可以使用 pg_stat_database 粗略评估临时文件。
可以使用 pg_stat_statements 评估查询执行期间与临时文件相关的输入/输出。
可以通过数据库管理系统的服务器日志跟踪临时文件。
跟踪活动临时文件很重要。
后台写入进程和检查点进程都会产生输入/输出，后者可能对整体性能产生显著影响。
应使用 pg_stat_bgwriter 视图评估后台写入和检查点进程产生的输入/输出。