Supera la protección integral de ID de transacción (TXID)

En esta página, se describe lo que puedes hacer cuando tu base de datos se ejecuta en la protección contra ajustes del ID de transacción en PostgreSQL. Se manifiesta como un mensaje ERROR de la siguiente manera:

database is not accepting commands to avoid wraparound data loss in database
dbname.

Stop the postmaster and vacuum that database in single-user mode.

You might also need to commit or roll back old prepared transactions, or drop
stale replication slots.

Como alternativa, puede aparecer un mensaje WARNING como el siguiente:

database dbname must be vacuumed within 10985967 transactions.

To avoid a database shutdown, execute a database-wide VACUUM in that database.

Descripción general de los pasos

  • Averigua qué base de datos y qué tablas generan el ajuste.
  • Verifica si hay algo que retenga (AUTO)VACUUM (por ejemplo, un ID de transacción atascado).
  • Mide la velocidad de AUTOVACUUM. Si es lento, de manera opcional, puedes intentar acelerarlo.
  • Si es necesario, ejecuta más comandos VACUUM manualmente.
  • Investiga otras formas de acelerar vacuum. A veces, la forma más rápida es descartar la tabla o algunos índices.

Muchas de las recomendaciones de los valores de las marcas no son exactas, puesto que dependen de muchos parámetros de la base de datos. Lee los documentos vinculados al final de esta página para obtener un análisis más detallado de este tema.

Encuentra la base de datos y la tabla que causan el ajuste

Encuentra la base de datos

Para saber qué bases de datos contienen las tablas que causan el ajuste, ejecuta la siguiente consulta:

SELECT datname, 
       age(datfrozenxid), 
       2^31-1000000-age(datfrozenxid) as remaining
  FROM pg_database
 ORDER BY 3

La base de datos con el valor remaining cercano a 0 es la que causa el problema.

Cómo encontrar la tabla

Conéctate a esa base de datos y ejecuta la siguiente consulta:

SELECT c.relnamespace::regnamespace as schema_name,
       c.relname as table_name,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
       2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as remaining
  FROM pg_class c
  LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
 WHERE c.relkind IN ('r', 'm')
 ORDER BY 4;

Esta consulta muestra las tablas que causan el problema.

Para tablas TEMPORARY

Si schema_name comienza con pg_temp_, la única forma de resolver el problema es descartar la tabla, porque PostgreSQL no te permite usar VACUUM en tablas temporales creadas en otras sesiones. A veces, si esa sesión está abierta y se puede acceder a ella, puedes vaciar la tabla, pero este no suele ser el caso. Usa las siguientes instrucciones de SQL para descartar la tabla temporal:

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
DROP TABLE pg_temp_<N>.<tablename>;

Si este era el único bloqueador, en alrededor de un minuto, autovacuum recoge este cambio y mueve el datfrozenxid hacia delante en pg_database. Esto resuelve el estado de solo lectura de la protección contra ajustes.

Tablas normales

Para las tablas normales (que no son temporales), continúa con los pasos siguientes que se indican a continuación para ver si algo bloquea la limpieza, si VACUUM se ejecuta lo suficientemente rápido y si la tabla más importante se está limpiando.

Verifica si hay un ID de transacción atascado

Un posible motivo por el que el sistema puede quedarse sin ID de transacción es que PostgreSQL no puede inmovilizar (es decir, marcar como visible para todas las transacciones) los ID de transacción creados después de que se inició la transacción más antigua actualmente en ejecución. Esto se debe a las reglas de control de simultaneidad de varias versiones (MVCC). En casos extremos, esas transacciones pueden ser tan antiguas que hacen que sea imposible para VACUUM limpiar las transacciones antiguas para el límite completo de ajuste de ID de transacciones de 2,000 millones de transacciones, lo que provoca que todo el sistema deje de aceptar DML nuevo. Por lo general, también verás advertencias en el archivo de registro que dicen WARNING: oldest xmin is far in the past.

Deberías continuar con la optimización solo después de que se corrija el ID de transacción atascado.

