Uso de autovacuum de PostgreSQL en Amazon RDS for PostgreSQL - Amazon Relational Database Service

Uso de autovacuum de PostgreSQL en Amazon RDS for PostgreSQL

Le recomendamos que use la característica autovacuum para mantener en buen estado su instancia de base de datos PostgreSQL. Autovacuum automatiza el comienzo de los comandos VACUUM y ANALYZE. Comprueba las tablas con una gran cantidad de tuplas insertadas, actualizadas o eliminadas. Después de esta verificación, recupera el almacenamiento mediante la eliminación de datos obsoletos o tuplas de la base de datos de PostgreSQL.

De forma predeterminada, autovacuum está activado para las instancias de base de datos de Amazon RDS for PostgreSQL que crea por medio de cualquiera de los grupos de parámetros de base de datos de PostgreSQL predeterminados. Entre ellas se incluyen default.postgres10, default.postgres11, y así sucesivamente. Todos los grupos de parámetros predeterminados de la base de datos PostgreSQL tienen un parámetro rds.adaptive_autovacuum que se establece en 1, lo que activa la característica. Otros parámetros de configuración asociados con la característica autovacuum también se establecen de forma predeterminada. Debido a que estos valores predeterminados son algo genéricos, puede beneficiarse de ajustar algunos de los parámetros asociados con la característica autovacuum para su carga de trabajo específica.

A continuación, puede encontrar más información sobre autovacuum y cómo ajustar algunos de sus parámetros en su instancia de base de datos de RDS for PostgreSQL. Para obtener información más específica, consultePrácticas recomendadas para trabajar con PostgreSQL.

Asignación de memoria para autovacuum

Uno de los parámetros más importantes que influyen en el rendimiento de autovacuum es el parámetro maintenance_work_mem . Este parámetro determina cuánta memoria se asigna para que autovacuum la use para examinar una tabla de base de datos y para alojar todos los ID de fila a los que se va a aplicar el vacío. Si define el parámetro maintenance_work_mem en un valor demasiado bajo, el proceso de vacío puede tener que examinar la tabla varias veces para completar su trabajo. Esta variedad de análisis puede tener un impacto negativo en el rendimiento.

Cuando haga cálculos para determinar el valor del parámetro maintenance_work_mem, debe recordar dos cosas:

  • La unidad predeterminada para este parámetro es el kilobyte (KB).

  • El parámetro maintenance_work_mem funciona en combinación con el parámetro autovacuum_max_workers. Si tiene demasiadas tablas pequeñas, asigne más autovacuum_max_workers y menos maintenance_work_mem. Si tiene tablas grandes (por ejemplo, de 100 GB o más), asigne más memoria y menos procesos de trabajo. Debe tener suficiente memoria asignada para que funcione en la tabla más grande. Cada autovacuum_max_workers puede utilizar la memoria que asigne. Por lo tanto, asegúrese de que la combinación de procesos de trabajo y memoria sea igual a la memoria total que desea asignar.

En términos generales, para los hosts grandes, defina el parámetro maintenance_work_mem en un valor comprendido entre uno y dos gigabytes (entre 1 048 576 y 2 097 152 KB). Para los hosts extremadamente grandes, defina el parámetro en un valor comprendido entre dos y cuatro gigabytes (entre 2 097 152 y 4 194 304 KB). El valor que establezca para este parámetro depende de la carga de trabajo. Amazon RDS actualizó el valor predeterminado de este parámetro para que sean kilobytes calculados de la siguiente manera.

GREATEST({DBInstanceClassMemory/63963136*1024},65536).

Reducción de la probabilidad de reinicio del identificador de transacción

En algunos casos, la configuración de grupos de parámetros relacionada con autovacuum puede no ser lo suficientemente agresiva como para evitar el reinicio del identificador de transacción. Para solucionar esto, RDS for PostgreSQL proporciona un mecanismo que adapta los valores de los parámetros de autovacuum automáticamente. El ajuste de parámetros autovacuum adaptativo es una característica de RDS para PostgreSQL. Puede encontrar una explicación detallada sobre el reinicio del identificador de transacción en la documentación de PostgreSQL.

