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

Breve descrizione

Quando elimini righe di una tabella, una colonna di identità dei metadati nascosta, DELETE_XID, viene contrassegnata con l'ID della transazione che ha eliminato la riga.

Se è presente una transazione attiva di lunga durata iniziata prima dell'eliminazione, VACUUM non può ripulire le righe e lo spazio su disco non può essere recuperato.

Per ulteriori informazioni sulla colonna DELETE_XID, consulta Ottimizzazione dello storage per tabelle limitate.

Risoluzione

Per verificare la presenza di transazioni di lunga durata nel cluster Amazon Redshift, esegui questa query:

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 la transazione xid 50341 è rimasta attiva 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)

Per verificare di aver eliminato righe dalla tabella Amazon Redshift, esegui questa seguente:

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 corrispondente all’eliminazione delle righe (xid 50350) è iniziata dopo la transazione di lunga durata (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 lo spazio delle righe eliminate, scegli una delle seguenti opzioni:

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

Dopo aver completato l'azione precedente, esegui nuovamente l'operazione VACUUM.

Esamina le transazioni di lunga durata

Per verificare l'attività in una transazione di lunga durata, esegui una query sulla vista SVL_STATEMENTTEXT:

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

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)

Per verificare che le query vengano eseguite nella transazione, esegui una query sulla vista STV_INFLIGHT:

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

Esempio di output:

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

Cause comuni delle 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 è disattivato. La transazione rimane attiva fino a quando l'utente non la chiude con i comandi COMMIT o ROLLBACK o fino a quando la sessione non viene terminata.
• Un utente avvia una transazione utilizzando BEGIN, ma non la chiude mai con il comando COMMIT o ROLLBACK.

Informazioni correlate

Vacuum delle tabelle

Riduzione al minimo dei tempi di un'operazione vacuum