A continuación, te presentamos cuatro motivos posibles por los que podría haber un ID de transacción atascado, con información sobre cómo mitigar cada uno de ellos:

  • Transacciones de larga duración: Debes identificarlas y cancelar o finalizar el backend para desbloquear el proceso de vaciado.
  • Transacciones de preparación huérfanas: Reviértelas.
  • Ranuras de replicación abandonadas: Descarta las ranuras abandonadas.
  • Transacción de larga duración en la réplica, con hot_standby_feedback = on: Identifícalas y cancela o finaliza el backend para desbloquear el proceso de vaciado.

En estos casos, la siguiente consulta muestra la antigüedad de la transacción más antigua y la cantidad de transacciones restantes hasta el ajuste:

 WITH q AS (
SELECT
  (SELECT max(age(backend_xmin))
      FROM pg_stat_activity  WHERE state != 'idle' )       AS oldest_running_xact_age,
  (SELECT max(age(transaction)) FROM pg_prepared_xacts)    AS oldest_prepared_xact_age,
  (SELECT max(greatest(age(catalog_xmin),age(xmin))) FROM pg_replication_slots)        AS oldest_replication_slot_age,
  (SELECT max(age(backend_xmin)) FROM pg_stat_replication) AS oldest_replica_xact_age
)
SELECT *,
       2^31 - oldest_running_xact_age AS oldest_running_xact_left,
       2^31 - oldest_prepared_xact_age AS oldest_prepared_xact_left,
       2^31 - oldest_replication_slot_age AS oldest_replication_slot_left,
       2^31 - oldest_replica_xact_age AS oldest_replica_xact_left
FROM q;

Esta consulta podría mostrar cualquiera de los valores *_left cuya distancia del ajuste es cercana o menor a 1 millón. Este valor es el límite de protección contra ajustes cuando PostgreSQL deja de aceptar comandos de escritura nuevos. En este caso, consulta Quita bloqueadores de VACUUM o Ajusta VACUUM.

Por ejemplo, la consulta anterior podría mostrar la siguiente respuesta:

┌─[ RECORD 1 ]─────────────────┬────────────┐
│ oldest_running_xact_age       2146483655 │
│ oldest_prepared_xact_age      2146483655 │
│ oldest_replication_slot_age   ¤          │
│ oldest_replica_xact_age       ¤          │
│ oldest_running_xact_left      999993     │
│ oldest_prepared_xact_left     999993     │
│ oldest_replication_slot_left  ¤          │
│ oldest_replica_xact_left      ¤          │
└──────────────────────────────┴────────────┘

En el ejemplo anterior, oldest_running_xact_left y oldest_prepared_xact_left están dentro del límite de protección contra ajustes de 1 millón. En este caso, primero debes quitar los bloqueadores para que el VACUUM pueda continuar.

Quita los bloqueadores de VACUUM

Transacciones de larga duración

En la consulta anterior, si oldest_running_xact es igual a oldest_prepared_xact, ve a la sección Transacción de preparación huérfana, porque el valor en ejecución más reciente también incluye las transacciones preparadas.

Es posible que primero debas ejecutar el siguiente comando como el usuario postgres:

GRANT pg_signal_backend TO postgres;

Si la transacción infractora pertenece a alguno de los usuarios del sistema (comienza con cloudsql...), no puedes cancelarla directamente. Debes reiniciar la base de datos para cancelarla.

A fin de identificar una consulta de larga duración y cancelarla o terminarla para desbloquear el proceso de vaciado, primero selecciona algunas de las consultas más antiguas. La línea LIMIT 10 ayuda a ajustar el resultado en la pantalla. Es posible que debas repetir esto después de resolver las consultas en ejecución más antiguas.

SELECT pid,
       age(backend_xid) AS age_in_xids,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       state,
       query
 FROM pg_stat_activity
 WHERE state != 'idle'
 ORDER BY 2 DESC
 LIMIT 10;

Si age_in_xids se devuelve como NULL, significa que no se le asignó un ID de transacción permanente a la transacción, y se puede ignorar de forma segura.

Cancela las consultas en las que el valor xids_left_to_wraparound se aproxima a 1 millón.

Si state es active, la consulta se puede cancelar con SELECT pg_cancel_backend(pid);. De lo contrario, debes finalizar toda la conexión con SELECT pg_terminate_backend(pid);, donde pid es el pid de la consulta anterior.

