主页 / dba / 问题 / 17653
Accepted
Bhavik Ambani
Asked: 2012-05-10 09:40:53 +0800 CST

更新具有数千万条记录的表时性能下降

  • 772

我想更新表（我的是 20-30），每个表都有数千万条记录。

问题是更新过程花费了太多时间，而且此时 CPU 使用率也非常高。我想以这样一种方式进行处理，即它在处理数据时不能使用太多 CPU。如果处理时间增加，那么这对我来说不是问题，但它应该使用有限的 CPU 资源来处理（更新）表。我使用 PostgreSQL 作为数据库，服务器操作系统是 Linux。

我的示例查询可以是这样的

UPDATE TEMP 
SET CUSTOMERNAME = 
  ( select customername from user where user.customerid = temp.customerid );
postgresql performance

4 个回答

  1. Best Answer

    第一个问题是：为什么不使用大量 CPU 时间很重要？查询将在某些资源上出现瓶颈；如果可以引入足够多的额外磁盘访问，每秒使用的 CPU 时间就会减少，但这真的是一种改进吗？您希望饱和什么资源？了解您为何强调这一点可能有助于引导人们提供您认为有用的答案。

    正如评论中所建议的那样，您的查询使用连接而不是相关子查询可能会运行得更快。是这样的：

    UPDATE temp
  SET customername = user.customername
  FROM user
  WHERE user.customerid = temp.customerid;

    另一件需要知道的重要事情是您是否要更新表中的所有行。有些值已经正确了吗？如果是这样，您将通过不更新不需要它的行来获得很大的性能提升。添加AND temp.customername is distinct from user.customername到WHERE条款中。

    如果限制每个语句中更新的行数，并且VACUUM ANALYZE在每次 UPDATE 之后，您将避免表膨胀。如果最小化 CPU 时间的目的是为了避免对并发事务的性能影响，这会给你机会在开始一组的sleep下一个之前引入一个短暂的延迟（以 a 或类似的形式）UPDATE行。

    更好的是，为什么要将信息冗余地存储在临时表中而不是在需要时加入它？（有时有充分的理由；通常没有。）

    • 10

  2. 如果您遵循了 kgrittn 的非常好的建议，但仍然存在性能问题，您可能需要分批执行更新。您仍然会执行基于集合的更新，但将它们限制为前 1000 个（或任何适合您的数字，我使用了 500 到 50,000 个）不匹配的记录，然后继续循环直到完成所有记录。

    • 5

  3. 如果有索引TEMP.CUSTOMERNAME并且您正在更新表的重要部分，TEMP则在更新之前删除该索引并在更新之后重建它。

    • 1

  4. PostgreSQL 无法减少进程可以使用的 CPU 时间量。在 Linux 上，您可以使用renice命令等操作系统功能来执行此操作。有关更多信息和一些示例，请参阅优先级。

    • 1

相关问题