在PostgreSQL调试随机慢写

在Web应用程序中，面对我们无法理解的性能问题并不罕见。特别是与数据库一起使用，我们将它们视为这个巨大的“黑匣子”，99％的时间令人惊讶地毫无疑问地关心它。哎呀，我们甚至使用像orms的东西，基本上“隐藏”我们与数据库的互动，让我们认为我们不需要关心这些东西。

如果你正在开发一些小的，所在的东西，简单，那可能是这种情况。无论设计多么差或配置，您的数据库都可能表现正常。您不会使用ORMS构建的“天真”查询的任何问题，一切都会正常工作。

但是，随着应用程序的增长，数据库是可以制作或制动的东西。它是最艰难的事情（你不能只是旋转多个实例），很难重新设计或MIGRATEAND基本上它是您的应用程序的核心，服务和存储所有数据。因此它的表现至关重要。

在这篇文章中，我将展示调试一个看似奇怪的数据库性能下降的真实例子。我显然打算分享解决方案以及要避免的解决方案，我也想带你去跑步和向你展示一些工具和amp ;可以帮助您挖掘SQL性能的进程。

我们将学习的数据库是AWS Aurora RDS（PostgreSQL 12）。它是一个群集数据库，并且有两个副本，读者（只读副本）和写入器。 AWS Aurora非常接近实际的PostgreSQL，顶部有一些零滞后功能（以及某些管理功能当然）。这里讨论的整个过程不应进入自我管理的RDS PostgreSQL。

我们将要学习的问题是（看似）更新/插入语句的随机性能不佳。在一个特定的表中观察到这个问题，它有〜20000000行和23个索引。

因此，大多数写作（＆gt; 99.99％）才能完成，一些陈述需要超过40秒。一些康复的Sented Up and datement_timeout设置（它设置为100岁！）。至少可以说是令人困惑的。

第一个假设是数据库上的写卷太多。虽然这部分是真实的，但我们的证据不支持这可能是问题的根本原因，因为它均匀地发生了高写卷，而且在写体积最小的时段中也是如此。

应用程序通常是用批量写作行。这也被认为是可能的原因，并且批量写入被删除。然而，剩余问题（一次在一次写一行时，性能变得更糟）。

此表具有许多访问模式，因此需要众多索引执行井。它有23个B树索引，6个外键约束，3个Brin索引和1个GIN索引（用于全文搜索）。虽然尚不清楚索引在写性能中发挥作用（自从每次写作，你需要每一个索引更新），但这是Notexplain为什么大多数更新都非常快，一些泄漏速度。

最后假设是数据库中可能竞争锁。具体而言，可能很长时间运行的大型事务和长期锁定资源。然后，其他写入等待更新锁定的rowsand无法完成。这似乎是一个很好的假设，它不能被手头的数据被解释。所以是时候进一步调查了

为了帮助我们检查我们的假设，PostgreSQL提供了一些工具。可以通过其配置（PostgreSQL.conf或AWS中的参数组）启用。一些有趣的选择是：

log_lock_waits：启用这将指示Deadlock检测器在陈述超出DeadLock_Timeout时记录日志。没有能够开销启用此操作，因为Deadlock检测器应该运行，如果它是，那么它实际上是免费的（源）

auto_explain：这实际上是许多配置（auto_explain.log_min_duration，auto_explain.log_Analyze等）.They控制后的PostgreSQL何时以及如何在运行查询上自动执行解释。那些也有用的预防措施，以确保表现不佳的陈述将离开痕迹和amp;查询您的计划才能调试。您可以在这里阅读更多

log_statement：这非常有用。它可以启用记录全部/大多数/错误语句等。如果您想找到数据库的错误，这是一个常见的做法，可以将其设置为一段时间，收集outputand与PGBadger等工具分析它。您可以在此处查看所有与日志记录相关的选项

所以，我开始启用这些，重现问题，看看哎呀正在发生什么。

但是，在测试您的舞台实例上无关的东西时，我设法重现了可预测的问题。为此，我所要做的就是在此表上更新5000行。虽然这样做，但每次或两次每5000个更新，更新都会采取＆gt; 40年代！

这是一个真正的祝福，因为它排除了“太多写入音量”假设（我们的暂存数据库有零流量）和龙龙锁，因为没有进程锁定我正在更新的行。

我在有问题的查询上进行了解释分析，看看发生了什么

