Postgres 性能三角
摘要： 本文通过三个相互关联的参数——内存分配、磁盘 I/O 和并发——探讨 Postgres 性能调优，类似于摄影中的曝光三角。光圈、快门速度和 ISO 一样，每个参数都涉及数据库管理员需要在维持系统吞吐量和稳定性之间进行的权衡。
每个对摄影有一定了解的人都知道曝光三角的概念：光圈、快门速度和 ISO。根据你的艺术创作意图，你可以调整这三个参数，并意识到这样做需要权衡。在处理了几个案例并向客户展示解决方案之后，我开始以类似的方式思考 Postgres 性能调优——有些基本参数可以调整，而 DBA 做出的选择是有代价的：
- 内存分配
- 磁盘 I/O
- 并发
粗略地说，这三者都会影响吞吐量——你的系统能完成多少工作。
需要说明：我知道从学术角度来说，"吞吐量"并不能完全涵盖这些概念的综合平衡，但请暂且容忍我这种说法！
让我们谈谈这三个参数是如何与整个系统协同工作的，以及权衡取舍是什么样的。
内存分配
当你在 Postgres 中增加内存分配时，无论是 shared_buffers 还是 work_mem，系统运行起来都会感觉更流畅。最明显的是，查询溢出到磁盘的次数减少了，排序和连接操作保持在内存中，缓存命中率提高了。但有一个很容易被忽视的权衡，特别是对于这两个参数。单个复杂查询可能会消耗多份 work_mem（参见 Laetitia 关于这个问题的精彩文章）。将其乘以并发查询的数量，你就开始看到操作系统开始使用交换空间、在检查点时不断抖动，甚至调用 OOM Killer。因此，虽然更多的内存可以让事情变得更快，但它也在悄悄降低系统能够安全处理的并发量。
这类似于光圈——你可以花大价钱买一些快速镜头，但你也会得到更浅的景深（以一种令人烦恼的方式）。
磁盘 I/O
当内存不够时，或者当访问模式需要时，数据就会进入磁盘。我们在顺序扫描、随机索引查找以及排序或哈希产生的临时文件中看到了这样的例子。例如，降低 work_mem 可能会增加磁盘 I/O，因为排序会溢出到临时文件中。我们可以通过添加索引、增加 work_mem，或者简单地重写查询来尽量减少磁盘 I/O。
另一个影响磁盘 I/O 的方法是调整代价，以鼓励查询规划器选择一种扫描方式而不是另一种。无论如何，我们平衡磁盘 I/O 和内存使用的尝试一开始可能相当直接，但在规模上可能会变得复杂。这就是分区和只读副本发挥作用的地方，但我开始跑题了……
索引尤其是一件有趣的事情。添加索引感觉像是一个容易的胜利，因为它导致扫描的行数减少，每个查询的 CPU 工作量也随之减少，同时磁盘活动也减少了，但这里有代价：
- 每次
INSERT都会更新每个相关索引 - 每次
UPDATE都可能重写索引条目 - 每次
DELETE都会留下清理工作（vacuum）
在规模上，我们还会看到其他影响：
- 索引会变大
- 缓存命中率下降（因为需要缓存的内容更多）
- 随机 I/O 增加
因此，一个帮助某个查询的索引可能会悄悄让其他查询变慢，或者让写入更昂贵。
这就像在低光环境下提高 ISO 来补偿。你拍到了照片，但噪点出现在其他地方。
并发
到目前为止，这一切都有些是针对单个查询的。但当你引入并发时，情况就变了。在高需求服务中，本能的反应是增加 max_connections 以允许服务扩展，但根据我的经验，这种并发是有代价的。有些人没有注意到每个连接都有自己的内存占用，占用 Postgres 内部数据结构中的一个位置，并使系统面临 CPU 需求增加和资源争用的风险。
在摄影类比中，你可以在阳光明媚的日子把 ISO 调得很低，但这还不够。很快，你就会缩小光圈并增加快门速度，然后你就失去了你真正想要追求的艺术表现力。那么摄影师怎么做呢？他们使用 ND 滤镜来限制有多少光线到达传感器。
在 Postgres 中，这个"ND 滤镜"就像是连接池，比如 PgBouncer。与其让数千个连接争夺 CPU：你可以限制活动查询，为每个实际的数据库会话分配更多资源，并用一点延迟换取稳定性。有时候，为了保持吞吐量，你需要一些额外的辅助工具。
Postgres 的艺术
作为 DBA，你可以计算最优的索引使用、内存大小和预期的 I/O 模式，但这些计算往往假设一个稳定状态。每个 DBA 都知道真正的生产系统总是在变化，由于流量模式、扩展以及应用端新功能的推出都会导致变化。随着组织的变化，保持数据库高性能的工作取决于 DBA 既要扮演数据库管理员的角色，也要扮演数据库艺术家的角色，与内部团队合作了解要添加/删除哪些索引、允许多少并发、以及如何分配内存而不至于耗尽。
与其问"什么是最佳配置？"，问以下问题可能更有用：
- 我的系统目前在哪里付出代价——内存、磁盘还是 CPU？
- 如果我缓解这里的压力，它会转移到哪里？
- 我们能容忍多大的新压力？
成本不会消失——它们只是转移——而 DBA 的工作是帮助决策者决定把它转移到哪里。
结论
摄影不仅仅是曝光——还有构图、色彩校正、外部灯光等等。同样，本讨论只是数据库管理的一部分。在创建健壮和可扩展的数据库方面，还有更多内容需要讨论。我之所以想强调这个话题，是因为我确实发现一些用户在考虑数据库架构时没有权衡所有因素。我们肯定可以让数据库表现良好，但没有一种放之四海而皆准的解决方案适用于所有情况。需要思考、规划、测试，并与利益相关者讨论，才能提出一个好的解决方案。
