Ajuste del vaciado automático en Azure Database for PostgreSQL: servidor flexible

Artículo
01/23/2024

SE APLICA A: Azure Database for PostgreSQL: servidor flexible

En este artículo se proporciona información general sobre la característica de autovacuum para el servidor flexible de Azure Database for PostgreSQL y las guías de solución de problemas de características que están disponibles para supervisar el sobredimensionamiento de la base de datos, los bloqueadores de autovacuo e información sobre la distancia a la que se encuentra la base de datos de una situación de emergencia o de solución de problemas.

¿Qué es el vaciado automático?

La coherencia interna de los datos en PostgreSQL se basa en el mecanismo control de simultaneidad de varias versiones (MVCC), que permite al motor de base de datos mantener varias versiones de una fila y proporciona una mayor simultaneidad con un bloqueo mínimo entre los distintos procesos.

Las bases de datos de PostgreSQL necesitan un mantenimiento adecuado. Por ejemplo, cuando se elimina una fila, no se quita físicamente. En su lugar, la fila se marca como "inactiva". De forma similar a las actualizaciones, la fila se marca como "inactiva" y se inserta una nueva versión de la fila. Estas operaciones dejan atrás los registros inactivos, denominados tuplas inactivas, incluso después de que finalicen todas las transacciones que podrían ver esas versiones. A menos que se limpien, las tuplas inactivas permanecen, consumiendo espacio en disco y sobredimensionando tablas e índices, lo que da lugar a un rendimiento lento de las consultas.

PostgreSQL usa un proceso denominado vaciado automático para limpiar automáticamente las tuplas inactivas.

Aspectos internos del vaciado automático

El vaciado automático lee páginas buscando tuplas inactivas y, si no encuentra ninguna, descarta la página. Cuando el vaciado automático encuentra tuplas inactivas, las quita. El costo se basa en:

vacuum_cost_page_hit: coste de leer una página que ya está en búferes compartidos y no necesita una lectura de disco. El valor predeterminado se establece en 1.
vacuum_cost_page_miss: coste de capturar una página que no está en búferes compartidos. El valor predeterminado se establece en 10.
vacuum_cost_page_dirty: coste de escribir en una página cuando se encuentran tuplas inactivas en ella. El valor predeterminado se establece en 20.

La cantidad de trabajo que realiza el vaciado automático depende de dos parámetros:

autovacuum_vacuum_cost_limit es la cantidad de trabajo que el vaciado automático hace en una sola vez.
autovacuum_vacuum_cost_delay número de milisegundos que el vaciado automático está en suspensión después de alcanzar el límite de costos especificado por el parámetro autovacuum_vacuum_cost_limit.

En todas las versiones admitidas actualmente de Postgres, el valor predeterminado de autovacuum_vacuum_cost_limit es 200 (en realidad, se establece en -1, lo que hace que sea igual al valor de vacuum_cost_limit normal que, de manera predeterminada, es 200).

En cuanto a autovacuum_vacuum_cost_delay, en la versión 11 de Postgres, el valor predeterminado es de 20 milisegundos, mientras que en la versión 12 y posteriores, el valor predeterminado es de 2 milisegundos.

El vaciado automático se reactiva 50 veces (50*20 ms=1000 ms) cada segundo. Cada vez que se activa, el vaciado automático lee 200 páginas.

Esto significa que, en un segundo, el vaciado automático puede hacer lo siguiente:

~80 MB/s [ (200 páginas/vacuum_cost_page_hit) * 50 * 8 KB por página] si todas las páginas con tuplas inactivas se encuentran en búferes compartidos.
~8 MB/s [ (200 páginas/vacuum_cost_page_miss) * 50 * 8 KB por página] si todas las páginas con tuplas inactivas se leen del disco.
~4 MB/s [ (200 páginas/vacuum_cost_page_dirty) * 50 * 8 KB por página] el vaciado automático puede escribir hasta 4 MB/s.

Supervisión del vaciado automático

Use las siguientes consultas para supervisar el vaciado automático:

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

Las columnas siguientes ayudan a determinar si el vaciado automático está alcanzando a la actividad de la tabla:

dead_pct: porcentaje de tuplas inactivas en comparación con las tuplas activas.
last_autovacuum: la fecha de la última vez que se vació automáticamente la tabla.
last_autoanalyze: la fecha de la última vez que se analizó automáticamente la tabla.

Cuándo desencadena PostgreSQL el vaciado automático

Una acción de vaciado automático (ANALYZE o VACUUM) se desencadena cuando el número de tuplas inactivas supera un número determinado que depende de dos factores: el recuento total de filas de una tabla, más un umbral fijo. ANALYZE, de forma predeterminada, se desencadena cuando cambia el 10 % de la tabla más 50 filas, mientras que VACUUM se desencadena cuando cambia el 20 % de la tabla más 50 filas. Como el umbral VACUUM es el doble de alto que el umbral ANALYZE, ANALYZE se desencadena antes que VACUUM.

