ProHoster > Blog > Administración > Cando falla o ASPIRADOR, limpamos a mesa manualmente

Cando falla o ASPIRADOR, limpamos a mesa manualmente

VACUUM pode "limpar" dunha táboa en PostgreSQL só o que ninguén pode ver - é dicir, non hai nin unha soa solicitude activa que se iniciase antes de que se cambiaran estes rexistros.

Pero e se aínda existe un tipo tan desagradable (carga OLAP a longo prazo nunha base de datos OLTP)? Como limpar activamente a mesa de cambio rodeado de longas consultas e non pisar un rastrillo?

Cando falla o ASPIRADOR, limpamos a mesa manualmente

Poñendo o anciño

En primeiro lugar, imos determinar cal é o problema que queremos resolver e como pode xurdir.

Normalmente ocorre esta situación nunha mesa relativamente pequena, pero no que se produce moitos cambios. Normalmente isto ou diferente metros/agregados/valoraciones, no que adoita executarse UPDATE, ou cola-búfer para procesar un fluxo constante de eventos, cuxos rexistros son constantemente INSERTAR/BORRAR.

Tentemos reproducir a opción con valoracións:

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, noutra conexión, comeza unha longa, longa solicitude, recollendo algunhas estatísticas complexas, pero non afectando á nosa mesa:

SELECT pg_sleep(10000);

Agora actualizamos o valor dun dos contadores moitas, moitas veces. Para a pureza do experimento, imos facelo en transaccións separadas mediante dblinkcomo sucederá 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

Que pasou? Por que mesmo pola máis sinxela ACTUALIZACIÓN dun só rexistro tempo de execución degradado en 7 veces — de 0.524 ms a 3.808 ms? E a nosa valoración vaise construíndo cada vez máis lentamente.

Todo é culpa de MVCC.

Todo se trata Mecanismo MVCC, o que fai que a consulta busque todas as versións anteriores da entrada. Entón, imos limpar a nosa táboa de versións "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, non hai nada que limpar! Paralelo A solicitude de execución está a interferir con nós - despois de todo, pode algún día querer recorrer a estas versións (e se?), e deberían estar dispoñibles para el. E polo tanto, nin sequera VACUUM FULL nos axudará.

"Derrumbando" a mesa

Pero sabemos con certeza que esa consulta non precisa da nosa táboa. Polo tanto, aínda intentaremos devolver o rendemento do sistema aos límites adecuados eliminando da táboa todo o innecesario, polo menos "manualmente", xa que VACUUM cede.

Para que quede máis claro, vexamos o exemplo do caso dunha táboa de memoria intermedia. É dicir, hai un gran fluxo de INSERT/DELETE e ás veces a táboa está completamente baleira. Pero se non está baleiro, debemos gardar o seu contido actual.

#0: Avaliación da situación

Está claro que pode tentar facer algo coa táboa mesmo despois de cada operación, pero isto non ten moito sentido: a sobrecarga de mantemento será claramente maior que o rendemento das consultas de destino.

Formulemos os criterios: "é hora de actuar" se:

  • VACUUM lanzouse hai moito tempo
    Esperamos unha carga pesada, así que déixao estar 60 segundos dende o último [auto]VACUO.
  • o tamaño da táboa física é maior que o obxectivo
    Imos definilo como o dobre do número de páxinas (bloques de 8 KB) en relación ao tamaño mínimo - 1 blk para heap + 1 blk para cada índice - para unha táboa potencialmente baleira. Se esperamos que unha certa cantidade de datos permanecerá sempre no búfer "normalmente", é razoable modificar esta fórmula.

Solicitude de verificación

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: Aínda VACUO

Non podemos saber de antemán se unha consulta paralela está a interferir significativamente connosco - exactamente cantos rexistros quedaron "desactualizados" desde que comezou. Polo tanto, cando decidimos procesar a táboa dalgún xeito, en calquera caso, primeiro deberiamos executala VACUUM - a diferenza de VACUUM FULL, non interfire cos procesos paralelos que traballan con datos de lectura e escritura.

Ao mesmo tempo, pode limpar inmediatamente a maior parte do que queremos eliminar. Si, e as consultas posteriores nesta táboa dirixiranse a nós por "caché quente", o que reducirá a súa duración e, polo tanto, o tempo total de bloqueo doutros mediante a nosa transacción de servizo.

#2: Alguén está na casa?

Comprobamos se hai algo na táboa:

TABLE tbl LIMIT 1;

Se non queda nin un só rexistro, podemos aforrar moito no procesamento simplemente facendo TRONCAR:

Actúa do mesmo xeito que un comando DELETE incondicional para cada táboa, pero é moito máis rápido xa que en realidade non analiza as táboas. Ademais, libera inmediatamente espazo no disco, polo que non é necesario realizar unha operación VACUUM despois.

Se precisa restablecer o contador de secuencias da táboa (REINICIAR IDENTIDADE) depende de ti.

#3: Todo o mundo - tórnanse!

Dado que traballamos nunha contorna altamente competitiva, mentres estamos aquí comprobando que non hai entradas na táboa, alguén xa puido escribir algo alí. Non debemos perder esta información, entón que? É certo, temos que asegurarnos de que ninguén pode anotalo con certeza.

Para iso necesitamos habilitar SERIALIZABLE-illamento para a nosa transacción (si, aquí comezamos unha transacción) e bloquear a táboa "firmemente":

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

Este nivel de bloqueo está determinado polas operacións que queremos realizar nel.

#4: Conflito de intereses

Chegamos aquí e queremos "bloquear" o sinal: e se alguén estivese activo nese momento, por exemplo, lendo nel? "Colgaremos" á espera de que se libere este bloque, e outros que queiran ler toparanse con nós...

Para evitar que isto suceda, "sacrificarémonos": se non puidemos obter un bloqueo nun período de tempo (aceptablemente curto), recibiremos unha excepción da base, pero polo menos non interferiremos demasiado. outros.

Para iso, configure a variable de sesión lock_timeout (para versións 9.3+) ou/e declaración_tempo de espera. O principal que hai que lembrar é que o valor statement_timeout só se aplica a partir da seguinte instrución. É dicir, así en pegar - non funcionará: 

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

Para non ter que ocuparse de restaurar o valor "antigo" da variable máis tarde, utilizamos o formulario AJUSTE LOCAL, que limita o ámbito da configuración á transacción actual.

Lembramos que statement_timeout aplícase a todas as solicitudes posteriores para que a transacción non poida alcanzar valores inaceptables se hai moitos datos na táboa.

#5: Copiar datos

Se a táboa non está completamente baleira, os datos terán que ser gardados de novo usando unha táboa temporal auxiliar:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Sinatura EN CAÍDA DE COMPROMISO significa que no momento en que finalice a transacción, a táboa temporal deixará de existir e non é necesario eliminala manualmente no contexto de conexión.

Dado que asumimos que non hai moitos datos "en directo", esta operación debería realizarse con bastante rapidez.

Ben, iso é todo! Non esquezas despois de completar a transacción executar ANALIZA para normalizar as estatísticas da táboa se é necesario.

Montando o guión 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;

É posible non copiar os datos unha segunda vez?En principio, é posible se o oid da propia táboa non está ligado a ningunha outra actividade 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;

Imos executar o script na táboa de orixe e comprobar 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

Todo funcionou! A táboa reduciuse 50 veces e todas as ACTUALIZACIÓNS volven a funcionar rapidamente.

Fonte: www.habr.com

Engadir un comentario