[转帖]PostgreSQL（三） 内存参数优化和原理（work_mem）内存表 pgfincore插件使用方法

postgresql,内存,参数,优化,原理,work,mem,pgfincore,插件,使用,方法 · 浏览次数 : 0

小编点评

**Pgfincore工具介绍** Pgfincore是一款用于数据预热的工具，当数据库启动时，查询效率是会慢的。但是，它可以降低查询效率，因为当数据被预热到内存中时，查询效率会提高。 **主要功能** * 数据预热 * 降低查询效率 * 提升查询效率 * 降低磁盘读写频率 **使用步骤** 1. 安装Pgfincore 2. 将数据预热到内存中 3. 启动数据库 4. 测试查询效率 **性能提升** * 数据预热可以减少磁盘读写频率 * 降低查询效率可以减少数据库查询效率 * 提升查询效率可以减少查询数据的读取频率 **其他特性** * 可用于数据预热 * 降低查询效率 * 提升查询效率 * 降低磁盘读写频率 * 可用于数据预热 **使用场景** * 对查询效率的要求比较高 * 查询的数据比较固定 * 系统读写频率不高的情况下 * 可以降低查询效率 **注意** * 当操作系统的写入太过频繁，导致缓存过多的话，会把加载到内存中的表挤出去。 * Pgfincore这个工具只是把表加载到内存中，但是没有把表固定在内存当中。

正文

1.常用内存参数

1.1 shared_buffers

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

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

测试语句：

  1. EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM viid_vehicle.vehiclestructured
  2.  WHERE recordid=1220098697128879629;
  3.  
  4. --运行结果
  5. Index Scan using idx_vehiclestructured_recordid on vehiclestructured  (cost=0.43..8.45 rows=1 width=167) 
  6. (actual time=61.687..61.689 rows=1 loops=1)
  7.    Index Cond: (recordid = '1220098697128879629'::bigint)
  8.    Buffers: shared read=4
  9.  Planning time: 295.504 ms
  10.  Execution time: 62.220 ms
  11.

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

  1. Index Scan using idx_vehiclestructured_recordid on vehiclestructured (cost=0.43..8.45 rows=1 width=167) 
  2. (actual time=0.018..0.019 rows=1 loops=1)
  3.    Index Cond: (recordid = '1220098697128879629'::bigint)
  4.    Buffers: shared hit=4
  5.  Planning time: 0.090 ms
  6.  Execution time: 0.063 ms

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

 

1.2 work_mem

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

例子：先设置work_mem为1MB。

SET work_mem='1MB';

测试语句：

  1. EXPLAIN ANALYZE SELECT * FROM viid_vehicle.vehiclestructured_test WHERE plateno = '浙G98948' ORDER BY vehiclebrand;
  2.  
  3. --运行结果
  4. Sort  (cost=162.14..162.23 rows=39 width=167) (actual time=310.765..310.772 rows=38 loops=1)
  5.    Sort Key: vehiclebrand
  6.    Sort Method: quicksort  Memory: 35kB
  7.    ->  Index Scan using idx_vehiclestructured_test_plateno on vehiclestructured_test  (cost=0.43..161.11 rows=39 width=167) (actual time=41.104..310.648 rows=38 loops=1)
  8.          Index Cond: ((plateno)::text = '浙G98948'::text)
  9.  Planning time: 194.344 ms
  10.  Execution time: 310.828 ms

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

测试语句：

  1. EXPLAIN ANALYZE SELECT * FROM viid_vehicle.vehiclestructured_test WHERE plateno LIKE '%S906%' ORDER BY vehiclebrand;
  2.  
  3. --运行结果
  4.  Sort  (cost=3919.22..3921.70 rows=991 width=167) (actual time=134.259..135.692 rows=3863 loops=1)
  5.    Sort Key: vehiclebrand
  6.    Sort Method: external merge  Disk: 720kB
  7.    ->  Bitmap Heap Scan on vehiclestructured_test  (cost=79.68..3869.90 rows=991 width=167) (actual time=105.735..129.871 rows=3863 loops=1)
  8.          Recheck Cond: ((plateno)::text ~~ '%S906%'::text)
  9.          Rows Removed by Index Recheck: 30
  10.          Heap Blocks: exact=3867
  11.          ->  Bitmap Index Scan on idx_vehiclestructured_test_plateno_like  (cost=0.00..79.43 rows=991 width=0) (actual time=105.213..105.213 rows=3893 loops=1)
  12.                Index Cond: ((plateno)::text ~~ '%S906%'::text)
  13.  Planning time: 471.028 ms
  14.  Execution time: 136.904 ms

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

