PostgreSQL: 后端进程长时间活动

Question

现在我遇到了一个很大的障碍。

我正在使用PostgreSQL 10及其新的表分区功能。

有时许多查询不返回结果，同时当我通过pg_stat_activity检查后端进程时，许多后端进程是活动的。一开始，我认为这些进程只是在等待锁定，但是这些事务仅包含SELECT语句，其他后端没有使用需要ACCESS EXCLUSIVE锁的任何查询。而且这些仅包含SELECT语句的查询在计划方面也没有问题，并且通常工作正常。计算机资源（CPU、内存、IO、网络）也没有问题。因此，这些事务应该永远不会发生冲突。并且我通过pg_locks和pg_blocking_pids()彻底检查了这些事务的锁定，最终我未能找到任何使查询变慢的锁定。许多处于活动状态的后端仅持有ACCESS SHARE锁，因为它们仅使用SELECT。

现在我认为这些现象不是由锁引起的，而与新的表分区相关。

那么，为什么会有许多后端处于活动状态呢？

能否有人帮助我？任何评论都将不胜感激。

下面的图片是pg_stat_activity结果的一部分。如果您需要其他信息，请告诉我。

编辑

我的查询不处理大量数据。返回类型如下：

uuid UUID
,number BIGINT
,title TEXT
,type1 TEXT
,data_json JSONB
,type2 TEXT
,uuid_array UUID[]
,count BIGINT

由于它有JSONB列，我无法计算出确切的值，但它不是大型JSON。 通常这些查询速度适中（约1.5秒），因此绝对没有问题，但当其他进程工作时，会发生这种现象。 如果统计信息不正确，则查询始终很慢。

编辑2

这是统计数据。由于连接数接近100，我无法显示所有统计信息。

- Kazuya Tomita

---

1. 当您的查询从表中返回大量数据并且您将该数据显示在查询工具上而不是插入到其他表或进行任何其他操作时，有时会出现这种情况。 您不应仅为了显示大量数据而进行查询。
2. 或者可能是由于缺少统计信息。请对该表进行分析和清理。您能否发布查询和返回的数据量？

- Anuraag Veerapaneni

在生产环境中遇到了类似的问题，有时活动查询的数量达到了±200。在我的情况下，所有的查询都是不同的，并且正在查询不同的表，但是和你的情况一样，并没有被任何写锁定。在调查过程中，我们尝试了调整work_mem和shared_buffers，这确实有所帮助。在你的情况下，我会采取同样的方式。我们通过DataDog监控我们的DB服务器，如果你有类似的监控可用，你可能可以调整配置来解决你的问题。我们使用的是9.3版本，没有分区表。 - Sergey Telshevsky

@Mohamed Anees A 请看EDIT2。我已经展示了，但是你为什么想要查看它呢？我不明白你为什么想要检查application_names。 - Kazuya Tomita

@Sergey Telshevsky 很好的信息！今天，我将 work_mem 从4MB改为32MB。然而，出现了一些问题。我没有改变 shared_buffers，因为 shared_buffers 大于内存的30%。在您的情况下，您如何处理这些参数？ - Kazuya Tomita

我也会选择共享缓冲区。这取决于您的情况，但是 - PG使用系统缓存，因此简而言之，您有30％的内存重复。此外 - 只有一个写入器。这不像8.x那样有很多问题，但我不会让他太忙。 - Michał Zaborowski

Answer 1

对我来说，这看起来像是应用程序问题，而不是postgresql的问题。active状态意味着您的事务仍未提交。

那么为什么您的应用程序可能无法向数据库发送commit呢？

尝试检查一下您的应用程序代码中何时打开事务，读取数据，提交事务和回滚事务。 编辑： 顺便说一句，为了确保，请在出现问题之前和查询开始挂起时检查资源使用情况。尝试运行top和iotop来检查是否在出现问题时postgres真的像疯了一样占用了您的cpu或disk。如果没有，我建议您在应用程序中寻找问题。

