Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

redshift drop or truncate table very very slow

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

like image 201
user2916054 Avatar asked Oct 24 '13 13:10

user2916054


People also ask

Which is faster TRUNCATE or drop table?

In SQL, the TRUNCATE command is used to remove all the rows from the table. However, the structure of the table and columns remains the same. It is faster than the DROP command.

How long does it take to drop a table Redshift?

Redshift has very fast I/O, so that opeation should take less than 1 second for any cluster type or size.

Why drop is faster than TRUNCATE?

1. DROP : DROP is a DDL(Data Definition Language) command and is used to remove table definition and indexes, data, constraints, triggers etc for that table. Performance-wise the DROP command is quick to perform but slower than TRUNCATE because it gives rise to complications.

Why is my Redshift query taking so long?

The Interleaved Sort would be causing Redshift to read many more disk blocks to find your data, thereby significantly increasing query time. The DISTKEY should typically be set to the field that is most used in a JOIN statement on the table.


1 Answers

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).

like image 155
Gerardo Grignoli Avatar answered Sep 28 '22 01:09

Gerardo Grignoli