商务服务
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/ , 查看更多   
最新文章
PICO启动“全民优化师”活动,征集VR游戏性能优化方案
创作期截至4月16日,评选期则是4月22日至5月9日(映维网Nweon 2025年03月31日)PICO正在举行名为“全民优化师”的活动,邀请你
TOSHIBA代理商东芝手机「TOSHIBA代理商」
TOSHIBA东芝半导体公司,在国际市场上盛名远扬,家喻户晓。在日本之外,东芝拥有100多家子公司和协作公司的庞大全球网络,仅海外
互传手机互传换机助手手机互传「互传手机互传换机助手」
互传手机互传换机助手,一般又称互传换机助手app。互传换机助手app是一款很受网友好评的手机换机工具。可以实现手机之间数据批量
噬心工具箱手机工具箱「噬心工具箱」
噬心工具箱这是一款方便好用的手机工具箱应用,用户可以使用软件轻松处理图片,超多使用功能,让用户生活更方便。噬心工具箱app
儋州清明假期旅游迎来热潮,民俗体验趣味十足
南海网4月6日消息(记者赵航)花落日渐暖,风起正清明,今年清明假期,踏青赏花趣味十足。4月5日,“福暖春日,泉享安康”温泉美
【教程】全民K歌,用手机唱卡拉OK,朋友一起听!手机k歌「【教程】全民K歌,用手机唱卡拉OK,朋友一起听!」
用手机软件来唱卡拉OK,我知道的有两个软件推荐给您,全民K歌和唱吧。两个软件功能都很强,以前年轻人似乎用唱吧的多一些,现在
繁荣“入境游”,“游”出“日常感”是关键
原标题:繁荣“入境游”,“游”出“日常感”是关键年初至今,我国入境游市场持续复苏,到清明节期间迎来小高峰。数据显示,清明
苹果iPhone14和13对比区别是什么 苹果美版值得买吗?手机壳多少钱「苹果iPhone14和13对比区别是什么 苹果美版值得买吗?」
处理器、屏幕、摄像头、内存等不同处理器相同:iPhone14的处理器没有变化,还是iPhone13 Pro上的满血版A15 仿生芯片,只有iPhone
两个人在线观看免费完整版日本手机在线播放「两个人在线观看免费完整版日本」
格式“武神主宰08集”是武神一部出色的武侠古装影片,充斥着大量精彩的主宰空手道、木棉三绝和剑术招式的武神视觉盛宴。它是主宰
北京:“以花为媒”培育新消费
  4月的北京,繁花似锦装点千年古都,满城春色中涌动着勃勃生机。  北京玉渊潭公园樱花绽放,吸引游客慕名赏花;元大都城垣