PostgreSQL将数据加载到buffer cache中操作方法

2024-11-29 09:42:23

我们都知道数据在缓存中访问远比在磁盘中访问速度要快，那么我们怎么在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其它介绍：

下面主要介绍下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限制用户登录错误次数的实例代码

在oracle中我们可以通过设置FAILED_LOGIN_ATTEMPTS来限制用户密码登录错误的次数,但是在postgresql中是不支持这个功能的.尽管PostgreSQL支持event trigger,可是event局限于DDL,对于登录登出事件是没办法使用event trigger的. 不过像登录新建会话触发某个事件这个需求可以通过hook实现,不过该方法比较复杂,需要修改内核代码,在客户端认证中添加逻辑,判断输入密码次数统计.这里推荐一种比较简单的方法实现类似的功能. 这里我们要使用到
自定义函数实现单词排序并运用于PostgreSQL(实现代码)

Python实现逻辑 1.按照分隔符将字符串分割为列表类型 drugs.split(separator) 2.列表排序 drug_list.sort() 3.列表拼接 separator.join(drug_list) 4.类型判断由于数据库中对应字段可能会出现为NULL的情况,即在Python中为None,而类型None 无split() 用法,因此添加条件判断是否为真.如果为NULL,则直接返回None. Python实现 def 单词排序(drugs,separator): if dru
postgresql 中的时间处理小技巧(推荐)

时间格式处理按照给定格式返回:to_char(timestamp,format) 返回相差的天数:(date(time1) - current_date) 返回时间戳对应的的日期[yyyy-MM-dd]:date(timestamp) 计算结果取两位小数(方便条件筛选):round((ABS(a-b)::numeric / a), 2) * 100 < 10 时间运算加减运算 '-' :前x天/月/年 '+' :后x天/月/年 current_timestamp - interval 'x
postgresql影子用户实践场景分析

在实际的生产环境 ,我们经常会碰到这样的情况:因为业务场景需要,本部门某些重要的业务数据表需要给予其他部门查看权限,因业务的扩展及调整,后期可能需要放开更多的表查询权限.为解决此种业务需求,我们可以采用创建视图的方式来解决,已可以通过创建影子用户的方式来满足需求,本文主要介绍影子用户的创建及授权方法. 场景1:只授予usage on schema 权限 session 1: --创建readonly用户,并将test模式赋予readonly用户. postgres=# create user r
在PostgreSQL中使用ltree处理层次结构数据的方法

在本文中,我们将学习如何使用PostgreSQL的ltree模块,该模块允许以分层的树状结构存储数据. 什么是ltree? Ltree是PostgreSQL模块.它实现了一种数据类型ltree,用于表示存储在分层树状结构中的数据的标签.提供了用于搜索标签树的广泛工具. 为什么选择ltree? ltree实现了一个物化路径,对于INSERT / UPDATE / DELETE来说非常快,而对于SELECT操作则较快通常,它比使用经常需要重新计算分支的递归CTE或递归函数要快如内置的查询语法和专
PostgreSQL用户登录失败自动锁定的处理方案

墨墨导读:PostgreSQL使用session_exec插件实现用户密码验证失败几次后自动锁定,本文介绍一种处理方案. 一.插件session_exec安装配置篇下载插件并编译安装. https://github.com/okbob/session_exec $ unzip session_exec-master.zip $ cd session_exec-master/ $ make pg_config=/opt/pgsql/bin/pg_config $ make pg_config=/
PostgreSQL的中文拼音排序案例

前一段时间开发人员咨询,说postgresql里面想根据一个字段做中文的拼音排序,但是不得其解环境: OS:CentOS 6.3 DB:PostgreSQL 9.2.4 TABLE: tbl_kenyon 场景: postgres=# \d tbl_kenyon Table "public.tbl_kenyon" Column | Type | Modifiers --------+------+--------------- vname | text | --使用排序后的结果,不是
PostGreSql 判断字符串中是否有中文的案例

我就废话不多说了,大家还是直接看代码吧~ 实例 imos=# select 'hello' ~ '[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]'; ?column? ---------- f (1 row) imos=# imos=# select 'hello中国' ~ '[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]'; ?column? ---------- t (1 row) 补充:Post
如何使用PostgreSQL进行中文全文检索

开始安装首先是安装 PgSQL,这里我使用的是 PgSQL 9.6,PgSQL 10 也刚发布了,有兴趣的可以尝下鲜. PgSQL 的安装可以说非常复杂了,除了要安装 Server 和 Client 外,还需要安装 devel 包.为了实现空间索引功能,我们还要安装最重要的 PostGIS 插件,此插件需要很多依赖,自己手动安装非常复杂而且很可能出错. 推荐自动化方式安装,Yum 一定要配合 epel 这样的 Yum 源,保障能将依赖一网打尽.当然最好的还是使用 docker 来运行,找个镜
PostgreSQL将数据加载到buffer cache中操作方法