El ajuste de parámetros de autovacuum adaptativo está activado de forma predeterminada para las instancias de RDS for PostgreSQL con el parámetro dinámico rds.adaptive_autovacuum establecido en ON (Activado). Le recomendamos encarecidamente que mantenga esta opción activada. Sin embargo, para apagar el ajuste de parámetros autovacuum adaptativo, establezca el parámetro rds.adaptive_autovacuum en 0 u OFF.

El reinicio de identificador de transacción sigue siendo posible incluso cuando Amazon RDS ajusta los parámetros de autovacuum. Le animamos a implementar una alarma Amazon CloudWatch para el reinicio de identificador de transacción. Para obtener más información, consulte la publicación Implement an early warning system for transaction ID wraparound in RDS for PostgreSQL (Implementar un sistema de alerta temprana para el ajuste de ID de transacción en RDS for PostgreSQL) en el Blog de Base de datos de AWS.

Con el ajuste de parámetros de autovacuum adaptable activado, Amazon RDS comienza a ajustar los parámetros de autovacuum cuando la métrica de CloudWatch MaximumUsedTransactionIDs alcanza el valor del parámetro autovacuum_freeze_max_age o 500 000 000, el que sea mayor.

Amazon RDS continúa ajustando los parámetros para el autovacuum si una tabla continúa tendiendo hacia el ajuste de ID de transacción. Cada uno de estos ajustes dedica más recursos a autovacuum para evitar el reinicio. Amazon RDS actualiza los siguientes parámetros relacionados con autovacuum:

RDS modifica estos parámetros solo si el nuevo valor hace que autovacuum sea más agresivo. Estos parámetros se modifican en la memoria en la instancia de base de datos. Los valores en el grupo de parámetros no han cambiado. Para ver la configuración en memoria actual, utilice el comando de SQL PostgreSQL SHOW de PostgreSQL.

Cuando Amazon RDS modifica alguno de estos parámetros de autovacuum, genera un evento para la instancia de base de datos afectada. Este evento se puede ver en la AWS Management Console y a través de la API de Amazon RDS. Una vez que la métrica CloudWatch MaximumUsedTransactionIDs vuelve por debajo del límite, Amazon RDS restablece los parámetros relacionados con el autovacuum en la memoria a los valores especificados en el grupo de parámetros. Luego, genera otro evento correspondiente a este cambio.

Determinar si las tablas de una base de datos necesitan vacío

Puede utilizar la siguiente consulta para mostrar el número de transacciones sin vaciar en una base de datos. La columna datfrozenxid de una fila pg_database de una base de datos es un límite inferior en los identificadores de transacción normales que aparecen en esa base de datos. Esta columna es el mínimo de los valores relfrozenxid por tabla dentro de la base de datos.

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;

Por ejemplo, los resultados de ejecutar la consulta anterior podrían ser los siguientes.

datname | age mydb | 1771757888 template0 | 1721757888 template1 | 1721757888 rdsadmin | 1694008527 postgres | 1693881061 (5 rows)

Cuando la antigüedad de una base de datos llega a los dos mil millones de identificadores de transacción, se produce el reinicio de los TransactionID (XID) y la base de datos cambia al modo de solo lectura. Puede usar esta consulta para generar una métrica y ejecutarla varias veces al día. De manera predeterminada, autovacuum está configurado para mantener la antigüedad de las transacciones en un máximo de 200,000,000 (autovacuum_freeze_max_age).

Una estrategia de monitorización de muestra podría ser la siguiente:

  • Establezca el valor autovacuum_freeze_max_age en 200 millones de transacciones.

  • Si una tabla llega a 500 millones de transacciones sin vaciar, se dispara una alarma de gravedad baja. No es un valor disparatado, pero podría indicar que autovacuum no puede mantener el ritmo.

  • Si una tabla llega a mil millones, se debe interpretar como una alarma para adoptar medidas. En general, conviene mantener las antigüedades más cerca de autovacuum_freeze_max_age por motivos de rendimiento. Le recomendamos que investigue utilizando las recomendaciones que siguen.

  • Si una tabla llega a 1500 millones de transacciones sin vaciar, se dispara una alarma de gravedad alta. En función de la velocidad con la que la base de datos use los identificadores de transacción, esta alarma puede indicar que el sistema está agotando el tiempo para ejecutar autovacuum. En ese caso, le recomendamos una solución inmediata.

