Redshift: Behaviour when disk is full?

1
votes

I have a multi-node redshift cluster and noticed this behavior. A given query with given date range used to return me a specific number of rows, say - X. In the last few days, the cluster disk got filled upto 99.75%. Data loads were still succesful but the same query returned me Y number of rows with Y<X. So it looks like Redhsift removed some random rows(I have reasons to believe its random)

Now I resized the cluster and ran the same query to again get X number of rows. I made this hypothesis that:-

Under low disk, Redshift will archive some random rows(S3?) to keep the availability and restore data accordingly when the disk space is available.

However I am unable to find any official documentation for this behavior with disk space thresholds for archiving at restoring (It didn't restored at 90%, but at 65% when I added a new node). Could anybody point me to the correct document or confirm this behavior?

2
amazon-web-servicesamazon-redshift

2 Answers

1
votes

Answering my own question. I contacted Amazon on the forums, where I was later contacted by one of the product manager of redshift on personal chat. They are able to reproduce the bug with interleaved keys, were a VACUUM REINDEX operation can corrupt the indices, giving wrong answer to count queries. Amazon gave me two solutions:-

  1. After corruption, perform a deep copy of the table and run VACUUM REINDEX again.
  2. Switch to composite keys.

Option 1, is clearly very taxing for both resources and manpower. We switched to composite keys, which is again not the perfect solution for us, but working. Last update was 2 weeks back. Follow the discussion here:- Vacuum reindex causing wrong data counts in the table.

0
votes

Amazon Redshift would not remove any data.

You should always aim to keep spare storage capacity on the cluster to provide adequate space for VACUUM operations and for complex queries.

Clusters actually have more disk space than publicly shown, but this extra capacity should not be relied upon.

You should consult the STL_LOAD_ERRORS table to see whether any errors occurred during your load that resulted in fewer rows being loaded.