Ultrapasse a proteção de repetição do ID da transação (TXID)

Esta página descreve o que pode fazer quando a sua base de dados encontra a proteção de repetição de ID de transação no PostgreSQL. Manifesta-se como uma mensagem ERROR, da seguinte forma:

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.

Em alternativa, pode ser apresentada uma mensagem WARNING da seguinte forma:

database dbname must be vacuumed within 10985967 transactions.

To avoid a database shutdown, execute a database-wide VACUUM in that database.

Vista geral dos passos

  • Descubra que base de dados e que tabelas estão a causar a repetição.
  • Verifique se existe algo a impedir o (AUTO)VACUUM (por exemplo, um ID de transação bloqueado).
  • Medir a velocidade do AUTOVACUUM. Se for lento, pode tentar acelerá-lo.
  • Se necessário, execute mais alguns comandos VACUUM manualmente.
  • Investigue outras formas de acelerar o aspirador. Por vezes, a forma mais rápida é eliminar a tabela ou alguns índices.

Muitas das recomendações para valores de flags não são propositadamente exatas porque dependem de muitos parâmetros da base de dados. Leia os documentos com links no final desta página para uma análise mais aprofundada sobre este tópico.

Encontre a base de dados e a tabela que estão a causar a repetição

Encontrar a base de dados

Para saber que base de dados ou bases de dados contêm as tabelas que estão a causar a repetição, execute a seguinte consulta:

SELECT datname, 
       age(datfrozenxid), 
       2^31-1000000-age(datfrozenxid) as remaining
  FROM pg_database
 ORDER BY 3

A base de dados com o valor remaining próximo de 0 é a que está a causar o problema.

Encontrar a tabela

Associe-se a essa base de dados e execute a seguinte 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 devolve a tabela ou as tabelas que estão a causar o problema.

Para tabelas TEMPORARY

Se o schema_name começar por pg_temp_, a única forma de resolver o problema é eliminar a tabela, uma vez que o PostgreSQL não permite que execute o comando VACUUM em tabelas temporárias criadas noutras sessões. Por vezes, se essa sessão estiver aberta e acessível, pode limpar a tabela aí, mas isto não acontece frequentemente. Use as seguintes declarações SQL para eliminar a tabela temporária:

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
DROP TABLE pg_temp_<N>.<tablename>;

Se este foi o único bloqueio, o autovacuum deteta esta alteração em cerca de um minuto e move o datfrozenxid para a frente em pg_database. Isto resolve o estado de só de leitura da proteção contra a repetição.

Tabelas normais

Para tabelas normais (ou seja, não temporárias), continue com os passos seguintes para ver se algo está a bloquear a limpeza, se o VACUUM está a ser executado com rapidez suficiente e se a tabela mais importante está a ser limpa.

Verifique se existe um ID da transação bloqueado

Um dos motivos possíveis pelos quais o sistema pode ficar sem IDs de transação é que o PostgreSQL não consegue congelar (ou seja, marcar como visíveis para todas as transações) os IDs de transação criados após o início da transação em execução mais antiga. Isto deve-se às regras de controlo de concorrência de várias versões (MVCC). Em casos extremos, estas transações podem ficar tão antigas que impossibilitam o VACUUM de limpar as transações antigas para todo o limite de 2 mil milhões de IDs de transações e fazer com que todo o sistema deixe de aceitar novos DMLs. Normalmente, também vê avisos no ficheiro de registo a indicar WARNING: oldest xmin is far in the past.

Só deve avançar para a otimização depois de o ID da transação bloqueado ter sido corrigido.

Seguem-se quatro potenciais motivos pelos quais pode existir um ID da transação bloqueado, com informações sobre como mitigar cada um deles:

  • Transações de longa duração: identifique-as e cancele ou termine o backend para desbloquear o aspirador.
  • Transações de preparação órfãs: reverta estas transações.
  • Slots de replicação abandonados: elimine os slots abandonados.
  • Transação de execução prolongada na réplica, com hot_standby_feedback = on: identifique-as e cancele ou termine o back-end para desbloquear a limpeza.