Transacciones de preparación huérfanas

Enumera todas las transacciones preparadas:

DB_NAME=> SELECT age(transaction),* FROM pg_prepared_xacts ;
┌─[ RECORD 1 ]┬───────────────────────────────┐
 age          2146483656                    
 transaction  2455493932                    
 gid          trx_id_pin                    
 prepared     2021-03-03 16:54:07.923158+00 
 owner        postgres                      
 database     DB_NAME                       
└─────────────┴───────────────────────────────┘

Para revertir las transacciones preparadas huérfanas más antiguas, usa el gid de la última consulta (en este caso, trx_id_pin) como el ID de transacción:

ROLLBACK PREPARED trx_id_pin;

También puedes confirmarlo:

COMMIT PREPARED trx_id_pin;

Consulta la documentación de SQL ROLLBACK PREPARED para obtener una explicación completa.

Ranuras de replicación abandonadas

En caso de que se abandone la ranura de replicación porque la réplica existente se detiene, está en pausa o tiene algún otro problema, puedes borrar la réplica de gcloud o la consola de Google Cloud.

Primero, comprueba que la réplica no esté inhabilitada como se describe en Administra réplicas de lectura. Si la réplica está inhabilitada, vuelve a habilitarla. Si el retraso sigue siendo alto, borra la réplica.

Las ranuras de replicación se pueden ver en la vista del sistema de pg_replication_slots.

La siguiente consulta recupera la información relevante:

SELECT *, age(xmin) AS age FROM pg_replication_slots;
┌─[ RECORD 1 ]────────┬─────────────────────────────────────────────────┐
 slot_name            cloudsql_1_355b114b_9ff4_4bc3_ac88_6a80199bd738 
 plugin               ¤                                               
 slot_type            physical                                        
 datoid               ¤                                               
 database             ¤                                               
 active               t                                               
 active_pid           1126                                            
 xmin                 2453745071                                      
 catalog_xmin         ¤                                               
 restart_lsn          C0/BEF7C2D0                                     
 confirmed_flush_lsn  ¤                                               
 age                  59                                              
└─────────────────────┴─────────────────────────────────────────────────┘

En este ejemplo, el valor pg_replication_slots está en buen estado (antigüedad == 59). Si la antigüedad fuera cercana a 2,000 millones, deberías borrar la ranura. No hay una forma fácil para saber cuál es la réplica,en caso de que la consulta muestre varios registros. Por lo tanto, debes verificarlos todos en caso de que haya una transacción de larga duración en cualquier réplica.

Transacciones de larga duración en réplicas

Verifica las réplicas de la transacción en ejecución más antigua con hot_standby_feedback establecido en on y, luego, inhabilítala en la réplica.

La columna backend_xmin en la vista pg_stat_replication tiene el TXID más antiguo necesario en la réplica.

Para que avance, detén la consulta que lo retiene en la réplica. Para descubrir qué consulta la retiene, usa la consulta en Transacciones de larga duración, pero esta vez ejecútala en la réplica.

Otra opción es reiniciar la réplica.

Configura VACUUM

Configura las dos marcas siguientes:

  • autovacuum_vacuum_cost_delay = 0
  • autovacuum_work_mem = 1048576

La primera inhabilita cualquier límite de disco para vaciado de PostgreSQL, a fin de que VACUUM pueda ejecutarse a máxima velocidad. De forma predeterminada, autovacuum está limitado, por lo que no usará toda la E/S del disco en los servidores más lentos.

La segunda marca, autovacuum_work_mem, disminuye la cantidad de pases de limpieza del índice. Si es posible, debería ser lo suficientemente grande como para almacenar todos los ID de filas inactivas en una tabla que VACUUM limpiará. Cuando configures este valor, ten en cuenta que esta es la cantidad máxima de memoria local que puede asignar VACUUM. Asegúrate de no permitir más de lo que está disponible, con algo de reserva. Si dejas la base de datos en ejecución en modo de solo lectura, también considera la memoria local que se usa para las consultas de solo lectura.

En la mayoría de los sistemas, usa el valor máximo (1 GB o 1048576 KB, como se muestra en el ejemplo). Este valor equivale a aproximadamente 178 millones de tuplas inactivas. Cualquier valor mayor provoca varios análisis de índice.

