postgres truncate是缓慢的 [英] postgres truncate is slow

问题描述

在postgres 9.2（CentOS）中，TRUNCATE TABLE命令偶尔需要很长时间才能运行。有一次，花费超过1.5小时截断一个具有100K记录的表，在其他情况下更长。当我使用pgAdmin截断表时，也发生了这个问题。可能的原因是什么？如何提高截断性能？

In postgres 9.2 (CentOS), TRUNCATE TABLE command occasionally took a really long time to run. One time, it took more than 1.5 hours to truncate a table with 100K records, even longer in other cases. This problem also happened when I used pgAdmin to truncate table. What is the possible cause? and how to improve the truncation performance?

服务器上有16GB内存，shared_buffers = 1536MB

There is 16GB of memory on the server and shared_buffers = 1536MB

提前感谢。

推荐答案

TRUNCATE 必须冲洗 shared_buffers ，并且它必须取消链接旧文件，在文件系统上缓慢删除，如 ext3 。

TRUNCATE has to flush shared_buffers for the table being truncated, and it has to unlink the old file, which can be slow on file systems with slow deletion like ext3.

1.5小时是相当极端的，但我们通常最多只是秒钟。很可能您有其他会话在表上持有锁，阻止 TRUNCATE 继续进行。请参阅 pg_catalog.pg_locks 和 pg_catalog.pg_stat_activity 。

1.5 hours is pretty extreme though, we're usually talking seconds at most. It is highly likely that you have other sessions holding locks on the table that prevent the TRUNCATE from proceeding. See pg_catalog.pg_locks and pg_catalog.pg_stat_activity.

关于锁定监控的PostgreSQL wiki文章应该很有用。

The PostgreSQL wiki article on lock monitoring should be useful.

另请参阅： PostgreSQL截断速度

这篇关于postgres truncate是缓慢的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！