En esta página se describe lo que puedes hacer cuando tu base de datos se encuentra con la protección de envolvente de ID de transacción en PostgreSQL. Se manifiesta como un mensaje ERROR, como se indica a continuación:
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.
También 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.
Pasos que deben seguirse
- Averigua qué base de datos y qué tablas están provocando el ajuste de texto.
- Comprueba si hay algo que impida que se ejecute (AUTO)VACUUM (por ejemplo, un ID de transacción bloqueado).
- Mide la velocidad de AUTOVACUUM. Si es lento, puedes intentar acelerarlo.
- Si es necesario, ejecuta algunos comandos VACUUM más manualmente.
- Busca otras formas de acelerar la aspiradora. A veces, la forma más rápida es eliminar la tabla o algunos índices.
Muchas de las recomendaciones de valores de las marcas no son exactas a propósito porque dependen de muchos parámetros de la base de datos. Para obtener un análisis más detallado sobre este tema, consulta los documentos enlazados al final de esta página.
Busca la base de datos y la tabla que provocan el salto de línea
Buscar la base de datos
Para saber qué base de datos o bases de datos contienen las tablas que provocan el ajuste de texto, 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.
Buscar 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 devuelve la tabla o las tablas que causan el problema.
Para tablas TEMPORARY
Si schema_name empieza por pg_temp_, la única forma de resolver el problema es eliminar la tabla, ya que PostgreSQL no permite ejecutar VACUUM en tablas temporales creadas en otras sesiones. A veces, si esa sesión está abierta y es accesible, puedes hacer un vacío en la tabla, pero no suele ser así.
Usa las siguientes instrucciones SQL para eliminar la tabla temporal:
SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
DROP TABLE pg_temp_<N>.<tablename>;
Si este era el único bloqueo, en aproximadamente un minuto, el autovacuum recogerá este cambio y moverá datfrozenxid hacia adelante en pg_database. De esta forma, se resuelve el estado de solo lectura de la protección envolvente.
Tablas normales
En el caso de las tablas normales (es decir, no temporales), sigue los pasos que se indican a continuación para comprobar si hay algo que impida la limpieza, si VACUUM se está ejecutando lo suficientemente rápido y si se está limpiando la tabla más importante.
Comprobar si hay un ID de transacción bloqueado
Una de las posibles razones por las que el sistema puede quedarse sin IDs de transacción es que PostgreSQL no puede congelar (es decir, marcar como visible para todas las transacciones) ningún ID de transacción creado después de que se iniciara la transacción más antigua que se esté ejecutando. Esto se debe a las reglas de control de simultaneidad multiversión (MVCC). En casos extremos, estas transacciones pueden ser tan antiguas que impidan que VACUUM limpie las transacciones antiguas durante todo el límite de 2000 millones de IDs de transacción y provoquen que todo el sistema deje de aceptar nuevas DML. Normalmente, también verás advertencias en el archivo de registro, como WARNING: oldest
xmin is far in the past.
Solo debes pasar a la optimización una vez que se haya solucionado el problema con el ID de transacción.
A continuación, se indican cuatro posibles motivos por los que puede haber un ID de transacción bloqueado, así como información sobre cómo mitigar cada uno de ellos:
- Transacciones de larga duración: identifícalas y cancela o finaliza el backend para desbloquear el robot aspirador.
- Transacciones de preparación huérfanas: restaura estas transacciones.
- Slots de replicación abandonados: elimina los slots abandonados.
- 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 vacío.
En estos casos, la siguiente consulta devuelve la antigüedad de la transacción más antigua y el número de transacciones que quedan hasta que se reinicie el contador:
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 puede devolver cualquiera de los valores de *_left que se hayan registrado cerca o a menos de 1 millón de la vuelta. Este valor es el límite de protección de envolvente cuando PostgreSQL deja de aceptar nuevos comandos de escritura. En ese caso, consulta Eliminar bloqueadores de VACUUM o Ajustar VACUUM.
Por ejemplo, la consulta anterior podría devolver lo siguiente:
┌─[ 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 │ ¤ │ └──────────────────────────────┴────────────┘
donde oldest_running_xact_left y oldest_prepared_xact_left están dentro del límite de protección envolvente de 1 millón. En ese caso, primero debes eliminar los bloqueos para que se pueda llevar a cabo la
VACUUM.
Quitar bloqueadores de VACUUM
Transacciones de larga duración
En la consulta anterior, si oldest_running_xact es igual a oldest_prepared_xact, vaya a la sección
Transacción de preparación huérfana, ya que el valor de última ejecución también incluye las transacciones preparadas.
Puede que primero tengas que ejecutar el siguiente comando como usuario postgres:
GRANT pg_signal_backend TO postgres;
Si la transacción infractora pertenece a alguno de los usuarios del sistema (empezando por cloudsql...), no podrás cancelarla directamente. Debes reiniciar la base de datos para cancelarla.
Para identificar una consulta de larga duración y cancelarla o finalizarla para desbloquear el vacío, primero selecciona algunas de las consultas más antiguas. La línea LIMIT 10 ayuda a
ajustar el resultado en la pantalla. Puede que tengas que repetir este paso después de resolver las consultas 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 devuelve NULL, significa que no se ha asignado un ID de transacción permanente a la transacción y se puede ignorar sin problemas.
Cancela las consultas en las que 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 terminar 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
Lista 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 │ └─────────────┴───────────────────────────────┘
Revierte las transacciones preparadas huérfanas más antiguas. Para ello, usa el gid de la última consulta (en este caso, trx_id_pin) como ID de transacción:
ROLLBACK PREPARED trx_id_pin;
También puedes confirmarlo:
COMMIT PREPARED trx_id_pin;
Consulta la documentación de ROLLBACK PREPARED de SQL para obtener una explicación completa.
Ranuras de replicación abandonadas
Si se abandona el espacio de réplica porque la réplica existente se ha detenido, pausado o tiene algún otro problema, puedes eliminar la réplica desde la consola gcloud o Google Cloud .
Primero, comprueba que la réplica no esté inhabilitada, tal como se describe en Gestionar réplicas de lectura. Si la réplica está inhabilitada, vuelve a habilitarla. Si la latencia sigue siendo alta, elimina la réplica.
Los slots de replicación se pueden ver en la vista de pg_replication_slots
sistema.
La siguiente consulta obtiene la información pertinente:
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 de pg_replication_slots es correcto (age == 59).
Si la edad fuera cercana a los 2000 millones, te convendría eliminar el espacio. No hay una forma sencilla de saber qué réplica es cuál en caso de que la consulta devuelva varios registros. Por lo tanto, comprueba todas las réplicas por si hay alguna transacción de larga duración.
Transacciones de larga duración en réplicas
Comprueba las réplicas de la transacción en curso más antigua con hot_standby_feedback
definido como on y deshabilítala en la réplica.
La columna backend_xmin de la vista pg_stat_replication
tiene la TXID más antigua que necesita la réplica.
Para que avance, detén la consulta que lo retiene en la réplica. Para descubrir qué consulta está provocando el retraso, usa la consulta de Transacciones de larga duración, pero esta vez ejecútala en la réplica.
Otra opción es reiniciar la réplica.
Configurar VACUUM
Define las dos siguientes marcas:
- autovacuum_vacuum_cost_delay = 0
- autovacuum_work_mem = 1048576
La primera inhabilita la limitación del disco para el proceso de limpieza de PostgreSQL, de modo que VACUUM pueda ejecutarse a toda velocidad. De forma predeterminada, el autovacuum se limita para que no utilice todas las operaciones de E/S de disco en los servidores más lentos.
La segunda marca, autovacuum_work_mem, reduce el número de pases de limpieza de índices. Si es posible, debe ser lo suficientemente grande como para almacenar todos los IDs de las filas eliminadas de una tabla que VACUUM va a limpiar. Al definir este valor, ten en cuenta que es la cantidad máxima de memoria local que puede asignar cada VACUUM en ejecución. Asegúrate de no permitir más de lo que está disponible y de dejar algo de reserva. Si dejas la base de datos en modo de solo lectura, también debes tener en cuenta 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 se ajusta a unos 178 millones de tuplas inactivas. Si se añade más, se seguirán produciendo varias pasadas de análisis de índice.
Estas y otras marcas se explican con más detalle en el artículo Optimizar, monitorizar y solucionar problemas de operaciones VACUUM en PostgreSQL.
Después de definir estas marcas, reinicia la base de datos para que el autovacuum se inicie con los nuevos valores.
Puedes usar la vista pg_stat_progress_vacuum para monitorizar el progreso de los VACUUMs iniciados por autovacuum. En esta vista se muestran los VACUUMs que se están ejecutando en todas las bases de datos y en las tablas (relaciones) de otras bases de datos cuyo nombre no puedes consultar mediante la columna relid de la vista.
Para identificar las bases de datos y las tablas que necesitan una operación VACUUM, usa las consultas de Optimizar, monitorizar y solucionar problemas de operaciones VACUUM en PostgreSQL. Si la VM del servidor es lo suficientemente potente y tiene el ancho de banda para ejecutar más procesos VACUUM en paralelo que los que inicia autovacuum, puedes iniciar algunos VACUUM manuales.
Comprobar la velocidad de VACUUM
En esta sección se describe cómo comprobar la velocidad de VACUUM y cómo acelerarla, si es necesario.
Comprobar los autovaciados en curso
Todos los back-ends que ejecutan VACUUM se pueden ver en la vista del sistema pg_stat_progress_vacuum.
Si la fase actual es scanning heap, puedes monitorizar el progreso viendo los cambios en la columna heap_blks_scanned.
Por desgracia, no hay una forma sencilla de determinar la velocidad de análisis en otras fases.
Estimar la velocidad de análisis de VACUUM
Para estimar la velocidad de análisis, primero debes almacenar los valores base y, a continuación, calcular el cambio a lo largo del tiempo para estimar el tiempo de finalización. Primero, debes guardar una captura de heap_blks_scanned junto con una marca de tiempo mediante la siguiente consulta de captura:
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';
Como no podemos guardar nada en tablas que ya estén en envolvente, usa set_config(flag, value) para definir dos marcas definidas por el usuario (save.ts y save.heap_blks_scanned) con los valores actuales de pg_stat_progress_vacuum.
En la siguiente consulta, usamos estos dos valores 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 cada vez. Este número es suficiente si solo se está ejecutando un autovacuum en esta base de datos, con más de una fila por base de datos. Se deben añadir condiciones de filtro adicionales
('AND relid= …'') a WHERE para indicar una sola fila de
autovacuum. Esto también se aplica a la siguiente consulta.
Una vez que haya guardado los valores base, puede 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 │ └───────────────────┴──────────────────┘
Esta consulta compara los valores actuales con los valores base guardados y calcula pages_per_second y remaining_time, lo que nos permite decidir si VACUUM se está ejecutando lo suficientemente rápido o si queremos acelerarlo. El valor remaining_time solo se aplica a la fase scanning heap.
Otras fases también llevan tiempo, a veces incluso más. Puedes leer más sobre la aspiración
y consultar entradas de blog en Internet en las que se debaten algunos de los aspectos complejos de la aspiración.
Acelerar VACUUM
La forma más sencilla y rápida de acelerar el análisis de VACUUM es definir autovacuum_vacuum_cost_delay=0. Puedes hacerlo desde la
Google Cloud consola.
Lamentablemente, el VACUUM que ya está en ejecución no recoge este valor y es posible que tengas que 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 ha aumentado de menos de 300 páginas por segundo a unas 6500 páginas por segundo, y el tiempo restante previsto para la fase de análisis del montículo ha disminuido de 9 horas a 23 minutos.
La velocidad de análisis de las otras fases no es tan fácil de medir, pero debería mostrar una aceleración similar.
También puedes hacer que autovacuum_work_mem sea lo más grande posible para evitar varias pasadas por los índices. Un recorrido de índice se produce cada vez que la memoria se llena con punteros de tuplas inactivas.
Si la base de datos no se usa para otros fines, asigna a autovacuum_work_mem
un 80% de memoria libre después de asignar la cantidad necesaria para shared_buffers.
Es el límite superior de cada uno de los procesos VACUUM iniciados por el vaciado automático. Si quieres seguir ejecutando cargas de trabajo de solo lectura, usa menos memoria.
Otras formas de mejorar la velocidad
Evitar la limpieza de índices
En el caso de las tablas de gran tamaño, VACUUM dedica la mayor parte del tiempo a limpiar los índices.
PostgreSQL 14 tiene optimizaciones especiales para evitar la limpieza de índices si el sistema corre el riesgo de envolverse.
En PostgreSQL 12 y 13, puedes ejecutar manualmente la siguiente instrucción:
VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;
En las versiones 11 y anteriores, puedes DROP el índice antes de ejecutar vacuum y volver a crearlo más adelante.
Si se elimina el índice cuando ya se está ejecutando un autovacuum en esa tabla, es necesario cancelar el vacuum en ejecución y, a continuación, ejecutar inmediatamente el comando de eliminación del índice antes de que el autovacuum consiga iniciar de nuevo el vacuum en esa tabla.
Primero, ejecuta la siguiente instrucción para encontrar el PID del proceso de autovacuum que necesitas finalizar:
SELECT pid, query
FROM pg_stat_activity
WHERE state != 'idle'
AND query ilike '%vacuum%';
A continuación, ejecuta las siguientes instrucciones para finalizar el proceso de limpieza en segundo plano y eliminar uno o varios índices:
SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
SELECT pg_terminate_backend(<pid>);DROP INDEX <index1>;DROP INDEX <index2>; ...
Eliminar la tabla infractora
En algunos casos excepcionales, puedes eliminar la tabla. Por ejemplo, si se trata de una tabla que se puede restaurar fácilmente desde otra fuente, como una copia de seguridad u otra base de datos.
Seguirás necesitando usar cloudsql.enable_maintenance_mode = 'on' y, probablemente, también terminar el VACUUM en esa tabla, tal como se muestra en la sección anterior.
VACUUM FULL
En casos excepcionales, es más rápido ejecutar VACUUM FULL FREEZE,
normalmente cuando la tabla solo tiene una pequeña proporción de tuplas activas.
Puedes comprobarlo en la vista pg_stat_user_tables (a menos que se haya producido un fallo que haya borrado las estadísticas).
El comando VACUUM FULL copia las tuplas activas en un archivo nuevo, por lo que debe haber suficiente espacio disponible para el nuevo archivo y sus índices.
Siguientes pasos
- Consulta más información sobre VACUUM para envolventes.
- Más información sobre la aspiración rutinaria
- Más información sobre la aspiración automática
- Consulta más información sobre cómo optimizar, monitorizar y solucionar problemas de operaciones VACUUM en PostgreSQL.