为什么 Amazon Redshift VACUUM 操作没有回收磁盘空间？

简短描述

当您删除表行时，隐藏的元数据标识列 (DELETE_XID) 会使用删除该行的事务 ID 进行标记。

如果在删除之前存在一个活动且长时间运行的事务，则 VACUUM 无法清理行，也无法回收磁盘空间。

有关 DELETE_XID 列的详细信息，请参阅针对窄表优化存储。

解决方法

要检查 Amazon Redshift 集群上是否存在长时间运行的事务，请运行以下查询：

rsdb=# select *,datediff(s,txn_start,getdate())/86400||' days '||datediff(s,txn_start,getdate())%86400/3600||' hrs '||datediff(s,txn_start,getdate())%3600/60||' mins '||datediff(s,txn_start,getdate())%60||' secs' duration from svv_transactions where lockable_object_type='transactionid' and  pid<>pg_backend_pid() order by 3;

以下输出显示 xid 50341 已激活 19 分 37 秒：

txn_owner  | txn_db |  xid  |  pid  |         txn_start          |   lock_mode   | lockable_object_type | relation | granted |           duration
-----------+--------+-------+-------+----------------------------+---------------+----------------------+----------+---------+------------------------------
 superuser | rsdb   | 50341 | 21612 | 2019-08-19 20:20:33.147622 | ExclusiveLock | transactionid        |          | t       | 0 days 0 hrs 19 mins 37 secs
(1 row)

要确认您是否从 Amazon Redshift 表中删除了行，请运行以下查询：

select a.query, a.xid, trim(c.name) tablename, b.deleted_rows, a.starttime, a.endtime
from stl_query a
join (select query, tbl, sum(rows) deleted_rows from stl_delete group by 1,2) b
on a.query = b.query
join (select id, name from stv_tbl_perm group by 1,2) c
on c.id = b.tbl
where a.xid in (select distinct xid from stl_commit_stats)
and trim(c.name) = 'tablename'
order by a.starttime;

以下输出显示标记为行删除 (xid 50350) 的事务是在长时间运行的事务 (xid 50341) 之后启动的：

query  |  xid  | tablename | deleted_rows |         starttime          |          endtime
-------+-------+-----------+--------------+----------------------------+----------------------------
 18026 | 50350 | test      |            5 | 2019-08-19 20:20:48.137594 | 2019-08-19 20:20:50.125609
(1 rows)

要允许 VACUUM DELETE 回收这些已删除的行，请选择以下选项之一：

• 等待长时间运行的事务完成。
• 使用 PG_TERMINATE_BACKEND 语句终止保存长时间运行的事务的会话。

完成上述操作后，重新运行 VACUUM 操作。

调查长时间运行的事务

要检查长时间运行的事务中的活动，请查询 SVL_STATEMENTTEXT 视图：

rsdb=# select pid, xid, trim(label), starttime, endtime, trim(text) from svl_statementtext where xid = 50341 order by starttime , sequence;

输出示例：

pid  |  xid  |  btrim  |         starttime          |          endtime           |          btrim
-------+-------+---------+----------------------------+----------------------------+--------------------------
 21612 | 50341 | default | 2019-08-19 20:20:31.733843 | 2019-08-19 20:20:31.733844 | begin;
 21612 | 50341 | default | 2019-08-19 20:20:33.146937 | 2019-08-19 20:20:35.020556 | select * from sometable;
(2 rows)

要检查查询是否在事务中运行，请查询 STV_INFLIGHT 视图：

rsdb=# select query, xid, pid, starttime, trim(text) from stv_inflight where xid = 50341;

输出示例：

query | xid | pid | starttime | btrim
-------+-----+-----+-----------+-------
(0 rows)

事务长时间运行的常见原因

以下行为可能导致事务长时间运行：

• 用户从关闭自动提交的客户端启动隐式事务。该事务会一直保持活动状态，直到用户使用 COMMIT 或 ROLLBACK 命令关闭事务，或者直到会话终止。
• 用户启动事务并使用 BEGIN，但从不使用 COMMIT 或 ROLLBACK 命令关闭事务。

相关信息

对表执行 vacuum 操作

最大程度地减少 vacuum 次数