三月 22, 2024
摘要：在本教程中，您将学习如何分析页面缓存对 PostgreSQL 性能的影响。
目录
介绍
您有没有遇到这样的问题：已经重启了 PostgreSQL 服务器，但是第二次运行同样的查询仍然快得多，这是为什么？
这个问题的答案很简单，因为重新启动数据库服务器只会清除数据库缓冲区的缓存，但是其他缓存没有变化，这些缓存是：
缓冲区缓存 - PostgreSQL 从磁盘加载包含表和索引的页面的共享缓冲池，以直接从内存工作，从而减少磁盘访问。
页面缓存 - 操作系统通常会缓存文件 IO，除非您通过使用 O_DIRECT 标志，或者以直接 IO 模式挂载文件系统，来显式跳过页面缓存。
硬件缓存 - CPU 状态缓存可能会轻微地影响到查询执行速度，但硬件 IO 缓存可能会造成巨大影响。其中一个是硬件 RAID 缓存，但更重要的是 SAN 缓存，它可能影响非常大。
准备工作
让我们通过一个示例来更好地了解，页面缓存会如何影响查询性能。
假设我们有一个名为t1的表：
CREATE TABLE t1 (id integer, str text);
下面是用于生成数据的示例 SQL 查询：
INSERT INTO t1 (id, str)
SELECT i, repeat('Pg', 64)
FROM generate_series(1, 1000000) AS s(i);
我们已经给此表填充了数百万行示例数据。
页面缓存示例
在我们观察页面缓存对查询性能的影响之前，我们需要先停止 PostgreSQL 服务器，首先以 root 帐户清理系统页面缓存：
# echo 3 > /proc/sys/vm/drop_caches
然后，启动 PostgreSQL 服务器。
现在，假设我们要检索总共的记录数：
SET max_parallel_workers_per_gather TO 0;
EXPLAIN (analyze, buffers) SELECT count(*) FROM t1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=32909.00..32909.01 rows=1 width=8) (actual time=439.977..439.978 rows=1 loops=1)
Buffers: shared read=20409
-> Seq Scan on t1 (cost=0.00..30409.00 rows=1000000 width=0) (actual time=0.244..349.652 rows=1000000 loops=1)
Buffers: shared read=20409
Planning:
Buffers: shared hit=13 read=6
Planning Time: 3.522 ms
Execution Time: 440.979 ms
(8 rows)
表现很好。让我们重新启动 PostgreSQL 服务器。
实际上，我们可以通过 pgfincore 来查看页面缓存的统计信息。
现在，让我们再次检索记录总数，看看它会如何影响性能：
SET max_parallel_workers_per_gather TO 0;
EXPLAIN (analyze, buffers) SELECT count(*) FROM t1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=32909.00..32909.01 rows=1 width=8) (actual time=199.904..199.906 rows=1 loops=1)
Buffers: shared read=20409
-> Seq Scan on t1 (cost=0.00..30409.00 rows=1000000 width=0) (actual time=1.131..113.739 rows=1000000 loops=1)
Buffers: shared read=20409
Planning:
Buffers: shared hit=13 read=6
Planning Time: 0.413 ms
Execution Time: 199.955 ms
(8 rows)
现在查询性能明显更好。我们已将执行时间缩短了两倍以上！