Por que a operação VACUUM do Amazon Redshift não recupera espaço em disco?

Descrição resumida

O espaço em disco poderá não ser recuperado se houver transações de longa duração que permanecem ativas. Quando linhas são excluídas, uma coluna de identidade de metadados oculta, DELETE_XID, é marcada com a ID da transação que excluiu essa linha. Se houver uma transação ativa de longa duração iniciada antes da exclusão, a operação VACUUM não poderá apagar as linhas. Isso significa que o espaço em disco não poderá ser recuperado. Para obter mais informações sobre a coluna DELETE_XID, consulte Otimização de armazenamento para tabelas estreitas.

Resolução

1. Para verificar transações de longa duração no cluster, execute a seguinte consulta:

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;

A seguinte saída mostra que xid 50341 esteve ativo por 19 minutos e 37 segundos:

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)

2. Execute a seguinte consulta para confirmar se as linhas foram excluídas da tabela do 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;

A seguinte saída mostra que a transação marcada para exclusão de linha (xid 50350) começou após a transação de longa duração (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)

Para permitir que VACUUM DELETE recupere essas linhas excluídas, escolha uma das seguintes opções e execute novamente a operação VACUUM:

• Aguarde a conclusão da transação de longa duração.
• Use a instrução PG_TERMINATE_BACKEND para encerrar a sessão que está mantendo a transação de longa duração.

Investigar as transações de longa duração

Consulte a visualização SVL_STATEMENTTEXT para verificar a atividade em uma transação de longa duração:

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

Veja um exemplo de saída:

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)

Consulte a visualização STV_INFLIGHT para verificar se as consultas estão sendo executadas na transação:

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

Veja um exemplo de saída:

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

Problemas comuns que causam transações de longa duração

O seguinte comportamento pode resultar em transações de longa duração:

• Um usuário inicia uma transação implícita de um cliente em que a confirmação automática está desativada. A transação permanece ativa até que o usuário a feche explicitamente com o comando COMMIT ou ROLLBACK, ou até que a sessão seja encerrada.
• Um usuário inicia uma transação explicitamente usando BEGIN, mas nunca a fecha com o comando COMMIT ou ROLLBACK.

Informações relacionadas

Vacuum de tabelas

Gerenciamento dos tempos de vacuum