In Redshift, if your query operation hangs or stops responding, below are the possible causes as well as its corresponding solution:
Connection to the Database Is Dropped
Reduce the size of maximum transmission unit (MTU). The MTU size determines the maximum size, in bytes, of a packet that can be transferred in one Ethernet frame over your network connection
Connection to the Database Times Out
Your client connection to the database appears to hang or timeout when running long queries, such as a COPY command. In this case, you might observe that the Amazon Redshift console displays that the query has completed, but the client tool itself still appears to be running the query. The results of the query might be missing or incomplete depending on when the connection stopped. This effect happens when idle connections are terminated by an intermediate network component
Client-Side Out-of-Memory Error Occurs with ODBC
If your client application uses an ODBC connection and your query creates a result set that is too large to fit in memory, you can stream the result set to your client application by using a cursor. For more information, see DECLARE and Performance Considerations When Using Cursors.
Client-Side Out-of-Memory Error Occurs with JDBC
When you attempt to retrieve large result sets over a JDBC connection, you might encounter client-side out-of-memory errors.
There Is a Potential Deadlock
If there is a potential deadlock, try the following:
– View the STV_LOCKS and STL_TR_CONFLICT system tables to find conflicts involving updates to more than one table.
– Use the PG_CANCEL_BACKEND function to cancel one or more conflicting queries.
– Use the PG_TERMINATE_BACKEND function to terminate a session, which forces any currently running transactions in the terminated session to release all locks and roll back the transaction.
– Schedule concurrent write operations carefully.
andytanoko 