在连接Postgres方面，节点速度比.NET Core快20倍

本文介绍了在连接Postgres方面，节点速度比.NET Core快20倍的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个服务器连接到Azure上托管的 PostgresSQL 9.6 数据库。服务器正在做一件事-每5秒用 SELECT 1 查询访问Postgres数据库。

I have two servers connecting to a PostgresSQL 9.6 db hosted on Azure. The servers are doing one thing - hitting the Postgres db with a SELECT 1 query every 5 seconds.

典型时间连接到数据库并获取数据：

Typical time to connect to db and get data:

• 节点： 25 MS
• .NET Core 3.1使用Npsql 4.1.1（我也尝试过4.1.2，没有差异）： 500 MS

• Node: 25 MS
• .NET Core 3.1 using Npsql 4.1.1(I have tried 4.1.2 too, no diff): 500 MS

我的问题是，我的.NET Core应用程序在获取数据方面比Node慢了20倍。我相信。由于某些原因，.NET Core没有建立连接池。在本地运行应用程序和在Azure App Services上运行应用程序时都会出现这种缓慢情况-没什么区别。 我想解决.NET-> Postgres的缓慢性。

My problem is that my .NET Core app is 20x slower than Node in getting data. I believe .NET Core is not pooling connections for some reason. This slowness occurs with both running the app locally and while running it on Azure App Services - no difference. I want to solve the .NET --> Postgres slowness.

请仅略过相关细节，而不要阅读过去的内容。这一点-我相信只有 .NET Core 代码才有意义。

Please only skim the relevant details and don't read the whole thing past this point - I believe only the .NET Core code is relevant.

A PsPing 从我的机器（在 Node 和 .NET Core 应用正在运行：

