Postgres和.Net - 连接池 - 最佳实践

Question

我是一位有用的助手，可以为您翻译文本。

我有一个使用websocket接收用户请求并连接到PostgreSQL数据库以处理数据的.Net Core（C#）应用程序。

当用户向后端发出新请求时，调用的端点函数将创建一个新的SQL连接并运行查询。

// Endpoint available via Websocket
public async Task someRequest(someClass someArg)
{
    /* Create a new SQL connection for this user's request */
    using var conn = new NpgsqlConnection(connstr.getConnStr());
    conn.Open();

    /* Call functions and pass this SQL connection for any queries to process this user request */
    somefunction(conn, someArg);
    anotherFunction(conn, someArg);

    /* Request processing is done */
    /* conn is closed automatically by the "using" statement above */
}

当此请求完成时，连接将被 using 语句关闭。然而，默认情况下，此连接将返回到Postgres的“连接池”中，并显示为空闲。

由于每个新用户请求都会创建一个新的SQL连接，因此连接池中的旧的“空闲”SQL连接永远不会再次使用。

目前：

1. 由于这些空闲连接堆积并达到最大连接池大小，我暂时将空闲连接超时设置得非常低。否则，这些空闲连接会堆积并达到打开连接的人工上限。 2. 我也尝试在连接字符串中添加 Pooling=false。我的理解是，这将阻止连接在 .Net 应用程序关闭后保持空闲，但它似乎仍在空闲状态。

问题：在 .Net/C# 中处理Postgres的连接池的最佳实践是什么？

1. 如果我能更好地利用Postgres连接池，重复使用已经打开的连接比为每个用户请求创建一个新的连接更有效率。
2. 我的想法是有一个函数来创建新的Postgres连接，跟踪它们，并在用户发出新请求时将它们分配给调用者。这是一个可怕的想法吗？
3. 或者，我只需禁用连接池/设置非常低的空闲超时，并像现在一样为每个请求创建一个新的SQL连接吗？

除了我正在做的事情之外，我无法找到许多正确利用Postgre连接池的例子。此应用程序平均有3,000-4,000个并发用户，因此我不能有一个单一的静态连接处理所有内容。在.Net中处理这个问题的最佳实践是什么？

编辑 看起来连接池是NPGSQL本地支持的而不是Postgres。如果使用相同的数据库、用户、密码创建新连接，它将使用其中一个空闲的“池化”连接而不是打开另一个连接。

问题在于，在我禁用连接池之前，似乎它并没有这样做。在晚上，一直有错误消息被发送，导致应用程序停机了一个小时甚至更长时间。

连接池已用尽，请提高MaxPoolSize（当前为100）或Timeout（当前为15秒）。现在可能确实需要同时使用100多个活动连接，但我猜其中大部分是空闲的，就像我以前看到的那样。

编辑#2：我现在尝试允许连接池（默认设置），似乎它会立即增加空闲连接，因为它们由请求创建，但不会重用这些连接。一旦达到最大池容量，应用程序由于无法创建新连接/请求而被锁定。

DBeaver Img-服务器会话： 这里的红色是活动连接，蓝色是空闲连接。

每个应用程序中的SQL连接都是从一个单一/共享的连接字符串环境变量创建的。 Host=IP;Port=somePort;Username=someUser;Password=somePass;Database=someDb;Maximum Pool Size=100 我能保持应用程序运行的唯一方法是将idle_in_transaction_session_timeout设置为'10s'，以便经常清除空闲连接，因为池似乎不起作用。

当我使用postgres清除空闲连接时，idle_in_transaction_session_timeout和Pooling=false，我的数据库活动就像这样：

我还在我的代码中进行了搜索，每个新建SQL连接的实例都使用了using语句，如上面的代码示例所示。这应该可以防止任何类型的连接泄漏。

是否有某种postgres配置项会导致此问题？每次连接字符串都相同，每个连接都有C#的using语句。我不确定为什么NPGSQL在启用池化时没有重复使用这些空闲连接。

我已在我的开发服务器上测试了在循环中大量创建新连接，并且池化似乎完全正常。因此，我可以确定我所拥有的using语句格式不会引起任何问题。但是，如果我现在在生产服务器上启用池化，则空闲连接立即增加并达到上限，不允许新连接。生产服务器的指标显示每秒约1,000个事务和每秒约4-5个活动SQL会话/连接。我是否需要增加最大池限制？

- Brian S

---

确保每次打开连接时连接字符串都相同；如果是这样，Npgsql的池应该可以正常工作并从池中获取现有的空闲连接。如果仍然遇到问题，您可以尝试增加MaxPoolSize以查看是否有所帮助（如果您的实际需求更高）。如果仍然无法解决问题，请检查您是否在所有地方都关闭了连接（例如使用using）-如果没有，这将导致泄漏，这可能会解释这个问题。 - Shay Rojansky

@ShayRojansky，感谢您的回复。就我而言，池化似乎无法正常工作。所有的SQL连接都是使用一个不变的环境变量创建的。如果我在这个连接字符串中启用池化，空闲连接将会增加到200（最大值），并且不允许新的连接。 - Brian S

我创建了一个API命令，可以动态更改连接字符串以测试开启或关闭连接池的情况。Host=IP;Port=5432;Username=someUser;Password=somePass;Database=someDb;Maximum Pool Size=200我将修改主贴以展示更多细节。 - Brian S

我已经在我的开发服务器上测试了在循环中垃圾邮件新连接，并且池化似乎工作得很好。因此，我可以确定我使用的using语句格式没有问题。但是，如果我现在在生产服务器上启用池化，空闲连接会立即向上垃圾邮件并达到上限，不允许新连接。生产服务器的指标显示每秒约1,000个事务和每秒约4-5个活动SQL会话/连接。我是否只需要增加最大池限制？我尝试将其设置为200，但该限制在几秒钟内就达到了。 - Brian S

如果您在某个时刻需要超过200个连接，那么是的，您需要增加最大池大小 - 但这是相当大的负载（或者可能意味着您的查询运行非常缓慢）。然而，我建议您再次检查连接字符串是否始终保持不变等等... 您还可以通过检查Npgsql的日志来了解更多信息。 - Shay Rojansky

Answer 1

问题最终似乎与Postgres数据库配置本身有关。我们收到了许多“pq：could not resize shared memory segment. No space left on device”错误信息。

该数据库在Docker容器中运行。我将容器的shm_size增加到6gb，以利用更多正在运行的资源。

从那里开始，在DB本身的postgresql.conf文件中，对于shared_buffers和max_connections设置似乎存在很多混淆。

对于shared_buffers，根据Postgres文档，应将其设置为shm_size的1/4，即1500M。这曾经被设置为128M，对于容器内存大小而言太低了。

对于max_connections，将其降低回默认值100。即使有3000-5000个并发用户，后端也只会在任何给定时刻使用5-10个活动连接。

希望这些配置项能够帮助未来的某些人。

- Brian S