追求 PostgreSQL 性能

（或者：那些不知道自己在做什么的人如何设法提高冗长查询的性能。）

我正在开发一个包含 200 多个表和大约 2 GB 数据的系统。该系统使用 Django 构建，并且严重依赖 ORM。我们不编写 SQL，所有查询都是通过 Django 查询集构建的。有些查询相当复杂，涉及 10 个或更多表。

大约一个月前，我的同事发现至少有5个比较复杂的查询在我们的测试服务器上的运行时间比在他的开发笔记本电脑上要长。我觉得这很奇怪，所以我尝试重现这种情况——而且我做到了。这些查询在我们每台笔记本电脑上的运行速度都比在我们可用的服务器上快（通常大约快一半）。

Table 1 – base line results
| Query   | server | laptop |
| -----   | ------:| ------:|
| Query 1 |    31s |    17s |
| Query 2 |    44s |    24s |
| Query 3 | 1m 47s |    46s |
| Query 4 |    28s |    17s |

于是搜寻开始了......我们知道数据量只会继续增加，我们必须将较长查询的运行时间缩短到我们的开发环境附近。

第一步是获取正在运行的实际查询的副本，以便将运行查询所需的时间与 Python 和 Django ORM 带来的开销完全隔离。这些查询相当庞大——两个最大查询的生成 SQL 语句大约有 16,000 个字符。（以每行 100 个字符计算，这意味着这将是一个 160 行的查询——而且是整行。实际上，格式化这些查询最终会有 200 多行。）

然后是检查基本情况的时间。我们处理的是源数据的相同副本。我们使用相同的脚本构建数据库。数据库构建完成后，我们会手动运行清理、重建索引和分析。这些系统上有足够的内存来设置缓冲区，使其大于正在访问的数据。

现在是时候看看这些系统之间的差异了。

诚然，我对 PostgreSQL 内部机制的了解非常有限。我对操作系统和文件系统的了解更深入。所以我想先从我了解的方面开始。

现在，我承认我有点 Linux 偏执。我根本不相信一台运行 PostgreSQL 的 Windows 笔记本电脑的速度会比一台运行 Linux 的服务器快两倍以上，而且硬件性能比那些笔记本电脑好得多。现在，我不再把操作系统的差异作为一个重要因素。

服务器在 Docker 容器中运行 PostgreSQL，数据存储在 Docker 卷中。更改为将数据目录映射到主机上的映射目录。没有变化。更改为使用在主机上运行的 PostgreSQL 实例。仍然没有区别。

这台服务器实际上是在 VMWare ESXI 中运行的虚拟机。服务器镜像是 Ubuntu 20.04，磁盘映射为 LVM 卷。问题可能是由多层磁盘映射引起的吗？磁盘接口的选择又如何呢？（ATA 和 SCSI）所以我们为该镜像分配了两个新的“物理”驱动器，分别对应一个可用的接口。我没有对这两个驱动器进行分区，而是直接在这些设备上创建了文件系统。在这些文件系统上创建了表空间，创建了数据库……仍然没有任何变化。

接下来发现的差异是所使用的 PostgreSQL 版本。我的笔记本电脑运行的是 PostgreSQL 12，同事的笔记本电脑运行的是 PostgreSQL 10，测试服务器运行的是 PostgreSQL 13。啊……13 版本有问题吗？将测试服务器回退到 12 版本——没有区别。安装并测试 10 版本——有区别！虽然差异很大（约 20%），但远不能解释所有差异——而且从 13 版本降到 10 版本也不是一个可接受的答案。但至少我找到了继续搜索的方向。

我的探索的下一阶段持续了将近一周。我知道我必须阅读大量资料才能理解可能发生的事情。我首先查看了所有发行说明和其他可用文档，以找出可能导致此问题的原因。我看到的其中一项更改提到了查询规划器，以及它如何使用各种设置来改进执行计划。

