postgresql Postgres在删除5亿行后查询非常慢

rkue9o1l  于 2023-03-29  发布在  PostgreSQL
关注(0)|答案(2)|浏览(431)

我有一个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

mtb9vblg

mtb9vblg1#

您可能遇到了索引膨胀的问题。请尝试重建和/或重新组织索引。您可以使用REINDEX命令重建表或特定索引上的所有索引,或者使用CLUSTER命令对表数据进行物理重新排序,以匹配索引的顺序。
同时增加自动真空设置。增加autovacuum_vacuum_scale_factor和autovacuum_analyze_scale_factor设置,以确保在每次运行期间真空处理和分析更多数据
希望这能解决你的问题。

sr4lhrrt

sr4lhrrt2#

你说autovac一直在运行,但不清楚为什么会这样。可能是autovac被高度限制,所以它需要很长时间来完成它的工作,而你还没有看到它完成。或者它可能需要在索引上进行大量的传递,因为autovacuum_work_mem太低,所以每次只能清理这么多元组(当然,这些都是相互复合的)。或者可能autovac在取得任何真实的的进展之前不断被竞争锁打断,这将显示在日志文件中。手动真空可以解决所有这些问题,因为它将运行unthrottled(在默认设置下,无论如何),并将使用任何本地设置,你有autovacuum_work_mem,并不会自动取消自己当confonted与阻塞进程.
你可以将一些元组从表的末尾移到表的前面。这意味着你向我们展示的查询会立即在前面找到那些元组,因此提前结束。如果没有看到这些查询,很难知道其他类型的查询可能会做什么。(您的synchronize_seqscans设置可能会干扰此操作。如果将其设置为on,则默认设置,除非RDS对其进行了更改,那么这不应该是一个大问题,因为查询将只是从最后一个离开的地方开始,这应该是在表的填充部分,所以当它需要跨越巨大的空区域时,问题应该只发生在大约1000/10 e6的时间)
你可以通过如下方式来完成这个元组移动:

with t as (delete from foobar where ctid>('1000000,1') returning *)
insert into foobar select * from t;

确保您现有的任何触发器在面对这样的过程时都能做正确的事情,或者禁用它们。
理想情况下,您将重复执行此操作,每次降低CTID常数,直到所有元组都被移到最前面(您可能希望在小块中执行此操作,以最大限度地缩短任何给定元组被锁定的时间,另外,如果您最终需要取消它,则不会丢失之前批处理的所有工作),然后VACUUM会将表截短到一个更小的大小。这个操作只会使索引上的任何膨胀变得更糟,所以如果它很麻烦的话,你需要重新索引(并发地)来摆脱它。

相关问题