将work_mem的值增加到2MB。

 SET work_mem='2MB';
  1. --运行结果
  2.   Sort  (cost=3919.22..3921.70 rows=991 width=167) (actual time=136.279..137.135 rows=3863 loops=1)
  3.    Sort Key: vehiclebrand
  4.    Sort Method: quicksort  Memory: 1123kB
  5.    ->  Bitmap Heap Scan on vehiclestructured_test  (cost=79.68..3869.90 rows=991 width=167) (actual time=113.936..134.132 rows=3863 loops=1)
  6.          Recheck Cond: ((plateno)::text ~~ '%S906%'::text)
  7.          Rows Removed by Index Recheck: 30
  8.          Heap Blocks: exact=3867
  9.          ->  Bitmap Index Scan on idx_vehiclestructured_test_plateno_like  (cost=0.00..79.43 rows=991 width=0) (actual time=113.368..113.368 rows=3893 loops=1)
  10.                Index Cond: ((plateno)::text ~~ '%S906%'::text)
  11.  Planning time: 636.118 ms
  12.  Execution time: 38.633 ms

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

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

 

1.3 maintenance_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操作，会导致其他的操作性能降低。

 

1.4 effective_cache_size

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

 

1.5 wal_buffers

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

 

2.内存和OS缓存

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

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

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

 

3.手动加载内存

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

3.1 pgfincore安装

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

https://gitee.com/mirrors/PgFincore?utm_source=alading&utm_campaign=repo

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

  1. tar  –zxvf  pgfincore-v1.1.1.tar.gz
  2. cd  pgfincore-1.1.1
  3. make
  4. make install

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

  1. su postgres
  2. psql

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

  1. CREATE EXTENSION pgfincore;
  2. --查看是否创建成功：
  3. SELECT * FROM pg_extension;
  4. --结果
  5. extname |  extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
  6. plpgsql   |       10 |           11 | f              | 1.0        |       
  7. pgfincore |       10 |         2200 | t              | 1.1.1      |     

 

3.2 pgfincore常用方法

pgfincore() 对象的cache情况

pgfadvise_willneed() 将对象手动刷入cache

pgfadvise_dontneed() 将对象手动刷出cache

pgsysconf() 操作系统的cache情况

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

  1. SELECT * FROM pgsysconf_pretty();
  2.     os_page_size | os_pages_free | os_total_pages
  3. -------------+--------------+----------------
  4.     4096 bytes    | 118 MB          | 32 GB
  5.

3.3 pgfincore性能测试

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

    测试语句：

  1. EXPLAIN ANALYZE SELECT * FROM viid_vehicle.vehiclestructured WHERE plateno LIKE '%S906%';
  2.  
  3. --运行结果
  4. Bitmap Heap Scan on vehiclestructured  (cost=83.84..1982.68 rows=496 width=167) (actual time=117.193..6559.904 rows=1913 loops=1)
  5.    Recheck Cond: ((plateno)::text ~~ '%S906%'::text)
  6.    Rows Removed by Index Recheck: 14
  7.    Heap Blocks: exact=1908
  8.    ->  Bitmap Index Scan on idx_vehiclestructured_plateno_like  (cost=0.00..83.72 rows=496 width=0) (actual time=74.204..74.205 rows=1927 loops=1)
  9.          Index Cond: ((plateno)::text ~~ '%S906%'::text)
  10.  Planning time: 260.415 ms
  11.  Execution time: 6561.209 ms

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

    测试语句：

  1. SELECT * FROM pgfadvise_willneed('viid_vehicle.vehiclestructured');
  2.  
  3. EXPLAIN  ANALYZE  SELECT * FROM viid_vehicle.vehiclestructured WHERE plateno LIKE '%S906%';
  4.  
  5.  
  6. Bitmap Heap Scan on vehiclestructured_a010000_20150101  (cost=83.84..1982.68 rows=496
  7. width=167) (actual time=74.726..88.282 rows=1913 loops=1)
  8.    Recheck Cond: ((plateno)::text ~~ '%S906%'::text)
  9.    Rows Removed by Index Recheck: 14
  10.    Heap Blocks: exact=1908
  11.    ->  Bitmap Index Scan on idx_vehiclestructured_a000000_20150101_plateno_like  (cost=
  12. 0.00..83.72 rows=496 width=0) (actual time=74.455..74.455 rows=1927 loops=1)
  13.          Index Cond: ((plateno)::text ~~ '%S906%'::text)
  14.  Planning time: 360.554 ms
  15.  Execution time: 88.953 ms

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

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

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

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

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

 

 

文章知识点与官方知识档案匹配，可进一步学习相关知识
PostgreSQL技能树首页概览6103 人正在系统学习中
postgresql 内存 参数 优化 原理 work mem pgfincore 插件 使用 方法

与[转帖]PostgreSQL（三） 内存参数优化和原理（work_mem）内存表 pgfincore插件使用方法相似的内容：

[转帖]PostgreSQL（三） 内存参数优化和原理（work_mem）内存表 pgfincore插件使用方法

