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

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！