作者：瀚高PG实验室 （Highgo PG Lab) - 海无涯

先来看一下官方给出的解释

work_mem (integer)

Specifies the amount of memory to be usedby internal sort operations and hash tables before writing to temporary diskfiles. The value defaults to four megabytes (4MB). Note that for a complexquery, several sort or hash operations might be running in parallel; eachoperation will be allowed to use as much memory as this value specifies beforeit starts to write data into temporary files. Also, several running sessionscould be doing such operations concurrently. Therefore, the total memory usedcould be many times the value of work_mem; it is necessary to keep this fact inmind when choosing the value. Sort operations are used for ORDER BY, DISTINCT,and merge joins. Hash tables are used in hash joins, hash-based aggregation,and hash-based processing of IN subqueries.

 

Work_mem默认值为4M（9.3及其以前版本默认值为1M），最小为64kB。这些内存是被用来完成切换到临时磁盘文件之前的内部sort（排序）操作和Hash（哈希）操作。如果未分配足够的内存，就会导致物理I/O，这就会导致响应时间的高峰值，看上去该值设置的越高越好。实际上呢？来看一下内存分配的简略图:

work_mem怎么调整 work memo_调优

我们发现work_mem是属于每个后端缓冲区中的内存，换句话说，它并不是像shared_buffers一样在服务器层级进行分配，它是被分配给每个用户。

下面来看一组查询语句：

select * from lines, lineitems
where lines.lineid = lineitems.lineid
and lineid=6
order by baz;

这组语句中涉及到了两个sort操作，假设每个sort操作需要的内存为32M（假设此时work_mem的值为32M），这组查询就用了2*32共计64M的内存。

在一种极端情况下，max_connections=1000且每个连接数都有两个sort操作，那么，我们用到的work_mem极限总值就是1000*2*32M=64000M。是不是一个相当恐怖的值，此时的数据库处于什么“性能”，可想而知，所以说work_mem的值设置还是相当重要的。

   那么该值应该设置多少呢？我的建议是2-8M。这是基于目前硬件的平均水平的建议，仍需工程师们根据实际情况来分配。此外在有复杂查询的时候我们也可以在会话级别修改该值。例如：

 

highgo=# show work_mem;
 work_mem 
----------
 64kB
(1 行记录)
 
highgo=# select * from (select * from tt1order by id) t limit 10;
 id|               name               
----+----------------------------------
  1 |7cdf8400646e20848daad46a17fc6b78
  1 |e17557873bfb369b611cfe6f8e7346c0
  1 |cf9920dd1f8a7be7e56a85f8a3e018f6
  1 |c550abd180fa1c2523ca5f4b411ae46f
  1 |cf9920dd1f8a7be7e56a85f8a3e018f6
  1 |f5e64cc0bb4833e992ebc84626d795f4
  1 |cf9920dd1f8a7be7e56a85f8a3e018f6
  1 |cf9920dd1f8a7be7e56a85f8a3e018f6
  1 |b1f0bf5915e1cff6f51a7d29a7dfbdd8
  1 |bd7cb4074f223632079526ea0e8c2d32
(10 行记录)
 
时间：484.442 ms   (多次查询平均值为482.374ms)
highgo=# set work_mem = '1MB';
SET
时间：0.845 ms
highgo=# show work_mem;
 work_mem 
----------
 1MB
(1 行记录)
 
时间：0.307 ms
highgo=# select * from (select * from tt1order by id) t limit 10;
 id|               name               
----+----------------------------------
  1 |7cdf8400646e20848daad46a17fc6b78
  1 |e17557873bfb369b611cfe6f8e7346c0
  1 |cf9920dd1f8a7be7e56a85f8a3e018f6
  1 |c550abd180fa1c2523ca5f4b411ae46f
  1 |cf9920dd1f8a7be7e56a85f8a3e018f6
  1 |f5e64cc0bb4833e992ebc84626d795f4
  1 |cf9920dd1f8a7be7e56a85f8a3e018f6
  1 |cf9920dd1f8a7be7e56a85f8a3e018f6
  1 |b1f0bf5915e1cff6f51a7d29a7dfbdd8
  1 |bd7cb4074f223632079526ea0e8c2d32
(10 行记录)
 
时间：321.926 ms   （多次查询平均值为313.877ms）

 

可以发现相同查询所用时间确实会随着work_mem的提升有所下降。

 

此外我们也可以通过explain analyze检查是否有足够的work_mem ，如下：

 

sort (cost=0.02..0.03 rows=1 width=0)(actual time=2270.744..22588.341 rows=1000000 loops=1)  
Sort Key: (generate_series(1, 1000000)) 
Sort Method: external merge Disk:13696kb  -> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.006..144.720 rows=1000000loops=1) Total runtime: 3009.218 ms (5 rows)


 

以上的 query分析显示，这里需要从硬盘取走13MB的东西。所以这个query应给setwork_mem到“14MB+原值”才能确保性能。

 

 

在复杂的查询语句种可以通过临时文件的产生量来更直观的检测work_mem大小产生的影响。

highgo=# select temp_files,temp_bytes from pg_stat_database where datname = 'highgo';
 temp_files | temp_bytes 
------------+------------
          3 |     164796
(1 row)

highgo=# select temp_files,temp_bytes from pg_stat_database where datname = 'highgo';
 temp_files | temp_bytes 
------------+------------
          0 |          0
(1 row)

BY 海无涯