redshift drop或truncate table非常非常慢 [英] redshift drop or truncate table very very slow
问题描述
当在我的redshift数据库中删除或截断一个不太大的表(4M行)时,需要非常长的时间(数小时)才能完成.有人遇到同样的问题吗?
When drop or truncate a not too big table(4M rows) in my redshift database, it take very very long(hours) to complete. Does anybody experience the same issue?
谢谢
推荐答案
Redshift具有非常快的I/O,因此对于任何群集类型或大小,操作都应少于1秒. 正如diemacht所说,造成此问题的原因是,您与一个打开的事务还有另一个联系.
Redshift has very fast I/O, so that opeation should take less than 1 second for any cluster type or size. As diemacht said, the issue is caused because you have another connection with an open transaction.
我有一个类似的问题:客户端崩溃导致事务打开"但无法到达.
STV_LOCKS表上没有数据库锁:(使用select table_id, last_update, lock_owner, lock_owner_pid from stv_locks;
)
I had a similar issue: A crash on the client left a transaction 'open' but unreacheable.
No db locks appeared on the STV_LOCKS table: (using select table_id, last_update, lock_owner, lock_owner_pid from stv_locks;
)
此外,没有查询仍在运行:(已通过select pid, trim(user_name), starttime, query , substring(query,1,20), status from stv_recents where status='Running';
进行了检查)
Also, no query was still running: (checked with: select pid, trim(user_name), starttime, query , substring(query,1,20), status from stv_recents where status='Running';
)
因此解决方案是列出用户会话:SELECT * FROM STV_SESSIONS
然后使用以下命令将其杀死:SELECT pg_terminate_backend(pid)
So the solution was to list the user sessions: SELECT * FROM STV_SESSIONS
And then kill it using: SELECT pg_terminate_backend(pid)
或KILL'EM ALL版本:
Or the KILL'EM ALL version:
SELECT pg_terminate_backend(process) FROM STV_SESSIONS where user_name='user_name' and process != pg_backend_pid();
请注意,CANCEL {pid}
无效! (查询已取消,但交易仍处于打开和锁定状态.)
Note that CANCEL {pid}
did not work! (the query was cancelled but the transaction was still open and locking).
这篇关于redshift drop或truncate table非常非常慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!