PG使用插件pg_prewarm实现数据预加热
Tags： PGpg_prewarm优化插件预加热
在MySQL中，在配置参数innodb_buffer_pool_dump_at_shutdown=1后，若在正常关闭MySQL时，就可以将内存缓冲区的信息 dump到一个文件内部（该文件名为ib_buffer_pool，为MySQL 5.6新特性），然后启动时通过加载该文件内部的块（需要配置innodb_buffer_pool_load_at_startup=1），实现对内存缓冲区的预热，从而提高数据库重启后的查询性能。
在PostgreSQL中，也有这种功能，只需要安装pg_prewarm插件即可。更好的消息是，从PG 9.4开始，pg_prewarm插件融入了 PostgreSQL发行版中，无需下载编译安装程序。
安装插件pg_prewarm
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | C:\Users\lhrxxt>psql -U postgres -h 192.168.66.35 -p 15433 Password for user postgres: psql (13.3) Type "help" for help. postgres=# select * from pg_available_extensions where name like '%prewarm%' order by name; name | default_version | installed_version | comment ------------+-----------------+-------------------+----------------------- pg_prewarm | 1.2 | | prewarm relation data (1 row) postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ pageinspect | 1.8 | public | inspect the contents of database pages at a low level pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (3 rows) postgres=# create extension pg_prewarm ; CREATE EXTENSION postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ pageinspect | 1.8 | public | inspect the contents of database pages at a low level pg_prewarm | 1.2 | public | prewarm relation data pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (4 rows) postgres=# \dx pg_prewarm List of installed extensions Name | Version | Schema | Description ------------+---------+--------+----------------------- pg_prewarm | 1.2 | public | prewarm relation data (1 row) postgres=# \dx+ pg_prewarm Objects in extension "pg_prewarm" Object description ------------------------------------------------------- function autoprewarm_dump_now() function autoprewarm_start_worker() function pg_prewarm(regclass,text,text,bigint,bigint) (3 rows) postgres=# \sf+ pg_prewarm 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 PARALLEL SAFE 1 AS '$libdir/pg_prewarm', $function$pg_prewarm$function$ |
主要函数pg_prewarm的参数含义如下：
regclass：要做prewarm的表名
mode：prewarm模式。prefetch表示异步地将数据预加载到os cache；read表示同步预取，最终结果和 prefetch 一样,但它是同步方式,支持所有平台；buffer表示同步读入PG的shared buffer，默认为 buffer
fork：relation fork的类型。一般用main，其他类型有visibilitymap和fsm，默认为main
first_block & last_block：first_block 表示开始 prewarm 的数据块，last_block 表示最后 prewarm 的数据块。表的first_block=0，last_block可通过pg_class的relpages字段获得
RETURNS int8：函数返回pg_prewarm处理的block数目（整型），pg_prewarm 函数返回的是加载后的数据块数
autoprewarm_dump_now表示在服务器启动期间没有配置自动预热功能时，可以使用此命令启动自动预热工作程序。autoprewarm_start_worker立马对 autoprewarm. blocks文件进行更新，如果自动预热进程当前没有运行，那么希望在下次重启之后运行它，这样做会很有用。本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
pg_prewarm使用
pg_prewarm 模块可以将数据预先加载到数据库缓存，也可以预先加载到操作系统缓存。
所以，预热有两种方式，
一种是手动调用pg_prewarm函数，用于将当前所需的数据装入内存。
另一种是自动执行，要要设置shared_preload_libraries参数。设置完毕后，系统将自动运行一个后台工作进程postgres: autoprewarm master，它定期将shared_buffers中的内容写入到文件 autoprewarm. blocks中，以便在重新启动数据库后，快速加载该文件内部的数据块，实现预热功能。
配置shared_preload_libraries参数：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | postgres=# show shared_preload_libraries; shared_preload_libraries -------------------------- pg_stat_statements (1 row) postgres=# alter system set shared_preload_libraries=pg_stat_statements,pg_prewarm; ALTER SYSTEM [pg13@lhrpg pgdata]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2021-08-03 17:20:04.458 CST [6464] LOG: redirecting log output to logging collector process 2021-08-03 17:20:04.458 CST [6464] HINT: Future log output will appear in directory "pg_log". done server started [pg13@lhrpg pgdata]$ postgres=# show shared_preload_libraries; shared_preload_libraries -------------------------------- pg_stat_statements, pg_prewarm (1 row) |
注意：
1、对于shared_preload_libraries，多个参数不要整体放在单引号中，可以每个单引号内一个参数，例如：
1 2 3 | alter system set shared_preload_libraries=pg_stat_statements,pg_prewarm; -- 或 alter system set shared_preload_libraries='pg_stat_statements','pg_prewarm'; |
2、变量shared_preload_libraries指定一个或者多个要在服务器启动时预载入的共享库。它包含一个由逗号分隔的库名列表，其中每个名称都会按LOAD命令的方式解析。项之间的空格会被忽略，如果需要在库名中包含空格或者逗号，请把库名放在双引号内。这个参数只能在服务器启动时设置。如果指定的库没有找到，服务器将无法启动。
3、重启数据库后可以看到多了一个进程postgres: autoprewarm master，如下所示：
1 2 3 4 5 6 7 8 9 10 11 | [pg13@lhrpg pgdata]$ ps -ef | grep pg13 pg13 6464 0 0 17:20 ? 00:00:00 /pg13/pg13/bin/postgres pg13 6465 6464 0 17:20 ? 00:00:00 postgres: logger pg13 6467 6464 0 17:20 ? 00:00:00 postgres: checkpointer pg13 6468 6464 0 17:20 ? 00:00:00 postgres: background writer pg13 6469 6464 0 17:20 ? 00:00:00 postgres: walwriter pg13 6470 6464 0 17:20 ? 00:00:00 postgres: autovacuum launcher pg13 6471 6464 0 17:20 ? 00:00:00 postgres: stats collector pg13 6472 6464 0 17:20 ? 00:00:00 postgres: autoprewarm master pg13 6473 6464 0 17:20 ? 00:00:00 postgres: logical replication launcher pg13 6679 6464 0 17:23 ? 00:00:00 postgres: postgres postgres 172.17.0.1(33765) idle |
文件autoprewarm.blocks：
1 2 | [pg13@lhrpg pgdata]$ ll -lrt autoprewarm.blocks -rw------- 1 pg13 postgres 3549 Aug 3 17:25 autoprewarm.blocks |
功能测试
我们来使用虚拟机测试一下，把shared_buffers为128MB，我们创建一个75MB的表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | [pg13@lhrpg pgdata]$ pgbench -i -s5 dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data (client-side)... 500000 of 500000 tuples (100%) done (elapsed 0.30 s, remaining 0.00 s) vacuuming... creating primary keys... done in 2.86 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 1.38 s, vacuum 0.50 s, primary keys 0.97 s). [pg13@lhrpg pgdata]$ postgres=# show shared_buffers; shared_buffers ---------------- 128MB (1 row) postgres=# select pg_size_pretty(pg_total_relation_size('pgbench_accounts')); pg_size_pretty ---------------- 75 MB (1 row) postgres=# select count(*) from pgbench_accounts; count -------- 500000 (1 row) |
接下来进行全表扫描测试：
1 2 3 4 5 6 7 8 9 10 | postgres=# explain (analyze,buffers) select * from pgbench_accounts; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Seq Scan on pgbench_accounts (cost=0.00..13197.00 rows=500000 width=97) (actual time=0.077..135.820 rows=500000 loops=1) Buffers: shared hit=2144 read=6053 Planning: Buffers: shared hit=9 dirtied=1 Planning Time: 0.196 ms Execution Time: 179.849 ms (6 rows) |
首次运行shared hit 2144，read 6053，时间需要180ms。
1 2 3 4 5 6 7 8 | postgres=# explain (analyze,buffers) select * from pgbench_accounts; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Seq Scan on pgbench_accounts (cost=0.00..13197.00 rows=500000 width=97) (actual time=0.072..137.052 rows=500000 loops=1) Buffers: shared hit=2176 read=6021 Planning Time: 0.100 ms Execution Time: 181.600 ms (4 rows) |
再运行一次shared hit 2176，read 6021。shared buffer命中有所提高，我们想把全部缓存到内存，多执行几次就行了。若要立即完成，则必须手工执行pg_prewarm函数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | postgres=# select * from pg_prewarm('pgbench_accounts'); pg_prewarm ------------ 8197 (1 row) postgres=# select current_setting('block_size'); current_setting ----------------- 8192 (1 row) postgres=# select 8197*8; ?column? ---------- 65576 (1 row) -- pg_prewarm 函数返回的是加载后的数据块数,这里返回的是 8197 个块, 我设置的数据库块大小为 8 k. 如果表比较大, 也可以指定表的 block 范围. postgres=# explain (analyze,buffers) select * from pgbench_accounts; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Seq Scan on pgbench_accounts (cost=0.00..13197.00 rows=500000 width=97) (actual time=0.033..70.017 rows=500000 loops=1) Buffers: shared hit=8197 Planning Time: 0.106 ms Execution Time: 108.810 ms (4 rows) |
手动执行后，可以看到现在全部都已经是shared_hit了，时间仅需108ms。
此时，我们再看一下我们的autoprewarm.blocks文件，这5列记录的分别是数据库的oid，表空间的oid，relfilenode，数据文件的ForkNumber和BlockNumber
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [pg13@lhrpg ~]$ cd $PGDATA [pg13@lhrpg pgdata]$ ll -h autoprewarm.blocks -rw------- 1 pg13 postgres 88K Aug 3 17:35 autoprewarm.blocks [pg13@lhrpg pgdata]$ more autoprewarm.blocks <<3844>> 0,1664,1262,0,0 0,1664,1260,0,0 12661,1663,1259,0,0 12661,1663,1259,0,1 12661,1663,1259,0,2 。。。 [pg13@lhrpg pgdata]$ more autoprewarm.blocks | grep 24793 | more 12661,1663,24793,0,8192 12661,1663,24793,0,8193 12661,1663,24793,0,8194 [pg13@lhrpg pgdata]$ more autoprewarm.blocks | grep 24793,0 | wc -l 8197 |
下一步，我们将重新启动数据库，看看启动后，是否有预热功能。
1 2 3 4 5 6 7 | [pg13@lhrpg pgdata]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2021-08-03 17:43:34.758 CST [7966] LOG: redirecting log output to logging collector process 2021-08-03 17:43:34.758 CST [7966] HINT: Future log output will appear in directory "pg_log". done server started |
告警日志输出：
1 2 3 4 5 6 7 8 9 10 11 12 13 | 2021-08-03 17:43:34.630 CST [6464] LOG: received fast shutdown request 2021-08-03 17:43:34.632 CST [6464] LOG: aborting any active transactions 2021-08-03 17:43:34.632 CST [6679] FATAL: terminating connection due to administrator command 2021-08-03 17:43:34.633 CST [6464] LOG: background worker "logical replication launcher" (PID 6473) exited with exit code 1 2021-08-03 17:43:34.647 CST [6467] LOG: shutting down 2021-08-03 17:43:34.662 CST [6464] LOG: database system is shut down 2021-08-03 17:43:34.758 CST [7966] LOG: starting PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2021-08-03 17:43:34.759 CST [7966] LOG: listening on IPv4 address "0.0.0.0", port 5433 2021-08-03 17:43:34.759 CST [7966] LOG: listening on IPv6 address "::", port 5433 2021-08-03 17:43:34.762 CST [7966] LOG: listening on Unix socket "/pg13/pgdata/.s.PGSQL.5433" 2021-08-03 17:43:34.775 CST [7968] LOG: database system was shut down at 2021-08-03 17:43:34 CST 2021-08-03 17:43:34.780 CST [7966] LOG: database system is ready to accept connections 2021-08-03 17:43:34.958 CST [7975] LOG: autoprewarm successfully prewarmed 9908 of 9908 previously-loaded blocks |
重新启动后，您会发现日志中打印出一条的信息autoprewarm successfully prewarmed，它表示预热成功。再次执行查询，可以看到全部数据都是shared hit。
1 2 3 4 5 6 7 8 9 10 | postgres=# explain (analyze,buffers) select * from pgbench_accounts; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Seq Scan on pgbench_accounts (cost=0.00..13197.00 rows=500000 width=97) (actual time=0.026..71.136 rows=500000 loops=1) Buffers: shared hit=8197 Planning: Buffers: shared hit=60 Planning Time: 0.984 ms Execution Time: 110.285 ms (6 rows) |
参数配置
1 2 3 4 5 6 | postgres=# SELECT name, setting, unit FROM pg_settings WHERE name LIKE 'pg_prewarm%'; name | setting | unit ---------------------------------+---------+------ pg_prewarm.autoprewarm | on | pg_prewarm.autoprewarm_interval | 300 | s (2 rows) |
有2个参数设置，pg_prewarm.autoprewarm表示是否启动预热功能，pg_prewarm.autoprewarm_interval表示进程将在多少秒内刷新一次至autoprewarm. blocks文件，默认情况下为300秒刷一次。
官网
http://postgres.cn/docs/13/pgprewarm.html
pg_prewarm模块提供一种方便的方法把关系 数据载入到操作系统缓冲区或者 PostgreSQL缓冲区。可以使用pg_prewarm函数手工执行预热，或者通过在shared_preload_libraries中包括pg_prewarm来自动执行预热。在后一种情况中，系统将运行一个后台工作者，它会周期性地把共享内存中的内容记录在一个名为autoprewarm.blocks的文件中，并且在重新启动后用两个后台工作者重新载入那些块。
1. 函数
1 2 3 | pg_prewarm(regclass, mode text default 'buffer', fork text default 'main', first_block int8 default null, last_block int8 default null) RETURNS int8 |
第一个参数是要预热的关系。第二个参数是要使用的预热方法，下文将会 进一步讨论。第三个参数是要被预热的关系分叉，通常是main。 第四个参数是要预热的第一个块号（NULL也被接受，它等同于 零）。第五个参数是要预热的最后一个块号（NULL表示一直 预热到关系的最后一个块）。返回值是被预热的块数。
有三种可用的预热方法。prefetch会向操作系统发出异步 预取请求（如果支持异步预取），不支持异步预取则抛出一个错误。 read会读取要求范围的块。与prefetch 不同，它是同步的并且在所有平台上都被支持，但是可能较慢。buffer会把要求范围的块读入道数据库的缓冲区。
注意使用任意一种方法尝试预热比能缓存的数量更多的块 — 使用 prefetch或者read（由 OS）或者使用 buffer（由PostgreSQL ） — 将很可能导致高编号块被读入时把低编号的块从缓冲区中逐出的情况。 被预热的数据也不享受对缓冲区替换的特别保护，因此其他系统活动可能会在刚刚 被预热的块被读入后很快就将它们逐出。反过来，预热也可能把其他数据逐出缓存。 由于这些原因，预热通常在启动时最有用，那时缓冲大部分都为空。
1 | autoprewarm_start_worker() RETURNS void |
启动主要的autoprewarm工作者。这通常将会自动发生，但是如果没有在服务器启动时配置自动预热并且用户希望在稍晚的时候启动该工作者，这个函数就能发挥作用。
1 | autoprewarm_dump_now() RETURNS int8 |
立即更新autoprewarm.blocks。如果autoprewarm工作者没有运行但用户希望它在下一次重启后运行，则这个函数会很有用。返回值是写入到autoprewarm.blocks中的记录数。
2. 配置参数
pg_prewarm.autoprewarm(boolean)控制服务器是否应该运行autoprewarm工作者。默认这个参数为on。这个参数只能在服务器启动时设置。
pg_prewarm.autoprewarm_interval(int)这是更新
autoprewarm.blocks的间隔。默认是300秒。如果被设置为0，该文件将不会以常规的间隔方式转储，而是只在服务器关闭时转储。
其他问题
可能有人会想：我直接将表select *全表查询一遍不就可以将数据加载到缓存中了嘛，为什么还需要使用pg_prewarm呢？因为对于大小超过shared_buffer/4的表进行全表扫描时，pg一般不会使用全部的shared_buffer，而是只使用很少一部分的shared_buffer。所以，将大表加载到缓存中不能用一个查询来直接实现的，而pg_prewarm正好可以满足这个需求。
