为PostgreSQL配置work_mem

     work_mem也许是Postgres中最令人困惑的参数。work_mem用于确定在某些操作期间可以使用多少内存。从表面上看，work_mem的设置似乎很简单，毕竟，work_mem只需指定将数据写入磁盘之前内部排序操作和哈希表可用的内存量。然而，如果不去配置work_mem可能会带来许多问题。但是，更令人不安的是，当您在数据库中收到内存不足的错误并跳出调整work_mem时，只是因为它以不太直观的方式进行了一次提示。

设置默认内存

  work_mem在Postgres中的默认值为4MB，这可能有点低。这意味着每个Postgres活动（每个连接，某些排序等）在开始溢出到磁盘之前只可能会消耗4MB。当Postgres开始将临时文件写入磁盘时，显然这样会比内存慢得多。当启用后log_temp_files后，您可以通过在PostgreSQL日志中搜索temporary file来查看是否溢出到了磁盘。如果你看到了temporary file，它往往意味着你需要增加work_mem。

  首先需要清楚，我们很难获得work_mem比较完美的正确配置，但如果你正在尝试寻找一个适合所有场景的答案，通常设置为64MB。

它不仅仅是用于“查询”的内存管理

  让我们用一个例子来探讨如何考虑优化work_mem设置。

  假设你有一定的内存，比如10 GB。如果您有100个正在运行的Postgres查询，并且每个查询都有10 MB的连接开销，那么100个连接将占用100 * 10 MB =1 GB 的内存 - 这将为您留下9 GB的内存。

  剩余9 GB内存，假设您work_mem为100个正在运行的查询提供90 MB 。但事实上并非如此，work_mem并不是基于每个查询而设置，而是基于sort/hash操作的数量来设置。但是每个查询会发生多少sort/hash和join操作？现在这变为了一个复杂的问题。如果您还有其他消耗内存的进程（如autovacuum），则复杂的问题会变得更复杂。

  即使为维护任务和vacuum保留一点，并且我们限制我们的连接，这样仍没有那么快。

  Postgres现在有并行查询。这可以显着提高查询速度，但每个正在运行的进程都可以使用指定数量的work_mem。如果使用我们默认的64 MB和100个连接，我们将远远超出我们预期的让每个核心运行查询的每个核心消耗的内存。

过大的work_mem可能会导致更多的问题

  所以我们可以看到，如要让它变得更完美就需要付出比理想下更多的工作。让我们稍微回过头来试试这个：我们可以从work_mem=16 MB开始，当我们看到temporary file时逐渐增加work_mem。但为什么不给每个查询尽可能多的内存？或者说如果每个进程可能消耗高达1 GB的内存有什么危害？那么另一个极端是，查询开始消耗太多内存，比你的服务器上可用的更多。当发生这种情况时，您将获得100个查询，这些查询具有5个不同的排序操作和一些散列连接，实际上很可能耗尽数据库可用的所有内存。

  当您消耗的内存超过计算机上可用的内存时，您可以开始查看Postgres日志中的out of memory错误，或者在更糟糕的情况下，OOM killer可以开始随机杀死正在运行的进程以释放内存。Postgres中的内存不足错误只是你正在运行的查询的错误，因为linux中的OOM killer开始杀死正在运行的进程，在某些情况下甚至可能包括Postgres本身。

  当您看到out of memory错误时，您有两种选择：通过升级到更大的实例来充分利用机器所有的RAM，或者减少work_mem使用的内存量。答案是：出现out-of-memory最好减少work_mem而不是增加，因为这是每个进程可以消耗的内存量，而太多的操作正在利用那么多的内存。

work_mem的一般指导

  您可以继续调优和调整work_mem，也可以针对工作负载配对的一些广泛指导原则进行配置：

  如果你有许多短时间运行的查询，这些查询运行得非常频繁，并且执行简单的查找和连接，那么保持较低的work_mem是较为理想的。如果您的工作负载相对较少，并且一次执行非常复杂的排序和连接的活动查询，那么授予更多内存以防止事件溢出可以为您带来丰厚的回报。