由于VACUUM查询，Amazon Redshift的磁盘使用率为100％ [英] Amazon Redshift at 100% disk usage due to VACUUM query

问题描述

阅读Amazon Redshift文档，我在某个从未曾清理过的400GB表上运行了VACUUM，以尝试提高查询性能. 不幸的是，VACUUM使表增加到1.7TB(!!)，并使Redshift的磁盘使用率达到100％. 然后，我尝试通过在超级用户队列中运行CANCEL查询来停止VACUUM(您可以通过运行"set query_group ='superuser';"来输入它)，但是尽管查询没有引发错误，但这对保持运行的vaccum查询.

Reading the Amazon Redshift documentatoin I ran a VACUUM on a certain 400GB table which has never been vacuumed before, in attempt to improve query performance. Unfortunately, the VACUUM has caused the table to grow to 1.7TB (!!) and has brought the Redshift's disk usage to 100%. I then tried to stop the VACUUM by running a CANCEL query in the super user queue (you enter it by running "set query_group='superuser';") but although the query didn't raise an error, this had no effect on the vaccum query which keeps running.

我该怎么办?

推荐答案

显然，当前您无能为力. 我在支持亚马逊的电话上待了一个小时，他们没有停止真空操作的工具. 他们默默地打开了有关CANCEL查询的票证，不适用于VACUUM查询.

Apparently, currently there is not much you can do. I was on the phone with amazon support for an hours, they didn't have the tools to stop the vacuum operation. They opened a ticket about CANCEL query silently not working on VACUUM queries.

他们建议我为群集创建快照(如果您已创建了以前的快照，通常应该花几分钟的时间)，然后重新启动群集. 它确实可以正常工作，这意味着真空停止了，并且清除了一些磁盘空间(600GB)，但是该表仍然是其原始大小的两倍以上.因为再次对其进行清理会带来很大的风险，所以我诉诸于创建它的深层副本，该副本应创建表的经清理的副本. (您可以在此处了解有关深层复制的信息- http://docs.aws.amazon.com/redshift/latest/dg/performing-a-deep-copy.html ).

They suggested I take snapshot of the cluster (normally should take a few minutes if you have made previous snapshots), and then that I restart the cluster. It sort of worked, meaning that the vacuum stopped, and some of the disk space was cleared (600GB), but the table remained more than twice its original size. Because vacuuming it again would be too risky, I resorted to creating a deep copy of it, which should created a vacuumed copy of the table. (You can read about deep copy here - http://docs.aws.amazon.com/redshift/latest/dg/performing-a-deep-copy.html).

这篇关于由于VACUUM查询，Amazon Redshift的磁盘使用率为100％的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！