商务服务
PostgreSQL(三) 内存参数优化和原理(work_mem)内存表 pgfincore插件使用方法
2024-12-28 06:47

shared_buffers是PostgreSQL用于共享缓冲区的内存,是由8kb大小的块所形成的数组。PostgreSQL在进行更新、查询等操作时,首先从磁盘把数据读取到内存,之后进行更新,最后将数据写回磁盘。shared_buffers可以暂时存放从磁盘读取的数据,能够让用户下次访问不需要去磁盘直接从里面读取出来,增加查询效率。shared_buffers的系统默认值通常为128MB。但是当PostgreSQL服务器的内存大于1G时,则shared_buffers的合理起始值为系统内存的25%,如果由于业务原因可以将shared_buffers设置的更大,但是PostgreSQL依赖于操作系统的缓存,因此建议分配不要超过系统内存的40%。建议设置系统内存的25%-40%。

PostgreSQL(三) 内存参数优化和原理(work_mem)内存表 pgfincore插件使用方法

在PostgreSQL中,shared_buffers可以通过explain (analyze,buffers)来查看出多少数据块来源磁盘,多少来源shared_buffers,即内存的。

测试语句

 

再次运行同样的查询计划,运行结果

 

shared read(共享读取)指的是其取自磁盘且不被缓存。再次执行该查询计划后,结果以shared hit的形式展现,hit就是读取shared_buffers的。

 

work_mem是PostgreSQL在写入临时磁盘文件之前,进行内部sort(order by)和hash(join)操作需要使用的内存量。work_mem需要通过explain analyze分析语句来确定合适的值。

例子:先设置work_mem为1MB。

 

测试语句

 

看到当前的Sort Method为quicksort(快速排序),对38行数据进行排序,PostgreSQL使用了35kb内存,接下来增大排序的数据量

测试语句

 

根据上面可以看出来,当排序的数据量达到3893行时,Sort Method由使用内存排序到需要借助临时磁盘文件720kb,说明work_mem设置的值已经被占满了。

将work_mem的值增加到2MB。

 
 

    可以看到当设置的work_mem到2MB时,3863行数据的排序全会在内存中进行,使用了1123kb,同时执行时间从原来136ms到38ms。

实际生产环境中, work_mem值需要经过多次测试才能设定比较合理合适的值。需要注意的是work_mem是每单个连接用户使用的内存,也就是实际需要的内存为max_connections * work_mem,必须保证max_connections*work_mem的值不要超过实际可用的内存。

 

  maintenance_work_mem定义的内存主要影响vacuum,analyze,create index,reindex等操作,这些命令用到的频率不高,但是会消耗较多的资源,系统默认值通常为64MB,可以分配高一点的内存,让上述命令进行地快速一点。当系统内存为32G时,可以分配512MB-1024MB的内存。

1.3.1 vacuum

  上面提到了vacuum,在这里详细解释一下,在PostgreSQL中vacuum是一个非常重要的命令。数据库数据在不断地执行更新,删除等操作,而vacuum就是用来维护数据库磁盘空间。

    在PostgreSQL中实际上执行了delete操作后,该记录只是被标示为删除状态,并没有释放其空间,在以后进行insert和update操作时,该删除状态的空间是不能够被重用的。只有经过vacuum清理后,空间才能真正得到释放。总结一下,vacuum有以下三个功能

    (1). 释放delete操作后的空间,并且再利用。

    (2). 更新PostgreSQL查询计划用得统计数据。

    (3). 避免事务ID的重置而引起非常老的数据丢失。

    执行vacuum有手动和自动两种形式:1.自动vacuum的执行由autovacuum参数值决定,默认值为on,autovacuum还有很多相关的参数。2.手动执行vacuum一般是在每天数据库空闲的时候进行,因为执行vacuum的时候,有大量的I/O操作,会导致其他的操作性能降低。

 

effective_cache_size是优化器假设查询可以使用的最大内存(包括PostgreSQL和系统缓存),和shared_buffer内存无关,只是给优化器生产计划使用的一个假设值。该值建议设置为可用空闲内存的25%-50%,设置值大点,可以让优化器更倾向使用索引扫描而不是顺序扫描。

 

    wal_buffers是用于还没写入磁盘的WAL(预写入日志)数据的共享内存量,主要影响数据库的写入性能。该参数的默认值为-1,PostgreSQL会自动选择一个合理的值。但是一般情况给的值都不超过1MB,wal_buffers的内容是在每次事务提交时写入磁盘,因此设置太大的值也没有用处。但是根据官网原话,将此值设置为至少几兆可以提高许多客户端一次提交的写入性能。

 

PostgreSQL是一个跨平台数据库,其缓存很大程度上依赖于操作系统。而shared_buffers共享缓存其实就是在复制OS缓存的操作,下图1为数据在PostgreSQL内部的流向图

图1 数据在PostgreSQL内部的流向图

