Long query in Amazon Redshift never return

Question

I am facing a weird problem when running long queries in Amazon Redshift (>30min). Such queries do not provide feedback upon completion, even though they are completed when looking through the Redshift console or querying the stv tables.

We tested this in SQL Workbench/J, which uses the JDBC driver and a Node.js application using a Javascript-only driver. In all cases, queries keep going forever and never complete, not even with an error.

Answer 1

You can try to add ?tcpKeepAlive=true at the end of the connection string. I find it helpful in some long running queries.

The same goes for other TCP keep alive settings for your operating system.

For example, for Mac:

sudo sysctl -w net.inet.tcp.always_keepalive=1

You would probably also want to optimize your queries not to take too long time. There are many options for that, depends on your actual queries. But a quick improvement you can get with the new SSD based nodes: http://aws.amazon.com/about-aws/whats-new/2014/01/23/amazon-redshift-ssd-node-type/