Resolución de bloqueadores de vaciado identificables en Aurora PostgreSQL - Amazon Aurora
Instrucción activaInactividad en la transacciónTransacción preparadaRanura de replicación lógicaInstancias de lectorTablas temporales

Resolución de bloqueadores de vaciado identificables en Aurora PostgreSQL

Autovacuum lleva a cabo vaciados de forma intensiva y reduce la antigüedad de los ID de transacción hasta situarlos por debajo del umbral especificado por el parámetro autovacuum_freeze_max_age de la instancia de RDS. Esta antigüedad se puede consultar mediante la métrica MaximumUsedTransactionIDs de Amazon CloudWatch.

Para encontrar la configuración de autovacuum_freeze_max_age (que tiene un valor predeterminado de 200 millones de ID de transacción) para una instancia de Amazon RDS, puede utilizar la siguiente consulta:

SELECT
    TO_CHAR(setting::bigint, 'FM9,999,999,999') autovacuum_freeze_max_age
FROM
    pg_settings
WHERE
    name = 'autovacuum_freeze_max_age';

Tenga en cuenta que postgres_get_av_diag() solo comprueba si hay bloqueadores de vaciado intensivo cuando la antigüedad supera el umbral de autovacuum adaptativo de Amazon RDS de 500 millones de ID de transacción. Para que postgres_get_av_diag() detecte los bloqueadores, el bloqueador debe tener al menos 500 millones de transacciones de antigüedad.

La función postgres_get_av_diag() identifica los siguientes tipos de bloqueadores:

Instrucción activa

En PostgreSQL, una instrucción activa es una instrucción SQL que la base de datos está ejecutando actualmente. Incluye consultas, transacciones o cualquier operación en curso. Al realizar la supervisión mediante pg_stat_activity, la columna de estado indica que el proceso con el PID correspondiente está activo.

La función postgres_get_av_diag() muestra un resultado similar al siguiente cuando identifica una instrucción que resulta ser una instrucción activa.

blocker               | Active statement
database              | my_database
blocker_identifier    | SELECT pg_sleep(20000);
wait_event            | Timeout:PgSleep
autovacuum_lagging_by | 568,600,871
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (29621);"}

Acción sugerida

Siguiendo las instrucciones de la columna suggestion, el usuario puede conectarse a la base de datos en la que se encuentra la instrucción activa y, tal como se especifica en la columna suggested_action, se recomienda revisar detenidamente la opción de finalizar la sesión. Si la finalización es segura, se puede utilizar la función pg_terminate_backend() para finalizar la sesión. Esta acción la puede realizar un administrador (como la cuenta maestra de RDS) o un usuario con el privilegio pg_terminate_backend() necesario.

aviso

Al finalizar la sesión, se desharán (ROLLBACK) los cambios que haya realizado. En función de sus requisitos, es posible que quiera volver a ejecutar la instrucción. Sin embargo, se recomienda hacerlo únicamente después de que el proceso de autovacuum haya finalizado su operación de vaciado intensivo.

Inactividad en la transacción

El concepto de inactividad en una instrucción de transacción se refiere a cualquier sesión en la que se haya abierto una transacción explícita (por ejemplo, emitiendo una instrucción BEGIN), se haya realizado algún trabajo y se esté esperando a que el cliente pase más trabajo o dé la señal de finalización de la transacción emitiendo una instrucción COMMIT, ROLLBACK o END (lo que daría como resultado un COMMIT implícitamente).

La función postgres_get_av_diag() muestra un resultado similar al siguiente cuando identifica una instrucción idle in transaction como bloqueador.

blocker               | idle in transaction
database              | my_database
blocker_identifier    | INSERT INTO tt SELECT * FROM tt;
wait_event            | Client:ClientRead
autovacuum_lagging_by | 1,237,201,759
suggestion            | Connect to database "my_database", review carefully and you may consider terminating the process using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_terminate_backend (28438);"}

Acción sugerida

Como se indica en la columna suggestion, puede conectarse a la base de datos en la que se encuentra la sesión de inactividad en la transacción y finalizar la sesión mediante la función pg_terminate_backend(). El usuario puede ser su usuario administrador (cuenta maestra de RDS) o un usuario con el privilegio pg_terminate_backend().

aviso

Al finalizar la sesión, se desharán (ROLLBACK) los cambios que haya realizado. En función de sus requisitos, es posible que quiera volver a ejecutar la instrucción. Sin embargo, se recomienda hacerlo únicamente después de que el proceso de autovacuum haya finalizado su operación de vaciado intensivo.

Transacción preparada

PostgreSQL permite realizar transacciones que forman parte de una estrategia de confirmación de dos fases denominada transacciones preparadas. Se habilitan al establecer el parámetro max_prepared_transactions en un valor distinto de cero. Las transacciones preparadas han sido diseñadas para garantizar que una transacción sea duradera y permanezca disponible incluso después de que la base de datos se bloquee, se reinicie o se desconecte del cliente. Al igual que las transacciones normales, se les asigna un identificador de transacción y pueden afectar al autovacuum. Si se deja en un estado preparado, el autovacuum no puede realizar la congelación y podría provocar un reinicio del ID de transacción.

