咱们之前的博客文章“咱们最讨厌的 PostgreSQL 部分”评论了大家最喜欢的 DBMS 多版别并发操控 (MVCC) 完结所带来的问题。其间包括版别仿制、表胀大、索引保护和真空办理。本文将评论针对每个问题优化 PostgreSQL 的办法。

尽管 PostgreSQL 的 MVCC 完结是 Oracle 和 MySQL 等其他广泛运用的数据库中最差的，但它仍然是咱们最喜欢的 DBMS，并且咱们仍然喜欢它！经过分享咱们的见地，咱们期望帮助用户释放这个强大的数据库体系的悉数潜力。好消息是 OtterTune 会主动为您处理许多此类问题（但不是悉数！）。

问题#1：版别仿制

当查询修正元组时，无论更新其一列还是一切列，PostgreSQL 都会经过仿制其一切列来创立新版别。这种仿制或许会导致许多数据重复并添加存储需求，特别是关于具有许多列和较大行大小的表。

优化：不幸的是，假如不对 PostgreSQL 的内部结构进行重大重写（这会形成损坏），就没有处理此问题的处理办法。这不像在情景喜剧中替换了一个没有人注意到的人物。正如咱们在上一篇文章中说到的，EnterpriseDB 在 2013 年经过 zheap 项目开端了这条路途，但该项目的最终一次更新是在 2021 年。其他人现已对 PostgreSQL 代码进行了硬分叉，以取代其 MVCC 完结。闻名的比如包括 OrioleDB 和 YugabyteDB。但对这些体系的更改永久不会合并回主 PostgreSQL 代码库。所以咱们暂时只能运用 PostgreSQL 的append-only MVCC。

Problem #2: Table Bloat问题#2：表胀大

PostgreSQL 将过期版别（死元组）和活动元组存储在同一页面上。尽管 PostgreSQL 的 autovacuum 作业程序最终会删去这些死元组，但写入深重的作业负载或许会导致它们累积的速度快于真空处理的速度。此外，主动整理仅删去死元组以供重用（例如，存储新版别），并且不会回收未运用的存储空间。在查询履行期间，PostgreSQL 将死元组加载到内存中（由于 DBMS 将它们与活元组混合在页面上），从而添加磁盘 IO 并危害功能，由于 DBMS 检索无用的数据。假如您正在运转 Amazon 的 PostgreSQL Aurora，这将添加 DBMS 的 IOPS，并导致您给 Jeff Bezos(amazon的老板) 更多的钱！

优化：咱们主张监控 PostgreSQL 的表胀大，然后定时回收未运用的空间。 内置pgstattuple模块能够准确核算数据库中的可用空间，但它需求全表扫描，这关于出产环境中的大表来说不实用。

$ psql -c "CREATE EXTENSION pgstattuple" -d $DB_NAME
$ psql -c "SELECT * FROM pgstattuple('$TABLE_NAME')" -d $DB_NAME

或许，能够运用一次性查询或脚原本估计表的未运用空间；它们比 pgstattuple 更快、更轻量，由于它们供给了表胀大的粗略估计。假如未运用的空间量很大，则 pg_repack 扩展会从臃肿的表和索引中删去并回收页面。它在线作业，不需求在处理进程中对表进行独占确认（与VACUUM FULL不同）。

以下指令将把 pg_repack 扩展安装到自我办理的 DBMS 中（请参阅 Amazon 的 PostgreSQL RDS 阐明），然后紧缩单个表。

$ psql -c "CREATE EXTENSION pg_repack" -d $DB_NAME
$ pg_repack -d $DB_NAME --table $TABLE_NAME

为了最大极限地削减对数据库功能的潜在影响，OtterTune 主张咱们的客户在流量较低的非高峰时段发动此进程。

问题#3：二级索引保护

当应用程序对表履行UPDATE查询时，PostgreSQL 还有必要更新该表的一切索引以将条目添加到新版别。这些索引更新添加了 DBMS 的内存压力和磁盘 I/O，特别是关于具有许多索引的表（一位 OtterTune 客户在单个表上有 90 个索引！）。随着表中索引数量的添加，更新元组时发生的开支也会添加。 PostgreSQL 防止更新仅堆元组 (HOT) 更新的索引，其间 DBMS 将新版别存储在与从前版别相同的页面上。但正如咱们在上一篇文章中说到的，OtterTune 客户的 PostgreSQL 数据库仅对 46% 的更新操作运用 HOT 优化。

优化：削减 PostgreSQL 索引写入扩大的明显处理办法是削减每个表的索引数量。但这说起来简单做起来难。咱们主张从表中重复和未运用的索引开端。人们能够经过查看数据库的模式来辨认重复索引，以查看两个索引是否以相同的顺序引用相同的列并运用相同的数据结构（例如，B+树与哈希表）。关于未运用的索引，PostgreSQL 保护索引级目标（例如，pg_stat_all_indexes.idx_scan），用于盯梢在索引上发动的索引扫描的数量。假如索引的该值为零，则一切应用程序的查询都不会运用该索引。保证忽略未运用的主键或唯一索引，由于 DBMS 运用它们对表强制履行完整性束缚。

下面的屏幕截图显现了 OtterTune 的类似查看，用于主动查找不必要的索引。

PostgreSQL MVCC的弊端优化方案

