Perché l'operazione Amazon Redshift VACUUM non recupera spazio su disco?

Breve descrizione

Lo spazio su disco potrebbe non essere recuperato se ci sono transazioni di lunga durata che rimangono attive. Quando le righe vengono eliminate, una colonna di identità dei metadati nascosta, DELETE_XID, viene contrassegnata con l'ID transazione che ha eliminato la riga. Se è presente una transazione attiva di lunga durata iniziata prima dell'eliminazione, VACUUM non è in grado di pulire le righe. Ciò significa che non è possibile recuperare lo spazio su disco. Per ulteriori informazioni sulla colonna DELETE_XID, consulta Ottimizzazione dello storage per tabelle limitate.

Soluzione

1.    Per verificare la presenza di transazioni di lunga durata nel cluster, esegui la query seguente:

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;

L'output seguente mostra che xid 50341 è rimasto attivo per 19 minuti e 37 secondi:

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.    Esegui la query seguente per verificare se le righe sono state eliminate dalla tabella 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;

L'output seguente mostra che la transazione contrassegnata per l’eliminazione della riga (xid 50350) è iniziata dopo la transazione a lungo termine (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)

Per consentire a VACUUM DELETE di recuperare queste righe eliminate, scegli una delle seguenti opzioni, quindi esegui di nuovo l’operazione VACUUM:

• Attendi il completamento della transazione di lunga durata.
• Usa l'istruzione PG_TERMINATE_BACKEND per terminare la sessione che contiene la transazione di lunga durata.

Esamina le transazioni di lunga durata

Interroga la vistaSVL_STATEMENTTEXT per controllare l'attività di una transazione di lunga durata:

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

Ecco un esempio di output:

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)

Interroga la vista STV_INFLIGHT per verificare se le query sono in esecuzione nella transazione:

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

Ecco un esempio di output:

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

Problemi comuni che causano transazioni di lunga durata

Il seguente comportamento può comportare transazioni di lunga durata:

• Un utente avvia una transazione implicita da un client in cui il commit automatico è disabilitato. La transazione rimane attiva fino a quando l'utente non la chiude esplicitamente con i comandi COMMIT o ROLLBACK o fino alla chiusura della sessione.
• Un utente avvia una transazione in modo esplicito utilizzando BEGIN, ma non la chiude mai con il comando COMMIT o ROLLBACK.

Informazioni correlate

Vacuum delle tabelle

Gestione dei tempi di vacuum