问题描述
在 postgres 9.2 (CentOS) 中，TRUNCATE TABLE 命令有时需要很长时间才能运行.有一次，截断一个有 100K 记录的表需要 1.5 多个小时，在其他情况下甚至更长.当我使用 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, as 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 文章应该很有用.
另请参阅:Postgresql 截断速度