在 CentOS 上使用的 postgres 9.2 版本中,TRUNCATE TABLE 命令有时需要很长时间才能执行。有一次,它花费了超过1.5小时来清空一个有10万条记录的表,在其他情况下需要更长时间。当我使用 pgAdmin 清空表时,也会出现这个问题。可能的原因是什么?如何提高截断性能?服务器上有16GB内存,shared_buffers = 1536MB。
检查是否有任何查询阻止了truncate
操作
SELECT
activity.pid,
activity.usename,
activity.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
如果必要,终止它:SELECT pg_terminate_backend(PID);
blocking_id
而不是pid
。 - undefinedTRUNCATE
命令必须清空shared_buffers
中与被截断表相关的数据,还需要删除旧文件,而如果使用像ext3
这样删除缓慢的文件系统会导致操作很慢。
然而1.5小时太长了,通常只需要几秒钟。极有可能有其他会话锁定了该表,阻止TRUNCATE
继续执行。可以查看pg_catalog.pg_locks
和pg_catalog.pg_stat_activity
。
PostgreSQL锁监控页面可能会有所帮助。
尝试断开与数据库的所有其他连接。我花了很长时间来清空58000条记录。
在我断开PyCharm DB Navigator、dev server等与我的postgres数据库的连接之后,总共只花费了118毫秒。
systemctl status postgresql-12
然后我注意到很多 "TRUNCATED Waiting",尝试重新加载Postgres但没有成功,最后只能杀死卡住的进程。
这之后就可以正常工作了,我们能够重新启动任务,只需要不到一秒钟的时间。