Superação da proteção de encapsulamento do ID da transação (TXID)

Nesta página, descrevemos o que é possível fazer quando o banco de dados encontrar a proteção de wrapper de código de transações no PostgreSQL. Ele se manifesta como uma mensagem ERROR parecida com esta:

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.

Outra possibilidade é ver uma mensagem WARNING parecida com esta:

database dbname must be vacuumed within 10985967 transactions.

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

Visão geral das etapas

  • Descubra quais bancos de dados e tabelas estão causando o encapsulamento.
  • Verifique se há algo impedindo o (AUTO)VACUUM (por exemplo, um ID de transação travado).
  • Meça a velocidade do AUTOVACUUM. Se ele for lento, como opção, tente acelerá-lo.
  • Se necessário, execute mais alguns comandos VACUUM manualmente.
  • Descubra outras maneiras de acelerar o processo de vacuum. Às vezes, a maneira mais rápida é remover a tabela ou alguns índices.

Muitas das recomendações para valores de sinalizações não são exatas e isso é intencional, porque dependem de muitos parâmetros do banco de dados. Leia os documentos vinculados no fim desta página para uma análise mais detalhada sobre esse tópico.

Encontrar o banco de dados e a tabela que estão causando o encapsulamento

Como encontrar o banco de dados

Para descobrir quais bancos de dados contêm as tabelas que estão causando o encapsulamento, execute esta consulta:

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

O banco de dados com o valor remaining próximo de 0 é o que está causando o problema.

Como encontrar a tabela

Conecte-se ao banco de dados e execute esta 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;

Essa consulta retorna as tabelas que estão causando o problema.

Para tabelas TEMPORARY

Se o schema_name começar com pg_temp_, a única maneira de resolver o problema é remover a tabela, porque o PostgreSQL não permite executar o VACUUM de tabelas temporárias criadas em outras sessões. Às vezes, se a sessão está aberta e acessível, é possível executar o processo de vacuum da tabela nela, mas isso nem sempre acontece. Use as seguintes instruções SQL para remover a tabela temporária:

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

Se esse for o único bloqueador, em cerca de um minuto, o processo de autovacuum captará essa mudança e moverá o datfrozenxid para pg_database. Isso resolve o estado somente leitura da proteção de encapsulamento.

Tabelas normais

Para as tabelas normais (não temporárias), siga as próximas etapas e veja se algo está bloqueando a limpeza, se o VACUUM está funcionando rápido o suficiente e se a tabela mais importante está em processo de vacuum.

Verificar se há um código de transação travado

Um possível motivo para o sistema ficar sem códigos de transação é que o PostgreSQL não pode congelar (ou seja, marcar como visível para todas as transações) nenhum código de transação criado após a transação mais antiga em execução ter iniciado. Isso ocorre devido às regras de controle de simultaneidade de várias versões (MVCC, na sigla em inglês). Em casos extremos, é possível que essas transações se tornem tão antigas que impossibilitam que VACUUM limpe todas as transações antigas em todo o limite de encapsulamento de dois bilhões de IDs de transação e façam com que todo o sistema pare de aceitar a nova DML. Normalmente, você também vê avisos no arquivo de registros, dizendo WARNING: oldest xmin is far in the past.

Migre para a otimização somente depois que o ID da transação travada for corrigido.

Veja a seguir quatro possíveis motivos para um código de transação travado, com informações sobre como mitigar cada um deles:

  • Transações de longa duração: identifique e cancele-as ou encerre o back-end para destravar o vacuum.
  • Transações de preparação órfãs: reverta essas transações.
  • Slots de replicação abandonados: descarte os slots abandonados.
  • Transação de longa duração na réplica, com hot_standby_feedback = on: identifique e cancele-a ou encerre o back-end para destravar o vacuum.

Para esses cenários, a consulta a seguir retorna a idade da transação mais antiga e o número de transações restantes até a conclusã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;

Essa consulta pode retornar qualquer um dos valores *_left relatado perto ou abaixo de um milhão de distância do encapsulamento. Esse valor é o limite de proteção quando o PostgreSQL para de aceitar novos comandos de gravação. Nesse caso, consulte Remover bloqueadores VACUUM ou Ajustar VACUUM.

Por exemplo, a consulta anterior pode retornar:

┌─[ 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 um milhão de proteções circulares. Nesse caso, primeiro é preciso remover os bloqueadores para que o VACUUM possa prosseguir.

Remover bloqueadores VACUUM

Transações de longa duração

Na consulta acima, se oldest_running_xact for igual a oldest_prepared_xact, vá para a seção Transação de preparação órfã, porque o valor da última execução também inclui as transações de preparação.

Talvez primeiro seja necessário executar o seguinte comando como o usuário postgres: 

GRANT pg_signal_backend TO postgres;

Se a transação pertencer a qualquer um dos usuários do sistema (a partir de cloudsql...), não será possível cancelá-la diretamente. É necessário reiniciar o banco de dados para cancelá-la.

Para identificar uma consulta de longa duração e cancelá-la ou encerrá-la para desbloquear o aspirador, primeiro selecione algumas das consultas mais antigas. A linha LIMIT 10 ajuda a ajustar o resultado na tela. Pode ser necessário repetir isso 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 voltar como NULL, isso significa que a transação não recebeu um ID de transação permanente e pode ser ignorada.

Cancelar as consultas em que o xids_left_to_wraparound está próximo de 1 milhão.

Se state for active, a consulta poderá ser cancelada usando SELECT pg_cancel_backend(pid);. Caso contrário, será necessário encerrar toda a conexão usando SELECT pg_terminate_backend(pid);, em que pid é o pid da consulta anterior

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

Liste todas as transações de preparação:

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 mais órfãs usando o gid da última consulta (neste caso, trx_id_pin) como o ID da transação:

ROLLBACK PREPARED trx_id_pin;

Confirme também: 

COMMIT PREPARED trx_id_pin;

Consulte a documentação do SQL ROLLBACK PREPARED (em inglês) para ver uma explicação completa.

Slots de replicação abandonada

Caso o slot de replicação seja abandonado porque a réplica atual está interrompida, pausada ou tem algum problema para avançar, é possível excluir a réplica em gcloud ou no Console do Google Cloud.

Primeiro, verifique se a réplica não está desativada, conforme descrito em Como gerenciar réplicas de leitura. Se a réplica estiver desativada, ative-a novamente. Se o atraso permanecer alto, exclua a réplica,

Os slots de replicação estão visíveis na visualização do sistema pg_replication_slots.

A seguinte consulta recebe 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 é íntegro (age == 59). Se a idade era próxima de 2 bilhões, você quer excluir o slot. Não há uma maneira fácil de saber qual réplica é qual, caso a consulta retorne vários registros. Portanto, verifique todos eles caso haja 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-a na réplica.

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

Para avançar, pare a consulta que a retém na réplica. Para descobrir qual consulta está retendo, use a consulta em Transações de longa duração, mas, desta vez, execute-a na réplica.

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

Configurar VACUUM

Defina as duas sinalizações a seguir:

  • autovacuum_vacuum_cost_delay = 0
  • autovacuum_work_mem = 1048576

A primeira desativa qualquer limite de disco para o vacuum pelo PostgreSQL para que o VACUUM seja executado em velocidade máxima. Por padrão, o autovacuum é limitado para não usar todo o E/S de disco nos servidores mais lentos.

A segunda sinalização, autovacuum_work_mem, diminui o número de passagens de limpeza de índice. Se possível, ela precisa ser grande o suficiente para armazenar todos os IDs de linhas mortas em uma tabela que o vacuum limpará. Ao definir esse valor, considere que essa é a quantidade máxima de memória local que cada VACUUM em execução pode alocar. Verifique se você não permite mais do que o disponível, com alguns deles reservados. Se você deixar o banco de dados em execução no modo somente leitura, considere também a memória local usada para consultas somente leitura.

Na maioria dos sistemas, use o valor máximo (1 GB ou 1.048.576 KB), conforme mostrado na amostra. Isso se encaixa em cerca de 178 milhões de tuplas mortas. Qualquer outro valor faz com que vários índices sejam permitidos.

Essas e outras sinalizações são explicadas com mais detalhes em Como otimizar, monitorar e solucionar problemas de operações VACUUM no PostgreSQL.

Depois de definir essas sinalizações, reinicie o banco de dados para que o autovacuum inicie com os novos valores.

Use a visualização pg_stat_progress_vacuum para monitorar o progresso de VACUUMs iniciados automaticamente. Essa visualização mostra VACUUMs em execução em todos os bancos de dados e, nas tabelas (relações) de outros bancos de dados, não é possível pesquisar o nome da tabela usando a coluna de visualização relid.

Para identificar os bancos de dados e as tabelas que precisam ser limpos em seguida, use as consultas em Como otimizar, monitorar e solucionar problemas de operações VACUUM no PostgreSQL. Se a VM do servidor for eficiente o bastante e tiver a largura de banda para mais processos VACUUM paralelos do que a iniciada pelo autovacuum, será possível iniciar alguns aspiradores manuais.

Verificar a velocidade do VACUUM

Nesta seção, descrevemos como verificar a velocidade do VACUUM e como acelerá-lo, se necessário.

Verificar execução de autovacuum

Todos os back-ends que executam VACUUM estão visíveis na visualização do sistema pg_stat_progress_vacuum (em inglês).

Se a fase atual for scanning heap, será possível monitorar o progresso observando as alterações na coluna heap_blks_scanned. Não é fácil determinar a velocidade de verificação em outras fases.

Estimar a velocidade de leitura do VACUUM

Para estimar a velocidade da verificação, primeiro é necessário armazenar os valores base e, em seguida, calcular a alteração ao longo do tempo para estimar o tempo de conclusão. Primeiro, é preciso salvar um snapshot de heap_blks_scanned com um carimbo de data/hora usando a seguinte consulta de snapshot:

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

Como não podemos salvar nada em tabelas que já estejam envolvidas, use set_config(flag, value) para definir duas sinalizações definidas pelo usuário:save.ts e save.heap_blks_scanned - os valores atuais depg_stat_progress_vacuum.

Na próxima consulta, usamos esses dois valores como base de comparação para determinar a velocidade e estimar o tempo de conclusão.

OBSERVAÇÃO: WHERE datname = DB_NAME restringe a investigação a um banco de dados por vez. Esse número será suficiente se houver apenas um vácuo em execução neste banco de dados, com mais de uma linha por banco de dados. Condições extras de filtro ('AND relid= …'') precisam ser adicionadas a WHERE para indicar uma única linha de vácuo. Isso também é válido para a próxima consulta.

Depois de salvar os valores-base, é possível 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 │
└───────────────────┴──────────────────┘

Essa consulta compara os valores atuais com os valores-base de salvamento e calcula pages_per_second e remaining_time, o que nos permite decidir se o VACUUM está sendo executado rápido o suficiente ou se queremos aumentá-lo. O valor remaining_time é apenas para a fase scanning heap. Outras fases também levam tempo, às vezes ainda mais. É possível ler mais sobre o aspirador e ver postagens do blog na Internet discutindo alguns dos aspectos complexos do aspirador.

Acelerar VACUUM

A maneira mais fácil e rápida de acelerar a verificação de VACUUM é configurar autovacuum_vacuum_cost_delay=0. Isso pode ser feito no Console do Google Cloud.

Infelizmente, o VACUUM já em execução não seleciona esse valor, e pode ser necessário reiniciar o banco de dados.

Depois de uma reinicialização, você verá um resultado semelhante a este:

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

Nesta amostra, a velocidade aumentou de menos de 300 páginas por segundo para 6.500 páginas por segundo. O tempo restante estimado para a fase de verificação de heap diminuiu de 9 horas para 23 minutos.

A velocidade de verificação das outras fases não é tão fácil de medir, mas é possível que haja uma aceleração semelhante.

Considere também tornar o autovacuum_work_mem o maior possível para evitar vários passes de índices. Uma passagem de índice acontece sempre que a memória é preenchida com ponteiros de tupla inativas.

Se o banco de dados não estiver sendo usado de outra forma, defina autovacuum_work_mem para ter cerca de 80% de memória livre depois de permitir a quantidade necessária para shared_buffers. É o limite máximo de cada um dos processos VACUUM iniciados automaticamente. Se quiser continuar executando cargas de trabalho somente leitura, use menos memória.

Outras maneiras de aumentar a velocidade

Evitar o processo de vacuum em índices

Para tabelas grandes, o VACUUM passa a maior parte do tempo limpando índices.

O PostgreSQL 14 tem otimizações especiais para evitar a limpeza de índices quando o sistema está em risco de encapsulamento.

No PostgreSQL 12 e 13, é possível executar manualmente esta instrução:

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

Nas versões 11 e anteriores, é possível executar o DROP do índice antes de executar o processo de vacuum e recriá-lo mais tarde.

Para remover o índice quando um processo de autovacuum já está em execução nessa tabela, é preciso cancelar o processo e executar imediatamente o comando de remover o índice antes que o autovacuum seja reiniciado nessa tabela.

Primeiro, execute a instrução a seguir para encontrar o PID do processo de autovacuum a ser encerrado:

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

Em seguida, execute as instruções a seguir para encerrar o processo de vacuum e remover 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>; ...

Remover a tabela ofensiva

Em alguns casos raros, é possível remover a tabela. Por exemplo, se for uma tabela fácil de restaurar de outra fonte, como um backup ou outro banco de dados.

Ainda é preciso usar cloudsql.enable_maintenance_mode = 'on' e provavelmente também encerrar o VACUUM nessa tabela, conforme mostrado na seção anterior.

VACUUM FULL

Em casos raros, é mais rápido executar VACUUM FULL FREEZE, geralmente quando a tabela tem apenas uma pequena proporção de tuplas ativas. Isso pode ser verificado na visualização pg_stat_user_tables (a menos que tenha havido uma falha que excluiu permanentemente as estatísticas).

O comando VACUUM FULL copia tuplas ativas para um novo arquivo. Portanto, há espaço suficiente disponível para o novo arquivo e os índices dele.

A seguir