因此，我的下一步是对不同版本的查询运行“解释分析”。Bingo！服务器上的慢查询对表进行了顺序扫描，而较快的系统则使用了索引。我还发现 JIT 占用了超过 30 秒的总运行时间。

研究越多，想法越多。PostgreSQL 有一个专门讨论性能问题的邮件列表，存档里也有很多信息。在邮件列表和文档中，我发现了一些可能相关的设置。

我开始做一些细微的改动（关闭 JIT 就是其中之一），很快就看到了一些改进。虽然这仍然没有解决问题，但我开始有信心自己正朝着正确的方向前进。

为了记录，以下是我决定重点关注的设置列表。（注意，这是在对各种缓冲区和缓存设置进行所有适当的更改之后。）

seq_page_cost
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost
parallel_tuple_cost
parallel_setup_cost

这时我有点迷茫了。我的前几次实验确实显示出了一些改进，但我并没有发现我所做的更改与改进程度（或没有改进）之间有任何直接关系。我知道这是因为我对这些设置的具体含义缺乏理解，但这确实让我很难确切地知道我想要什么更改。

所以，在不知道还能尝试什么的情况下，我决定依靠一些暴力破解的方法。我编写了一个脚本，它会更改设置，重启 PostgreSQL，运行查询，保存结果，然后重复这个过程。接下来就是查看所有结果了。

我很快意识到，这些设置的组合数量会迅速增长到失控的程度。如果我为每个变量分别测试 3 个值，就会产生 2187 种组合。如果每次测试需要 1 分钟，那么完整的测试集将持续超过 36 小时。如果我尝试为这 7 个变量中的每个变量运行 5 个值，则需要 54 天才能完成。
我需要想出一个不同的方法。我运行了更多有限测试，每个变量的组合数量也更多。例如，我对 seq_page_cost 和 random_page_cost 的不同组合运行了 25 个测试，同时保持其他所有值不变。然后，我从中选取最佳组合，对 cpu_tuple_cost、cpu_index_tuple_cost 和 cpu_operator_cost 的 5 个不同值分别运行了 125 个测试。取其中的最佳结果，然后返回并重新运行之前的 25 个测试。重复此过程，直到最佳值的结果稳定下来。

这些更改中的许多组产生的整体影响非常小。对于一个 30 秒的查询，我会得到大量的测试结果，这些结果的差异都在十分之一秒以内。然而，结果会在某些时间结果周围显示出较大的聚集，我很高兴看到这种情况——当 PostgreSQL 将表访问从索引搜索更改为顺序扫描时，就会出现巨大的差异。

现在我们到了关键时刻。经过这些，我们目前确定的价值观是：

seq_page_cost = 0.01
random_page_cost = 0.01
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.5
cpu_operator_cost = 0.25
parallel_tuple_cost = 1
parallel_setup_cost = 100

注意：我的理解是，这些都是相对的，而且都可以重新缩放。然而，情况似乎并非如此。我尝试将所有值乘以 100，使它们全部变成整数，但测试的运行时间失败了。我猜还有另一个我遗漏的设置也需要调整。

Table 2 – final results
| Query   | server before | After | Laptop |
| ------  | -------------:| -----:|-------:|
| Query 1 |           31s |    4s |    17s |
| Query 2 |           44s |    5s |    24s |
| Query 3 |        1m 47s |   33s |    46s |
| Query 4 |           28s |    4s |    17s |

因此，现在服务器不仅变得更好，而且性能显著提升！

如果我从中学到什么教训的话，那就是：

1. 我非常感谢 PostgreSQL 团队提供的文档和邮件列表。如果没有这些资源，我不可能走到今天这一步。
2. 有很多信息可用于帮助解决性能问题 - 可能只需要进行一些挖掘和阅读。
3. 在某些硬件配置中，PostgreSQL 默认值对于复杂的数据关系来说并不是最佳的。