【赵渝强老师】PostgreSQL的数据预热扩展pg_prewarm

PostgreSQL从开始就设计成可以扩展的。因此加载到数据库的扩展可以像它们被打包在数据库里一样。PostgreSQL源代码的contrib/目录里面有大量这种扩展。该目录下的README文件包含PostgreSQL扩展的相应帮助信息。它们包含转换工具、全文索引、XML工具、额外的数据类型和索引方法等等。PostgreSQL也支持单独开发额外的扩展。这里重点介绍一下数据预热扩展pg_prewarm。pg_prewarm扩展可以把关系数据预存入操作系统缓冲区或者PostgreSQL缓冲区从而提高PostgreSQL数据库读取数据的效率。PostgreSQL数据库可以通过两种不同的方式进行数据预热即数据的手工预热和数据的自动预热。视频讲解如下【赵渝强老师】PostgreSQL的数据预热扩展pg_prewarm一、 安装pg_prewarm扩展在使用数据预热功能之前需要先按照pg_prewarm扩展下面是具体的安装步骤。1进入PostgreSQL源码目录下的contrib/pg_prewarm目录。cd postgresql-15.3/contrib/pg_prewarm/2编译并安装pg_prewarm。make make install3修改postgresql.conf参数文件中的shared_preload_libraries参数。shared_preload_libraries file_fdw,postgres_fdw,pg_prewarm4重启PostgreSQL数据库服务器。5创建pg_prewarm扩展。postgres# create extension pg_prewarm;6查看pg_prewarm扩展的详细信息。postgres# \dx pg_prewarm# 输出的信息如下Objects in extensionpg_prewarmObject description-------------------------------------------------------functionautoprewarm_dump_now()functionautoprewarm_start_worker()functionpg_prewarm(regclass,text,text,bigint,bigint)(3 rows)# 从这里的输出信息可以看出pg_prewarm扩展自动创建了三个与数据预热相关的函数。二、 数据的手工预热数据的手工预热主要是通过函数pg_prewarm()完成下面展示了该函数的申明。pg_prewarm(regclass,mode text defaultbuffer,fork text defaultmain,first_block int8 default null,last_block int8 default null)RETURNS int8# 这里最重要的参数就是第一个参数regclass它表示要进行预热的表名函数的返回值是预热块的数量。下面通过一个具体的示例来演示如何使用pg_prewarm()函数。1创建一张表用于进行测试。postgres# create table prewarmtable(pid int,pname varchar(20));2往表中插入5000万条测试数据。postgres# insert into prewarmtable select n,name||n from generate_series(1,50000000) n;3执行一条简单的查询语句并查看相应的执行计划。postgres# explain (analyze,buffers,timing) select * from prewarmtable;# 输出的信息如下QUERY PLAN--------------------------------------------------------Seq Scan on prewarmtable(cost0.00..808940.56 rows50010856 width16)(actual time0.035..5339.230 rows50000000 loops1)Buffers: shared read308832 Planning: Buffers: shared hit14 read7 Planning Time: 0.347 ms Execution Time: 8385.500 ms(6 rows)# 从输出的结果可以看出此时从磁盘上读取了308832数据块。4再次执行同样的查询语句并查看相应的执行计划。postgres# explain (analyze,buffers,timing) select * from prewarmtable;# 输出的信息如下QUERY PLAN--------------------------------------------------------Seq Scan on prewarmtable(cost0.00..808940.56 rows50010856 width16)(actual time0.035..5304.973 rows50000000 loops1)Buffers: shared hit32 read308800 Planning Time: 0.030 ms Execution Time: 8351.421 ms(4 rows)# 从输出的结果可以看出第二次读取数据时有32个数据块被执行了缓存。5重启PostgreSQL数据库服务器以达到清空缓存的目的。6使用pg_prewarm()函数预热表prewarmtable的数据。postgres# select pg_prewarm(prewarmtable);pg_prewarm------------308832(1 row)7再次执行同样的查询语句并查看相应的执行计划。postgres# explain (analyze,buffers,timing) select * from prewarmtable;# 输出的信息如下QUERY PLAN--------------------------------------------------------Seq Scan on prewarmtable(cost0.00..808940.56 rows50010856 width16)(actual time0.017..5470.853 rows50000000 loops1)Buffers: shared hit16308 read292524 Planning: Buffers: shared hit3 read3 Planning Time: 0.120 ms Execution Time: 8620.384 ms(6 rows)# 从输出的结果可以看出由于使用了pg_prewarm()函数预热数据# 此时读取数据时有16308个数据块被执行了缓存。三、 数据的自动预热当pg_prewarm扩展安装配置成功后PostgreSQL数据库服务器会周期性地把共享内存中的内容记录在一个名为autoprewarm.blocks的文件中并在数据库服务器重新启动时读取该文件以达到数据预热的目的。文件autoprewarm.blocks默认保存在$PGDATA目录下如下所示。[postgresmydb pgsql]$pwd/home/postgres/training/pgsql[postgresmydb pgsql]$ lldata/autoprewarm.blocks-rw-------1 postgres postgres 425037...data/autoprewarm.blocks下面通过一个简单的测试来验证重启数据库服务器后数据是否会自动进行预热。1重启PostgreSQL数据库服务器以达到清空缓存的目的。2执行一条简单的查询语句并查看相应的执行计划。postgres# explain (analyze,buffers,timing) select * from prewarmtable;# 输出的信息如下QUERY PLAN--------------------------------------------------------Seq Scan on prewarmtable(cost0.00..808940.56 rows50010856 width16)(actual time0.026..8544.466 rows50000000 loops1)Buffers: shared hit16191 read292641 Planning: Buffers: shared hit18 read3 Planning Time: 0.461 ms Execution Time: 13426.254 ms(6 rows)# 从输出的结果可以看出# 此时读取数据时有16191个数据块被执行了自动预热加载到了缓存中。在默认情况下pg_prewarm扩展会每隔5分钟将内存中的数据写入文件autoprewarm.blocks中这是由参数pg_prewarm.autoprewarm_interval决定如下所示。postgres# show pg_prewarm.autoprewarm_interval ;# 输出的信息如下pg_prewarm.autoprewarm_interval---------------------------------5min(1 row)当重启PostgreSQL数据库服务器时通过后台进程autoprewarm master将预热数据文件autoprewarm.blocks重新加载到内存中。通过使用ps命令可以看到该进程的信息如下所示[postgresmydb pgsql]$ps-ef|grep postgres: postgres 107174 107172...postgres: checkpointer postgres 107175 107172...postgres: background writer postgres 107176 107172...postgres: walwriter postgres 107177 107172...postgres: autovacuum launcher postgres 107178 107172...postgres: archiver postgres 107179 107172...postgres: stats collector postgres 107180 107172...postgres: autoprewarm master postgres 107181 107172...postgres: logical replication launcher postgres 107186 107172...postgres: postgres postgres[local]idle