PostgreSQL（三） 内存参数优化和原理（work_mem）内存表 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，即内存的。

测试语句：

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

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

Index Scan using idx_vehiclestructured_recordid on vehiclestructured (cost=0.43..8.45 rows=1 width=167) 
(actual time=0.018..0.019 rows=1 loops=1)
   Index Cond: (recordid = '1220098697128879629'::bigint)
   Buffers: shared hit=4
 Planning time: 0.090 ms
 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';

测试语句：

EXPLAIN ANALYZE SELECT * FROM viid_vehicle.vehiclestructured_test WHERE plateno = '浙G98948' ORDER BY vehiclebrand;
 
--运行结果
Sort  (cost=162.14..162.23 rows=39 width=167) (actual time=310.765..310.772 rows=38 loops=1)
   Sort Key: vehiclebrand
   Sort Method: quicksort  Memory: 35kB
   ->  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)
         Index Cond: ((plateno)::text = '浙G98948'::text)
 Planning time: 194.344 ms
 Execution time: 310.828 ms

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

测试语句：

EXPLAIN ANALYZE SELECT * FROM viid_vehicle.vehiclestructured_test WHERE plateno LIKE '%S906%' ORDER BY vehiclebrand;
 
--运行结果
 Sort  (cost=3919.22..3921.70 rows=991 width=167) (actual time=134.259..135.692 rows=3863 loops=1)
   Sort Key: vehiclebrand
   Sort Method: external merge  Disk: 720kB
   ->  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)
         Recheck Cond: ((plateno)::text ~~ '%S906%'::text)
         Rows Removed by Index Recheck: 30
         Heap Blocks: exact=3867
         ->  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)
               Index Cond: ((plateno)::text ~~ '%S906%'::text)
 Planning time: 471.028 ms
 Execution time: 136.904 ms

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

将work_mem的值增加到2MB。

 SET work_mem='2MB';

--运行结果
  Sort  (cost=3919.22..3921.70 rows=991 width=167) (actual time=136.279..137.135 rows=3863 loops=1)
   Sort Key: vehiclebrand
   Sort Method: quicksort  Memory: 1123kB
   ->  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)
         Recheck Cond: ((plateno)::text ~~ '%S906%'::text)
         Rows Removed by Index Recheck: 30
         Heap Blocks: exact=3867
         ->  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)
               Index Cond: ((plateno)::text ~~ '%S906%'::text)
 Planning time: 636.118 ms
 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下：

tar  –zxvf  pgfincore-v1.1.1.tar.gz
cd  pgfincore-1.1.1
make
make install

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

su postgres
psql

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

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

 

3.2 pgfincore常用方法

pgfincore() 对象的cache情况

pgfadvise_willneed() 将对象手动刷入cache

pgfadvise_dontneed() 将对象手动刷出cache

pgsysconf() 操作系统的cache情况

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

SELECT * FROM pgsysconf_pretty();
    os_page_size | os_pages_free | os_total_pages
-------------+--------------+----------------
    4096 bytes    | 118 MB          | 32 GB
 

3.3 pgfincore性能测试

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

    测试语句：

EXPLAIN ANALYZE SELECT * FROM viid_vehicle.vehiclestructured WHERE plateno LIKE '%S906%';
 
--运行结果
Bitmap Heap Scan on vehiclestructured  (cost=83.84..1982.68 rows=496 width=167) (actual time=117.193..6559.904 rows=1913 loops=1)
   Recheck Cond: ((plateno)::text ~~ '%S906%'::text)
   Rows Removed by Index Recheck: 14
   Heap Blocks: exact=1908
   ->  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)
         Index Cond: ((plateno)::text ~~ '%S906%'::text)
 Planning time: 260.415 ms
 Execution time: 6561.209 ms

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

    测试语句：

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

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

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

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

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

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