Si una tabla supera constantemente estos límites, modifique aún más sus parámetros de autovacuum. De manera predeterminada, usar VACUUM manualmente (que tiene deshabilitados los retardos basados en el costo) es un procedimiento más agresivo que usar el autovacuum predeterminado, pero es también más intrusivo para el sistema en su conjunto.

Le recomendamos lo siguiente:

Determinar qué tablas cumplen actualmente los requisitos de autovacuum

A menudo, hay una o dos tablas que necesitan vacío. Autovacuum se dirige siempre a las tablas cuyo valor relfrozenxid sea superior al número de transacciones en autovacuum_freeze_max_age. De lo contrario, si el número de tuplas obsoletas desde el último VACUUM supera el límite de vacío, la tabla se vacía.

El umbral de autovacuum se define como:

Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples

Mientras está conectado a la base de datos, ejecute la siguiente consulta para ver una lista de tablas que autovacuum considera aptas para el vacío.

WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold'), vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'), fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'), sto AS (select opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation, pg_size_pretty(pg_table_size(c.oid)) as table_size, age(relfrozenxid) as xid_age, coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age, (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1) left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid WHERE c.relkind = 'r' and nspname <> 'pg_catalog' AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup -- or 1 = 1) ORDER BY age(relfrozenxid) DESC LIMIT 50;

Determinar si autovacuum se está ejecutando actualmente y durante cuánto tiempo

Si necesita aspirar manualmente una tabla, asegúrese de determinar si el autovacuum se está ejecutando actualmente. Si es así, es posible que deba ajustar los parámetros para que funcione de manera más eficiente o desactivar el autovacuum temporalmente para que pueda ejecutar manualmente VACUUM.

Use la siguiente consulta para determinar si se está ejecutando autovacuum, cuánto tiempo lleva en ejecución y si se encuentra en espera en otra sesión.

SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;

Después de ejecutar la consulta, debería ver un resultado similar al siguiente.

datname | usename | pid | state | wait_event | xact_runtime | query --------+----------+-------+--------+------------+-------------------------+-------------------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+ | | | | | | FROM pg_stat_activity + | | | | | | WHERE query like '%VACUUM%' + | | | | | | ORDER BY xact_start; +

Existen varios problemas que pueden provocar una sesión de autovaccum de larga duración (es decir, que tarde varios días). El problema más común es que el valor del parámetro maintenance_work_mem sea demasiado bajo para el tamaño de la tabla o la velocidad de las actualizaciones.

Le recomendamos que utilice la siguiente fórmula para establecer el valor del parámetro maintenance_work_mem.

GREATEST({DBInstanceClassMemory/63963136*1024},65536)

Las sesiones de autovacuum con una duración corta también pueden indicar problemas:

  • Pueden indicar que no hay un número de autovacuum_max_workers suficientemente alto para la carga de trabajo. En ese caso, tendrá que especificar el número de procesos de trabajo.

  • Puede indicar que hay una corrupción de índice (autovacuum falla y se reinicia en la misma relación pero no avanza). En este caso, ejecute un manual vacuum freeze verbose table para ver la causa exacta.

Realización de una inmovilización de vacío manual

Puede ocurrir que desee realizar una operación de vacío manual en una tabla que ya tenga un proceso de vacío en ejecución. Esto resulta útil si se ha identificado una tabla con una antigüedad cercana a dos mil millones de transacciones (o por encima del umbral que esté monitorizando).

Los siguientes pasos son pautas, con varias variaciones en el proceso. Por ejemplo, durante las pruebas, suponga que descubre que el parámetro maintenance_work_mem se definió en un valor demasiado bajo y que tiene que adoptar medidas de forma inmediata en una tabla. Sin embargo, quizás no desea rebotar la instancia en ese momento. Con las consultas de las secciones anteriores, puede determinar qué tabla está causando el problema y comprobar que hay una sesión de autovacuum que lleva mucho tiempo en ejecución. Sabe que tiene que cambiar el ajuste del parámetro maintenance_work_mem, pero también tiene que adoptar medidas de inmediato y aplicar el vacío en la tabla afectada. El siguiente procedimiento muestra qué hacer en esa situación.