Las ecuaciones exactas de cada acción son:

Análisis automático = factor_escala_análisis_vaciado automático * tuplas + umbral_análisis_vaciado automático
Vaciado automático = factor_escala_vaciado_vaciado automático * tuplas + umbral_vaciado_vaciado automático

Por ejemplo, el análisis se desencadena después de que cambien 60 filas en una tabla que contenga 100 filas, y el vaciado automático se desencadena cuando cambian 70 filas en la tabla, mediante las ecuaciones siguientes:

Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

Use la siguiente consulta para enumerar las tablas de una base de datos e identificar las tablas que cumplen los requisitos para el proceso de vaciado automático:

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_analyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

Nota

La consulta no tiene en cuenta que el vaciado automático se puede configurar por tabla mediante el comando de DDL "alter table".

Problemas comunes del vaciado automático

Revise la siguiente lista de posibles problemas comunes con el proceso de vaciado automático.

No seguimiento del ritmo de un servidor con mucha actividad

El proceso de vaciado automático calcula el costo de cada operación de E/S, acumula un total para cada operación que realiza y se pausa una vez alcanzado el límite superior del costo. autovacuum_vacuum_cost_delay y autovacuum_vacuum_cost_limit son los dos parámetros de servidor que se usan en el proceso.

De forma predeterminada, autovacuum_vacuum_cost_limit se establece en –1, lo que significa que el límite de costos del vaciado automático es el mismo valor que el parámetro vacuum_cost_limit, que tiene como valor predeterminado 200. vacuum_cost_limit es el costo de un vaciado manual.

Si autovacuum_vacuum_cost_limit se establece en -1, el vaciado automático usa el parámetro vacuum_cost_limit, pero si autovacuum_vacuum_cost_limit se establece en un número mayor que -1, se considera el parámetro autovacuum_vacuum_cost_limit.

En caso de que el vaciado automático no mantenga el ritmo, se podrían cambiar los parámetros siguientes:

Parámetro	Descripción
`autovacuum_vacuum_scale_factor`	Valor predeterminado: `0.2`, rango: `0.05 - 0.1`. El factor de escala es específico de la carga de trabajo y debe establecerse en función de la cantidad de datos de las tablas. Antes de cambiar el valor, investigue la carga de trabajo y los volúmenes de tabla individuales.
`autovacuum_vacuum_cost_limit`	Predeterminado: `200`. El límite de los costos podría aumentar. El uso de CPU y E/S en la base de datos debe supervisarse antes y después de realizar cambios.
`autovacuum_vacuum_cost_delay`	Valor predeterminado de la versión 11 de Postgres: `20 ms`. El parámetro se podría reducir a `2-10 ms`. Versiones 12 y posteriores de Postgres : valor predeterminado: `2 ms`.

Nota

El valor autovacuum_vacuum_cost_limit se distribuye proporcionalmente entre los trabajos de vaciado automático en ejecución, de modo que, si hay más de uno, la suma de los límites de cada trabajo no supere el valor del parámetro autovacuum_vacuum_cost_limit

Ejecución permanente del vaciado automático

La ejecución continua del vaciado automático podría afectar al uso de CPU y E/S en el servidor. Los siguientes pueden ser posibles motivos:

`maintenance_work_mem`

El demonio del vaciado automático usa autovacuum_work_mem, que está establecido de forma predeterminada en -1, lo que signiffica que autovacuum_work_mem tendría el mismo valor que el parámetro maintenance_work_mem. En este documento se da por supuesto que autovacuum_work_mem está establecido en -1 y maintenance_work_mem lo usa el demonio del vaciado automático.

Si maintenance_work_mem es bajo, puede aumentarse hasta 2 GB en el servidor flexible de Azure Database for PostgreSQL. Una regla general es asignar 50 MB a maintenance_work_mem por cada 1 GB de RAM.

Gran número de bases de datos

El vaciado automático intenta iniciar un trabajo en cada base de datos cada autovacuum_naptime segundos.

Por ejemplo, si un servidor tiene 60 bases de datos y autovacuum_naptime se establece en 60 segundos, el trabajo de vaciado automático se inicia cada segundo [tiempo de inactividad_vaciado automático/Número de bases de datos].

Es recomendable aumentar autovacuum_naptime si hay más bases de datos en un clúster. Al mismo tiempo, el proceso de vaciado automático se puede hacer más agresivo aumentando autovacuum_cost_limit y disminuyendo los parámetros autovacuum_cost_delay y aumentando autovacuum_max_workers del valor predeterminado de 3 a 4 o 5.

