PostgreSQL中的work_mem参数

在SQLServer中有一个内存授予（Memory Grant）的概念，意思是一个执行一个查询语句所需的内存巨细，如果获取不到这个内存，则查询申请等待内存，因此就会受到影响。PostgreSQL有一个类似于此的work_mem参数，该参数也是执行跟查询所使用的内存有关的，那么work_mem的具体寄义是什么呢？

 
work_mem参数

1，work_mem的定义

  查询操纵(比方排序或哈希表)可使用的最大内存容量，注意是一个操纵节点（比如一个SQL中包含了排序和聚合操纵，这两个操纵最大可用内存都可以达到work_mem），而不是整个SQL语句的最大可用内存。
2，work_mem默认值

  默认值是4MB，该参数是最大概首先实行修改的参数之一，通常情况下，增长这个值的结果是 PostgreSQL 可以在内存中完成更多操纵，而不必写到磁盘上，这往往会加快查询速率。
3，work_mem过大的副作用

如果work_meory设得太大，而体系又有大量并发，那么PostgreSQL会遭遇到OOM非常，并报出类似 “Out of memory: Killed process ... (postgres)” 的信息，Linux操纵体系会自动kill掉占用内存最大的postmaster进程。办理这个题目的方法之一是使用 overcommit_memory 参数，并将其设为 2，从根本上防止 Linux 内核 过度分配内存。
4，怎样评估work_men的设置

通常情况下，在确保体系不会因为OOM导致被kill掉的情况下，增长这个值的结果是 PostgreSQL可以在内存中完成更多操纵，而不必写到磁盘上，这往往会加快查询速率。如果一个操纵超出work_mem的巨细，则会写入磁盘的临时文件，通过磁盘来替代内存的使用。
4.1，SQL内存占用
  work_mem是一个计算节点的最大内存使用限定，并不是SQL语句的最大内存使用限定，如果SQL语句中涉及多个计算，那么整个SQL语句耗费的内存将会是N个work_mem的总和
4.2，并行查询内存占用
  对于并行查询，如果你有4个并行 worker（max_parallel_workers_per_gather = 4）和1个leader，总共就是5个进程，每个进程都可以使用work_mem，终极是5*work_mem的内存占用
4.3，hash操纵的内存占用
  参数为hash_mem_multiplier，默认值为2，哈希的操纵可以使用两倍的work_mem，如果想让哈希操纵比排序操纵使用更多内存，也可以把这个值设得更高，同时保持 work_mem 稳定。
  PostgreSQL 文档建议 hash_mem_multiplier 可上调至 8.0。这样做的动机是：
  保持 work_mem 低，避免大排序占用太多内存，让哈希操纵优先在内存中执行，提高哈希操纵速率，大型排序操纵可以容忍溢写到磁盘
4.4,怎样判断work_mem不足
  当某个查询必要用到 超过work_mem的内存 来进行排序或哈希操纵时，PostgreSQL会在磁盘上创建临时文件。
  log_temp_files 控制 哪些巨细的临时文件会被记录到日志。
  可以这只log_temp_files=0，这样所有遇到work_mem不足导致磁盘使用的情况，都会记录到日志中，该参数默认值为-1，不记录临时文件的使用
 
如果非要用公式，可以参考：(average freeable memory * 4) / max_connections这只是一个 守旧默认值参考，远高于 4 MB 的默认值。实际上，更好的方法是 观察临时文件生成情况，并根据必要使用 hash_mem_multiplier 区分哈希操纵和排序操纵。
另一个参考公式：SharedBuffers + work_mem * 2 * max_connection + (memory for file system and operation system) < RAM
https://thebuild.com/blog/2023/03/13/everything-you-know-about-setting-work_mem-is-wrong/
参考：https://pganalyze.com/blog/5mins-postgres-work-mem-tuning

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。