Update.my_table（成本= 0.43..8.45行= 1宽= 832）（实际时间= 2.037..2.037行= 0循环= 1）缓冲区：共享命中= 152 Read = 1 I / O定时：Read = 1.22 - ＆gt;索引扫描在public.my_table上使用my_table_pkey扫描（成本= 0.43...45行= 1宽度= 837）（实际时间= 0.024..0.026行= 1循环= 1）输出：（...）索引COND :( my_table。 ID = 130561719）缓冲区：共享命中= 4planning时间：1.170麦克风时间：2.133毫秒

Update.my_table（成本= 0.56..8.58行= 1宽度= 832）（实际时间= 34106.965..34106.966行= 0循环= 1）缓冲区：共享命中= 431280 READ = 27724＆lt; -----这是巨大的!! I / O定时：READ = 32469.021 - ＆gt;索引扫描在public.my_table上使用my_table_pkey扫描（成本= 0.56...58行= 1宽= 832）（实际时间= 0.100..0.105行= 1循环= 1）输出：（...）索引COND :( my_table。 ID = 130561719）缓冲区：共享命中= 7planning时间：23.872麦克风时间：34107.047毫秒

两种情况下预测成本是相同的（虽然运行时间明显没有）

最后一个是一个明确的指标，有一个问题。但我无法弄清楚导致的事情。我开始做各种实验，希望减轻它。我试过了：

做一个全部习惯（参考），希望可能发生这种情况，因为Autovacuumdid不起作用。可悲的是，没有结果。

在表上进行分析以强制PostgreSQL更新其统计数据，并更有效地执行查询。再次，没有运气。

在放弃之前，我决定接触大师。我在dba stackexchange上写下了以下帖子，这是数据库管理员和开发人员使用数据库的TargetEdCommunity。

对我来说，令人惊讶的是，即使在我解释了我的案子的具体细节之前，如果我的桌子有一个杜松子酒指数，人们就会在评论中询问。他们非常确定它有一个。此外，他们建议我有一个被称为Fastupdate的东西。

这让我进入了文件（再次）。让我们从中引出一点：

更新GIN索引往往是缓慢的，因为反向索引的内在性质：插入或更新一个堆行可能导致许多插入索引（从索引项目中提取的每个键）。截至PostgreSQL 8.4，GIN能够通过将新元组插入临时未学列表的待处理条目中来推迟大部分工作。

这描述了我们的案例100％。大多数写作，因为他们没有触发待处理列表的清理，很快就会燃烧。但是，触发正常列表清理时（其大小增长超过gin_pending_list_limit），在清理完成待定列表之前执行写入的过程并将索引同步。

我继续检查我的索引是否具有Fastupdate Set。这是索引存储参数中的选项。要检查，您可以使用\ d +＆lt; index_name＆gt;在psql。我没有在那里看到任何东西，而是在Create Index Commandi上读取默认情况下，Fastupdate默认情况下。我将其切换为执行一些测试：

运行像上面的陈述时要小心。对于一个，这将触发锁定，直到索引存储参数已更改.OROVER，禁用Fastupdate意味着您也将手动清理待定列表（使用SELECT GIN_CLEAN_PENDS_LIST（））ORREBUILD索引（使用REINDEX）。这两种情况都可能导致生产系统中的表现或完整性问题，所以要小心。

瞧！问题已经消失了。每次写作都采取了相同，可预测的时间。然而，正如所料，它显着慢。所以我不愿意考虑禁用Fastupdate Altogther。

此时，在我的Stackexchange帖子中提交了完整的解决方案，我看到了其他一些更多可行的选择：

我可以更积极地运行真空，希望它将在背景上清理待处理的列表，并且我的查询永远不会触发清理。但是，我认为这不会再次可靠。

我可以设置更高的gin_pending_list_limit（默认值：4MB）。这意味着清理会真的很少见，但它可能会影响选择的级别（他们也必须阅读待定列表），如果发生清理，则会花费大量的时间。

我可以设置一个后台进程，以定期执行SELECT GIN_CLEAN_PENDS_LIST（）。但是，就像选项1一样，这不会保证任何东西

我可以设置一个较小的gin_pending_list_limit，以便清理更频繁但花费更少的时间。

我决定使用最后一个，并跑一些实验，了解这将如何影响系统。出于好奇心，我甚至丢弃了指数，看看影响性能有多大数量。您可以看到以下结果：

插入5000行的平均时间是相同的，而无需Fastupdate，并且具有任何大小的Gin_Pending_List_limit，预期将为预期的。

更新不触发清理的同时，无论Gin_Pending_List_limit如何（再次，预期）。

具有128KB的值，触发清除的更新需要4秒，这是非常容忍的

当索引被删除时，我们看到了一个巨大的性能提升（使用非批量更新和gt速度快3倍; 6倍以批量更快！）

通过实验，128kb似乎是一个很好的价值。所以我选择了这样的方式。

通过Postgresql.conf（或AWS RDS中的DB参数组）。这会影响所有GIN索引。在AWS RDS中，它不需要重启（它是一个动态参数）。但是，如果您正在运行一个自我管理的PostgreSQL，您将很可能需要重新启动更改PostgreSQL.conf以生效

通过更改索引存储参数（ALTER index＆lt; index_name＆gt; set（gin_pending_list_limit = 128））。但这可能会导致问题的数量（见上文的纸币）

通过改变特定用户的gin_pending_list_limit（更改用户＆lt; user_name＆gt; set gin_pending_list_limit = 128）。这会影响所有新连接，不需要重启。

就个人而言，我会选择第一个。在这种情况下，由于一些不相关的问题，我必须与后者一起去。但他们都可以做到这一点。

在监测1周后，没有随机失败的写作，这是一个惊人的救济，因为这个问题永远存在。整个过程大约需要一个星期，除了获得关于GIN指数内部的知识，它还为Howmuch提供了一些洞察力GIN索引可以影响写入时间并触发了在PostgreSQL中重新考虑的全文搜索的讨论。

有建议吗？ 我喜欢收到你的来信！ 不要在我的任何社交渠道中犹豫不决。