O VACUUM pode "limpar" de uma tabela no PostgreSQL apenas o que
ninguém pode ver - ou seja, não há uma única consulta ativa iniciada antes que esses registros tenham sido alterados.
Mas se houver um tipo desagradável (carga OLAP de longo prazo na base OLTP)? Como
limpar uma tabela de mudanças ativas, cercada por longas consultas e não pisar em um ancinho?
Nós espalhamos um ancinho
Primeiro, determinamos o que é e como o problema que queremos resolver pode surgir.
Geralmente, essa situação ocorre
em uma tabela relativamente pequena , mas na qual há
muitas alterações . Normalmente, esses são
contadores / agregados / classificações diferentes , nos quais o UPDATE é frequentemente executado com freqüência, ou uma
fila de buffer para processar algum tipo de fluxo de eventos em execução constante, registros sobre os quais sempre são INSERT / DELETE.
Vamos tentar reproduzir a opção com classificações:
CREATE TABLE tbl(k text PRIMARY KEY, v integer); CREATE INDEX ON tbl(v DESC);
E, paralelamente, em uma conexão diferente, uma consulta longa é iniciada, coletando algumas estatísticas complexas, mas
sem afetar nossa tabela :
SELECT pg_sleep(10000);
Agora, atualizamos o valor de um dos contadores muitas e muitas vezes. Para a pureza do experimento, faremos isso
em transações separadas usando dblink , pois isso acontecerá na realidade:
DO $$ DECLARE i integer; tsb timestamp; tse timestamp; d double precision; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port')); FOR i IN 1..10000 LOOP tsb = clock_timestamp(); PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$); tse = clock_timestamp(); IF i % 1000 = 0 THEN d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000; RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5); END IF; END LOOP; PERFORM dblink_disconnect(); END; $$ LANGUAGE plpgsql;
NOTICE: i = 1000, exectime = 0.524 NOTICE: i = 2000, exectime = 0.739 NOTICE: i = 3000, exectime = 1.188 NOTICE: i = 4000, exectime = 2.508 NOTICE: i = 5000, exectime = 1.791 NOTICE: i = 6000, exectime = 2.658 NOTICE: i = 7000, exectime = 2.318 NOTICE: i = 8000, exectime = 2.572 NOTICE: i = 9000, exectime = 2.929 NOTICE: i = 10000, exectime = 3.808
O que aconteceu? Por que, mesmo para a ATUALIZAÇÃO mais simples de um único registro
, o tempo de execução diminuiu
7 vezes - de 0,524ms para 3,808ms? E nossa classificação está sendo construída mais lentamente e mais lentamente.
MVCC é o culpado
É tudo sobre
o mecanismo MVCC , que força a solicitação a examinar todas as versões anteriores do registro. Então, vamos limpar nossa tabela das versões "mortas":
VACUUM VERBOSE tbl;
INFO: vacuuming "public.tbl" INFO: "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages DETAIL: 10000 dead row versions cannot be removed yet, oldest xmin: 597439602
Oh, não há nada para limpar! Uma
consulta paralela
está nos incomodando - afinal, um dia ele pode querer se referir a essas versões (e se?), E elas devem estar disponíveis para ele. E mesmo o VACUUM FULL não nos ajudará.
"Fixando" a mesa
Mas sabemos com certeza que nossa tabela não precisa de nossa consulta. Portanto, vamos tentar retornar o desempenho do sistema para uma estrutura adequada, tendo jogado fora tudo supérfluo da tabela - pelo menos "manualmente", já que o VACUUM passa.
Para tornar mais claro, vamos considerar um exemplo de uma tabela de buffer. Ou seja, existe um grande fluxo INSERT / DELETE e, às vezes, a tabela está completamente vazia. Mas, se não estiver vazio, precisamos
salvar o conteúdo atual .
# 0: avaliar a situação
Está claro que você pode tentar fazer algo com a tabela mesmo após cada operação, mas isso não faz muito sentido - a sobrecarga de manutenção será claramente maior que a taxa de transferência das solicitações direcionadas.
Formulamos os critérios - "é hora de agir", se:
- O VACUUM está em execução há muito tempo
Esperamos uma carga grande, portanto esperemos 60 segundos desde o último VACUUM [automático]. - tamanho da tabela física maior que o destino
Nós o definimos como o número dobrado de páginas (blocos de 8 KB) em relação ao tamanho mínimo - 1 blk por heap + 1 blk para cada um dos índices - para uma tabela potencialmente vazia. Se esperamos que uma certa quantidade de dados permaneça sempre no buffer "normalmente", é razoável reforçar essa fórmula.
Pedido de verificação SELECT relpages , (( SELECT count(*) FROM pg_index WHERE indrelid = cl.oid ) + 1) << 13 size_norm
relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 1105920 | 3392.484835
# 1: VÁCUO Enfim
Não podemos saber com antecedência se a consulta paralela está realmente nos impedindo - exatamente quantos registros estão "desatualizados" desde o seu início. Portanto, quando decidimos processar a tabela de alguma forma, em qualquer caso, você deve primeiro executar o
VACUUM nela - ao contrário do VACUUM FULL, não interfere nos processos paralelos com os dados de leitura e gravação.
Ao mesmo tempo, ele pode limpar imediatamente a maior parte do que gostaríamos de remover. Sim, e as solicitações subsequentes para esta tabela nos serão enviadas
em um "cache quente" , o que reduzirá sua duração - e, portanto, o tempo total para bloquear outras pessoas com nossa transação de veiculação.
# 2: Tem alguém em casa?
Vamos verificar - há alguma coisa na tabela:
TABLE tbl LIMIT 1;
Se não houver um único registro, podemos economizar muito no processamento - apenas executando
TRUNCATE :
Ele age da mesma maneira que o comando DELETE incondicional de cada tabela, mas muito mais rápido, pois na verdade não verifica as tabelas. Além disso, libera imediatamente espaço em disco, portanto, não é necessário executar uma operação VACUUM após ela.
Se você precisa redefinir o contador da sequência da tabela (RESTART IDENTITY) ao mesmo tempo - decida por si mesmo.
# 3: Tudo - por sua vez!
Como trabalhamos em condições de alta competitividade, enquanto verificamos aqui a ausência de entradas na tabela, alguém já pode escrever algo lá. Não devemos perder essa informação, e daí? É isso mesmo, deve ser feito para que ninguém possa gravar com certeza.
Para fazer isso, precisamos ativar o isolamento
SERIALIZABLE para nossa transação (sim, aqui iniciamos a transação) e bloquear a tabela "firmemente":
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
Esse nível de bloqueio é devido às operações que queremos executar nele.
# 4: Conflito de interesses
Chegamos aqui e queremos "trancar" o tablet - e se alguém estava ativo nele naquele momento, por exemplo, leu? Vamos “travar” em antecipação ao lançamento deste bloco, enquanto outros que desejam ler já estarão enterrados em nós ...
Para impedir que isso aconteça, "sacrifique-se" - se ainda não conseguimos a trava por um tempo (pequeno e permitido), obteremos uma exceção do banco de dados, mas pelo menos não vamos incomodar o resto.
Para fazer isso, defina a variável de sessão
lock_timeout (para versões 9.3+) ou / e
statement_timeout . O principal a lembrar é que o valor de statement_timeout se aplica somente a partir da próxima instrução. Ou seja, assim na colagem,
não vai funcionar :
SET statement_timeout = ...;LOCK TABLE ...;
Para não lidar com a restauração posterior do valor “antigo” da variável, usamos o formulário
SET LOCAL , que limita o escopo das configurações à transação atual.
Lembre-se de que statement_timeout se aplica a todos os pedidos subseqüentes para que a transação não possa se estender para valores inaceitáveis se houver muitos dados na tabela.
# 5: copiar dados
Se a tabela não estiver completamente vazia, os dados deverão ser salvos novamente através do rótulo temporário auxiliar:
CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
A assinatura
ON COMMIT DROP significa que, no momento em que a transação termina, a tabela temporária deixará de existir e você não precisa excluí-la manualmente no contexto da conexão.
Como assumimos que não há muitos dados "ativos", essa operação deve ser rápida o suficiente.
Bem, isso é tudo! Lembre
- se
de executar ANALYZE após a transação ser concluída para normalizar as estatísticas da tabela, se necessário.
Coletamos o roteiro final
Usamos um "pseudo python":
E você não pode copiar os dados uma segunda vez?Em princípio, é possível se o oid da tabela em si não estiver vinculado a outras atividades do lado BL ou FK do lado DB:
CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL); INSERT INTO _swap_%table TABLE %table; DROP TABLE %table; ALTER TABLE _swap_%table RENAME TO %table;
Vamos executar o script na tabela de origem e verificar as métricas:
VACUUM tbl; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s'; LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE; CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl; TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tmp_swap; COMMIT;
relpages | size_norm | size | vaclag ------------------------------------------- 0 | 24576 | 49152 | 32.705771
Tudo deu certo! A tabela diminuiu 50 vezes e todas as atualizações são executadas rapidamente novamente.