Warum wird durch den Amazon Redshift VACUUM-Vorgang kein Speicherplatz zurückgewonnen?

Kurzbeschreibung

Wenn Tabellenzeilen gelöscht werden, wird eine versteckte Metadaten-Identitätsspalte (DELETE_XID) mit der Transaktions-ID markiert, mit der die Zeile gelöscht wurde.

Wenn eine aktive, lang andauernde Transaktion vorliegt, deren Ausführung vor der Löschung begann, kann VACUUM die Zeilen nicht bereinigen und der Festplattenspeicherplatz kann nicht zurückgewonnen werden.

Weitere Informationen zur Spalte DELETE\ _XID findest du unter Optimieren des Speichers für schmale Tabellen.

Lösung

Führe die folgende Abfrage aus, um im Amazon-Redshift-Cluster nach lang andauernden Transaktionen zu suchen:

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;

Die folgende Ausgabe zeigt, dass xid 50341 19 Minuten und 37 Sekunden lang aktiv war:

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)

Um zu bestätigen, dass du Zeilen aus der Amazon-Redshift-Tabelle gelöscht hast, führe die folgende Abfrage aus:

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;

Die folgende Ausgabe zeigt, dass die zum Löschen von Zeilen markierte Transaktion (xid 50350) nach der lang andauernden Transaktion (xid 50341) gestartet wurde:

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)

Damit VACUUM DELETE diese gelöschten Zeilen zurückfordern kann, wähle eine der folgenden Optionen:

• Warte, bis die lang andauernde Transaktion abgeschlossen ist.
• Verwende die Anweisung PG_TERMINATE_BACKEND, um die Sitzung mit der lang andauernden Transaktion zu beenden.

Nachdem du die vorhergehende Aktion abgeschlossen hast, führe den VACUUM-Vorgang erneut aus.

Untersuchen der Transaktionen mit langer Laufzeit

Frage die SVL_STATEMENTTEXT-Ansicht ab, um die Aktivität in einer lang andauernden Transaktion zu überprüfen:

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

Beispielausgabe:

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)

Frage die STV_INFLIGHT-Ansicht ab, um zu überprüfen, ob Abfragen in der Transaktion ausgeführt werden:

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

Beispielausgabe:

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

Häufige Ursachen für lang andauernde Transaktionen

Das folgende Verhalten kann zu Transaktionen mit langer Laufzeit führen:

• Ein Benutzer startet eine implizite Transaktion von einem Client aus, auf dem die automatische Festschreibung deaktiviert ist. Die Transaktion bleibt aktiv, bis der Benutzer die Transaktion mit dem Befehl COMMIT oder ROLLBACK schließt oder bis die Sitzung beendet wird.
• Ein Benutzer startet eine Transaktion mit BEGIN, schließt die Transaktion jedoch niemals mit dem Befehl COMMIT oder ROLLBACK.

Ähnliche Informationen

Bereinigen von Tabellen

Minimierung der Bereinigungszeiten