Postgresql截断速度

Question

Postgresql截断速度

72

我们使用 Postgresql 9.1.4 作为我们的数据库服务器。我一直在尝试加速测试套件，因此开始对数据库进行分析，以确定究竟发生了什么。我们使用database_cleaner在测试结束时截断表。是的，我知道事务更快，但在某些情况下我不能使用它们，所以我不关心这个问题。

我关心的是TRUNCATE为什么如此耗时（比DELETE更耗时），以及在我的CI服务器上需要更长的时间。

目前，在本地（Macbook Air上）完整的测试套件需要28分钟。查看日志，每次我们截断表格...即:

TRUNCATE TABLE table1, table2  -- ... etc

执行截断需要超过1秒的时间。在我们的CI服务器上（Ubuntu 10.04 LTS）查看日志，截断表需要整整8秒钟，而构建需要84分钟。

当我切换到:deletion策略时，我的本地构建时间为20分钟，而CI服务器则降至44分钟。这是一个显著的差异，我真的很惊讶它可能是什么原因。我已经调整了 CI服务器上的数据库，它有16GB的系统内存、4GB的共享缓存...和一块SSD。所有都是好东西。怎么会:

a. 它比我的2GB内存的Macbook Air慢那么多
b. TRUNCATE比DELETE慢如此之多，当postgresql文档明确说明应该更快。

有什么想法吗？

- brad

你是在 MacBook 上运行测试和数据库，还是在 CI 服务器上运行测试和数据库？测试和数据库在同一台机器上吗？ - Szymon Lipiński

1

顺便说一句，你做错了... 你不能在测试之后清除数据库。你应该在运行测试之前清除它。你不能确保在测试后数据库被清除。 - Szymon Lipiński

哪些postgresql.conf参数正在使用？我想知道你是否正在运行fsync = off（如果您不介意丢失所有数据，例如在测试中，则可以）在这种情况下，“DELETE”和“TRUNCATE”之间的平衡可能会有所不同。我还对您的shared_buffers感兴趣。 - Craig Ringer

当你说“使用事务”时，是指打开一个事务、进行一些测试，然后回滚吗？因为在我看来，这只是测试的一半。如果你使用 SERIALIZABLE 事务、DEFERRABLE INITIALLY DEFERRED 约束等，在提交时可能会发生很多事情，所以提交测试更明智。 - Craig Ringer

共享缓冲区=4GB；fsync=关闭；表的数量约为50。未知数据量...但是每个表的行数不超过20行，用于特定测试。 - brad

显示剩余3条评论

4个回答

6

Brad，提醒您一下。我已经深入研究了一个非常类似的问题。

相关问题：30个表中有少量行- TRUNCATE是清空它们并重置附加序列的最快方法吗？请还要查看这个问题和 pull request: https://github.com/bmabey/database_cleaner/issues/126 https://github.com/bmabey/database_cleaner/pull/127 还有这个帖子：http://archives.postgresql.org/pgsql-performance/2012-07/msg00047.php 很抱歉以回答的方式写这篇文章，但我没有找到任何评论链接，可能是因为那里已经有太多评论了。

- Stanislav Pankevich

嘿，谢谢Stanislaw。实际上我看到了那些帖子，这促使我升级了数据库清理器以使用大规模截断。然而，这对我帮助不大。在PG上似乎删除策略更快，这就是我最终使用的方法。 - brad

1

最近我遇到了类似的问题，即：

在使用 DatabaseCleaner 运行测试套件时，不同硬件系统之间运行时间差异很大，
将 DatabaseCleaner 策略更改为 :deletion 可以提供约10倍的性能提升。

缓慢的根本原因是用于数据库存储的具有日志记录功能（ext4）的文件系统。在 TRUNCATE 操作期间，日志记录守护程序（jbd2）使用了约90％的磁盘 IO 容量。我不确定这是否是错误、边缘情况还是实际上在这些情况下的正常行为。然而，这解释了为什么 TRUNCATE 比 DELETE 慢得多-它生成了更多的磁盘写入。由于我不想真正使用 DELETE，所以我采取了设置 fsync=off 的方法，这足以缓解这个问题（在这种情况下数据安全性并不重要）。

- Maksym Kammerer

0

考虑一些替代方法：

创建一个空数据库，并在其中添加静态“fixture”数据，然后在其中运行测试。完成后，只需删除该数据库即可，这应该很快。
创建一个名为“test_ids_to_delete”的新表，其中包含表名和主键ID的列。将删除逻辑更新为将ID/表名插入此表中，这比运行删除要快得多。然后编写一个脚本“离线”运行以实际删除数据，可以在整个测试运行完成后或隔夜运行。

前者是“清洁室”方法，而后者意味着某些测试数据将在数据库中更长时间存在。我正在使用“脏”方法进行测试套件，其中包含约20,000个测试。是的，有时由于在开发数据库中有“额外”的测试数据而会出现问题。但有时这种“肮脏”有助于我们找到并修复错误，因为“混乱”更好地模拟了真实世界的情况，这是清洁室方法永远不会做到的。

- Mark Stosberg

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Craig Ringer · Accepted Answer

