PostgreSQL数据读取快慢怎么办

魁首哥

作者

PostgreSQL数据读取快慢怎么办

这篇文章将为大家详细讲解有关PostgreSQL数据读取快慢怎么办，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

PostgreSQL使用shared_buffers来缓存内存中的块。其思想是减少磁盘I/O并以最有效的方式加速数据库。在正常操作期间，数据库缓存将非常有用，并确保良好的响应时间。但是，如果由于某种原因重新启动数据库实例，会发生什么情况呢?你的PostgreSQL数据库性能将受到影响，直到你的I/O缓存再次填满。这需要一些时间，而且可能会严重影响查询响应时间。

在PostgreSQL 11中，一个新的autoprewarm功能被添加到contrib模块pg_prewarm中。这将使用在最后一次服务器重新启动之前持有的相同页面自动温暖共享缓冲区。为了实现这一点，Postgres现在有一个后台工作程序来定期记录文件中共享缓冲区的内容——“autoprewarm.blocks”。接下来，它将在服务器重启后重新加载这些页面。

PostgreSQL可以通过 pg_prewarm 的方式来提供预热缓冲的作用，pg_prewam 提供两种功能，手动缓冲和自动缓冲。

如何安装pg_prewarm及autoprewarm，以下基于pg 11 版本，在PG11中如果你编译安装则默认的pg_prewarm 会被安装到PG 中，确切地说，“autoprewarm master”将定期在文件“$PGDATA/autoprewarm.blocks”中记录共享缓冲区中的页面信息。更新“autoprewarm”的频率。块”由配置参数pg_prewarm_autoprewarm_interval决定。一旦服务器重新启动，主服务器将读取“autoprewarm”。块”，并排序要预热的页面列表。接下来，它将每次为每个数据库启动一个worker。然后，每个数据库工作程序(即autoprewarm工作程序)将加载属于其数据库的页面。

安装只需要两步

1 需要在 shared_preload_libraries 中添加

当然你也可以执行下面的命令

altersystemsetshared_preload_libraries='pg_prewarm';

从启动服务器后，可以看到 autoprewarm master 的这个线程

并且在数据目录可以看到新生成的文件

2 在你的数据库中执行（与预热无关）

create extension pg_prewarm;

装载你的扩展

在做完这些我们做一个测试，我们在打开的预热的机器上，进行分析查询

可以看到第二次已经走 cache了

我们关闭postgresql 再从重启动PG ，再次运行，看到图中查询已经走了cache

如果我们关闭数据库，在关闭期间将autoprewarm.blocks删除后，在重新启动PG 我们看看结果如何

很明确的看到查询中没有走缓冲。

那文件到底存储了什么

第一行表示页面的总数，后面的每一行表示关于页面的信息。每个页面都由数据库oid、表空间oid、关系的relfilenode、fork文件号和块号惟一地表示。

我们可以看一下系统中的 pg_prewarm函数，在PG 中国函数是使用C语言撰写的，当然如果用C语言来写 PG 的函数，效率是很高的。

CREATE OR REPLACE FUNCTION public.pg_prewarm(

regclass,

mode text DEFAULT 'buffer'::text,

fork text DEFAULT 'main'::text,

first_block bigint DEFAULT NULL::bigint,

last_block bigint DEFAULT NULL::bigint)

RETURNS bigint

LANGUAGE 'c'

COST 1

VOLATILE PARALLEL SAFE

AS '$libdir/pg_prewarm', 'pg_prewarm'

;

其中会将四个参数传递给函数 1 将prewarm表名传递，2 prewarm 的模式 3第三个参数表的fork类型，最后最后两个是开始和结束的block number

我们会进行以下测试，看在不同的模式下，如果数据上送到缓冲中的速度的提升，以及不同模式有什么差异。

上面是一个单表1.3G 的SIZE 的表。

下面我们需要对这个表进行一个整体的 COUNT 运算，我们来看看使用缓冲和不使用有什么区别

1 我们可以先裸奔一下，1.3G 大小的表进行COUNT运算时，整体的速度在不到2秒可以给出结果（I am sorry, 我的I/O 系统是 SSD）

2 我们将数据进行预读

select pg_prewarm('bloom_table','read','main');

3 我们将模式调整为 buffer 模式

explain analyze select count(*) from bloom_table;

4 最后我们使用 prefetch 模式，异步的方式进行预读

基本上这三种方式没有特别大的差别，但实际上 read 为使用OS 系统缓冲的模式，buffer使用 postgresql 的buffer 模式， prefetch 为异步读取数据模式。另一个需要注意的地方，就是大表的读取到缓冲中与大表直接指向语句是不一样的，如果你认为仅仅通过select * from table 的方式就能将数据读入到缓冲，postgresql 是不会允许这样的事情发生的，一般这样的操作仅仅是能将很少一部分数据预读进缓冲。当然如果你的内存比较小的情况，你要注意这个问题，突然将一个大表读入缓冲带来的问题，可能是将正在使用的数据从你的buffer 中挤出，这可不是什么好的操作。