探索 PostgreSQL 中的 pg_buffercache 插件
一、引言
在 PostgreSQL 数据库的性能优化和内部机制理解中，了解缓冲区缓存（Buffer Cache）的工作情况是至关重要的。pg_buffercache 插件为我们提供了深入洞察缓冲区缓存内容和使用情况的能力。
二、pg_buffercache 插件概述
pg_buffercache 是一个用于查看 PostgreSQL 数据库缓冲区缓存状态的插件。它能够帮助数据库管理员和开发人员了解数据页在缓冲区中的缓存情况，包括哪些页被缓存、缓存的状态（如干净、脏）以及相关的元数据。
三、下载与安装
-
确保已经安装了 PostgreSQL 的开发包和所需的编译工具。
-
下载
pg_buffercache插件的源代码。可以从 PostgreSQL 的官方扩展库或其他可靠的来源获取。 -
解压源代码，并进入解压后的目录。
-
使用以下命令进行编译和安装：
make make install -
在 PostgreSQL 数据库中启用插件。在
postgresql.conf文件中添加以下行：shared_preload_libraries = 'pg_buffercache' -
重新启动 PostgreSQL 服务以使更改生效。
-
创建pg_buffercache插件
10:52:04 (lxsu@[local]:5416)testz=#create extension pg_buffercache ; CREATE EXTENSION 10:52:20 (lxsu@[local]:5416)testz=#
四、pg_buffercache 表结构及字段介绍
10:52:45 (lxsu@[local]:5416)testz=#\d pg_buffercache
View "public.pg_buffercache"
┌──────────────────┬──────────┬───────────┬──────────┬─────────┐
│ Column │ Type │ Collation │ Nullable │ Default │
├──────────────────┼──────────┼───────────┼──────────┼─────────┤
│ bufferid │ integer │ │ │ │
│ relfilenode │ oid │ │ │ │
│ reltablespace │ oid │ │ │ │
│ reldatabase │ oid │ │ │ │
│ relforknumber │ smallint │ │ │ │
│ relblocknumber │ bigint │ │ │ │
│ isdirty │ boolean │ │ │ │
│ usagecount │ smallint │ │ │ │
│ pinning_backends │ integer │ │ │ │
└──────────────────┴──────────┴───────────┴──────────┴─────────┘
10:53:00 (lxsu@[local]:5416)testz=#
pg_buffercache 表包含了以下关键字段及其与系统表的关联：
bufferid：这是缓冲区的唯一标识符。relfilenode：与pg_class.relfilenode相关联，用于标识特定的数据库对象（如表或索引）。reltablespace：对应pg_tablespace.oid，表示对象所属的表空间。relblocknumber：表示在数据库对象内的块编号。isdirty：布尔值，指示缓冲区中的页面是否为脏页（即已修改但尚未写回磁盘）。usagecount：表示该缓冲区被使用的次数。
通过这些字段，我们可以获取到缓冲区中每个页面的详细信息，并与系统表进行关联分析，以全面了解数据库对象在缓冲区缓存中的状态。
五、案例分析与详细实验
假设我们有一个频繁访问的表 lxs_t1 ，并且表中有大量的数据，我们想从多个维度深入了解它在缓冲区缓存中的情况。
实验准备
-
创建一个包含大量数据的
lxs_t1表，例如：10:53:17 (lxsu@[local]:5416)testz=#CREATE TABLE lxs_t1 ( lxs_id INT PRIMARY KEY, customer_id INT, lxs_date DATE, total_amount DECIMAL(10, 2) ); CREATE TABLE 10:53:21 (lxsu@[local]:5416)testz=#INSERT INTO lxs_t1 (lxs_id, customer_id, lxs_date, total_amount) SELECT generate_series(1, 100000), random() * 1000, CURRENT_DATE - random() * INTERVAL '30 days', random() * 1000; INSERT 0 100000 10:53:27 (lxsu@[local]:5416)testz=# -
创建相关索引以增加查询的复杂性和数据页的访问需求。
10:53:31 (lxsu@[local]:5416)testz=#CREATE INDEX idx_lxs_date ON lxs_t1 (lxs_date); CREATE INDEX 10:53:32 (lxsu@[local]:5416)testz=# -
执行一些复杂的查询操作，例如涉及连接、聚合和排序的查询，以确保相关数据页被加载到缓冲区缓存中。
SELECT customer_id, SUM(total_amount) AS total_spent FROM lxs_t1 WHERE lxs_date BETWEEN '2023-01-01' AND '2023-06-01' GROUP BY customer_id lxs BY total_spent DESC;
实验 1：缓存页面数量与脏页分析
-
运行
SELECT * FROM pg_buffercache WHERE relfilenode = (SELECT relfilenode FROM pg_class WHERE relname = 'lxs_t1');10:53:29 (lxsu@[local]:5416)testz=#SELECT * FROM pg_buffercache WHERE relfilenode = (SELECT relfilenode FROM pg_class WHERE relname = 'lxs_t1'); ┌──────────┬─────────────┬───────────────┬─────────────┬───────────────┬────────────────┬─────────┬────────────┬──────────────────┐ │ bufferid │ relfilenode │ reltablespace │ reldatabase │ relforknumber │ relblocknumber │ isdirty │ usagecount │ pinning_backends │ ├──────────┼─────────────┼───────────────┼─────────────┼───────────────┼────────────────┼─────────┼────────────┼──────────────────┤ │ 622 │ 16595 │ 1663 │ 16432 │ 0 │ 636 │ t │ 5 │ 0 │ │ 623 │ 16595 │ 1663 │ 16432 │ 0 │ 635 │ t │ 5 │ 0 │ ... ... ... │ 1545 │ 16595 │ 1663 │ 16432 │ 0 │ 0 │ t │ 5 │ 0 │ │ 1658 │ 16595 │ 1663 │ 16432 │ 2 │ 0 │ t │ 3 │ 0 │ └──────────┴─────────────┴───────────────┴─────────────┴───────────────┴────────────────┴─────────┴────────────┴──────────────────┘ (641 rows) 10:53:37 (lxsu@[local]:5416)testz=# -
分析返回的结果，关注以下方面：
- 缓存中
lxs_t1表的数据页数量。通过统计不同的relblocknumber值来确定。这可以让我们了解表的哪些部分被频繁缓存，以及整个表被缓存的程度。 - 脏页的数量及分布。脏页通常表示尚未写回磁盘的数据更改。观察哪些数据页是脏页，以及它们在表中的位置，可以推测出最近哪些操作产生了大量的未写入数据。
- 结合表的大小和访问模式，分析缓存页面数量是否合理。如果一个经常访问的区域没有被充分缓存，可能需要考虑调整缓冲区大小或优化查询。
- 缓存中
实验 2：缓存命中率评估
-
启用 PostgreSQL 的统计收集功能，设置相关参数（如
track_io_timing）。10:55:33 (lxsu@[local]:5416)testz=#set track_io_timing=on; SET 10:55:38 (lxsu@[local]:5416)testz=# -
执行一系列对
lxs_t1表的不同类型查询操作，包括简单的点查询、范围查询和复杂的连接查询。-
简单点查询：
SELECT * FROM lxs_t1 WHERE lxs_id = 5000;10:55:38 (lxsu@[local]:5416)testz=#SELECT * FROM lxs_t1 WHERE lxs_id = 5000; ┌────────┬─────────────┬────────────┬──────────────┐ │ lxs_id │ customer_id │ lxs_date │ total_amount │ ├────────┼─────────────┼────────────┼──────────────┤ │ 5000 │ 822 │ 2024-11-26 │ 915.88 │ └────────┴─────────────┴────────────┴──────────────┘ (1 row) 10:55:59 (lxsu@[local]:5416)testz=# -
范围查询：
SELECT * FROM lxs_t1 WHERE lxs_date > '2023-03-01';10:57:29 (lxsu@[local]:5416)testz=#SELECT * FROM lxs_t1 WHERE lxs_date > '2023-03-01'; ┌────────┬─────────────┬────────────┬──────────────┐ │ lxs_id │ customer_id │ lxs_date │ total_amount │ ├────────┼─────────────┼────────────┼──────────────┤ │ 1 │ 842 │ 2024-12-03 │ 862.88 │ │ 2 │ 588 │ 2024-12-14 │ 326.86 │ │ 3 │ 41 │ 2024-12-09 │ 965.65 │ │ 4 │ 93 │ 2024-12-14 │ 114.79 │ │ 5 │ 180 │ 2024-11-19 │ 448.84 │ │ 6 │ 284 │ 2024-12-13 │ 37.06 │ │ 7 │ 694 │ 2024-11-27 │ 842.59 │ │ 8 │ 851 │ 2024-12-02 │ 952.58 │ ...... ...... ...... -
连接查询：
SELECT o.lxs_id, c.customer_name FROM lxs_t1 o JOIN lxs_t2 c ON o.customer_id = c.customer_id;
-
-
在每次查询执行后，通过以下查询计算缓存命中率：
10:59:21 (lxsu@[local]:5416)testz=#SELECT sum(heap_blks_hit) AS buffers_hit, sum(heap_blks_read) AS buffers_read, (sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))) * 100 AS cache_hit_ratio FROM pg_statio_user_tables WHERE relname = 'lxs_t1'; ┌─────────────┬──────────────┬──────────────────────────┐ │ buffers_hit │ buffers_read │ cache_hit_ratio │ ├─────────────┼──────────────┼──────────────────────────┤ │ 104461 │ 0 │ 100.00000000000000000000 │ └─────────────┴──────────────┴──────────────────────────┘ (1 row) 11:00:22 (lxsu@[local]:5416)testz=# -
分析不同查询类型的缓存命中率差异，并探讨其原因。例如，范围查询可能由于涉及更多的数据页而导致较低的命中率。
-
根据命中率评估缓冲区缓存对查询性能的影响。如果命中率较低，可能需要优化查询、增加缓冲区大小或调整数据库参数。
实验 3：不同操作对缓存的影响
-
执行插入新数据的操作：
INSERT INTO lxs_t1 (lxs_id, customer_id, lxs_date, total_amount) VALUES (100001, 500, CURRENT_DATE, 500.50); -
执行更新数据的操作：
UPDATE lxs_t1 SET total_amount = 600.50 WHERE lxs_id = 50000; -
执行删除数据的操作：
DELETE FROM lxs_t1 WHERE lxs_id = 20000; -
在每次操作后，立即查看
pg_buffercache的信息，观察以下方面：- 新插入的数据是否立即被缓存。
- 更新操作如何影响原数据页在缓存中的状态（是否变为脏页）。
- 删除操作后，相关数据页是否从缓存中移除或标记为可重用。
-
重复执行上述操作多次，观察缓存的变化模式。分析不同操作对缓存的填充、替换和脏页生成的影响，以优化数据操作的性能。
实验 4：缓存与并发查询
- 启动多个并发的查询会话，同时对
lxs_t1表进行查询操作。- 会话 1：执行复杂的聚合查询，如计算每月的订单总额。
- 会话 2：执行基于日期范围的查询，获取特定时间段内的订单详情。
- 会话 3：进行分页查询，获取特定页的订单记录。
- 在每个会话中，记录查询的执行时间和性能指标。
- 同时观察
pg_buffercache，了解以下情况：- 并发查询时，
lxs_t1表的数据页在缓冲区中的竞争情况。是否存在多个会话频繁访问相同的数据页，导致缓存争用。 - 不同会话的查询是否有效地共享缓存中的数据页，以提高整体缓存命中率。
- 当缓冲区空间有限时，如何选择淘汰数据页，以及这对各个会话的查询性能产生的影响。
- 并发查询时，
- 通过调整并发度和查询的复杂性，进一步研究缓冲区缓存在并发环境下的行为和性能瓶颈。
六、注意事项
pg_buffercache插件提供的是瞬时信息，需要在特定的时间点进行观察和分析。- 执行
pg_buffercache相关的查询可能会有一定的性能开销，特别是在大型数据库中，因此应谨慎使用，避免在生产环境的高负载期间频繁运行。 - 理解缓冲区缓存的工作原理和
pg_buffercache的输出结果需要对 PostgreSQL 的存储架构有一定的了解。
通过以上更详细和深入的多维度实验，我们可以更全面地了解 pg_buffercache 插件在实际场景中的应用和效果，为进一步优化数据库性能提供有力的依据。