Para realizar manualmente una inmovilización de vacío

  1. Abra dos sesiones en la base de datos que contiene la tabla en la que desea ejecutar el vacío. Para la segunda sesión, use "screen" u otra utilidad que mantenga la sesión activa si se interrumpe la conexión.

  2. En la sesión uno, obtenga el ID de proceso (PID) de la sesión de autovacuum que se ejecuta en la tabla.

    Ejecute la siguiente consulta para obtener el PID de la sesión de autovacuum.

    SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
  3. En la sesión dos, calcule la cantidad de memoria que necesitará para esta operación. En este ejemplo, determinamos que podemos permitirnos usar un máximo de 2 GB de memoria para esta operación y, por tanto, definimos maintenance_work_mem en 2 GB para la sesión actual.

    SET maintenance_work_mem='2 GB'; SET
  4. En la sesión dos, ejecute el comando vacuum freeze verbose para la tabla. El ajuste de informe detallado resulta útil porque, aunque PostgreSQL no ofrece actualmente un informe de progreso para esto, se puede ver la actividad.

    \timing on Timing is on. vacuum freeze verbose pgbench_branches;
    INFO: vacuuming "public.pgbench_branches" INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions in 43 out of 43 pages DETAIL: 0 dead row versions cannot be removed yet. There were 9347 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Time: 2.765 ms
  5. En la sesión uno, si autovacuum bloqueaba la sesión de vacío, en pg_stat_activity verá que la espera es “T” para su sesión de vacío. En este caso, debe finalizar el proceso de autovacuum de la siguiente manera.

    SELECT pg_terminate_backend('the_pid');

    En este punto, comienza la sesión. Es importante tener en cuenta que autovacuum se reiniciará inmediatamente, ya que esta tabla es probablemente la que ocupa una posición más alta en su lista de trabajo.

  6. Inicie el comando vacuum freeze verbose en la sesión dos y luego finalice el proceso de autovacuum en la sesión uno.

Reindexar una tabla cuando autovacuum se está ejecutando

Si un índice se ha dañado, autovacuum seguirá procesando la tabla y generará errores. Si intenta realizar un vacío manual en esta situación, recibirá un mensaje de error como el siguiente.

postgres=> vacuum freeze pgbench_branches; ERROR: index "pgbench_branches_test_index" contains unexpected zero page at block 30521 HINT: Please REINDEX it.

Cuando el índice está dañado y autovacuum intenta ejecutarse en la tabla, se enfrenta a una sesión de autovacuum que ya se está ejecutando. Cuando ejecuta un comando REINDEX, elimina un bloqueo exclusivo en la tabla. Las operaciones de escritura están bloqueadas y también las operaciones de lectura que usan ese índice específico.

Para reindexar una tabla cuando autovacuum se está ejecutando en ella

  1. Abra dos sesiones en la base de datos que contiene la tabla que desea vaciar. Para la segunda sesión, use "screen" u otra utilidad que mantenga la sesión activa si se interrumpe la conexión.

  2. En la sesión uno, obtenga el PID de la sesión de autovacuum que se ejecuta en la tabla.

    Ejecute la siguiente consulta para obtener el PID de la sesión de autovacuum.

    SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;
  3. En la sesión dos, ejecute el comando reindex.

    \timing on Timing is on. reindex index pgbench_branches_test_index; REINDEX Time: 9.966 ms
  4. En la sesión uno, si autovacuum estaba bloqueando, verá en pg_stat_activity que la espera es “T” para su sesión de vacío. En este caso, terminará el proceso de autovacuum.

    SELECT pg_terminate_backend('the_pid');

    En este punto, comienza la sesión. Es importante tener en cuenta que autovacuum se reiniciará inmediatamente, ya que esta tabla es probablemente la que ocupa una posición más alta en su lista de trabajo.

  5. Inicie el comando en la sesión dos y termine a continuación el proceso de autovacuum de la sesión 1.

Otros parámetros que afectan a autovacuum

La siguiente consulta mostrará los valores de algunos de los parámetros que afectan directamente a autovacuum y a su comportamiento. Los parámetros de autovacuum se describen en detalle en la documentación de PostgreSQL.

SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN ( 'autovacuum_max_workers', 'autovacuum_analyze_scale_factor', 'autovacuum_naptime', 'autovacuum_analyze_threshold', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_cost_delay', 'autovacuum_vacuum_cost_limit', 'vacuum_cost_limit', 'autovacuum_freeze_max_age', 'maintenance_work_mem', 'vacuum_freeze_min_age');

Aunque todos estos parámetros afectan a autovacuum, estos son algunos de los más importantes:

Establecimiento de parámetros autovacuum de nivel de tabla

Los parámetros de almacenamiento relacionados con autovacuum se pueden definir en el nivel de tabla, algo que puede resultar mejor que alterar el comportamiento de toda la base de datos. Para las tablas grandes, podría ser necesario definir unos ajustes agresivos, y es posible que no sea deseable que autovacuum se comporte de esa forma para todas las tablas.

La siguiente consulta mostrará qué tablas tienen habilitadas actualmente las opciones de nivel de tabla.

SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT null;

Un ejemplo en el que esto puede resultar útil es el de las tablas que son mucho más grandes que el resto de las tablas. Supongamos que dispone de una tabla de 300 GB y otras 30 tablas inferior a 1 GB. En ese caso, podría definir algunos parámetros concretos para la tabla grande con el fin de evitar alterar el comportamiento de todo el sistema.

ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);

Al hacer esto, desactiva el retraso de autovacuum basado en costos para esta tabla a expensas de un mayor uso de recursos en su sistema. Normalmente, autovacuum hace una pausa por autovacuum_vacuum_cost_delay cada vez que se alcanza autovacuum_cost_limit. En la documentación de PostgreSQL, puede obtener información detallada relativa al vacío basado en el costo.

Registro de actividades de autovacuum y vacuum

La información sobre las actividades de autovacuum se envía a postgresql.log basado en el nivel especificado en el parámetro rds.force_autovacuum_logging_level. Los siguientes son los valores permitidos para este parámetro y las versiones de PostgreSQL para las que ese valor es la configuración predeterminada:

  • disabled (PostgreSQL 10, PostgreSQL 9.6)

  • debug5, debug4, debug3, debug2, debug1

  • info (PostgreSQL 12, PostgreSQL 11)

  • notice

  • warning (PostgreSQL 14, PostgreSQL 13)

  • error, registro, fatal, panic

rds.force_autovacuum_logging_level funciona con el parámetro log_autovacuum_min_duration. El valor del parámetro log_autovacuum_min_duration es el límite (en milisegundos) por encima del cual se registran las acciones de autovacuum. Una configuración de -1 no registra nada, mientras que una configuración de 0 registra todas las acciones. Al igual que con rds.force_autovacuum_logging_level, los valores predeterminados para log_autovacuum_min_duration dependen de la versión, como se indica a continuación:

  • 10000 ms: PostgreSQL 14, PostgreSQL 13, PostgreSQL 12 y PostgreSQL 11

  • (empty): no hay valor predeterminado para PostgreSQL 10 y PostgreSQL 9.6

Es recomendable que defina rds.force_autovacuum_logging_level como WARNING. También recomendamos configurar log_autovacuum_min_duration a un valor de 1000 a 5000. Una configuración de 5000 registra la actividad que tarda más de 5000 milisegundos. Cualquier configuración que no sea -1 también registra mensajes si la acción de autovaccum se omite debido a un bloqueo en conflicto o relaciones eliminadas al mismo tiempo. Para más información, visite Automatic Vacuuming (Vacío automático) en la documentación de PostgreSQL.

Para solucionar problemas, puede cambiar el parámetro rds.force_autovacuum_logging_level a uno de los niveles de depuración, desde debug1 hasta debug5 para obtener la información más detallada. Le recomendamos que utilice la configuración de depuración durante periodos cortos y solo con el objetivo de solucionar problemas. Para más información, visite When to log (Cuándo registrarse) en la documentación de PostgreSQL.

nota

PostgreSQL permite a la cuenta rds_superuser consultar sesiones de autovacuum en pg_stat_activity. Por ejemplo, podrá identificar y finalizar una sesión de autovacuum que bloquea la ejecución de un comando o que hace que se ejecute más despacio que un comando de vacío emitido manualmente.