先来简单说明一下数据库的读和写:数据从磁盘流入内存叫读;数据从内存流入磁盘叫做写。在PostgreSQL中数据从磁盘到OS存储器再到shared_buffers的过程叫做读。针对这个现象,平时OS缓存有很大一部分是处于空闲状态或者是用得并不频繁,因此根据自身的生产环境需求可以合理来配置shared_buffers和OS缓存。

 

  pgfincore是一个PostgreSQL的外部扩展,能够将数据库中的表或索引手动提前加载到OS cache中,是数据预加载的一种,能够加快查询效率,但是加载到缓存中也不是永久的,当有频繁的缓存交换时,比如批量的最大限度的插入数据,会把加载的数据挤出去。

    在pgfoundry(搜索软件搜即可)或者

 

上下载pgfincore的安装包,将其解压到PostgreSQL源码目录的contrib下

 

    安装好了之后切换postgres用户进入psql

 

    创建扩展pgfincore(注意,需要在使用的库名下创建才可以使用)

 

 

pgfincore() 对象的cache情况

pgfadvise_willneed() 将对象手动刷入cache

pgfadvise_dontneed() 将对象手动刷出cache

pgsysconf() 操作系统的cache情况

pgsysconf_pretty() 同上,只不过输出更易懂,戴上了单位

 
 

(1). 测试没有将表添加到内存中

    测试语句

 

(2). 测试将表添加到内存中

    测试语句

 

    可以看出当将表手动加载到系统内存中时,查询效率是成倍地递增。

    Pgfincore适用于数据预热,当数据库启动时,查询效率是会慢的,这样能够提升效率。

    适用于下面这个生产场景:对查询效率的要求比较高,查询的数据比较固定,且系统的读写频率不高的情况下,可以使用。

    注意:当操作系统的写入太过频繁,导致缓存过多的话,会把加载到内存中的表挤出去。

    Pgfincore这个工具只是把表加载到内存中,但是没有把表固定在内存当中。

 

    以上就是本篇文章【PostgreSQL(三) 内存参数优化和原理(work_mem)内存表 pgfincore插件使用方法】的全部内容了,欢迎阅览 ! 文章地址:http://fabua.ksxb.net/news/4753.html 
     文章      相关文章      动态      同类文章      热门文章      栏目首页      网站地图      返回首页 海之东岸资讯移动站 http://fabua.ksxb.net/mobile/ , 查看更多   
最新文章
瑞莱智慧朱萌:做AI安全先锋,瑞莱智慧的智慧与担当 | 2025向新·AI未来
前言:2025年,AI撕裂数据与实体的边界,量子计算探索物理规则的边缘,绿色科技重塑增长的伦理,低空经济、机器人、大模型打开科
郴州地王大厦范围二手房, 解析 | 2025购房必看攻略,避坑省钱秘籍,核心价值实测,抢抓30%优惠!
郴州地王大厦范围二手房, 解析 | 2025购房必看攻略,避坑省钱秘籍,核心价值实测,抢抓30%优惠!郴州地王大厦市中心地标,交通便
90亿美元鲸落无声:22万倍收益离场背后的比特币新旧资本交替
作者:马丁一场价值90亿美元的比特币交易,揭开了加密世界新旧资本交替的序幕。7月末,加密货币市场见证了一场史诗级交易:数字资
易普力股票三个交易日涨幅偏离值超20%
雷达财经 文|杨洋 编|李亦辉7月23日,(002096)发布公告称,公司股票于2025年7月21日至23日连续三个交易日内收盘价格涨幅偏离值
泉州下达补助资金!南安19.05万元
为加快泉州数字经济高质量发展,推动大数据产业提质增效,经过前期的申报和审核工作,泉州市下达2025年数字经济发展专项资金市级
屏幕后的秘密!电影频道7.29将播出《玩命直播》
1905电影网 一个寻常的一天,你收到一个叫“玩命直播”的网站链接,里面是一款真人大冒险直播游戏,游戏规则是:你可以成为观看
庆阳到厦门曾厝埯旅游小吃, 2025必看_小吃攻略解析_必吃榜单曝光_省钱秘籍解密_10大美味盘点!
庆阳到厦门曾厝埯旅游小吃, 2025必看_小吃攻略解析_必吃榜单曝光_省钱秘籍解密_10大美味盘点!从庆阳一路南下厦门曾厝埯是个宝藏
“京彩绽放 益路奔腾”——2025北京体彩“益动京城”公益活动温情启幕​​
2025年7月30日,国家奥林匹克体育中心手球训练馆内气氛热烈非凡,备受瞩目的北京体彩“益动京城”公益活动在此正式启动,今年活
西安市民办义务教育学校电脑随机录取结束,剩余计划 4315 人
7月30日上午,西安市2025年民办义务教育学校电脑随机录取工作在各相关区县、开发区同时进行。本报记者作为公众监督团成员,在碑