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?
- Más información sobre VACUUM para ajuste
- Más información sobre el vaciado de rutina.
- Más información sobre el vaciado automático
- Más información sobre cómo optimizar, supervisar y solucionar problemas de operaciones de VACUUM en PostgreSQL