redshift drop or truncate table very very slow
Amazon Web-ServicesAmazon RedshiftAmazon Web-Services Problem Overview
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?
Thanks
Amazon Web-Services Solutions
Solution 1 - Amazon Web-Services
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.
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;
)
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';
)
So the solution was to list the user sessions: SELECT * FROM STV_SESSIONS
And then kill it using: SELECT pg_terminate_backend(pid)
Or the KILL'EM ALL version:
SELECT pg_terminate_backend(process) FROM STV_SESSIONS where user_name='user_name' and process != pg_backend_pid();
Note that CANCEL {pid}
did not work! (the query was cancelled but the transaction was still open and locking).
Solution 2 - Amazon Web-Services
In my experience, as @Gerardo Grignoli says, locks don't show up in the stv_locks
table, but they do show up in pg_locks
. Depending on your environment it may not be acceptable to kill an arbitrary long-running session listed in stv_sessions
. I find the pg_locks
table to be very reliable for detecting this type of lock:
select * from pg_locks where relation = (select oid from pg_class where relname = 'the_table')
select pg_cancel_backend(pid)
Typically, the issue is an ACCESS EXCLUSIVE
lock that's deadlocking the table. So, if many locks are listed, find and kill the ACCESS EXCLUSIVE
one.
Solution 3 - Amazon Web-Services
IMO AccessShareLock on tables also causes DDL commands to get stuck.
Run this query to figure out pids of AccessShareLock
select
current_time,
c.relname,
l.database,
l.transaction,
l.pid,
a.usename,
l.mode,
l.granted
from pg_locks l
join pg_catalog.pg_class c ON c.oid = l.relation
join pg_catalog.pg_stat_activity a ON a.procpid = l.pid
where l.pid <> pg_backend_pid();
Kill the processes using select pg_terminate_backend(<pid>);
Ensure that all your read-only applications close and releases all connections and hence these locks!
Solution 4 - Amazon Web-Services
I've experienced the same problem. It turned out to be opened transaction ran from somewhere else.
For example, if you have 2 shells open with redshift shell, you will not be able to drop a table from the first shell, that participate in an open transaction in the second shell.
After I committed / rolled back in the second window, truncate worked perfectly.
Hope it helped.