Resolver problemas de desempenho de limpeza no RDS para PostgreSQL

Esta seção discute os fatores que geralmente contribuem para um desempenho mais lento de limpeza e como resolvê-los.

Limpeza em índices grandes

O VACUUM opera em fases sequenciais: inicialização, verificação do heap, índice e limpeza do heap, limpeza de índices, truncamento de heap e limpeza final. Durante a verificação do heap, o processo remove, desfragmenta e congela páginas. Depois que o heap é verificado, o VACUUM limpa os índices, devolve páginas vazias ao sistema operacional e realiza tarefas finais de limpeza, como limpar o mapa de espaço livre e atualizar as estatísticas.

A limpeza de índice pode exigir várias passagens quando maintenance_work_mem (ou autovacuum_work_mem) é insuficiente para processar o índice. No PostgreSQL 16 e anterior, um limite de memória de 1 GB para armazenar IDs de tuplas mortas geralmente forçava várias passagens em índices grandes. O PostgreSQL 17 introduz o TidStore, que aloca memória dinamicamente em vez de usar uma matriz de alocação única. Isso remove a restrição de 1 GB, usa a memória com maior eficiência e reduz a necessidade de várias verificações para cada índice.

Índices grandes ainda podem exigir várias passagens no PostgreSQL 17 se a memória disponível não for suficiente para o processamento completo do índice de uma só vez. Normalmente, os índices maiores contêm mais tuplas mortas que exigem várias passagens.

Detectar operações de limpeza lentas

A função postgres_get_av_diag() pode detectar quando a execução de operações de limpeza está lenta devido a memória insuficiente. Para ter mais informações sobre essa função, consulte Instalar ferramentas de monitoramento e diagnóstico de autovacuum no RDS para PostgreSQL.

A função postgres_get_av_diag() emite os avisos a seguir quando a memória disponível não é suficiente para concluir a limpeza do índice em uma única passagem.

rds_tools 1.8

NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.

NOTICE: The current setting of autovacuum_work_mem is "XXX" and might not be sufficient. Consider increasing the setting, and if necessary, scaling up the Amazon RDS instance class for more memory. 
        Additionally, review the possibility of manual vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;).

rds_tools 1.9

NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.

NOTICE: The current setting of autovacuum_work_mem is XX might not be sufficient. Consider increasing the setting to XXX, and if necessary, scaling up the RDS instance class for more 
        memory. The suggested value is an estimate based on the current number of dead tuples for the table being vacuumed, which might not fully reflect the latest state. Additionally, review the possibility of manual 
        vacuum with exclusion of indexes using (VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) table_name;). For more information, see 
        Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide
        .

Quando a função postgres_get_av_diag() identificar uma operação de limpeza lenta que requer várias verificações do índice devido a autovacuum_work_mem insuficiente, ela gerará a seguinte mensagem:

NOTICE: Your vacuum is performing multiple index scans due to insufficient autovacuum_work_mem:XXX for index vacuuming. 
        For more information, see Working with PostgreSQL autovacuum in the Amazon Amazon RDS User Guide.

Orientação

Você pode aplicar as soluções alternativas a seguir usando o VACUUM FREEZE manual para acelerar o congelamento da tabela.

Aumentar a memória para limpeza

Conforme sugerido pela função postgres_get_av_diag(), é recomendável aumentar o parâmetro autovacuum_work_mem para lidar com possíveis restrições de memória no nível da instância. Embora autovacuum_work_mem seja um parâmetro dinâmico, é importante observar que, para que a nova configuração de memória entre em vigor, o daemon autovacuum precisa reiniciar seus processos. Para fazer isso:

Essa abordagem garante que a alocação de memória ajustada seja aplicada às novas operações do autovacuum.

Para obter resultados mais imediatos, considere realizar manualmente uma operação VACUUM FREEZE com uma configuração maintenance_work_mem maior em sua sessão:

SET maintenance_work_mem TO '1GB';
VACUUM FREEZE VERBOSE table_name;

Se você estiver usando o Amazon RDS e notar que precisa de memória adicional para oferecer suporte a valores mais altos para maintenance_work_mem ou autovacuum_work_mem, considere fazer upgrade para uma classe de instância com mais memória. Isso pode fornecer os recursos necessários para aprimorar as operações de limpeza manuais e automáticas, levando a um melhor desempenho geral da limpeza e do banco de dados.

Desativar INDEX_CLEANUP

O VACUUM manual no PostgreSQL versão 12 e posteriores permite pular a fase de limpeza do índice, enquanto o autovacuum de emergência no PostgreSQL versão 14 e posteriores faz isso automaticamente com base no parâmetro vacuum_failsafe_age.

Para obter orientação adicional sobre como lidar com índices grandes, consulte a documentação em Gerenciar o autovacuum com grandes índices .

Limpeza paralelo de índices

Desde o PostgreSQL 13, os índices podem ser limpos em paralelo por padrão usando o VACUUM manual, com um processo de operação de limpeza atribuído a cada índice. No entanto, para que o PostgreSQL determine se uma operação de limpeza se qualifica para execução paralela, critérios específicos devem ser atendidos:

É possível ajustar a configuração max_parallel_maintenance_workers com base no número de vCPUs disponíveis na sua instância do Amazon RDS e no número de índices na tabela para otimizar o tempo de resposta da limpeza.

