ProHoster > Blog > administração > Quando VACUUM falha, limpamos a mesa manualmente

Quando VACUUM falha, limpamos a mesa manualmente

VÁCUO pode “limpar” de uma tabela no PostgreSQL apenas o que ninguém pode ver - ou seja, não há uma única solicitação ativa iniciada antes da alteração desses registros.

Mas e se um tipo tão desagradável (carga OLAP de longo prazo em um banco de dados OLTP) ainda existir? Como limpe a mesa de troca ativa cercado por longas perguntas e não pisar em um rake?

Quando VACUUM falha, limpamos a mesa manualmente

Dispondo o ancinho

Primeiro, vamos determinar qual é o problema que queremos resolver e como ele pode surgir.

Geralmente esta situação acontece em uma mesa relativamente pequena, mas em que ocorre muitas mudanças. Geralmente isso ou diferente medidores/agregados/classificações, no qual UPDATE é frequentemente executado, ou fila de buffer para processar algum fluxo de eventos em constante andamento, cujos registros são constantemente 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); -- по этому индексу будем строить рейтинг

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

E paralelamente, em outra conexão, uma solicitação muito, muito longa é iniciada, coletando algumas estatísticas complexas, mas não afetando nossa mesa:

SELECT pg_sleep(10000);

Agora atualizamos o valor de um dos contadores muitas e muitas vezes. Para a pureza do experimento, vamos fazer isso em transações separadas usando dblinkcomo isso vai 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 o UPDATE mais simples de um único registro tempo de execução degradado em 7 vezes — de 0.524ms a 3.808ms? E nossa classificação está aumentando cada vez mais lentamente.

É tudo culpa do MVCC.

É tudo sobre Mecanismo MVCC, o que faz com que a consulta examine todas as versões anteriores da entrada. 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

Ah, não há nada para limpar! Paralelo A solicitação em execução está interferindo conosco - afinal, um dia ele pode querer recorrer a essas versões (e se?), e elas deveriam estar disponíveis para ele. E, portanto, mesmo VACUUM FULL não nos ajudará.

“Desmoronando” a mesa

Mas sabemos com certeza que essa consulta não precisa da nossa tabela. Portanto, ainda tentaremos devolver o desempenho do sistema aos limites adequados, eliminando da mesa tudo o que for desnecessário - pelo menos “manualmente”, já que o VACUUM desiste.

Para deixar mais claro, vejamos o exemplo do caso de uma tabela buffer. Ou seja, há um grande fluxo de INSERT/DELETE, e às vezes a tabela fica completamente vazia. Mas se não estiver vazio, devemos salve seu conteúdo atual.

Nº 0: Avaliando a situação

É 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 o rendimento das consultas alvo.

Vamos formular os critérios - “é hora de agir” se:

  • VACUUM foi lançado há muito tempo
    Esperamos uma carga pesada, então deixe estar segundo 60 desde o último [auto]VACUUM.
  • o tamanho da tabela física é maior que o alvo
    Vamos defini-lo como o dobro do número de páginas (blocos de 8 KB) em relação ao tamanho mínimo - 1 preto para heap + 1 preto para cada índice - para uma mesa potencialmente vazia. Se esperamos que uma certa quantidade de dados sempre permaneça “normalmente” no buffer, é razoável ajustar esta fórmula.

Solicitação de verificação

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- тут правильнее делать * current_setting('block_size')::bigint, но кто меняет размер блока?..
, pg_total_relation_size(oid) size
, coalesce(extract('epoch' from (now() - greatest(
    pg_stat_get_last_vacuum_time(oid)
  , pg_stat_get_last_autovacuum_time(oid)
  ))), 1 << 30) vaclag
FROM
  pg_class cl
WHERE
  oid = $1::regclass -- tbl
LIMIT 1;

relpages | size_norm | size    | vaclag
-------------------------------------------
       0 |     24576 | 1105920 | 3392.484835

#1: Ainda VÁCUO

Não podemos saber antecipadamente se uma consulta paralela está interferindo significativamente conosco - exatamente quantos registros ficaram “desatualizados” desde que começou. Portanto, quando decidimos processar a tabela de alguma forma, em qualquer caso, devemos primeiro executá-la VÁCUO - ao contrário do VACUUM FULL, não interfere nos processos paralelos que trabalham com dados de leitura e gravação.

Ao mesmo tempo, pode limpar imediatamente a maior parte do que gostaríamos de remover. Sim, e as consultas subsequentes nesta tabela irão para nós por "cache quente", o que reduzirá sua duração - e, portanto, o tempo total de bloqueio de outros por nossa transação de serviço.

#2: Tem alguém em casa?

Vamos verificar se há alguma coisa na tabela:

TABLE tbl LIMIT 1;

