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
- Saiba mais sobre o VACUUM (em inglês).
- Saiba mais sobre o vacuum de rotina (em inglês).
- Saiba mais sobre o modo autovacuum (em inglês).
- Saiba mais sobre Como otimizar, monitorar e resolver problemas de operações VACUUM no PostgreSQL