😁 💿 🍫 消除高负载下PostgreSQL中的瓶颈 🙌🏿 🐒 🕓

你好。目前，OTUS已开始招募新的“ PostgreSQL”课程。在这方面，我们传统上为您准备了有关该主题的有用材料的翻译。

根据Peter Zaitsev关于MySQL性能瓶颈的文章，我想谈谈PostgreSQL。

如今，ORM框架通常用于PostgreSQL。它们通常工作良好，但是随着时间的推移，负载会增加，因此有必要调整数据库服务器。与PostgreSQL一样可靠，但随着流量的增加，它可能会变慢。

有很多方法可以消除性能瓶颈，但是在本文中，我们将重点关注以下方面：

服务器参数
连接管理
自动真空设定
额外的自动真空设置
充气表（膨胀）
数据热点
应用服务器
复写
服务器环境

关于“类别”和“潜在影响”

“复杂性”是指实施建议的解决方案有多么容易。并且“潜在影响”表明了系统性能的改善程度。但是，由于系统的年代，系统类型，技术债务等原因。准确描述其复杂性和影响可能会出现问题。毕竟，在困难的情况下，最终的选择始终是您的选择。

分类：

复杂
- 低
- 平均
- 高
- 低中高
潜在影响
- 低
- 平均
- 高
- 低中高

服务器参数

难度：低。

潜在影响：高。

不久之前，有时postgres的当前版本可以在i386上运行。此后更改了默认设置，但仍将其配置为使用最少的资源。

这些设置非常容易更改，通常在初始安装过程中进行配置。这些参数的值不正确会导致较高的CPU和I / O利用率：

参数有效缓存大小〜50到75％
参数shared_buffers〜RAM量的1/ 4-1 /3
参数work_mem〜 10MB

尽管我们使用的是“ top_free” +“ cached ” ，但尽管有效， 推荐的值虽然可以是典型的，但可以更准确地计算出。计算shared_buffers的值是一个有趣的难题。您可以从两个角度看待它：如果您有一个小型数据库，则可以将shared_buffers的值设置得足够大以适合整个数据库在RAM中。另一方面，您可以配置仅将经常使用的表和索引加载到内存中（记住80/20

）。以前，建议将值设置为RAM数量的1/3，但是随着时间的推移，随着内存数量的增加，该值减小为1/4。如果分配的内存很少，则I / O和处理器负载将增加。达到处理器和I / O负载的平稳期将指示过多的内存分配。

要考虑的另一个因素是OS缓存。如果有足够的RAM，Linux将在内存中缓存表和索引，并且根据配置方式，PostgreSQL可能会认为PostgreSQL从磁盘而不是RAM读取数据。 postgres缓冲区和OS缓存中都存在同一页面，这是不使shared_buffers非常大的原因之一。使用pg_buffercache扩展您可以实时查看缓存的使用情况。work_mem

参数指定用于排序操作的内存量。将该值设置得太低会导致性能下降，因为排序将使用磁盘上的临时文件执行。另一方面，尽管设置较大的值不会影响性能，但是对于大量的连接，存在RAM耗尽的风险。通过分析所有请求和会话使用的内存，可以计算所需的值。使用EXPLAIN ANALYZE，您可以查看排序操作的执行方式，并通过更改会话的值来确定何时开始刷新磁盘。您还可以使用基准

系统。

连接管理

难度：低。

潜在影响：低-中-高高

负载通常与每单位时间增加的客户端会话相关。它们太多会阻塞进程，导致延迟甚至导致错误。

简单的解决方案是增加并发连接的最大数量：

# postgresql.conf: default is set to 100<br />max_connections

但是更有效的方法是连接池。解决方案很多，但是最受欢迎的是pgbouncer。PgBouncer可以使用以下三种模式之一管理连接：

(session pooling). . , . , . .
(transaction pooling). . PgBouncer , , .
(statement pooling). . . , .

您还应该注意安全套接字层（SSL）。启用后，默认情况下，连接将使用SSL，与未加密的连接相比，它将增加处理器负载。对于普通客户端，您可以配置不使用SSL（pg_hba.conf）的基于主机的身份验证，并将SSL用于管理任务或流复制。

自动真空设定

难度：中等。

潜在影响：低中。

多版本并发控制是使PostgreSQL成为流行的数据库解决方案的基本原则之一。但是，一个令人烦恼的问题是，对于每个更改或删除的记录，都会创建未使用的副本，这些副本最终必须处理掉。配置不当的自动真空过程可能会降低性能。此外，服务器负载越多，问题就越能体现出来。

以下参数用于控制autovacuum守护程序：

autovacuum_max_workers. ( ). , . . . .
maintenance_work_mem. , . , . , .
autovacuum_freeze_max_age TXID WRAPAROUND. , , . , , , . , txid, . / txid pg_stat_activity WRAPAROUND.

当心RAM和CPU超载。初始设置值越高，系统负载增加时资源耗尽的风险就越大。如果设置得太高，则当超过某个负载水平时，性能可能会急剧下降。

与计算work_mem相似，此值可以算术计算或进行基准测试以获得最佳值。

额外的自动真空设置

难度：高。

潜在影响：高。

由于其复杂性，仅当系统性能已接近主机的物理限制时才应使用此方法，而这实际上已成为问题。

运行时自动清理选项在中配置postgresql.conf。不幸的是，没有一种万能的解决方案可以在任何高负载系统中使用。

表的存储选项。通常在数据库中，很大一部分负载仅落在几个表上。自定义表的自动真空设置是避免手动启动VACUUM的好方法，这可能会严重影响系统。

您可以使用以下命令来自定义表：

ALTER TABLE .. SET STORAGE_PARAMETER

充气表（膨胀）

难度：低。

潜在影响：中高。

随着时间的流逝，由于表过度膨胀，不适当的清除策略可能会降低系统性能。因此，即使设置autovacuum守护程序并手动启动VACUUM也不能解决问题。在这种情况下，可以使用pg_repack扩展名。

使用pg_repack扩展，您可以在生产环境中重建和重新组织表和索引

数据热点

难度：高。

潜在影响：低中高。

与MySQL一样，PostgreSQL依靠您的数据流来摆脱热点，甚至可能会改变系统的体系结构。

首先，您应注意以下几点：

指标。确保要搜索的列上有索引。您可以使用系统目录和视图来监视和验证查询是否正在使用索引。使用pg_stat_statement和pgbadger扩展来分析查询性能。
仅堆元组（HOT）。索引可能太多。您可以通过删除未使用的索引来减少潜在的膨胀并减少表的大小。
. , , . , , , . , . , , .
. postgres. , .
. , . . , !