Para obter mais informações, consulte Parallel vacuuming in Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL.

Muitas tabelas ou bancos de dados para limpar

Conforme mencionado na documentação The Autovacuum Daemon do PostgreSQL, o daemon autovacuum é executado por meio de vários processos. Isso inclui um inicializador persistente de autovacuum, responsável por iniciar os processos de trabalho de autovacuum para cada banco de dados do sistema. O inicializador programa esses trabalhadores para iniciarem aproximadamente a cada autovacuum_naptime segundos por banco de dados.

Com 'N' bancos de dados, um novo trabalhador começa aproximadamente a cada [autovacuum_naptime/N segundos]. No entanto, o número total de trabalhadores simultâneos é limitado pela configuração autovacuum_max_workers. Se o número de bancos de dados ou tabelas que precisam ser limpos exceder esse limite, o próximo banco de dados ou tabela será processado assim que um trabalhador estiver disponível.

Quando muitas tabelas ou bancos de dados grandes precisam ser limpos simultaneamente, todos os trabalhadores de autovacuum disponíveis podem ficar ocupados por um longo período, atrasando a manutenção em outras tabelas e bancos de dados. Em ambientes com altas taxas de transação, esse gargalo pode aumentar rapidamente e potencialmente levar a problemas de conclusão de limpeza em sua instância do Amazon RDS.

Quando o postgres_get_av_diag() detecta um grande número de tabelas ou bancos de dados, ele fornece a seguinte recomendação:

NOTICE: Your database is currently running aggressive vacuum to prevent wraparound and it might be slow.

NOTICE: The current setting of autovacuum_max_workers:3 might not be sufficient. Consider increasing the setting and, if necessary, consider scaling up the Amazon RDS instance class for more workers.

Orientação

Aumentar autovacuum_max_workers

Para agilizar a limpeza, recomendamos ajustar o parâmetro autovacuum_max_workers para permitir mais trabalhadores simultâneos de autovacuum. Se os gargalos de desempenho persistirem, considere aumentar a escala verticalmente de sua instância do Amazon RDS para uma classe com mais vCPUs, o que pode melhorar ainda mais as capacidades de processamento paralelo.

A limpeza agressiva (para evitar conclusão) está em execução

A idade do banco de dados (MaximumUsedTransactionIDs) no PostgreSQL só diminui quando uma limpeza agressiva (para evitar conclusão) é concluída com sucesso. Até que essa limpeza termine, a idade continuará aumentando dependendo da taxa de transação.

A função postgres_get_av_diag() gera o seguinte NOTICE quando detecta uma limpeza agressiva. No entanto, ela só aciona essa saída depois que a limpeza estiver ativa por pelo menos dois minutos.

NOTICE: Your database is currently running aggressive vacuum to prevent wraparound, monitor autovacuum performance.

Consulte When an aggressive vacuum is already running para obter mais informações sobre a limpeza agressiva.

Use a seguinte consulta para verificar se uma limpeza agressiva está em andamento:

SELECT
    a.xact_start AS start_time,
    v.datname "database",
    a.query,
    a.wait_event,
    v.pid,
    v.phase,
    v.relid::regclass,
    pg_size_pretty(pg_relation_size(v.relid)) AS heap_size,
    (
        SELECT
            string_agg(pg_size_pretty(pg_relation_size(i.indexrelid)) || ':' || i.indexrelid::regclass || chr(10), ', ')
        FROM
            pg_index i
        WHERE
            i.indrelid = v.relid
    ) AS index_sizes,
    trunc(v.heap_blks_scanned * 100 / NULLIF(v.heap_blks_total, 0)) AS step1_scan_pct,
    v.index_vacuum_count || '/' || (
        SELECT
            count(*)
        FROM
            pg_index i
        WHERE
            i.indrelid = v.relid
    ) AS step2_vacuum_indexes,
    trunc(v.heap_blks_vacuumed * 100 / NULLIF(v.heap_blks_total, 0)) AS step3_vacuum_pct,
    age(CURRENT_TIMESTAMP, a.xact_start) AS total_time_spent_sofar
FROM
    pg_stat_activity a
    INNER JOIN pg_stat_progress_vacuum v ON v.pid = a.pid;

É possível determinar se é uma limpeza agressiva (para evitar conclusão) verificando a coluna de consulta na saída. A frase "para evitar conclusão" indica que se trata de uma limpeza agressiva.

query                  | autovacuum: VACUUM public.t3 (to prevent wraparound)

Por exemplo, suponha que você tenha um bloqueador com idade de transação de 1 bilhão e uma tabela exigindo uma limpeza agressiva para evitar conclusão na mesma idade da transação. Além disso, há outro bloqueador com idade de transação de 750 milhões. Depois de eliminar o bloqueador com idade de transação de 1 bilhão, a idade de transação não cairá imediatamente para 750 milhões. Ela permanecerá alta até que a tabela que precise da limpeza agressiva ou qualquer transação com idade superior a 750 milhões seja concluída. Durante esse período, a idade das transações do cluster do PostgreSQL continuará aumentando. Quando o processo de limpeza for concluído, a idade da transação cairá para 750 milhões, mas começará a aumentar novamente até que a limpeza adicional seja concluída. Esse ciclo continuará enquanto essas condições persistirem, até que a idade da transação caia para o nível configurado na instância do Amazon RDS, especificado por autovacuum_freeze_max_age.