1.常用内存参数 1.1 shared_buffers shared_buffers是PostgreSQL用于共享缓冲区的内存，是由8kb大小的块所形成的数组。PostgreSQL在进行更新、查询等操作时，首先从磁盘把数据读取到内存，之后进行更新，最后将数据写回磁盘。shared_buffers可以

postgresql 内存 参数 优化
0
[转帖]PostgreSQL 参数优化设置 32GB内存（推荐） 内存参数 检查点 日志参数 自动初始化参数shell脚本

1.修改参数列表 （1）执行计划 enable_nestloop = off #默认为on enable_seqscan = off #默认为on enable_indexscan = on enable_bitmapscan = on max_connections = 1000 #默认为100

postgresql 参数 优化 设置
0
[转帖]理解 postgresql.conf 的work_mem 参数配置

https://developer.aliyun.com/article/401250 简介： 主要是通过具体的实验来理解 work_mem 今天我们着重来了解 postgresql.conf 中的 work_mem 参数 官方文档描述如下： 指定在写入临时文件之前内部排序操作和散列表使用的内存量。

理解 postgresql conf work
0
[转帖]PostgreSQL任意命令执行漏洞利用（CVE-2019-9193）

https://zhuanlan.zhihu.com/p/143443516 最近没事曰曰内网，偶然发现了一个使用空密码的pg（是的，连爆破都省了）。用navicat连上去看了下几个库都是一些业务测试数据，没什么好收集；不死心，google了一下发现有个比较新的CVE好像可以操作一下~ 漏洞概述 最

postgresql 任意 命令 执行
0
[转帖]PostgreSQL中切换WAL是否会触发checkpoint

https://www.modb.pro/db/570143?utm_source=index_ori 内容概述 Oracle数据库中切换redo日志会触发检查点事件，那么在PostgreSQL中是否也会触发checkpoint事件呢？ Oracle中测试 [oracle@orcldb ~]$ sq

postgresql 切换 wal 是否
0
[转帖]备份VCSA内置Postgresql数据库

首先命令行远程登录到VCSA服务器，然后执行如下命令停掉VCSA的核心服务vmware-vpxd： vCenterServerAppliance:~ # service vmware-vpxd stop Stopping VMware vSphere Profile-Driven Storage S

备份 vcsa 内置 postgresql
0
[转帖]如何在CentOS 7上使用Barman备份，恢复和迁移PostgreSQL数据库

http://www.manongjc.com/detail/52-bdglcaimnhmjvkm.html 本文章向大家介绍如何在CentOS 7上使用Barman备份，恢复和迁移PostgreSQL数据库，主要内容包括其使用实例、应用技巧、基本知识点总结和需要注意事项，具有一定的参考价值，需要的

如何 centos 使用 barman
0
[转帖]gitlab:一次近乎完美的PostgreSQL版本大升级实践

作者 | Jose Finotto 译者 | 马可薇 策划 | 万佳 2020 年 5 月，我们与 OnGres 合作，对 GitLab 上的 Postgres 集群进行版本大更新，从 9.6 版本升级到 11 版本。升级全部在维护窗口内运行，没有丝毫差错；更新中所有涉及的内容、计划、测试，以及全流

gitlab 一次 近乎 完美
0
[转帖]墨天轮访谈 | IvorySQL王志斌—IvorySQL，一个基于PostgreSQL的兼容Oracle的开源数据库

https://zhuanlan.zhihu.com/p/532842415 分享嘉宾：王志斌 瀚高IvorySQL产品经理整理：墨天轮社区 导读 大家好，我是瀚高IvorySQL产品经理王志斌，IvorySQL是基于PostgreSQL的衍生开源项目。 我今天分享的内容主要分为三个部分：我们是谁？

天轮 访谈 ivorysql 一个
0
[转帖]postgresql 编译选项 --with-uuid=e2fs、--with-uuid=ossp 的理解

postgresql 的 rpm 包使用的是 ‘–with-uuid=e2fs’ postgresql 源码 configure 的帮助选项有 uuid 的几个选项，有啥区别？ # ./configure --help --with-uuid=LIB build contrib/uuid-ossp

postgresql 编译 选项 with
0
# 热门排行
微软 New Bing AI 申请与使用保姆级教程（免魔法） ChatGPT API使用介绍 ChatGPT开发实战 一篇带你了解如何使用纯前端类Excel表格构建现金流量表 手把手教你玩转 Excel 数据透视表 为什么 C# 可能是最好的第一编程语言 .NET 入门到高级路线 提高工作效率的神器：基于前端表格实现Chrome Excel扩展插件 React + Springboot + Quartz，从0实现Excel报表自动化 用Echarts实现前端表格引用从属关系可视化
© PmDaddy. 2023 PmDaddy教程网 All rights reserved.