Errores de memoria insuficiente

Los valores maintenance_work_mem demasiado agresivos podrían provocar periódicamente errores de memoria insuficiente en el sistema. Es importante comprender la RAM disponible en el servidor antes de realizar cualquier cambio en el parámetro maintenance_work_mem.

Exceso de interrupciones del vaciado automático

Si el vaciado automático consume muchos recursos, se puede hacer lo siguiente:

Parámetros de vaciado automático

Evalúe los parámetros autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, autovacuum_max_workers. La configuración incorrecta de los parámetros del vaciado automático podría provocar escenarios en los que el vaciado automático se vuelve demasiado perjudicial.

Si el vaciado automático es demasiado perturbador, tenga en cuenta lo siguiente:

Aumente autovacuum_vacuum_cost_delay y reduzca autovacuum_vacuum_cost_limit si se establece por encima del valor predeterminado de 200.
Reduzca el número de autovacuum_max_workers si se establece por encima que el valor predeterminado de 3.

Exceso de trabajos de vaciado automático

Aumentar el número de trabajos de vaciado automático no aumentará necesariamente la velocidad de vaciado. No se recomienda tener un gran número de trabajos de vaciado automático.

Aumentar el número de trabajos de vaciado automático dará como resultado un mayor consumo de memoria y, en función del valor de maintenance_work_mem, podría provocar una degradación del rendimiento.

Cada proceso de trabajador de vaciado automático solo recibe (1/trabajos_máximos_de_vaciado automático ) del total autovacuum_cost_limit, por lo que tener un número elevado de trabajos hace que cada uno vaya más lento.

Si aumenta el número de trabajos, también debe aumentarse autovacuum_vacuum_cost_limit o reducirse autovacuum_vacuum_cost_delay para que el proceso de vaciado sea más rápido.

Sin embargo, si hemos cambiado los parámetros de nivel de tabla autovacuum_vacuum_cost_delay o autovacuum_vacuum_cost_limit, los trabajos que se ejecutan en esas tablas están exentos de tenerse en cuenta en el algoritmo de equilibrio [límite_costo_vaciado automático/trabajos_máximos_vaciado automático].

Protección del ajuste del identificador de transacción de vaciado automático (TXID)

Cuando una base de datos se ejecuta en la protección del ajuste del identificador de transacción, se puede observar un mensaje de error como el siguiente:

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

Nota

Este mensaje de error es un descuido de hace tiempo. Normalmente, no es necesario cambiar al modo de usuario único. En su lugar, puede ejecutar los comandos VACUUM necesarios y realizar el ajuste para que VACUUM se ejecute rápidamente. Aunque no puede ejecutar ningún lenguaje de manipulación de datos (DML), todavía puede ejecutar VACUUM.

El problema de encapsulado se produce cuando la base de datos no está vacía o hay demasiadas tuplas inactivas que no se han podido quitar mediante el vaciado automático. Los motivos de esto pueden ser:

Carga de trabajo pesada

La carga de trabajo podría provocar demasiadas tuplas inactivas en un breve período, lo que dificulta la puesta al día del vaciado automático. Las tuplas muertas en el sistema se acumulan a lo largo de un periodo, lo que provoca una degradación del rendimiento de la consulta y lleva a una situación de ajuste. Una de las razones para que se produzca esta situación puede ser que los parámetros de vaciado automático no estén configurados adecuadamente y no se mantenga el ritmo de un servidor con mucha actividad.

Transacciones de ejecución prolongada

Las transacciones de larga duración en el sistema no permitirán quitar tuplas inactivas mientras se ejecuta el vaciado automático. Son un bloqueador para el proceso de vaciado. Al quitar las transacciones de larga duración, se liberan tuplas inactivas para su eliminación cuando se ejecuta el vaciado automático.

Las transacciones de ejecución prolongada se pueden detectar mediante la siguiente consulta:

    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;

Instrucciones preparadas

Si hay instrucciones preparadas sin confirmar, impedirían que se quiten las tuplas inactivas.
La consulta siguiente ayuda a buscar instrucciones preparadas sin confirmar:

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

Use COMMIT PREPARED o ROLLBACK PREPARED para confirmar o revertir estas instrucciones.

Ranuras de replicación sin usar

Las ranuras de replicación sin usar impiden que el vaciado automático reclame tuplas inactivas. La consulta siguiente ayuda a identificar las ranuras de replicación sin usar:

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

Use pg_drop_replication_slot() para eliminar ranuras de replicación sin usar.