Se não sobrar um único registro, podemos economizar muito no processamento simplesmente fazendo TRUNCAR:

Ele atua da mesma forma que um comando DELETE incondicional para 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 há necessidade de realizar uma operação VACUUM posteriormente.

Se você precisa redefinir o contador de sequência da tabela (RESTART IDENTITY), cabe a você decidir.

#3: Todos – revezem-se!

Como trabalhamos em um ambiente altamente competitivo, enquanto estamos aqui verificando se não há entradas na tabela, alguém já pode ter escrito alguma coisa ali. Não devemos perder essas informações, e daí? Isso mesmo, precisamos ter certeza de que ninguém pode anotar com certeza.

Para fazer isso, precisamos habilitar SERIALIZÁVEL-isolamento para nossa transação (sim, aqui iniciamos uma transação) e travamos a tabela “firmemente”:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

Este nível de bloqueio é determinado pelas operações que queremos realizar nele.

Nº 4: Conflito de interesses

Viemos aqui e queremos “bloquear” a placa - e se alguém estivesse ativo nela naquele momento, por exemplo, lendo-a? Ficaremos “pendurados” esperando a liberação desse bloco, e outros que quiserem ler vão se deparar com a gente...

Para evitar que isso aconteça, iremos “nos sacrificar” - se não conseguirmos obter um bloqueio dentro de um determinado (aceitavelmente curto) tempo, receberemos uma exceção da base, mas pelo menos não interferiremos muito com outros.

Para fazer isso, defina a variável de sessão lock_timeout (para versões 9.3+) ou/e declaração_tempo limite. A principal coisa a lembrar é que o valor statement_timeout só se aplica a partir da próxima instrução. Ou seja, assim na colagem - não vai funcionar: 

SET statement_timeout = ...;LOCK TABLE ...;

Para não ter que restaurar posteriormente o valor “antigo” da variável, usamos o formulário DEFINIR LOCAL, o que limita o escopo da configuração à transação atual.

Lembramos que statement_timeout se aplica a todas as solicitações subsequentes para que a transação não possa atingir valores inaceitáveis ​​se houver muitos dados na tabela.

Nº 5: Copiar dados

Se a tabela não estiver completamente vazia, os dados deverão ser salvos novamente usando uma tabela temporária auxiliar:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Assinatura NO COMMIT QUEDA significa que no momento em que a transação terminar, a tabela temporária deixará de existir e não há necessidade de excluí-la manualmente no contexto da conexão.

Como assumimos que não existem muitos dados “ao vivo”, esta operação deve ocorrer muito rapidamente.

Bom, isso é tudo! Não se esqueça depois de concluir a transação execute ANALISAR para normalizar as estatísticas da tabela, se necessário.

Montando o roteiro final

Usamos este “pseudo-python”:

# собираем статистику с таблицы
stat <-
  SELECT
    relpages
  , ((
      SELECT
        count(*)
      FROM
        pg_index
      WHERE
        indrelid = cl.oid
    ) + 1) << 13 size_norm
  , pg_total_relation_size(oid) size
  , coalesce(extract('epoch' from (now() - greatest(
      pg_stat_get_last_vacuum_time(oid)
    , pg_stat_get_last_autovacuum_time(oid)
    ))), 1 << 30) vaclag
  FROM
    pg_class cl
  WHERE
    oid = $1::regclass -- table_name
  LIMIT 1;

# таблица больше целевого размера и VACUUM был давно
if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60:
  -> VACUUM %table;
  try:
    -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    # пытаемся захватить монопольную блокировку с предельным временем ожидания 1s
    -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
    -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE;
    # надо убедиться в пустоте таблицы внутри транзакции с блокировкой
    row <- TABLE %table LIMIT 1;
    # если в таблице нет ни одной "живой" записи - очищаем ее полностью, в противном случае - "перевставляем" все записи через временную таблицу
    if row is None:
      -> TRUNCATE TABLE %table RESTART IDENTITY;
    else:
      # создаем временную таблицу с данными таблицы-оригинала
      -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table;
      # очищаем оригинал без сброса последовательности
      -> TRUNCATE TABLE %table;
      # вставляем все сохраненные во временной таблице данные обратно
      -> INSERT INTO %table TABLE _tmp_swap;
    -> COMMIT;
  except Exception as e:
    # если мы получили ошибку, но соединение все еще "живо" - словили таймаут
    if not isinstance(e, InterfaceError):
      -> ROLLBACK;

É possível não copiar os dados uma segunda vez?Em princípio, é possível se o oid da tabela em si não estiver vinculado a nenhuma outra atividade 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

Deu tudo certo! A tabela diminuiu 50 vezes e todos os UPDATEs estão funcionando rapidamente novamente.

Fonte: habr.com

Adicionar um comentário