Cuando las transacciones se dejan preparadas indefinidamente sin que las resuelva un administrador de transacciones, se convierten en transacciones preparadas huérfanas. La única forma de solucionar este problema es confirmar o revertir la transacción mediante los comandos COMMIT PREPARED o ROLLBACK PREPARED respectivamente.

nota

Tenga en cuenta que una copia de seguridad realizada durante una transacción preparada seguirá conteniendo esa transacción después de la restauración. Consulte la siguiente información sobre cómo localizar y cerrar dichas transacciones.

La función postgres_get_av_diag() muestra el siguiente resultado cuando identifica un bloqueador que es una transacción preparada.

blocker               | Prepared transaction
database              | my_database
blocker_identifier    | myptx
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database" and consider either COMMIT or ROLLBACK the prepared transaction using suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"COMMIT PREPARED 'myptx';",[OR],"ROLLBACK PREPARED 'myptx';"}

Acción sugerida

Como se menciona en la columna de sugerencias, conéctese a la base de datos en la que se encuentre la transacción preparada. Sobre la base de la columna suggested_action, revise detenidamente si desea enviar una instrucción COMMIT o ROLLBACK, y realizar la acción correspondiente.

Para supervisar las transacciones preparadas en general, PostgreSQL ofrece una vista de catálogo llamada pg_prepared_xacts. Puede utilizar la siguiente consulta para buscar transacciones preparadas.

SELECT
    gid,
    prepared,
    owner,
    database,
    transaction AS oldest_xmin
FROM
    pg_prepared_xacts
ORDER BY
    age(transaction) DESC;

Ranura de replicación lógica

El propósito de una ranura de replicación es almacenar los cambios no consumidos hasta que se repliquen en un servidor de destino. Para obtener más información, consulte Logical replication de PostgreSQL.

Existen dos tipos de ranuras de replicación lógica.

Ranuras de replicación lógica inactivas

Cuando finaliza la replicación, los registros de transacciones no consumidas no se pueden eliminar y la ranura de replicación queda inactiva. Aunque un suscriptor no utilice actualmente una ranura de replicación lógica inactiva, esta permanece en el servidor, lo que provoca la retención de los archivos WAL y evita la eliminación de los registros de transacciones antiguos. Esto puede aumentar el uso del disco y, específicamente, impedir que autovacuum limpie las tablas del catálogo interno, ya que el sistema debe evitar que se sobrescriba la información de LSN. Si este problema no se soluciona, puede provocar una sobrecarga del catálogo, una degradación del rendimiento y un mayor riesgo de que se produzcan vaciados previos al reinicio, lo que podría causar tiempo de inactividad en las transacciones.

Ranuras de replicación lógica activas pero lentas

A veces, la eliminación de las tuplas inactivas del catálogo se retrasa debido a la degradación del rendimiento de la replicación lógica. Este retraso en la replicación ralentiza la actualización de catalog_xmin y puede provocar una sobrecarga del catálogo y un vaciado previo al reinicio.

La función postgres_get_av_diag() muestra un resultado similar al siguiente cuando encuentra una ranura de replicación lógica que funciona como bloqueador.

blocker               | Logical replication slot
database              | my_database
blocker_identifier    | slot1
wait_event            | Not applicable
autovacuum_lagging_by | 1,940,103,068
suggestion            | Ensure replication is active and resolve any lag for the slot if active. If inactive, consider dropping it using the command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"SELECT pg_drop_replication_slot('slot1') FROM pg_replication_slots WHERE active = 'f';"}

Acción sugerida

Para resolver este problema, compruebe la configuración de la replicación para ver si hay problemas con el esquema o los datos de destino que puedan estar finalizando el proceso de aplicación. Los motivos más comunes son los siguientes:

  • Columnas faltantes

  • Tipos de datos incompatibles

  • Discrepancia de datos

  • Tabla faltante

Si el problema está relacionado con problemas de infraestructura:

Si la instancia está fuera de la red de AWS o en AWS EC2, consulte a su administrador sobre cómo resolver los problemas relacionados con la disponibilidad o la infraestructura.

Eliminación de la ranura inactiva

aviso

Precaución: Antes de eliminar una ranura de replicación, asegúrese exhaustivamente de que no tenga ninguna replicación en curso, de que esté inactiva y de que se encuentre en un estado irrecuperable. Si se elimina una ranura de forma prematura, se podría interrumpir la replicación o provocar la pérdida de datos.

Después de confirmar que la ranura de replicación ya no es necesaria, elimínela para permitir que el autovacuum continúe. La condición active = 'f' garantiza que solo se eliminará una ranura inactiva.

SELECT pg_drop_replication_slot('slot1') WHERE active ='f'

Instancias de lector