Estas y otras marcas se explican con más detalle en Optimiza, supervisa y soluciona problemas de operaciones de VACUUM en PostgreSQL.

Después de configurar estas marcas, reinicia la base de datos para que se inicie el proceso de autovacuum con los valores nuevos.

Puedes usar la vista pg_stat_progress_vacuum para supervisar el progreso de los VACUUM que se iniciaron de forma automática. En esta vista, se muestran los VACUUM que se ejecutan en todas las bases de datos y para tablas (relaciones) de otras bases de datos en las que no puedes buscar el nombre de la tabla mediante la columna de vista relid.

A fin de identificar las próximas bases de datos y tablas que necesitan limpiarse, usa las consultas de Optimiza, supervisa y soluciona problemas de operaciones de VACUUM en PostgreSQL. Si la VM del servidor tiene la potencia suficiente y tiene el ancho de banda para más procesos de VACUUM paralelos que los que inicia el vaciado automático, puedes iniciar algunos vaciados manuales.

Comprueba la velocidad de VACUUM

En esta sección, se describe cómo verificar la velocidad de VACUUM y cómo acelerarla si es necesario.

Revisa los procesos de autovacuum activos

Todos los backends que ejecutan VACUUM son visibles en la vista del sistema pg_stat_progress_vacuum.

Si la fase actual es scanning heap, puedes supervisar el progreso mediante la observación de los cambios en la columna heap_blks_scanned. Lamentablemente, no hay un método fácil para determinar la velocidad del análisis en otras fases.

Calcula la velocidad de análisis de VACUUM

Para estimar la velocidad de análisis, primero debes almacenar los valores base y, luego, calcular el cambio en el tiempo a fin de estimar el tiempo de finalización. En primer lugar, debes guardar una instantánea de heap_blks_scanned junto con una marca de tiempo mediante la siguiente consulta de instantánea:

SELECT set_config('save.ts', clock_timestamp()::text, false),
       set_config('save.heap_blks_scanned', heap_blks_scanned::text, false)
FROM pg_stat_progress_vacuum
WHERE datname = 'DB_NAME';

Debido a que no podemos guardar nada en las tablas que ya están en ajuste, usa set_config(flag, value) a fin de configurar dos marcas definidas por el usuario:save.ts y save.heap_blks_scanned para los valores actuales de pg_stat_progress_vacuum.

En la siguiente consulta, utilizaremos ambas como base de comparación para determinar la velocidad y estimar el tiempo de finalización.