Cuando la base de datos se ejecute con la protección de ajuste del identificador de la transacción, compruebe si hay algún bloqueador, como se ha mencionado anteriormente, y elimínelo manualmente para que el vaciado automático continúe y se complete. También puede aumentar la velocidad del vaciado automático si establece autovacuum_cost_delay en 0 y aumentan autovacuum_cost_limit en un valor mayor de 200. Pero los cambios en estos parámetros no se aplicarán a los trabajos de vaciado automático existentes. Reinicie la base de datos o elimine los trabajos existentes manualmente para aplicar los cambios de parámetro.

Requisitos específicos de la tabla

Es posible establecer parámetros de vaciado automático para tablas individuales. Es especialmente importante para tablas pequeñas y grandes. Por ejemplo, para una tabla pequeña que contiene solo 100 filas, el vaciado automático desencadena la operación VACUUM cuando cambian 70 filas (como se calculó anteriormente). Si esta tabla se actualiza con frecuencia, es posible que vea cientos de operaciones de vaciado automático al día. Esto impide que el vaciado automático mantenga otras tablas en las que el porcentaje de cambios no sea tan grande. Alternativamente, una tabla que contiene mil millones de filas debe cambiar 200 millones de filas para desencadenar operaciones de vaciado automático. El establecimiento de los parámetros de vaciado automático correctamente impide estos escenarios.

Para establecer la configuración de vaciado automático por tabla, cambie los parámetros del servidor como los ejemplos siguientes:

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);

Cargas de trabajo de solo inserción

En las versiones de PostgreSQL anteriores a la 13, el vaciado automático no se ejecutará en tablas con una carga de trabajo de solo inserción, ya que si no hay actualizaciones ni eliminaciones, no hay tuplas inactivas y no hay espacio libre que sea necesario reclamar. Pero se ejecutará el análisis automático para cargas de trabajo de solo inserción, ya que hay datos nuevos. Las desventajas de esto son:

El mapa de visibilidad de las tablas no se actualiza y, por tanto, el rendimiento de las consultas, especialmente cuando hay exámenes de solo índice, comienza a sufrir con el tiempo.
La base de datos puede encontrarse con la protección de ajuste del identificador de transacción.
No se establecerán bits de sugerencia.

Soluciones

Versiones de Postgres anteriores a la 13

Con la extensión pg_cron, se puede configurar un trabajo cron para programar un análisis de vaciado periódico en la tabla. La frecuencia del trabajo cron depende de la carga de trabajo.

Para obtener instrucciones paso a paso con pg_cron, consulte Extensiones.

Postgres 13 y versiones posteriores

El vaciado automático se ejecutará en tablas con una carga de trabajo de solo inserción. Dos nuevos parámetros de servidor autovacuum_vacuum_insert_threshold y autovacuum_vacuum_insert_scale_factor ayudan a controlar cuándo se puede activar el vaciado automático en las tablas de solo inserción.

Guías de solución de problemas

Con las guías de solución de problemas de características que están disponibles en el portal del servidor flexible de Azure Database for PostgreSQL, es posible supervisar el sobredimensionamiento en la base de datos o en el nivel de esquema individual, junto con la identificación de posibles bloqueadores para el proceso de autovacuo. Hay dos guías de solución de problemas disponibles; la primera es la supervisión del vaciado automático que se puede usar para supervisar el sobredimensionamiento en la base de datos o en el nivel de esquema individual. La segunda guía de solución de problemas es la de bloqueadores de vaciado automático y encapsulado, que ayuda a identificar posibles bloqueadores de vaciado automático junto con información sobre la distancia de las bases de datos en el servidor a una situación de emergencia o encapsulado. Las guías de solución de problemas también comparten recomendaciones para mitigar posibles problemas. Para ver cómo configurar las guías de solución de problemas para usarlas, siga las guías de solución de problemas de configuración.

Ajuste del vaciado automático en Azure Database for PostgreSQL: servidor flexible

¿Qué es el vaciado automático?

Aspectos internos del vaciado automático

Supervisión del vaciado automático

Cuándo desencadena PostgreSQL el vaciado automático

Problemas comunes del vaciado automático

No seguimiento del ritmo de un servidor con mucha actividad

Ejecución permanente del vaciado automático

maintenance_work_mem

Gran número de bases de datos

Errores de memoria insuficiente

Exceso de interrupciones del vaciado automático

Parámetros de vaciado automático

Exceso de trabajos de vaciado automático

Protección del ajuste del identificador de transacción de vaciado automático (TXID)

Carga de trabajo pesada

Transacciones de ejecución prolongada

Instrucciones preparadas

Ranuras de replicación sin usar

Requisitos específicos de la tabla

Cargas de trabajo de solo inserción

Soluciones

Versiones de Postgres anteriores a la 13

Postgres 13 y versiones posteriores

Guías de solución de problemas

Contenido relacionado

Recursos adicionales

`maintenance_work_mem`