主页 / dba / 问题 / 123244
Accepted
davetapley
Asked: 2015-12-09 14:37:45 +0800 CST

通过批量迭代表来更新比在 PostgreSQL 中的整个表更快

  • 772

我有一张大约有一百万行的表。

它正在生产中使用，我运行了一个UPDATE覆盖约 95% 的行。

五个小时后，我取消了请求，因为它花了很长时间。

该表有一个自动递增的 ID 列，因此我尝试将WHERE查询条件扩展为包含id BETWEEN 1 AND 500.

此更新在大约两秒钟内完成。然后我手动迭代了id500 个批次，例如BETWEEN 500 AND 1000，然后BETWEEN 1000 AND 1500。

按照这个速度，将需要 2000 批 500 来更新整个表。

以 2 秒的速度更新 2000 个批次仅需要一个多小时。

我的问题是：

  1. 造成这种差异的原因是什么？
  2. 我不关心事务隔离，所以有没有一种方法可以模拟这种“批量更新”，而无需编写 2000 次更新的脚本以单独运行。
postgresql performance

1 个回答

  1. Best Answer

    我会尽力简短地回答你的问题，但由于我并不真正了解你对 PostgreSQL 的舒适程度，而且我也没有太多时间进行深入的解释，所以我'将使答案保持简单，如果您想了解更多信息，可以要求澄清。

    1）为什么批量更快？

    由于PostgreSQL 的预写日志的结构、 RAM 中共享缓冲区空间的数量以及尝试UPDATE在单个事务中执行整个操作，我的猜测是您根本没有足够的计算资源来有效地处理更新到单次交易中有近百万条记录。

    PostgreSQL 有一个完善的并发控制系统，本质上意味着它必须UPDATE在你的操作期间保持你的 pre-rows 的旧副本可用UPDATE。这样一来，如果另一个客户端在您更新时尝试访问这些行、更新失败或您取消更新，您不会丢失旧信息。

    如果执行足够大的UPDATE，PostgreSQL 会将页面加载到内存中并修改它们，但最终会耗尽内存来处理，因此如果它希望能够加载更多页面，它会被迫立即将这些页面临时复制到磁盘并继续交易。

    而不是能够在一段时间内分摊磁盘写入，您只是迫使您的数据库进入瓶颈。

    2) 编写更新脚本

    您绝对可以通过在PL/pgSQL中创建函数来编写更新脚本。有很多关于 PL/pgSQL 的知识，包括很多我可能不知道的，但一般来说，你可以做这样的事情

    CREATE OR REPLACE FUNCTION mini_batch_update()
  RETURNS void AS
$BODY$
DECLARE

id_val integer;

BEGIN

  FOR id_val IN 0..2000
  LOOP

  **UPDATE GOES HERE**
  WHERE id > (500 * id_val) AND id <= 500 * (id_val + 1);

  END LOOP;


END
$BODY$
LANGUAGE plpgsql;

    我并没有花很多时间来使这个批处理功能处于最佳状态。我的意思是，为了简单起见，我只是硬编码了几个数值。在您的情况下，您可能希望获得更详细的信息并包括：1）检查最大 id 值以便您适当设置界限的东西，以及 2）即使我硬编码了 500 个批次，您也可以轻松地做到这一点函数输入参数。

    抱歉，我没有时间对此进行测试或确保它确实运行良好。祝你好运！

    • 10

相关问题