A PsPing to the db from my machine (on which both the Node and the .NET Core apps are running:

Connecting to foobarPostGres:5432 (warmup): from someIp: 19.98ms Connecting to foobarPostGres:5432: from someIp: 1.65ms Connecting to foobarPostGres:5432 from someIp: 1.18ms Connecting to foobarPostGres:5432: from someIp: 1.23ms Connecting to foobarPostGres:5432: from someIp: 1.06ms

为了完整起见，请使用 NODE 时间看起来像这样（请注意，它第一次建立连接时也是慢的）：

For sake of completeness, a sample of NODE times look like this (note that the first time it establishes a connection, it is also "slow"):

Attempting to establish a connection... Elapsed ms: 644.1334999799728 RESP: { '?column?': 1 } Elapsed ms: 22.76109904050827 RESP: { '?column?': 1 } Elapsed ms: 21.984400033950806 RESP: { '?column?': 1 } Elapsed ms: 26.043799996376038 RESP: { '?column?': 1 } Elapsed ms: 22.538798987865448 RESP: { '?column?': 1 }

.NET Core的连接时间看起来像这样：

5:13:32 PM: SLOW QUERY, CONN TIME: 4153, QUERY TIME: 18 5:13:53 PM: SLOW QUERY, CONN TIME: 707, QUERY TIME: 17 5:14:14 PM: SLOW QUERY, CONN TIME: 589, QUERY TIME: 16 5:14:35 PM: SLOW QUERY, CONN TIME: 663, QUERY TIME: 18 5:14:56 PM: SLOW QUERY, CONN TIME: 705, QUERY TIME: 16

请注意，超级慢的初始连接时间和很长的时间来建立后续请求的连接。

Note the super-slow initial connection time and a long time to establish a connection on subsequent requests.

无论如何，因为我很拼命，所以我现在将转储我的所有代码，并附上解释。连接字符串如下所示：

Anyway, because I am desperate, I am going to dump all my code now, with explanations. The connection string looks like this:

public static string CONNECTION_STRING { get { return $"Server={HOST}; User Id={USER}; Database={DB_NAME}; Port={PORT}; Password={PWD}; SSLMode=Prefer"; } }

据我了解，我应该将连接池池化如果使用此连接字符串，请单击框。请注意，我已经尝试过在两个数据库上都打开 SSL 并删除该行-这样做没有帮助。

It is my understanding that I should get connection pooling out of the box if I use this connection string. Note that I have tried turning of SSL on both the db and taking that line out - it did not help.

我的健康检查控制器如下所示：

My health check controller looks like this:

// GET api/health/getdbhealthselectone [HttpGet] [Route("getdbhealthselectone")] public async Task<IActionResult> GetDbHealthSelectOne() { int testData = await _healthCheckRepo.RunHealthCheckSelectOne(); return Ok(testData); }

我的健康检查回购方法如下：

My health check repo method looks like this:

public async Task<int> RunHealthCheckSelectOne() { await using var conn = new NpgsqlConnection(AzureDbConnectionInfo.CONNECTION_STRING); var connTimer = System.Diagnostics.Stopwatch.StartNew(); // TODO: Remove this testing line await conn.OpenAsync(); connTimer.Stop(); // TODO: Remove this testing line var msToConnect = connTimer.ElapsedMilliseconds; // TODO: Remove this testing line int testData = 999; var jobsQueryTimer = System.Diagnostics.Stopwatch.StartNew(); // TODO: Remove this testing line0 await using (var cmd = new NpgsqlCommand("SELECT 1", conn)) await using (var reader = await cmd.ExecuteReaderAsync()) while (await reader.ReadAsync()) { testData = reader.GetInt32(0); }; jobsQueryTimer.Stop(); // TODO: Remove this testing line var msToQuery = jobsQueryTimer.ElapsedMilliseconds; // TODO: Remove this testing line LogQueryIfSlow(msToConnect, msToQuery, _logger); // TODO: Remove this testing line return testData; }

请注意此处的计时器- await conn.OpenAsync（ ）; 是目前为止花费时间最多的部分，查询本身很快。另外，为了节省时间-我之前没有 async 地运行了这段代码，没有区别。

Note the timers here - await conn.OpenAsync(); is what takes the bulk of the time by far, the queries themselves are fast. Also, for sake of saving time - I have run this code WITHOUT async before, no difference.

最后，如果存在依赖项注入问题，则该存储库位于类库中，API项目会引用该存储库，并且：

Finally, in case there are dependency injection concerns, the repository is in a class library, the API project references it, and:

services.AddSingleton< IHealthCheckRepository ，HealthCheckRepository>（）;

这就是它的外观。

我相信这是所有相关信息-我一直在与Azure支持电话联系，他们发现数据库配置没有问题。 .NET Core应用程序非常轻巧，因此它不像它已经超载并且正在测试中，因此除了我的测试之外，没有任何流量。

I believe this is all the relevant information - I have been on the phone with Azure support and they found no issues with the db config. The .NET Core app is super light, so it's not like it's overloaded and it's in testing, so no traffic besides my tests.

Extra ：为了完整起见，这是我的整个节点应用程序，它可以打入数据库并发布性能（连接数据已取出）。

Extra: For the sake of completeness, here is my WHOLE node app which hits the db and gets the performance posted (conn data taken out).

const { Pool, Client } = require('pg'); const { performance } = require('perf_hooks'); const pool = new Pool({ user: 'SECRET', host: 'SECRET', database: 'SECRET', password: 'SECRET', port: 5432, }) function runQuery(pool) { var t0 = performance.now(); pool.query('SELECT 1', (err, res) => { if (err) { console.log('ERROR: ', err.stack) } else { console.log('RESP: ', res.rows[0]) } var t1 = performance.now(); console.log('Elapsed ms: ', t1-t0); //pool.end() }); } setInterval(() => {runQuery(pool)}, 5000);

编辑：为了后代，以下是时间修复连接池超时后的.NET Core –它比节点快，但在初始连接上似乎需要一段时间，但我没有检查一些默认值：

For posterity, here are the times in .NET Core after fixing the connection pool timeout - it's faster than node, except on that initial connection, which seems to take a while, but I haven't checked some defaults:

CONN: 1710 QUERY: 18 CONN: 0 QUERY: 16 CONN: 0 QUERY: 16 CONN: 0 QUERY: 17 CONN: 0 QUERY: 16 CONN: 0 QUERY: 23 CONN: 0 QUERY: 16 CONN: 0 QUERY: 16 CONN: 0 QUERY: 23 CONN: 0 QUERY: 16 CONN: 0 QUERY: 16

推荐答案

您需要设置最小池大小。这样做可以确保无论池使用情况如何，该数量的连接都对DB保持开放状态。

You need to set a min pool size. Doing so ensures that this amount of connections remains open to the DB regardless of the pool usage.

默认情况下（至少对于 NPGSQL ），最小大小为0，因此如果一段时间不使用该连接，它将被关闭。

By default (at least for NPGSQL), the min size is 0, so if the connection is not used for a while, it will be closed.

在您的测试中，您每5秒拨打一次电话，这并不多，并且该池可能决定关闭未使用的连接。根据文档，它应该保持打开状态300秒，而不只是15

In your test, you do one call every 5 seconds, which is not much, and the pool might decide to close the unused connection. According to the doc it should keep it open for 300 seconds though, not just 15