Cuando la configuración hot_standby_feedback está habilitada, evita que el autovacuum de la instancia de escritura elimine filas muertas que podrían seguir siendo necesarias para las consultas que se ejecutan en la instancia de lector. Este comportamiento es necesario porque las consultas que se ejecutan en la instancia de lector (también aplicable a las instancias de lector en la base de datos global de Aurora) requieren que esas filas permanezcan disponibles en la instancia de escritura, lo que evita conflictos y cancelaciones de consultas.

nota

hot_standby_feedback está habilitado de forma predeterminada y no se puede modificar en Aurora PostgreSQL.

La función postgres_get_av_diag() muestra un resultado similar al siguiente cuando encuentra una réplica de lectura con una ranura de replicación física como bloqueador.

blocker               | Oldest query running on aurora reader
database              | Not applicable
blocker_identifier    | my-aurora-reader-2
wait_event            | Not applicable
autovacuum_lagging_by | 540,122,859
suggestion            | Run the following query on the reader "my-aurora-reader-2" to find the long running query:                                                                                                 
                      | SELECT * FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 523476310;                                                                                                    
                      | Review carefully and you may consider terminating the query on reader using suggested_action.                                                      
suggested_action      | {"SELECT pg_terminate_backend(pid) FROM pg_catalog.pg_stat_activity WHERE backend_xmin::text::bigint = 523476310;","                                                                       
                      | [OR]                                                                                                                                                                                    
                      | ","Delete the reader if not needed"}

Como se recomienda en la columna suggested_action, revise detenidamente estas opciones para desbloquear el autovacuum.

  • Finalizar la consulta: de acuerdo con las instrucciones de la columna de sugerencias, puede conectarse a la réplica de lectura, tal y como se especifica en la columna suggested_action. Se recomienda revisar detenidamente la opción para finalizar la sesión. Si la finalización se considera segura, se puede utilizar la función pg_terminate_backend() para finalizar la sesión. Esta acción la puede realizar un administrador (como la cuenta maestra de RDS) o un usuario con el privilegio pg_terminate_backend() necesario.

    Puede ejecutar el siguiente comando SQL en la réplica de lectura para finalizar la consulta que impide que el proceso de vaciado en el principal pueda limpiar las filas antiguas. El valor de backend_xmin se indica en la salida de la función:

    SELECT
    pg_terminate_backend(pid)
FROM
    pg_catalog.pg_stat_activity
WHERE
    backend_xmin::text::bigint = backend_xmin;

  • Eliminar las instancias de lectura si no son necesarias: si la instancia de lectura ya no es necesaria, puede eliminarla. Esto eliminará la sobrecarga de replicación asociada y permitirá que el servidor principal recicle los registros de transacciones sin que la instancia se lo obstaculice.

Tablas temporales

Las tablas temporales, que se crean con la palabra clave TEMPORARY, residen en el esquema temporal (por ejemplo, pg_temp_xxx) y solo la sesión que las haya creado puede acceder a ellas. Las tablas temporales se eliminan al finalizar la sesión. Sin embargo, estas tablas son invisibles para el proceso de autovacuum de PostgreSQL y la sesión que las haya creado debe vaciarlas manualmente. Intentar vaciar la tabla temporal desde otra sesión no tiene ningún efecto.

En circunstancias poco habituales, puede existir una tabla temporal sin que sea propiedad de una sesión activa. Si la sesión propietaria finaliza inesperadamente debido a un bloqueo grave, un problema de red o un suceso similar, es posible que la tabla temporal no se limpie y quede como una tabla “huérfana”. Cuando el proceso de autovacuum de PostgreSQL detecta una tabla temporal huérfana, registra el siguiente mensaje:

LOG: autovacuum: found orphan temp table \"%s\".\"%s\" in database \"%s\"

La función postgres_get_av_diag() muestra un resultado similar al siguiente cuando identifica una tabla temporal como bloqueador. Para que la función muestre correctamente el resultado relacionado con las tablas temporales, debe ejecutarse en la misma base de datos en la que se encuentren esas tablas.

blocker               | Temporary table
database              | my_database
blocker_identifier    | pg_temp_14.ttemp
wait_event            | Not applicable
autovacuum_lagging_by | 1,805,802,632
suggestion            | Connect to database "my_database". Review carefully, you may consider dropping temporary table using command in suggested_action. For more information, see Working with PostgreSQL autovacuum in the Amazon RDS User Guide.
suggested_action      | {"DROP TABLE ttemp;"}

Acción sugerida

Siga las instrucciones que aparecen en la columna suggestion del resultado para identificar y eliminar la tabla temporal que impide la ejecución del autovacuum. Use el siguiente comando para eliminar la tabla temporal notificada por postgres_get_av_diag(). Reemplace el nombre de la tabla en función del resultado proporcionado por la función postgres_get_av_diag().

DROP TABLE my_temp_schema.my_temp_table;

La siguiente consulta se puede utilizar para identificar tablas temporales:

SELECT
    oid,
    relname,
    relnamespace::regnamespace,
    age(relfrozenxid)
FROM
    pg_class
WHERE
relpersistence = 't'
ORDER BY
    age(relfrozenxid) DESC;