OtterTune’s Unused and Duplicate index dashboard.
OtterTune 的未运用和重复索引仪表板。

一旦确认要删去的索引，下一步就是删去它们。可是，假如您的应用程序运用对象关系映射 (ORM) 框架来办理其数据库架构，那么您不期望手动删去索引，由于 ORM 或许会在将来的架构搬迁期间重新创立索引。在这种情况下，有必要更新应用程序代码中的架构。假如应用程序未运用 ORM，则能够运用DROP INDEX指令。

问题#4：真空办理

PostgreSQL 的功能在很大程度上取决于其 autovacuum 整理过时数据和修剪 MVCC 方案中版别链的有效性。但是，由于其复杂性，配置主动整理以正确运转并及时删去这些数据具有挑战性。默许的大局主动整理设置不适合大型表（数百万到数十亿的元组），由于触发整理或许需求很长时刻。此外，假如每个 autovacuum 调用需求很长时刻才干完结或被长时刻运转的业务阻塞，DBMS 将积累死元组并遭受陈腐统计数据的影响。将主动整理推迟太久会导致查询随着时刻的推移逐渐变慢，需求手动干涉来处理该问题。

优化：虽然在 PostgreSQL 中整理表很苦楚，但好消息是它是能够办理的。但正如咱们现在所评论的，这有许多进程，并且需求盯梢许多信息。

操控 autovacuum 的第一步是监督每个表的死元组数量。 PostgreSQL 的 pg_stat_all_tables视图供给了监控表的根本目标，包括死元组 (n_dead_tup) 和活动元组 (n_live_tup) 数量的估计。经过此类表级目标，您能够确认每个表过期元组的百分比，并确认哪些表需求额定的整理作业。

关于具有许多死元组的表，您能够调整其设置以使 PostgreSQL 更频频地触发 autovacuum。 PostgreSQL 答应您在表等级微调 autovacuum 参数，不同的表或许需求不同的最佳设置。最重要的旋钮是 autovacuum_vacuum_scale_factor：它指定在 PostgreSQL 调用 autovacuum 之前表中有必要存在的死元组的最小百分比。该旋钮的默许值为 20%。假如应用程序的一个表有 10 亿个元组，PostgreSQL 不会在该表上运转整理，直到至少有 2 亿个死元组。假如该表中的均匀元组大小为 1KB，则 2 亿个死元组将消耗 200GB 的磁盘存储空间。这乃至不包括指向这些表的索引指针的额定存储开支！为了防止此问题，您应该运用ALTER TABLESQL 指令将大型表的比例因子旋钮设置为小于 20%：

 ALTER TABLE table_name SET (autovacuum_ vacuum_scale_factor = 0.05);

接下来，您应该查看 autovacuum 是否被长时刻运转的业务阻塞。再次，咱们能够依靠 PostgreSQL 的内部遥测来获取这些信息。 pg_stat_activity 视图供给每个 PostgreSQL 作业线程（即进程）当时履行状况的实时数据。它显现每个活动业务已运转多长时刻。假如业务现已运转了几个小时，您应该考虑将其停止，以便 autovacuum 能够完结其操作。下面的示例查询查找一切运转时刻超过五分钟的业务：

SELECT pid, NOW() - xact_start AS duration, query, state
  FROM pg_stat_activity
 WHERE (NOW() - xact_start) > INTERVAL '5 minutes';

然后，您能够运用 pg_cancel_backend 办理函数停止查询：

SELECT pg_cancel_backend($PID_TO_KILL);

当然，在街上删去查询或许会发生意想不到的后果，因此您有必要保证杀死它们不会在您的应用程序中形成问题。为了防止将来出现相同的问题，请保证业务的查询不必要地运转更长的时刻，由于它们运用的是低效的查询方案。请参阅咱们之前关于优化查询功能的文章，例如欺骗ORDER BY...LIMIT和运转ANALYZE，了解如何运用 OtterTune 改善慢速查询。假如您不需求它们是原子的，您还能够重构您的应用程序，将大型业务分解为较小的作业单元（但不可否认，这并不总是简单做到）。

最终，您需求查看是否存在长时刻运转的真空进程，然后调整其他旋钮。与 pg_stat_activity 显现 PostgreSQL 作业线程的状况类似，pg_stat_progress_vacuum 视图显现活动 autovacuum 操作的状况。经过此视图，您能够确认真空是否需求几个小时乃至几天才干完结。假如您的 PostgreSQL DBMS 确实有长时刻运转的 Vacuum，那么 OtterTune 主张调整三个旋钮：

  1. autovacuum_work_mem 参数指定 DBMS 在每次 autovacuum 调用中能够运用的最大内存量。添加此参数能够加速整理速度，由于它能够在每次调用时修剪更多的死元组。
  2. autovacuum_vacuum_cost_limit 参数操控在 PostgreSQL 强制主动整理作业者暂时退出之前能够发生多少 I/O 活动主动整理作业者。该旋钮的值越高意味着主动整理将愈加积极。
  3. 与这种根据本钱的操控机制相关，autovacuum_vacuum_cost_delay 参数确认 autovacuum 作业线程在 DBMS 强制其退出后有必要等候多长时刻。较短的推迟意味着主动整理每次都会更快地恢复操作。

原文地址