PostgreSQL将数据加载到buffer cache中操作方法（post在什么之后）深度揭秘

随心笔谈2年前发布编辑

127 0

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

我们都知道数据在缓存中访问远比在磁盘中访问速度要快，那么我们怎么在pg中将指定的数据加载到缓存中呢，这有点类似于Oracle的in-memory。

当然要注意并不是把数据加载到内存中就一定是好的，因为相较于磁盘，内存总是有限的，所以一帮我们只是在特殊场合下将需要的数据加载到内存中来加快访问的速度。

我们可以使用pg_prewarm插件来将指定的表加载到OS Buffer或者pg shared buffer中。

bill=# create extension pg_prewarm ;
CREATE EXTENSION

构建测试表t1，t2，分别插入1000W条测试数据

bill=# create table t1(id int,info text);
CREATE TABLE
bill=# create table t2(id int,info text);
CREATE TABLE
bill=# insert into t1 select generate_series(1,10000000),md5(random()::text);
INSERT 0 10000000
bill=# insert into t2 select generate_series(1,10000000),md5(random()::text);
INSERT 0 10000000

测试前先清空shared_buffer，可以使用下面sql查看shared_buffer使用情况：

安装pg_buffercache插件：

bill=# create extension pg_buffercache;
CREATE EXTENSION

查询shared_buffer使用情况：

SELECT
c.relname,
count(*) AS buffers
FROM pg_buffercache b
INNER JOIN pg_class c
ON b.relfilenode=pg_relation_filenode(c.oid)
AND b.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname=current_database()))
GROUP BY c.relname
ORDER BY 2 DESC;
relname | buffers
—————————————–+———
pg_attribute | 36
pg_proc | 27
pg_class | 15
pg_operator | 14
pg_depend_reference_index | 13
pg_depend | 11
pg_attribute_relid_attnum_index | 10
pg_proc_proname_args_nsp_index | 9
……

可以看到t1和t2表均不在shared_buffer中，我们来手动将t2表加载到shared_buffer中。

bill=# SELECT pg_prewarm(‘t2’);
pg_prewarm
————
83334
(1 row)

可以看到全表扫描t2表的性能要提升不少。

bill=# explain analyze select * from t1;
QUERY PLAN
——————————————————————————————————————
Seq Scan on t1 (cost=0.00..183334.80 rows=10000080 width=37) (actual time=0.060..772.902 rows=10000000 loops=1)
Planning Time: 0.294 ms
Execution Time: 1044.922 ms
(3 rows)

Time: 1045.722 ms (00:01.046)

bill=# explain analyze select * from t2;
QUERY PLAN
——————————————————————————————————————
Seq Scan on t2 (cost=0.00..183334.80 rows=10000080 width=37) (actual time=0.012..519.691 rows=10000000 loops=1)
Planning Time: 0.280 ms
Execution Time: 790.607 ms
(3 rows)

Time: 791.314 ms

下面主要介绍下pg_prewarm函数：

该函式的创建语句如下：

CREATE FUNCTION pg_prewarm(regclass,
mode text default buffer,
fork text default main,
first_block int8 default null,
last_block int8 default null)
RETURNS int8
AS MODULE_PATHNAME, pg_prewarm
LANGUAGE C

参数如下：

regclass：要做prewarm的表名
mode：prewarm模式。prefetch表示异步预取到os cache；read表示同步预取；buffer表示同步读入PG的shared buffer
fork：relation fork的类型。一般用main，其他类型有visibilitymap和fsm
first_block & last_block：开始和结束块号。表的first_block=0，last_block可通过pg_class的relpages字段获得
RETURNS int8：函数返回pg_prewarm处理的block数目（整型）

可能有人会想：我直接将表select *全表查询一遍不就可以将数据加载到缓存中了嘛，为什么还需要使用pg_prewarm呢？因为对于大小超过shared_buffer/4的表进行全表扫描时，pg一般不会使用全部的shared_buffer，而是只使用很少一部分的shared_buffer。所以，将大表加载到缓存中不能用一个查询来直接实现的，而pg_prewarm正好可以满足这个需求。

https://www.postgresql.org/docs/13/pgprewarm.html

https://www.postgresql.org/docs/13/pgbuffercache.html

到此这篇关于PostgreSQL将数据加载到buffer cache中的文章就介绍到这了,更多相关PostgreSQL数据加载buffer cache内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:PostGreSql 判断字符串中是否有中文的案例PostgreSQL的中文拼音排序案例自定义函数实现单词排序并运用于PostgreSQL(实现代码)在PostgreSQL中使用ltree处理层次结构数据的方法postgresql 中的时间处理小技巧(推荐)Postgresql限制用户登录错误次数的实例代码PostgreSQL用户登录失败自动锁定的处理方案postgresql影子用户实践场景分析如何使用PostgreSQL进行中文全文检索