我已经从大约 7500 万行的 PostgreSQL 表中删除了大约 6500 万行。删除查询一完成,CPU 就骤降到 100%,持续约五分钟。
删除行的表有多个索引,并且在删除期间和删除之后被大量使用。不幸的是,我没有办法重现这个问题,因为它发生在生产环境中。
autovacuum 是否可能启动?如果是,它是否可以将具有 32 个 CPU 核心的数据库驱动到 100% CPU 使用率?如果是这样,是否有办法限制 autovacuum 的摄入量,以便在大量删除查询后不会降低数据库性能?
我正在使用 PostgreSQL 版本 14.8。
这听起来像是我以前遇到过的问题(或其某些变体),但如果无法访问您的服务器或无法重现它,我无法确定。
如果您有很多从该表中的索引列中选择最小值/最大值的查询,通常可以通过查询索引的端点来立即满足它们。但是当末尾的许多行被删除时,它需要往回走,直到找到仍然存在的极值点。当您有大量已删除的行需要遍历时,这可能需要一段时间。一旦删除的元组“对所有对象都已死亡”(删除时打开的所有事务都已消失),您应该能够在元组本身和索引条目上设置提示位(“microvacuum”或“killed items”)或“索引提示位”),它解决或至少改善了问题,然后它最终应该通过真空完全解决,真空不仅应该删除索引条目,还有索引页,里面除了死条目什么也没有。但在所有长期事务/快照消失之前,这些机制都不起作用,因此请确保您没有打开的事务。
总之,autovacuum 并不是造成问题的原因。相反,它正在尝试解决问题,但尚未完成,或者已因打开快照而受挫。
在旧版本中,在对合并连接进行成本估算时可能会发生同样的情况。规划过程进行了相同类型的终点探测,并遇到了相同的问题。不过,我认为 v14 已经解决了这个问题。