我有一个Postgres 11数据库，它的增长速度比预期的要快，最终达到了大约5亿行，在优化了我们的代码之后，我们能够在任何给定的时间将其降低到大约1000万行（平均每秒大约1，000 - 5，000次插入）。
问题是，在删除了4.9亿行之后，自动真空似乎一直在运行，并且查询所花费的时间仍然几乎与删除4.9亿行之前一样长。
由于停机对我们来说不是一个真正的选择，有没有关于我们如何帮助优化的建议？
1.我应该删除表上的任何索引并重新创建它们吗？这会有帮助吗？
1.由于自动真空似乎不断运行，我们不能做一个真空充分（不能有停机时间，如果可能的话），围绕这一点有什么建议？
我发布了另一个关于添加主键以使用AWS DMS升级到更新版本的Postgres的问题，我相信VACUUM可以并行运行（希望这会有所帮助）：Postgres add PRIMARY KEY to partitioned very active table without lock
有点用完了，希望有人能有一些建议：（
下面是对1000行的简单查询的输出，该查询需要大约18-19秒EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT * FROM "public"."table" LIMIT 1000 OFFSET 0;
Limit (cost=0.00..46.92 rows=1000 width=128) (actual time=18452.685..18468.174 rows=1000 loops=1)
Output: difficulty, user, worker, useragent, ipaddress, source, created
Buffers: shared hit=2585 read=322681 dirtied=321175 written=102
I/O Timings: read=13292.325 write=0.399
-> Seq Scan on public.shares_nexa (cost=0.00..14031593.64 rows=299050464 width=128) (actual time=18452.683..18468.080 rows=1000 loops=1)
Output: difficulty, user, worker, useragent, ipaddress, source, created
Buffers: shared hit=2585 read=322681 dirtied=321175 written=102
I/O Timings: read=13292.325 write=0.399
Planning Time: 0.056 ms
Execution Time: 18468.237 ms此特定表的n_dead_tup显示556993041
2条答案
按热度按时间mtb9vblg1#
您可能遇到了索引膨胀的问题。请尝试重建和/或重新组织索引。您可以使用REINDEX命令重建表或特定索引上的所有索引，或者使用CLUSTER命令对表数据进行物理重新排序，以匹配索引的顺序。
同时增加自动真空设置。增加autovacuum_vacuum_scale_factor和autovacuum_analyze_scale_factor设置，以确保在每次运行期间真空处理和分析更多数据
希望这能解决你的问题。
sr4lhrrt2#
你说autovac一直在运行，但不清楚为什么会这样。可能是autovac被高度限制，所以它需要很长时间来完成它的工作，而你还没有看到它完成。或者它可能需要在索引上进行大量的传递，因为autovacuum_work_mem太低，所以每次只能清理这么多元组（当然，这些都是相互复合的）。或者可能autovac在取得任何真实的的进展之前不断被竞争锁打断，这将显示在日志文件中。手动真空可以解决所有这些问题，因为它将运行unthrottled（在默认设置下，无论如何），并将使用任何本地设置，你有autovacuum_work_mem，并不会自动取消自己当confonted与阻塞进程.
你可以将一些元组从表的末尾移到表的前面。这意味着你向我们展示的查询会立即在前面找到那些元组，因此提前结束。如果没有看到这些查询，很难知道其他类型的查询可能会做什么。（您的synchronize_seqscans设置可能会干扰此操作。如果将其设置为on，则默认设置，除非RDS对其进行了更改，那么这不应该是一个大问题，因为查询将只是从最后一个离开的地方开始，这应该是在表的填充部分，所以当它需要跨越巨大的空区域时，问题应该只发生在大约1000/10 e6的时间）
你可以通过如下方式来完成这个元组移动：
确保您现有的任何触发器在面对这样的过程时都能做正确的事情，或者禁用它们。
理想情况下，您将重复执行此操作，每次降低CTID常数，直到所有元组都被移到最前面（您可能希望在小块中执行此操作，以最大限度地缩短任何给定元组被锁定的时间，另外，如果您最终需要取消它，则不会丢失之前批处理的所有工作），然后VACUUM会将表截短到一个更小的大小。这个操作只会使索引上的任何膨胀变得更糟，所以如果它很麻烦的话，你需要重新索引（并发地）来摆脱它。