我们都知道数据在缓存中访问远比在磁盘中访问速度要快,那么我们怎么在pg中将指定的数据加载到缓存中呢,这有点类似于Oracle的in-memory. 当然要注意并不是把数据加载到内存中就一定是好的,因为相较于磁盘,内存总是有限的,所以一帮我们只是在特殊场合下将需要的数据加载到内存中来加快访问的速度. 我们可以使用pg_prewarm插件来将指定的表加载到OS Buffer或者pg shared buffer中. 安装: bill=# create extension pg_prewarm ; CR
Oracle 高速批量数据加载工具sql*loader使用说明

SQL*Loader(SQLLDR)是Oracle的高速批量数据加载工具.这是一个非常有用的工具,可用于多种平面文件格式向Oralce数据库中加载数据.SQLLDR可以在极短的时间内加载数量庞大的数据.它有两种操作模式. 传统路径:(conventional path):SQLLDR会利用SQL插入为我们加载数据. 直接路径(direct path):采用这种模式,SQLLDR不使用SQL:而是直接格式化数据库块. 利用直接路径加载,你能从一个平面文件读数据,并将其直接写至格式化的数据库块,而绕
Oracle数据加载和卸载的实现方法

在日常工作中:经常会遇到这样的需求: Oracle 数据表跟文本或者文件格式进行交互:即将指定文件内容导入对应的 Oracle 数据表中:或者从 Oracle 数据表导出. 其他数据库中的表跟Oracle数据库进行交互. 若是少量数据:可选择的解决方案有很多.常用的用 Pl/SQL developer工具,或者手动转换为 INSERT 语句,或者通过API.但数据量大:用上面的方法效率太烂了.本文来说说 Oracle 数据的加载和卸载. Oracle中的DBLINK Oracle加载数据-外部表
解决JSON数据因为null导致数据加载失败的方法

一.首先分析问题: 使用NSJSONSerialization或者AFN框架的AFHTTPSessionManager(底层也是NSJSONSerialization)将NSData数据转化成OC对象,有时会出现URL正确,加载数据任然会报错: reason: '-[NSNull length]: unrecognized selector sent to instance 分析原因发现,转化出来的OC对象中含有null.所以,NSNull没有length方法,所以会报找不到方法错误. 二.解决
C#使用Jquery zTree实现树状结构显示异步数据加载

C#使用Jquery zTree实现树状结构显示_异步数据加载 JQuery-Ztree下载地址:https://github.com/zTree/zTree_v3 JQuery-Ztree数结构演示页面: http://www.treejs.cn/v3/demo.php#_101 关于zTree的详细解释请看演示页面,还有zTree帮助Demo. 下面简要讲解下本人用到的其中一个实例(直接上关键代码了): 异步加载节点数据: A-前台: <link href="zTree_v3-mas
使用加载图片解决在Ajax数据加载中页面出现短暂空白的问题(推荐)

在项目中用ajax异步获取数据后有时会因为数据问题或者网络问题,页面一直显示空白,现在用加载图片来过渡这种状态: <script> $(function(){ $.ajax({ url:'',//提供接口的文件地址链接 dataType:'json', type:'POST', beforeSend: function(){ $('#loading').html("<img src=\"images/loading.gif\" width=\"15
jQuery+Ajax请求本地数据加载商品列表页并跳转详情页的实现方法

效果: json文件: { "books":[ {"id":1,"imgUrl":"images/ly.jpg","price":"45.00","title":"论语","publish":"人民文学出版社","num":"303","desc":
easyui datagrid 大数据加载效率慢,优化解决方法(推荐)

在使用easyui datagrid途中发现加载数据的效率真的不是一般的差.经测试IE8加载300条数据就感觉明显的慢了,加载2000条数据就另人崩溃用时差不多60秒,就算在google浏览器测试结果也快不了几秒. 平时听闻easyui datagrid效率底下,自己测试才发现真是使人无法忍受. 笔者只好百度,google解决方法,发现一篇文章说改 //1.3.3版本是这样的,其它版本也是这句代码 $(_1e0).html(_1e4.join("")); 改为: $(_1e0)[0].
Android自定义加载控件实现数据加载动画

本文实例为大家分享了Android自定义加载控件,第一次小人跑动的加载效果眼前一亮,相比传统的PrograssBar高大上不止一点,于是走起,自定义了控件LoadingView去实现动态效果,可直接在xml中使用,具体实现如下 package com.*****.*****.widget; import android.content.Context; import android.graphics.drawable.AnimationDrawable; import android.util.
vue在使用ECharts时的异步更新和数据加载详解

前言最近在学习eCharts,学习到了异步更新和数据加载这一块,觉着有必要总结一下,方法以后的时候参考学习,在开始本文之前,对eCharts不熟悉的朋友们可以参考下这篇文章:http://www.jb51.net/article/128790.htm 下面话不多说了,来一起看看详细的介绍吧. 使用方法使用Echarts首先得先把Echarts.js引进来(放在文件的入口html文件里面) <script src="public/js/echarts.common.min.js&quo

PostgreSQL将数据加载到buffer cache中操作方法

安装：

性能测试：

性能测试：

pg_prewarm其它介绍：

参考链接：

相关推荐

随机推荐