时间戳

首页 > 解决方案 > 获取连接超时。游泳池可能已经满了。更新 10000 行时出错

node.js - 获取连接超时。游泳池可能已经满了。更新 10000 行时出错

问题描述

当我尝试使用 knexjs 更新 5000 行时，我收到错误超时获取连接。游泳池可能已经满了。”。

当我查看 CPU 使用率时。我发现 postgres pid 总是占用 90-98% 的 CPU 使用率，这是不正常的，我在每个 kenx 上都尝试通过 destroy() 进行尝试，但它破坏了连接并且没有解决它

这是我正在使用的代码

const knexDb = knex({ client: 'pg', connection: {
    host : '127.0.0.1',
    user : process.env.DB_USER,
    password : process.env.DB_PASSWORD,
    database : process.env.DB_DATABASE,
    port: process.env.DB_PORT
  }});

arrayWith5ThousandObj.map(data => {
    knexDb('users').where({
      user: data.user,
    })
    .update({
      product: data.product
    })
    .catch(err => console.error('update user products', err))
})

这是一个循环函数，每 1 分钟重复一次，我也尝试过.finally -> knexDb.destroy()，但它破坏了连接，我得到错误无法获取连接。

我想使用 knexjs 不断更新 5000 行或更多行，比如 10,000+，而且我认为 PostgreSQL 可以处理这个其他明智的大型网站，它每分钟执行 10s 数千个查询而不会出现问题。问题不在服务器上，因为服务器有 10 个 CPU 和 16gb 的 RAM，所以资源不是问题，我停止了服务器上所有正在运行的进程，除了这个应用程序。postgres pid 几乎完全不使用 CPU。所以问题在大量查询中发生。是否有批量更新，我可以使用 knexjs 一次更新所有 10,000 多行？

我最近尝试过这个解决方案

return knexDb.transaction(trx => {
    const queries = [];
    arrayWith5ThousandObj.forEach(data => {
        const query = knexDb('users')
            .where({
              user: data.user,
            })
            .update({
                product: data.product,
            })
            .transacting(trx); // This makes every update be in the same transaction
        queries.push(query);
    });

    Promise.all(queries) // Once every query is written
        .then(trx.commit) // We try to execute all of them
        .catch(trx.rollback); // And rollback in case any of them goes wrong
});

但我收到此错误：

{ error: deadlock detected
   at Connection.parseE (/*********/connection.js:601:11)
   at Connection.parseMessage (/*********/connection.js:398:19)
   at Socket.<anonymous> (/**********/connection.js:120:22)
   at Socket.emit (events.js:189:13)
   at addChunk (_stream_readable.js:284:12)
   at readableAddChunk (_stream_readable.js:265:11)
   at Socket.Readable.push (_stream_readable.js:220:10)
   at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
 name: 'error',
 length: 340,
 severity: 'ERROR',
 code: '40P01',
 detail:
  'Process 9811 waits for ShareLock on transaction 443279355; blocked by process 9808.\nProcess 9808 waits for ShareLock on transaction 443279612; blocked by process 9811.',
 hint: 'See server log for query details.',
 position: undefined,
 internalPosition: undefined,
 internalQuery: undefined,
 where: 'while locking tuple (1799,4) in relation "users"',
 schema: undefined,
 table: undefined,
 column: undefined,
 dataType: undefined,
 constraint: undefined,
 file: 'deadlock.c',
 line: '1140',
 routine: 'DeadLockReport' }

标签： node.jspostgresqlknex.js

解决方案

Knex 并不是真正适合这种大型浴缸更新的工具。特别是在您使用它的方式上，它的性能特别差。

在初始化 5k 个查询构建器时，所有构建器都是同时编译和执行的，但在使用事务时，所有查询都是通过单连接发送的。

因此，无论如何，所有更新都以串行方式发送到数据库服务器，并且这些更新的并发性为 0。

因此，有 5000 个 knex 对象被编译，5000 个带有绑定的 SQL 查询发送到 DB 驱动程序，然后它们被驱动程序缓冲并一一发送到服务器。

但这不应该导致死锁......所以你的代码中可能还有其他一些问题。

如果在查询中出现单个错误时不恢复所有数据并不重要，您可以尝试在多个事务中使用较小的批次...实际上我不明白为什么需要进行这种数据更新如果单行存在问题，则可以在事务中重新发送/记录。

我最好的建议是从数据库服务器设置批处理大小、连接池大小和连接限制，以匹配您推送到服务器的工作负载。

一直看到 postgreSQL pids CPU 使用率 98%

如果您通过单个事务进行大量更新，那么它确实不太可能导致高 CPU 使用率。您应该登录到该 SQL 服务器并检查在该工作负载期间它正在执行什么样的查询......也许您偶然在不同的事务中多次并行运行相同的更新代码，这也可以解释死锁问题。

批量更新对 SQL 来说是一个很大的问题，因为单个更新语句只能更新一行。在单个查询中运行多个更新的一种方法是使用 CTE 查询https://www.postgresql.org/docs/current/queries-with.html

这样您就可以构建一批更新查询并将它们添加为主查询的预查询https://knexjs.org/#Builder-所有这些查询都在数据库中作为原子操作运行，因此不需要事务来确保要么整批要么什么都不进去。

推荐阅读