最近在SO和PostgreSQL邮件列表中都出现了这个问题。

你上述两点的TL;DR：

(a) 更大的shared_buffers可能是CI服务器上TRUNCATE较慢的原因。不同的fsync配置或使用机械硬盘而不是SSD也可能是问题所在。

(b) TRUNCATE有固定的成本，但不一定比DELETE慢，而且它做了更多的工作。请参见下面的详细说明。

更新：这篇文章引起了pgsql-performance上的重要讨论。请参阅此线程。

更新2： 9.2beta3中已经添加了改进措施，可以帮助解决这个问题，请参见此帖子。

关于TRUNCATE与DELETE FROM的详细解释：

虽然我不是这个主题的专家，但我的理解是，TRUNCATE每个表的成本几乎是固定的，而DELETE至少是O(n)（n行）; 如果有任何外键引用正在被删除的表，则更糟糕。

我一直认为TRUNCATE的固定成本比在接近空表上进行DELETE的成本要低，但事实并非如此。

TRUNCATE table;比DELETE FROM table;做得更多。

TRUNCATE table执行后，数据库的状态与以下命令执行后基本相同:

DELETE FROM table;
VACUUM (FULL, ANALYZE) table; (仅限9.0+版本，请参见脚注)

... 当然，TRUNCATE并不是通过DELETE和VACUUM来实现其效果。

重点是，DELETE和TRUNCATE执行的是不同的操作，因此您不是在比较两个具有相同结果的命令。

DELETE FROM table;允许死行和膨胀保留，允许索引携带死条目，不更新查询规划器使用的表统计信息等。

TRUNCATE会给您一个全新的表和索引，就像它们刚刚被创建一样。这就像您删除了所有记录，重新索引了表并进行了VACUUM FULL。

如果您不在意表格中留下的垃圾，因为您即将再次填充它，那么最好使用DELETE FROM table;。由于您没有运行VACUUM，您会发现死行和索引条目会积累成膨胀，必须扫描然后忽略；这会减慢所有查询速度。如果您的测试实际上并没有创建和删除太多数据，您可能不会注意到或在意，如果需要，您可以在测试运行过程中进行一两个VACUUM。更好的方法是让积极的自动清理设置确保自动清理在后台为您完成。在整个测试套件运行后，仍然可以TRUNCATE所有表格，以确保没有影响在多次运行中累积。在9.0及更高版本中，全局对表格进行VACUUM(FULL, ANALYZE);至少与TRUNCATE同样好，而且更加容易。

我IRC，Pg有一些优化，这意味着它可能会注意到当您的事务是唯一能看到表的事务时，并立即将块标记为自由。在测试中，当我想要创建膨胀时，我必须有多个并发连接来完成。虽然我不会依赖于此。

对于没有外键引用的小表，DELETE FROM table;非常便宜。

要从没有外键引用的表中删除所有记录，Pg只需进行顺序表扫描并设置遇到的元组的xmax。这是一个非常便宜的操作 - 基本上是线性读取和半线性写入。据我所知，它不必触及索引；它们继续指向死元组，直到稍后的VACUUM清理包含仅死元组的块并将其标记为空闲为止。 DELETE 只有在存在大量记录、存在许多必须检查的外键引用或者将 VACUUM (FULL, ANALYZE) table; 的后续操作计入 DELETE 成本时才会变得昂贵。

在我的测试中，DELETE FROM table; 比 TRUNCATE 快 4 倍，分别为 0.5ms 和 2ms。这是在使用 SSD 的测试数据库上运行的，因为我不在乎数据的丢失，所以设置了 fsync=off。当然，DELETE FROM table; 没有执行所有相同的工作，如果我随后执行 VACUUM (FULL, ANALYZE) table;，则需要更长时间，达到了 21ms，因此仅当我实际上不需要表格原始状态时，DELETE 才是胜利者。 TRUNCATE table; 执行的固定成本工作和 housekeeping 多于 DELETE。

相比之下，TRUNCATE 要做很多工作。它必须为表、其 TOAST 表（如果有）和表拥有的每个索引分配新文件。必须将标题写入这些文件，并且系统目录可能也需要更新（不确定这一点，没有检查）。然后，它必须用新文件替换旧文件或删除旧文件，并确保文件系统已通过同步操作 - fsync() 或类似操作 - 已经跟上更改。我不确定是否在使用（数据错误的）选项 fsync = off 运行时会跳过同步。

我最近了解到，TRUNCATE 还必须刷新与旧表相关的所有 PostgreSQL 缓冲区。对于巨大的 shared_buffers，这可能需要相当长的时间。我怀疑这就是为什么在您的 CI 服务器上速度较慢的原因。

无论如何，您可以看到具有关联 TOAST 表（大多数都有）和几个索引的表的 TRUNCATE 可能需要几秒钟。不长，但比从近乎空的表中进行 DELETE 更长。

因此，你最好执行 DELETE FROM table;。

--

注意：在9.0版本之前的数据库中，CLUSTER table_id_seq ON table; ANALYZE table;或者VACUUM FULL ANALYZE table; REINDEX table;是与TRUNCATE相似的操作。在9.0版本中，VACUUM FULL实现方式有了很大的改进。