NOTA: WHERE datname = DB_NAME restringe la investigación a una base de datos a la vez. Este número es suficiente si solo hay un vaciado automático en ejecución en esta base de datos, con más de una fila por base de datos. Se deben agregar condiciones de filtro adicionales ('AND relid= …'') a WHERE para indicar una sola fila de vaciado automático. Esto también se aplica a la próxima consulta.

Una vez que hayas guardado los valores base, puedes ejecutar la siguiente consulta:

with q as (
    SELECT datname,
           phase,
           heap_blks_total,
           heap_blks_scanned,
           clock_timestamp() - current_setting('save.ts')::timestamp AS ts_delta,
           heap_blks_scanned - current_setting('save.heap_blks_scanned')::bigint AS scanned_delta
     FROM pg_stat_progress_vacuum
     WHERE datname = DB_NAME
), q2 AS (
SELECT *,
       scanned_delta / extract('epoch' FROM ts_delta) AS pages_per_second
  FROM q
)
SELECT *,
       (heap_blks_total - heap_blks_scanned) / pages_per_second AS remaining_time
  FROM q2
;
┌─[ RECORD 1 ]──────┬──────────────────┐
│ datname            DB_NAME          │
│ phase              scanning heap    │
│ heap_blks_total    9497174          │
│ heap_blks_scanned  18016            │
│ ts_delta           00:00:40.30126   │
│ as_scanned_delta   11642            │
│ pages_per_second   288.87434288655  │
│ remaining_time     32814.1222418038 │
└───────────────────┴──────────────────┘

En esta consulta, se comparan los valores actuales con los valores base de guardado y se calcula pages_per_second y remaining_time, lo que nos permite decidir si VACUUM se ejecuta lo suficientemente rápido o si queremos acelerarlo. El valor remaining_time es solo para la fase scanning heap. Otras fases también tardan tiempo, a veces aún más. Puedes leer más información sobre la limpieza y ver las entradas de blogs en Internet que analizan algunos de los aspectos complejos de vacuum.

Acelera el VACUUM

La forma más fácil y rápida de hacer que el análisis de VACUUM sea más rápido es configurar autovacuum_vacuum_cost_delay=0. Esto se puede hacer a través de la consola de Google Cloud.

Desafortunadamente, el VACUUM ya en ejecución no obtiene este valor, por lo que es posible que debas reiniciar la base de datos.

Después de reiniciar, es posible que veas un resultado similar al siguiente:

┌─[ RECORD 1 ]──────┬──────────────────┐
│ datname            DB_NAME          │
│ phase              scanning heap    │
│ heap_blks_total    9497174          │
│ heap_blks_scanned  222382           │
│ ts_delta           00:00:21.422615  │
│ as_scanned_delta   138235           │
│ pages_per_second   6452.76031894332 │
│ remaining_time     1437.33713040171 │
└───────────────────┴──────────────────┘

En este ejemplo, la velocidad aumentó de menos de 300 páginas/s a aproximadamente 6,500 páginas/s, y el tiempo restante estimado para la fase de análisis de montón disminuyó de 9 horas a 23 minutos.

La velocidad de análisis de las otras fases no es tan fácil de medir, pero deben tener una aceleración similar.

También considera hacer que autovacuum_work_mem sea lo más grande posible para evitar múltiples pases sobre índices. Un pase de índice ocurre cada vez que se llena la memoria con indicadores de tupla inactivas.

Si la base de datos no se usa de otra manera, configura autovacuum_work_mem para que tenga aproximadamente 80% de memoria libre después de permitir la cantidad requerida de shared_buffers. Este es el límite superior para cada uno de los procesos VACUUM iniciados automáticamente. Si quieres seguir ejecutando cargas de trabajo de solo lectura, usa menos memoria.

Otras formas de mejorar la velocidad

Evita limpiar los índices

En las tablas grandes, VACUUM pasa la mayor parte del tiempo limpiando los índices.

PostgreSQL 14 tiene optimizaciones especiales para evitar la limpieza de índices si el sistema está en riesgo de ajuste.

En PostgreSQL 12 y 13, puedes ejecutar de forma manual la siguiente declaración:

VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;

En las versiones 11 y anteriores, puedes usar DROP en el índice antes de ejecutar la limpieza y volver a crearlo más tarde.

Descartar el índice cuando una limpieza automática ya se está ejecutando en esa tabla requiere cancelar la limpieza en ejecución y, luego, ejecutar de inmediato el comando de descarte de índice antes de que la limpieza automática inicie de nuevo la limpieza en esa tabla.

Primero, ejecuta la siguiente declaración para encontrar el PID del proceso de limpieza automática que necesitas finalizar:

SELECT pid, query 
  FROM pg_stat_activity
 WHERE state != 'idle'
   AND query ilike '%vacuum%';

Luego, ejecuta las siguientes instrucciones para finalizar la limpieza en ejecución y descartar uno o más índices:

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
SELECT pg_terminate_backend(<pid>);DROP INDEX <index1>;DROP INDEX <index2>; ...

Descarta la tabla que causa el problema

En casos excepcionales, puedes descartar la tabla. Por ejemplo, si es una tabla fácil de restablecer desde otra fuente, como una copia de seguridad o alguna otra base de datos.

Aún debes usar cloudsql.enable_maintenance_mode = 'on' y es probable que también finalices VACUUM en esa tabla como se mostró en la sección anterior.

VACUUM FULL

En casos excepcionales, es más rápido ejecutar VACUUM FULL FREEZE, por lo general, cuando la tabla tiene solo una pequeña proporción de tuplas activas. Esto se puede verificar desde la vista pg_stat_user_tables (a menos que haya habido un error que haya borrado las estadísticas).

El comando VACUUM FULL copia las tuplas activas en un archivo nuevo, por lo que hay suficiente espacio para estar disponible para el archivo nuevo y sus índices.

¿Qué sigue?