Para estes cenários, a consulta seguinte devolve a idade da transação mais antiga e o número de transações restantes até à repetição:

 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 pode devolver qualquer um dos valores *_left comunicados perto ou abaixo de 1 milhão de distância da mudança de linha. Este valor é o limite de proteção de repetição quando o PostgreSQL deixa de aceitar novos comandos de escrita. Neste caso, consulte Remova bloqueadores de VACUUM ou Ajuste o VACUUM.

Por exemplo, a consulta anterior pode devolver:

┌─[ 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     │ ¤          │
└──────────────────────────────┴────────────┘

em que oldest_running_xact_left e oldest_prepared_xact_left estão dentro do limite de proteção de 1 milhão de euros. Neste caso, tem de remover primeiro os bloqueadores para que o comando VACUUM possa avançar.

Remova bloqueadores de VACUUM

Transações de longa duração

Na consulta anterior, se oldest_running_xact for igual a oldest_prepared_xact, aceda à secção Transação de preparação órfã, porque o valor latest running também inclui as transações preparadas.

Primeiro, pode ter de executar o seguinte comando como postgresutilizador: 

GRANT pg_signal_backend TO postgres;

Se a transação ofensiva pertencer a um dos utilizadores do sistema (começando por cloudsql...), não a pode cancelar diretamente. Tem de reiniciar a base de dados para a cancelar.

Para identificar uma consulta de execução prolongada e cancelá-la ou terminá-la para desbloquear o vácuo, selecione primeiro algumas das consultas mais antigas. A linha LIMIT 10 ajuda a ajustar o resultado ao ecrã. Pode ter de repetir este processo depois de resolver as consultas em execução mais antigas.

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;

Se age_in_xids for devolvido como NULL, significa que não foi atribuído um ID da transação permanente à transação e que pode ignorá-la em segurança.

Cancele as consultas em que o xids_left_to_wraparound se aproxima de 1 milhão.

Se state for active, a consulta pode ser cancelada através de SELECT pg_cancel_backend(pid);. Caso contrário, tem de terminar toda a ligação através de SELECT pg_terminate_backend(pid);, onde pid é o pid da consulta anterior

Transações de preparação órfãs

Liste todas as transações 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                       │
└─────────────┴───────────────────────────────┘

Reverta as transações preparadas órfãs mais antigas usando o gid da última consulta (neste caso, trx_id_pin) como o ID da transação:

ROLLBACK PREPARED trx_id_pin;

Em alternativa, confirme-o: 

COMMIT PREPARED trx_id_pin;

Consulte a documentação SQL ROLLBACK PREPARED para uma explicação completa.

Espaços de replicação abandonados

Caso o espaço de replicação seja abandonado porque a réplica existente foi interrompida, pausada ou tem outro problema, pode eliminar a réplica da consola gcloud ou Trusted Cloud .

Primeiro, verifique se a réplica não está desativada, conforme descrito no artigo Gerir réplicas de leitura. Se a réplica estiver desativada, ative-a novamente. Se o atraso continuar elevado, elimine a réplica.

Os slots de replicação são visíveis na vista de sistema pg_replication_slots.

A seguinte consulta obtém as informações relevantes:

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                                              │
└─────────────────────┴─────────────────────────────────────────────────┘

Neste exemplo, o valor pg_replication_slots é saudável (age == 59). Se a idade fosse próxima de 2 mil milhões, convém eliminar o espaço. Não existe uma forma fácil de saber qual é qual no caso de a consulta devolver vários registos. Por isso, verifique-as todas caso exista uma transação de longa duração em qualquer réplica.

Transações de longa duração em réplicas

Verifique as réplicas da transação em execução mais antiga com hot_standby_feedback definido como on e desative-o na réplica.

A coluna backend_xmin na visualização pg_stat_replication tem o valor TXID mais antigo necessário na réplica.

Para a mover para a frente, pare a consulta que a retém na réplica. Para descobrir que consulta está a atrasá-la, use a consulta em Transações de execução prolongada, mas, desta vez, execute-a na réplica.

Outra opção é reiniciar a réplica.

Configure o VACUUM

Defina as duas flags seguintes:

  • autovacuum_vacuum_cost_delay = 0
  • autovacuum_work_mem = 1048576

O primeiro desativa qualquer limitação do disco para a limpeza pelo PostgreSQL, para que o VACUUM possa ser executado à velocidade máxima. Por predefinição, o autovacuum é limitado para não usar todo o IO do disco nos servidores mais lentos.

A segunda flag, autovacuum_work_mem, diminui o número de passes de limpeza do índice. Se possível, deve ser suficientemente grande para armazenar todos os IDs de linhas eliminadas numa tabela que o VACUUM vai limpar. Quando definir este valor, tenha em atenção que este é o valor máximo de memória local que cada VACUUM em execução pode atribuir. Certifique-se de que não está a permitir mais do que o disponível, com algum espaço reservado. Se deixar a base de dados a ser executada no modo de leitura, também deve considerar a memória local usada para consultas de leitura.

Na maioria dos sistemas, use o valor máximo (1 GB ou 1048576 KB, conforme mostrado no exemplo). Este valor é adequado para cerca de 178 milhões de tuplos mortos. Qualquer outro valor continua a causar várias passagens de verificação do índice.

Estas e outras flags são explicadas mais detalhadamente no artigo Otimizar, monitorizar e resolver problemas de operações VACUUM no PostgreSQL.

Depois de definir estas flags, reinicie a base de dados para que o autovacuum seja iniciado com os novos valores.

Pode usar a vista pg_stat_progress_vacuum para monitorizar o progresso dos comandos VACUUM iniciados automaticamente. Esta vista mostra os VACUUMs em execução em todas as bases de dados e para tabelas (relações) de outras bases de dados que não pode procurar o nome da tabela através da coluna de vista relid.

Para identificar as bases de dados e as tabelas que precisam de limpeza de seguida, use consultas de Otimizar, monitorizar e resolver problemas de operações VACUUM no PostgreSQL. Se a VM do servidor for suficientemente potente e tiver largura de banda para mais processos VACUUM paralelos do que os iniciados pelo autovacuum, pode iniciar alguns vacuums manuais.

Verifique a velocidade do ASPIRADOR

Esta secção descreve como verificar a velocidade do VACUUM e como a acelerar, se necessário.

Verifique as limpezas automáticas em execução

Todos os backends que executam o VACUUM são visíveis na vista do sistema pg_stat_progress_vacuum.

Se a fase atual for scanning heap, pode monitorizar o progresso observando as alterações na coluna heap_blks_scanned. Infelizmente, não existe uma forma fácil de determinar a velocidade de análise noutras fases.

Estime a velocidade de análise do VACUUM

Para estimar a velocidade de análise, tem de armazenar primeiro os valores base e, em seguida, calcular a alteração ao longo do tempo para estimar o tempo de conclusão. Primeiro, tem de guardar um instantâneo de heap_blks_scanned juntamente com uma data/hora através da seguinte consulta de instantâneo:

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';

Uma vez que não podemos guardar nada em tabelas que já estejam em wraparound, use set_config(flag, value) para definir duas flags definidas pelo utilizador, save.ts e save.heap_blks_scanned, para os valores atuais de pg_stat_progress_vacuum.

Na consulta seguinte, usamos estes dois como base de comparação para determinar a velocidade e estimar o tempo de conclusão.

NOTA: WHERE datname = DB_NAME restringe a investigação a uma base de dados de cada vez. Este número é suficiente se existir apenas um autovacuum em execução nesta base de dados, com mais de uma linha por base de dados. As condições de filtro adicionais ('AND relid= …'') têm de ser adicionadas a WHERE para indicar uma única linha de autovacuum. Isto também se aplica à consulta seguinte.

Depois de guardar os valores base, pode executar a seguinte 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 os valores atuais com os valores base guardados e calcula pages_per_second e remaining_time, o que nos permite decidir se o VACUUM está a ser executado com rapidez suficiente ou se o queremos acelerar. O valor de remaining_time destina-se apenas à fase scanning heap. Outras fases também demoram tempo, por vezes, ainda mais. Pode ler mais sobre a aspiração e ver publicações no blogue na Internet que abordam alguns dos aspetos complexos da aspiração.

Aumentar velocidade do VACUUM

A forma mais fácil e rápida de tornar a análise VACUUM mais rápida é definir o parâmetro autovacuum_vacuum_cost_delay=0. Pode fazê-lo a partir da Trusted Cloud consola.

Infelizmente, o VACUUM já em execução não seleciona este valor e pode ter de reiniciar a base de dados.

Após um reinício, pode ver um resultado semelhante ao seguinte:

┌─[ 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 │
└───────────────────┴──────────────────┘

Neste exemplo, a velocidade aumentou de <300 páginas/seg para ~6500 páginas/seg e o tempo restante esperado para a fase de análise da memória temporária diminuiu de 9 horas para 23 minutos.

A velocidade de análise das outras fases não é tão fácil de medir, mas deve mostrar um aumento de velocidade semelhante.

Considere também tornar o autovacuum_work_mem o maior possível para evitar várias passagens por índices. Uma passagem de índice ocorre sempre que a memória é preenchida com ponteiros de tuplos mortos.

Se a base de dados não estiver a ser usada de outra forma, defina autovacuum_work_mem para ter ~80% de memória livre depois de permitir a quantidade necessária para shared_buffers. Este é o limite superior para cada um dos processos VACUUM iniciados automaticamente. Se quiser continuar a executar cargas de trabalho de leitura, use menos memória.

Outras formas de melhorar a velocidade

Evite a limpeza de índices

Para tabelas enormes, o VACUUM passa a maior parte do tempo a limpar os índices.

O PostgreSQL 14 tem otimizações especiais para evitar a limpeza de índices se o sistema estiver em perigo de renovação cíclica.

No PostgreSQL 12 e 13, pode executar manualmente a seguinte declaração:

VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;

Nas versões 11 e anteriores, pode DROP o índice antes de executar o comando vacuum e recriá-lo mais tarde.

A eliminação do índice quando já está em execução um autovacuum nessa tabela requer o cancelamento do vacuum em execução e, em seguida, a execução imediata do comando drop index antes que o autovacuum consiga iniciar novamente o vacuum nessa tabela.

Primeiro, execute a seguinte declaração para encontrar o PID do processo autovacuum que tem de terminar:

SELECT pid, query 
  FROM pg_stat_activity
 WHERE state != 'idle'
   AND query ilike '%vacuum%';

Em seguida, execute as seguintes declarações para terminar o vácuo em execução e eliminar um ou mais índices:

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
SELECT pg_terminate_backend(<pid>);DROP INDEX <index1>;DROP INDEX <index2>; ...

Elimine a tabela ofensiva

Em alguns casos raros, pode eliminar a tabela. Por exemplo, se for uma tabela que seja fácil de restaurar a partir de outra origem, como uma cópia de segurança ou outra base de dados.

Ainda tem de usar cloudsql.enable_maintenance_mode = 'on' e, provavelmente, também terminar o VACUUM nessa tabela, conforme mostrado na secção anterior.

VACUUM FULL

Em casos raros, é mais rápido executar VACUUM FULL FREEZE, normalmente quando a tabela tem apenas uma pequena proporção de tuplos ativos. Pode verificar esta informação na vista pg_stat_user_tables (a menos que tenha ocorrido uma falha de sistema que tenha eliminado as estatísticas).

O comando VACUUM FULL copia as tuplas ativas para um novo ficheiro, pelo que tem de haver espaço suficiente disponível para o novo ficheiro e os respetivos índices.

O que se segue?