¿Por qué la operación VACUUM de Amazon Redshift no recupera espacio en disco?

Breve descripción

Es posible que no se recupere espacio en disco si sigue habiendo transacciones de larga duración activas. Cuando se eliminan filas, la columna de identidad de metadatos oculta DELETE_XID se marca con el ID de transacción que eliminó la fila. Si hay una transacción activa de larga duración que comenzó antes de la eliminación, VACUUM no puede limpiar las filas. Esto significa que no se puede recuperar espacio en disco. Para obtener más información sobre la columna DELETE_XID, consulte Optimización del almacenamiento para tablas angostas.

Resolución

1.    Para comprobar si hay transacciones de larga duración en el clúster, ejecute la siguiente 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;

El siguiente resultado muestra que xid 50341 ha estado activo durante 19 minutos y 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.    Ejecute la siguiente consulta para confirmar si las filas se han eliminado de la tabla de 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;

El siguiente resultado muestra que la transacción marcada para eliminar filas (xid 50350) se inició después de la transacción de larga duración (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 estas filas eliminadas, elija una de las siguientes opciones y, a continuación, vuelva a ejecutar la operación VACUUM:

• Espere a que se complete la transacción de larga duración.
• Utilice la instrucción PG_TERMINATE_BACKEND para terminar la sesión que está obstaculizando la transacción de larga duración.

Investigación de las transacciones de larga duración

Consulte la vista SVL_STATEMENTTEXT para comprobar la actividad en una transacción de larga duración:

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

El siguiente es un ejemplo de salida:

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 la vista STV_INFLIGHT para comprobar si se están ejecutando consultas en la transacción:

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

El siguiente es un ejemplo de salida:

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

Problemas comunes que dan lugar a transacciones de larga duración

El siguiente comportamiento puede dar lugar a transacciones de larga duración:

• Un usuario inicia una transacción implícita desde un cliente donde la confirmación automática está deshabilitada. La transacción permanecerá activa hasta que el usuario la cierre explícitamente con el comando COMMIT o ROLLBACK, o hasta que se termine la sesión.
• Un usuario inicia una transacción explícitamente con BEGIN, pero no la cierra con los comandos COMMIT ni ROLLBACK.

Información relacionada

Limpieza de tablas

Administración de los tiempos de limpieza