PostgreSQL 教程: 页面缓存如何影响查询性能?

三月 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)

现在查询性能明显更好。我们已将执行时间缩短了两倍以上!