- Konstantin Labun

Answer 2

谢谢大家。

我终于解决了这个问题。 我注意到一个后台进程持有太多锁。因此，当我执行查询SELECT COUNT(*) FROM pg_locks WHERE pid = <pid>时，结果大约为10000。 locks_per_transactions参数为64，max_connections约为800。 因此，如果持有许多锁的查询数量很大，则会发生内存短缺（如果您感兴趣，请参见PostgreSQL中共享内存的计算代码）。 当我执行像SELECT * FROM（分区表）这样的查询时，会导致太多的锁。想象一下你有一个名为foo的分区表，表的数量为1000。然后您可以执行SELECT * FROM foo WHERE partion_id = <id>，后台进程将持有约1000个表锁（和索引锁）。因此，我将查询从SELECT * FROM foo WHERE partition_id = <id>更改为SELECT * FROM foo_(partitioned_id)。结果，问题似乎已经解决了。

- Kazuya Tomita

Answer 3

您说：

有时许多查询不会返回结果，但是当其他进程工作时，这种现象会发生。如果统计信息不正确，查询通常会很慢。

直接连接到Postgres实例并运行您需要的查询时，它们不会返回/很慢，或者在从应用程序运行查询时也会出现相同的问题？运行的后端进程，您能够成功地使用pg_terminate_backend($PID) 来终止它们吗，或者还有其他问题？为了排除语句本身存在的问题，请确保将 statement_timeout 设置为合理的超时时间以中止长时间运行的查询。在排除了这些问题之后，也许您遇到了一个应用程序挂起并且永远不允许来自PostgreSQL的send调用完成的情况。为了避免这种情况，如果可以（取决于操作系统），您可以调整保持活动状态的时间：https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-TCP-KEEPALIVES-IDLE（默认是2小时）

如果尝试以上任何操作对您的问题有所帮助，请告诉我们。

- Aaron Rumery

Answer 4

抱歉晚发帖，正如@Konstantin所指出的那样，这可能是由于您的应用程序（这就是我要求您的EDIT2的原因）。添加一些摘录：

• 表分区 对这些锁没有影响，那是完全不同的概念，不会在您的情况下阻塞锁。
• 在您的应用程序中，请检查连接是否在read()之后正确地close()并且在finally块中（从Java角度）。我不确定您的应用程序层。
• 检查最近是否错误地编写了SELECT..FOR UPDATE或任何类似的语句，导致此问题。
• 检查最近是否有任何表增大了，并且该列未被索引。这是选择语句运行几分钟的非常重要和频繁的原因。我还建议在应用程序中使用timeouts来选择语句。https://www.postgresql.org/docs/9.5/gin-intro.html 这可以让您提前开始。
• 我觉得可疑的另一件事是JSONB列，也许您的Jsonb值非常长，或者查询即使不需要也会不必要地选择JSONB值？
• 最后，如果您不需要Jsonb数据类型的某些特殊功能，则可以使用更快的JSON数据类型（神奇的最大值，有时为50倍！）

- Mohamed Anees A

Answer 5

看起来池化连接没有正确关闭，一些查询可能需要很长时间才能回应。正如其他答案所指出的那样，这是应用程序的问题，可能是连接泄漏。最有可能的是，这可能是由于某些已经挂起且未解决的事务上的待处理事务导致了许多未关闭的事务。

此外，PostgreSQL通常有一个或多个“helper”进程，例如stats collector、background writer、autovaccum daemon、walsender等，所有这些进程都显示为“postgres”实例。

我建议您检查代码的哪个部分启动了queries。尝试在应用程序之外运行您的查询并对查询性能进行一些benchmarking。

其次，您可以为某些查询（如果不是全部）设置一些timeout。

第三，您可以使用以下方法在一定时间后终止空闲事务：

SET SESSION idle_in_transaction_session_timeout = '5min';

希望它能够正常工作。干杯！

- Vikas Yadav